import { pgTable, text, integer, boolean, index, uniqueIndex } from 'drizzle-orm/pg-core';
import { createdAt, updatedAt, jsonObject, textArray } from './_common.js';

/**
 * Extensible taxonomy (CLAUDE.md §4, §101). Rows are seeded from data/taxonomy/*.json and can be
 * extended at runtime through approved taxonomy_proposals. Never hardcode categories in app code.
 */
export const categories = pgTable(
  'categories',
  {
    slug: text('slug').primaryKey(),
    parentSlug: text('parent_slug'),
    /** top-level family slug (e.g. trading_cards) for fast grouping */
    familySlug: text('family_slug').notNull(),
    name: text('name').notNull(),
    shortName: text('short_name'),
    description: text('description'),
    level: integer('level').notNull().default(0),
    /** 1 = launch wedge, 2, 3 = later phases (§180–182) */
    phase: integer('phase').notNull().default(3),
    active: boolean('active').notNull().default(true),
    /** slug of the condition scale in data/taxonomy/conditions.json */
    conditionScale: text('condition_scale'),
    /** grader slugs relevant to this category */
    graders: textArray('graders'),
    /** subindex ticker this category rolls into (e.g. RARE-TCG) */
    indexTicker: text('index_ticker'),
    /** JSON schema fragment describing category-specific attributes */
    attributeSchema: jsonObject<Record<string, unknown>>('attribute_schema'),
    sortOrder: integer('sort_order').notNull().default(0),
    /** compliance flags: e.g. ["jurisdiction_check", "neutral_presentation"] */
    complianceFlags: textArray('compliance_flags'),
    icon: text('icon'),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [index('categories_parent_idx').on(t.parentSlug), index('categories_family_idx').on(t.familySlug)],
);

export const brands = pgTable(
  'brands',
  {
    slug: text('slug').primaryKey(),
    name: text('name').notNull(),
    categorySlugs: textArray('category_slugs'),
    country: text('country'),
    foundedYear: integer('founded_year'),
    metadata: jsonObject<Record<string, unknown>>('metadata'),
    createdAt: createdAt(),
  },
  (t) => [index('brands_name_idx').on(t.name)],
);

export const franchises = pgTable('franchises', {
  slug: text('slug').primaryKey(),
  name: text('name').notNull(),
  owner: text('owner'),
  categorySlugs: textArray('category_slugs'),
  metadata: jsonObject<Record<string, unknown>>('metadata'),
  createdAt: createdAt(),
});

/** Sets / series / releases (Base Set, Alpha, Series 1 Prizm, LEGO theme year…). */
export const sets = pgTable(
  'sets',
  {
    slug: text('slug').primaryKey(),
    name: text('name').notNull(),
    code: text('code'),
    categorySlug: text('category_slug').notNull(),
    franchiseSlug: text('franchise_slug'),
    brandSlug: text('brand_slug'),
    releaseYear: integer('release_year'),
    releaseDate: text('release_date'),
    language: text('language'),
    totalItems: integer('total_items'),
    identifiers: jsonObject<Record<string, string>>('identifiers'),
    metadata: jsonObject<Record<string, unknown>>('metadata'),
    createdAt: createdAt(),
  },
  (t) => [index('sets_category_idx').on(t.categorySlug), uniqueIndex('sets_category_code_uq').on(t.categorySlug, t.code)],
);

export const graders = pgTable('graders', {
  slug: text('slug').primaryKey(), // psa, bgs, cgc, sgc, tag, wata, vga, pcgs, ngc, cbcs, anacs, iccs
  name: text('name').notNull(),
  categorySlugs: textArray('category_slugs'),
  /** ordered scale values e.g. ["1","1.5",…,"10"] */
  scale: jsonObject<{ values: string[]; top: string; type: 'numeric' | 'sheldon' | 'descriptive' }>('scale'),
  populationUrl: text('population_url'),
  verifyUrl: text('verify_url'),
  active: boolean('active').notNull().default(true),
});

export const taxonomyProposals = pgTable('taxonomy_proposals', {
  id: text('id').primaryKey(),
  proposedSlug: text('proposed_slug').notNull(),
  name: text('name').notNull(),
  parentSlug: text('parent_slug'),
  evidence: jsonObject<Record<string, unknown>>('evidence'),
  volumeEstimate: integer('volume_estimate'),
  status: text('status').notNull().default('pending'), // pending | approved | rejected
  decidedBy: text('decided_by'),
  decidedAt: text('decided_at'),
  createdAt: createdAt(),
});