-- name: GetDiskBrandSpecsByVehicleModelYear :many
SELECT
  *
FROM
  disk_brand_specs
WHERE
  "fk_idVehicleModelYear" = $1;

-- name: GetDiskBrandSpecsById :one
SELECT * FROM disk_brand_specs WHERE id = $1 LIMIT 1;

-- name: GetDiskBrandSpecsByPartNumber :one
SELECT * FROM disk_brand_specs WHERE "partNumber" = $1 LIMIT 1;

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

-- name: SearchDiskBrandSpecs :many
SELECT
  s.id,
  s."partNumber",
  s.product,
  s.serie,
  s.quantity,
  s.position,
  s.tier,
  s."imageProduct",
  s."vehicleAttrs",
  s.years,
  s."yearsRange",
  v.model AS vehicle_model,
  vb.brand AS vehicle_make
FROM disk_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: CreateDiskBrandSpec :one
INSERT INTO disk_brand_specs (
  "partNumber", "mipPartNumber", "product", "serie", "tier", "quantity",
  "position", "description", "type", "minThikness", "lugNuts", "ogDiameter",
  "maxDiameter", "materials", "piDiameter", "height",
  "includesHardware", "imageProduct", "vehicleAttrs", "imageProductDimentions",
  "technicalSheet", "fk_idVehicleModelYear", "fk_idDiskBrand", "years", "yearsRange"
)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25)
RETURNING *;

-- name: GetDiskBrandSpecsByBrand :many
SELECT * FROM disk_brand_specs
WHERE "fk_idDiskBrand" = $1;

-- name: ListDiskBrandSpecs :many
SELECT * FROM disk_brand_specs
WHERE (
sqlc.arg('search')::text = '' OR
  "id"::text ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "partNumber" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "mipPartNumber" 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
  "quantity"::text ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "description" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "type" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "position" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "minThikness" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "lugNuts" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "ogDiameter" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "maxDiameter" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "materials" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "piDiameter" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "height" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "includesHardware" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "imageProduct" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "vehicleAttrs" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "imageProductDimentions" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "technicalSheet" ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "fk_idVehicleModelYear"::text ILIKE '%' || sqlc.arg('search')::text || '%' OR
  "fk_idDiskBrand"::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: UpdateDiskBrandSpec :one
UPDATE disk_brand_specs SET "partNumber"=$2, "mipPartNumber"=$3, "product"=$4, "serie"=$5, "tier"=$6, "quantity"=$7, "description"=$8, "type"=$9, "position"=$10, "minThikness"=$11, "lugNuts"=$12, "ogDiameter"=$13, "maxDiameter"=$14, "materials"=$15, "piDiameter"=$16, "height"=$17, "includesHardware"=$18, "imageProduct"=$19, "vehicleAttrs"=$20, "imageProductDimentions"=$21, "technicalSheet"=$22, "fk_idVehicleModelYear"=$23, "fk_idDiskBrand"=$24, "years"=$25, "yearsRange"=$26 WHERE id=$1 RETURNING *;

-- name: DeleteDiskBrandSpec :one
DELETE FROM disk_brand_specs WHERE id=$1 RETURNING id;