/**
 * Idempotent additive DDL for i18n-content tables (spec §10).
 *
 * Donor→store collapse mapping (executable seeding lives in SP4 — documentation only).
 * fieldKeys are snake_case (FIELD_KEY_RE); donor camelCase column names are mapped on extract.
 *
 * | Donor table | entityType | fieldKeys (stored, snake_case) | provenance |
 * |---|---|---|---|
 * | deal_translations | deal | slug,title,description,special_instructions,pickup_address | per-field SourceHash/ManualOverride |
 * | category_translations | category | name | nameSourceHash, nameManualOverride |
 * | tag_translations | tag | name | nameSourceHash, nameManualOverride |
 * | vendor_translations | vendor | display_name,description | per-field hash/override |
 * | support_kb_article_translations | kb_article | slug,title,body_md | per-field manualOverride (no hash → '') |
 * | languages | — | — | direct map to languages |
 *
 * multideal: product_locale_data, deal_locale_data, deal_asset_locale_data, deal_option_locale_data
 * → translation_value (entityType=deal_asset, deal_option, etc.)
 * multideal: languages → languages (direct map)
 */
import { sql } from 'drizzle-orm'
import type { Querier, Schema } from '@platform-modules/db'

export const i18nContentMigrationSql = `
CREATE TABLE IF NOT EXISTS languages (
  code          text PRIMARY KEY,
  name_native   text,
  name_english  text,
  direction     text,
  search_config text,
  is_active     boolean NOT NULL,
  is_default    boolean NOT NULL DEFAULT false,
  sort_order    integer NOT NULL DEFAULT 0,
  created_at    timestamptz(3) NOT NULL DEFAULT NOW(),
  updated_at    timestamptz(3) NOT NULL DEFAULT NOW()
);
CREATE UNIQUE INDEX IF NOT EXISTS languages_one_default_uq
  ON languages (is_default) WHERE is_default = true;
CREATE INDEX IF NOT EXISTS languages_active_sort_idx ON languages (is_active, sort_order);
CREATE TABLE IF NOT EXISTS translation_value (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  entity_type     text NOT NULL,
  entity_id       text NOT NULL,
  field_key       text NOT NULL,
  locale          text NOT NULL REFERENCES languages(code),
  value           text NOT NULL,
  source_hash     text NOT NULL DEFAULT '',
  manual_override boolean NOT NULL DEFAULT false,
  model_id        text,
  status          text NOT NULL DEFAULT 'OK',
  translated_at   timestamptz(3),
  created_at      timestamptz(3) NOT NULL DEFAULT NOW(),
  updated_at      timestamptz(3) NOT NULL DEFAULT NOW()
);
CREATE UNIQUE INDEX IF NOT EXISTS tv_entity_field_locale_uq
  ON translation_value (entity_type, entity_id, field_key, locale);
CREATE INDEX IF NOT EXISTS tv_entity_locale_idx
  ON translation_value (entity_type, entity_id, locale);
CREATE INDEX IF NOT EXISTS tv_locale_status_idx
  ON translation_value (locale, status);
CREATE INDEX IF NOT EXISTS tv_entity_field_locale_val_idx
  ON translation_value (entity_type, field_key, locale, value) WHERE length(value) <= 512;
`.trim()

function migrationStatements(): string[] {
  return i18nContentMigrationSql
    .split(';')
    .map((statement) => statement.trim())
    .filter(Boolean)
}

export async function pushSchema<S extends Schema>(db: Querier<S>): Promise<void> {
  for (const statement of migrationStatements()) {
    await db.execute(sql.raw(statement))
  }
}