-- name: GetShockBrandSpecsByVehicleModelYear :many
SELECT
  *
FROM
  shock_brand_specs
WHERE
  "fk_idVehicleModelYear" = $1;

-- name: GetShockBrandSpecsById :one
SELECT * FROM shock_brand_specs WHERE id = $1 LIMIT 1;

-- name: GetShockBrandSpecsByPartNumber :one
SELECT * FROM shock_brand_specs WHERE "partNumber" = $1 LIMIT 1;

-- name: GetAllShockBrandSpecsByPartNumber :many
SELECT s.*, vb.brand AS vehicle_make, v.model AS vehicle_model, COALESCE(sb.name, '') AS brand_name
FROM shock_brand_specs s
JOIN vehicle_model_year v ON s."fk_idVehicleModelYear" = v.id
JOIN vehicle_brands vb ON v."fk_idVehicleBrand" = vb.id
LEFT JOIN shock_brands sb ON s."fk_idShockBrand" = sb.id
WHERE s."partNumber" = $1
ORDER BY vb.brand, v.model, s.years, s."yearsRange";

-- name: SearchShockBrandSpecs :many
SELECT
  s.id,
  s."partNumber",
  s.product,
  s.serie,
  s.quantity::integer AS quantity,
  s.position,
  s.tier,
  s."imageProduct",
  s."vehicleAttrs",
  s.years,
  s."yearsRange",
  v.model AS vehicle_model,
  vb.brand AS vehicle_make
FROM shock_brand_specs s
JOIN vehicle_model_year v ON s."fk_idVehicleModelYear" = v.id
JOIN vehicle_brands vb ON v."fk_idVehicleBrand" = vb.id
WHERE LOWER(vb.brand) = LOWER(sqlc.arg(make))
  AND (LOWER(v.model) = LOWER(sqlc.arg(model)) OR LOWER(v.model) LIKE LOWER(sqlc.arg(model)) || ' %')
  AND (
    sqlc.narg(year)::text IS NULL
    OR (s.years IS NOT NULL AND (
      EXISTS (
        SELECT 1 FROM unnest(string_to_array(btrim(s.years), ',')) AS p
        WHERE btrim(p) ~ '^\d{4}$' AND sqlc.narg(year)::int = btrim(p)::int
      )
      OR EXISTS (
        SELECT 1 FROM unnest(string_to_array(btrim(s.years), ',')) AS p
        WHERE btrim(p) ~ '^\d{4}\s*-\s*\d{4}$'
          AND sqlc.narg(year)::int BETWEEN
            LEAST(substring(btrim(p) from '^\d{4}')::int, substring(btrim(p) from '\d{4}$')::int)
            AND GREATEST(substring(btrim(p) from '^\d{4}')::int, substring(btrim(p) from '\d{4}$')::int)
      )
    ))
    OR (s."yearsRange" IS NOT NULL AND (
      EXISTS (
        SELECT 1 FROM unnest(string_to_array(btrim(s."yearsRange"), ',')) AS p
        WHERE btrim(p) ~ '^\d{4}$' AND sqlc.narg(year)::int = btrim(p)::int
      )
      OR EXISTS (
        SELECT 1 FROM unnest(string_to_array(btrim(s."yearsRange"), ',')) AS p
        WHERE btrim(p) ~ '^\d{4}\s*-\s*\d{4}$'
          AND sqlc.narg(year)::int BETWEEN
            LEAST(substring(btrim(p) from '^\d{4}')::int, substring(btrim(p) from '\d{4}$')::int)
            AND GREATEST(substring(btrim(p) from '^\d{4}')::int, substring(btrim(p) from '\d{4}$')::int)
      )
    ))
  );

-- name: CreateShockBrandSpec :one
INSERT INTO shock_brand_specs (
  "partNumber", "product", "serie", "quantity", "tier", "position",
  "accesories", "extendedLong", "compressedLong", "extension",
  "superiorMount", "inferiorMount", "imageProduct", "vehicleAttrs",
  "notes", "technicalSheet", "fk_idVehicleModelYear", "fk_idShockBrand", "years", "yearsRange"
)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20)
RETURNING *;

-- name: GetShockBrandSpecsByBrand :many
SELECT * FROM shock_brand_specs
WHERE "fk_idShockBrand" = $1;

-- name: ListShockBrandSpecs :many
SELECT * FROM shock_brand_specs
WHERE (sqlc.arg('search')::text = '' OR
  "partNumber" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "product" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "serie" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "tier" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "position" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "accesories" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "superiorMount" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "inferiorMount" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "imageProduct" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "vehicleAttrs" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "notes" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "technicalSheet" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "years" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "yearsRange" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "fk_idVehicleModelYear"::text ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "fk_idShockBrand"::text ILIKE '%' || sqlc.arg('search')::text || '%'
)
ORDER BY id LIMIT sqlc.arg('limit') OFFSET sqlc.arg('offset');

-- name: UpdateShockBrandSpec :one
UPDATE shock_brand_specs SET "partNumber"=$2, "product"=$3, "serie"=$4, "quantity"=$5, "tier"=$6, "position"=$7, "accesories"=$8, "extendedLong"=$9, "compressedLong"=$10, "extension"=$11, "superiorMount"=$12, "inferiorMount"=$13, "imageProduct"=$14, "vehicleAttrs"=$15, "notes"=$16, "technicalSheet"=$17, "fk_idVehicleModelYear"=$18, "fk_idShockBrand"=$19, "years"=$20, "yearsRange"=$21 WHERE id=$1 RETURNING *;

-- name: DeleteShockBrandSpec :one
DELETE FROM shock_brand_specs WHERE id=$1 RETURNING id;