import { sql } from "drizzle-orm";
import {
  doublePrecision,
  index,
  integer,
  jsonb,
  pgTable,
  text,
  timestamp,
  uniqueIndex,
  uuid,
} from "drizzle-orm/pg-core";
import type { NutritionDeclaration } from "@app/shared-types";
import { allergenEnum, createdAt, unitEnum, updatedAt, verificationStatusEnum } from "./_shared.js";
import { canonicalIngredients } from "./ingredients.js";

/**
 * Produktdatabas med versionshantering (spec §11): innehåll och näringsvärden
 * ändras över tid, därför är (gtin, version) unik och endast en rad är aktuell
 * (valid_to IS NULL).
 */
export const products = pgTable(
  "products",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    gtin: text("gtin"),
    name: text("name").notNull(),
    brand: text("brand"),
    canonicalIngredientId: text("canonical_ingredient_id").references(
      () => canonicalIngredients.id,
    ),
    packageSizeValue: doublePrecision("package_size_value"),
    packageSizeUnit: unitEnum("package_size_unit"),
    ingredientsText: text("ingredients_text"),
    allergens: allergenEnum("allergens").array().notNull().default([]),
    mayContainAllergens: allergenEnum("may_contain_allergens").array().notNull().default([]),
    nutrition: jsonb("nutrition").$type<NutritionDeclaration>(),
    imageUrls: text("image_urls").array().notNull().default([]),
    language: text("language").notNull().default("sv"),
    market: text("market").notNull().default("SE"),
    dataSource: text("data_source").notNull(),
    verificationStatus: verificationStatusEnum("verification_status")
      .notNull()
      .default("unverified"),
    version: integer("version").notNull().default(1),
    validFrom: timestamp("valid_from", { withTimezone: true }).notNull().defaultNow(),
    validTo: timestamp("valid_to", { withTimezone: true }),
    createdByUserId: uuid("created_by_user_id"),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [
    uniqueIndex("products_gtin_version_unique")
      .on(t.gtin, t.version)
      .where(sql`${t.gtin} IS NOT NULL`),
    index("products_gtin_current_idx")
      .on(t.gtin)
      .where(sql`${t.validTo} IS NULL`),
    index("products_canonical_idx").on(t.canonicalIngredientId),
  ],
);
