-- name: GetStrutBrandSpecsByVehicleModelYear :many
SELECT
  *
FROM
  strut_brand_specs
WHERE
  "fk_idVehicleModelYear" = $1;

-- name: GetStrutBrandSpecsById :one
SELECT * FROM strut_brand_specs WHERE id = $1 LIMIT 1;

-- name: GetStrutBrandSpecsByPartNumber :one
SELECT * FROM strut_brand_specs WHERE "partNumber" = $1 LIMIT 1;

-- name: GetAllStrutBrandSpecsByPartNumber :many
SELECT s.*, vb.brand AS vehicle_make, v.model AS vehicle_model, COALESCE(sb.name, '') AS brand_name
FROM strut_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 strut_brands sb ON s."fk_idStrutBrand" = sb.id
WHERE s."partNumber" = $1
ORDER BY vb.brand, v.model, s.years, s."yearsRange";

-- name: SearchStrutBrandSpecs :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 strut_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: CreateStrutBrandSpec :one
INSERT INTO strut_brand_specs (
  "partNumber", "product", "serie", "quantity", "tier", "position",
  "accesories", "imageProduct", "vehicleAttrs", "notes", "technicalSheet",
  "fk_idVehicleModelYear", "fk_idStrutBrand", "years", "yearsRange"
)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)
RETURNING *;

-- name: GetStrutBrandSpecsByBrand :many
SELECT * FROM strut_brand_specs
WHERE "fk_idStrutBrand" = $1;

-- name: ListStrutBrandSpecs :many
SELECT * FROM strut_brand_specs
WHERE (
sqlc.arg('search')::text = '' OR
  "id"::text ILIKE '%' || 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
  "quantity"::text 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
  "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
  "fk_idVehicleModelYear"::text ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "fk_idStrutBrand"::text ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "years" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "yearsRange" ILIKE '%' || sqlc.arg('search')::text || '%'
)
ORDER BY id LIMIT sqlc.arg('limit') OFFSET sqlc.arg('offset');

-- name: UpdateStrutBrandSpec :one
UPDATE strut_brand_specs SET "partNumber"=$2, "product"=$3, "serie"=$4, "quantity"=$5, "tier"=$6, "position"=$7, "accesories"=$8, "imageProduct"=$9, "vehicleAttrs"=$10, "notes"=$11, "technicalSheet"=$12, "fk_idVehicleModelYear"=$13, "fk_idStrutBrand"=$14, "years"=$15, "yearsRange"=$16 WHERE id=$1 RETURNING *;

-- name: DeleteStrutBrandSpec :one
DELETE FROM strut_brand_specs WHERE id=$1 RETURNING id;