import { pgTable, text, integer, boolean, real, index, uniqueIndex, jsonb, vector } from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';
import { createdAt, updatedAt, ts, money, ratio, jsonObject, textArray, tsvector } from './_common.js';

/**
 * Canonical asset (§109): the collectible object independent of grade/condition.
 * `canonical_key` is a deterministic normalised key (category|set|number|name|variant|language|year…)
 * used for entity resolution; `identifiers` hold deterministic external ids.
 */
export const assets = pgTable(
  'assets',
  {
    id: text('id').primaryKey(),
    slug: text('slug').notNull(),
    canonicalKey: text('canonical_key').notNull(),
    categorySlug: text('category_slug').notNull(),
    subcategorySlug: text('subcategory_slug'),
    familySlug: text('family_slug').notNull(),
    franchise: text('franchise'),
    brand: text('brand'),
    series: text('series'),
    setSlug: text('set_slug'),
    setName: text('set_name'),
    setCode: text('set_code'),
    name: text('name').notNull(),
    /** full display title, e.g. "Charizard #4/102 — Base Set 1st Edition Holo (1999)" */
    title: text('title').notNull(),
    model: text('model'),
    reference: text('reference'),
    number: text('number'),
    year: integer('year'),
    edition: text('edition'),
    variant: text('variant'),
    language: text('language'),
    region: text('region'),
    country: text('country'),
    material: text('material'),
    size: text('size'),
    color: text('color'),
    rarity: text('rarity'),
    productionQuantity: integer('production_quantity'),
    originalMsrp: money('original_msrp'),
    originalMsrpCurrency: text('original_msrp_currency'),
    releaseDate: text('release_date'),
    description: text('description'),
    heroImageUrl: text('hero_image_url'),
    identifiers: jsonObject<Record<string, string>>('identifiers'),
    metadata: jsonObject<Record<string, unknown>>('metadata'),
    /** ids of assets merged into this one (audit trail) */
    mergedFrom: textArray('merged_from'),
    /** 0–100 (§150) */
    dataQuality: real('data_quality').notNull().default(0),
    verified: boolean('verified').notNull().default(false),
    search: tsvector('search').generatedAlwaysAs(
      (): ReturnType<typeof sql> =>
        sql`setweight(to_tsvector('simple', coalesce(name, '')), 'A') || setweight(to_tsvector('simple', coalesce(set_name, '') || ' ' || coalesce(number, '') || ' ' || coalesce(variant, '') || ' ' || coalesce(edition, '')), 'B') || setweight(to_tsvector('simple', coalesce(brand, '') || ' ' || coalesce(franchise, '') || ' ' || coalesce(reference, '') || ' ' || coalesce(model, '') || ' ' || coalesce(year::text, '') || ' ' || coalesce(category_slug, '')), 'C')`,
    ),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [
    uniqueIndex('assets_canonical_key_uq').on(t.canonicalKey),
    uniqueIndex('assets_slug_uq').on(t.slug),
    index('assets_category_idx').on(t.categorySlug),
    index('assets_family_idx').on(t.familySlug),
    index('assets_set_idx').on(t.setSlug),
    index('assets_search_gin').using('gin', t.search),
    index('assets_title_trgm').using('gin', sql`${t.title} gin_trgm_ops`),
    index('assets_identifiers_gin').using('gin', t.identifiers),
  ],
);

/** Grade/condition-specific variant of an asset (PSA 10, CIB, deadstock size 10…) — §112, §117, §118. */
export const assetVariants = pgTable(
  'asset_variants',
  {
    id: text('id').primaryKey(),
    assetId: text('asset_id').notNull(),
    variantKey: text('variant_key').notNull(), // e.g. "psa|10", "raw|near_mint", "sealed", "bgs|9.5|black_label", "size|10"
    grader: text('grader'),
    grade: text('grade'),
    qualifier: text('qualifier'),
    condition: text('condition'),
    completeness: text('completeness'),
    sizeLabel: text('size_label'),
    label: text('label').notNull(), // human label "PSA 10"
    isDefault: boolean('is_default').notNull().default(false),
    createdAt: createdAt(),
  },
  (t) => [uniqueIndex('asset_variants_uq').on(t.assetId, t.variantKey), index('asset_variants_asset_idx').on(t.assetId)],
);

/** Denormalised, frequently read metrics per asset (rebuilt by valuation/indices workers). */
export const assetStats = pgTable(
  'asset_stats',
  {
    assetId: text('asset_id').primaryKey(),
    rivUsd: money('riv_usd'),
    rivLowUsd: money('riv_low_usd'),
    rivHighUsd: money('riv_high_usd'),
    rivConfidence: ratio('riv_confidence'),
    rivSampleSize: integer('riv_sample_size').notNull().default(0),
    rivVariantId: text('riv_variant_id'),
    latestSaleUsd: money('latest_sale_usd'),
    latestSaleAt: ts('latest_sale_at'),
    change1d: ratio('change_1d'),
    change7d: ratio('change_7d'),
    change30d: ratio('change_30d'),
    change90d: ratio('change_90d'),
    change1y: ratio('change_1y'),
    athUsd: money('ath_usd'),
    athAt: ts('ath_at'),
    atlUsd: money('atl_usd'),
    atlAt: ts('atl_at'),
    salesCount: integer('sales_count').notNull().default(0),
    sales30d: integer('sales_30d').notNull().default(0),
    sales1y: integer('sales_1y').notNull().default(0),
    volume30dUsd: money('volume_30d_usd'),
    activeListings: integer('active_listings').notNull().default(0),
    minAskUsd: money('min_ask_usd'),
    observationsCount: integer('observations_count').notNull().default(0),
    sourcesCount: integer('sources_count').notNull().default(0),
    liquidityScore: real('liquidity_score'),
    rarityScore: real('rarity_score'),
    momentum7d: real('momentum_7d'),
    momentum30d: real('momentum_30d'),
    momentum90d: real('momentum_90d'),
    momentum1y: real('momentum_1y'),
    trendingScore: real('trending_score'),
    valueOpportunity: real('value_opportunity'),
    dataQuality: real('data_quality'),
    watchers: integer('watchers').notNull().default(0),
    views30d: integer('views_30d').notNull().default(0),
    updatedAt: updatedAt(),
  },
  (t) => [
    index('asset_stats_riv_idx').on(t.rivUsd),
    index('asset_stats_trending_idx').on(t.trendingScore),
    index('asset_stats_liquidity_idx').on(t.liquidityScore),
    // movers, screener and opportunity rails order by these
    index('asset_stats_change_7d_idx').on(t.change7d),
    index('asset_stats_change_30d_idx').on(t.change30d),
    index('asset_stats_change_1y_idx').on(t.change1y),
    index('asset_stats_opportunity_idx').on(t.valueOpportunity).where(sql`value_opportunity is not null`),
    index('asset_stats_sales_30d_idx').on(t.sales30d),
  ],
);

export const variantStats = pgTable('variant_stats', {
  variantId: text('variant_id').primaryKey(),
  assetId: text('asset_id').notNull(),
  rivUsd: money('riv_usd'),
  rivLowUsd: money('riv_low_usd'),
  rivHighUsd: money('riv_high_usd'),
  rivConfidence: ratio('riv_confidence'),
  rivSampleSize: integer('riv_sample_size').notNull().default(0),
  latestSaleUsd: money('latest_sale_usd'),
  latestSaleAt: ts('latest_sale_at'),
  change30d: ratio('change_30d'),
  change1y: ratio('change_1y'),
  salesCount: integer('sales_count').notNull().default(0),
  sales30d: integer('sales_30d').notNull().default(0),
  activeListings: integer('active_listings').notNull().default(0),
  minAskUsd: money('min_ask_usd'),
  liquidityScore: real('liquidity_score'),
  updatedAt: updatedAt(),
}, (t) => [index('variant_stats_asset_idx').on(t.assetId)]);

export const images = pgTable(
  'images',
  {
    id: text('id').primaryKey(),
    assetId: text('asset_id'),
    listingId: text('listing_id'),
    saleId: text('sale_id'),
    sourceId: text('source_id'),
    url: text('url').notNull(),
    role: text('role').notNull().default('gallery'), // hero | gallery | source
    width: integer('width'),
    height: integer('height'),
    phash: text('phash'),
    embedding: vector('embedding', { dimensions: 512 }),
    attribution: text('attribution'),
    /** unchecked | ok | dead | blocked | error (§113 image pipeline) */
    status: text('status').notNull().default('unchecked'),
    checkedAt: ts('checked_at'),
    bytes: integer('bytes'),
    contentType: text('content_type'),
    /** sha1(url): key of the on-disk cache under RI_DATA_DIR/images */
    cacheKey: text('cache_key'),
    error: text('error'),
    createdAt: createdAt(),
  },
  (t) => [index('images_asset_idx').on(t.assetId), index('images_phash_idx').on(t.phash), uniqueIndex('images_url_uq').on(t.url), index('images_cache_key_idx').on(t.cacheKey), index('images_status_idx').on(t.status, t.checkedAt)],
);

/** Text embeddings for hybrid search / entity resolution (§112, §138). */
export const assetEmbeddings = pgTable('asset_embeddings', {
  assetId: text('asset_id').primaryKey(),
  model: text('model').notNull(),
  embedding: vector('embedding', { dimensions: 1536 }).notNull(),
  updatedAt: updatedAt(),
});

export const populationReports = pgTable(
  'population_reports',
  {
    id: text('id').primaryKey(),
    assetId: text('asset_id').notNull(),
    grader: text('grader').notNull(),
    sourceId: text('source_id').notNull(),
    sourceUrl: text('source_url'),
    reportDate: text('report_date').notNull(),
    total: integer('total').notNull(),
    byGrade: jsonb('by_grade').$type<Record<string, number>>().notNull(),
    createdAt: createdAt(),
  },
  (t) => [uniqueIndex('population_reports_uq').on(t.assetId, t.grader, t.reportDate)],
);

/** Empirical grade premiums (§118). */
export const gradePremiums = pgTable(
  'grade_premiums',
  {
    id: text('id').primaryKey(),
    categorySlug: text('category_slug').notNull(),
    grader: text('grader').notNull(),
    grade: text('grade').notNull(),
    marketMultiplier: real('market_multiplier').notNull(),
    sampleSize: integer('sample_size').notNull(),
    computedAt: ts('computed_at').notNull(),
  },
  (t) => [uniqueIndex('grade_premiums_uq').on(t.categorySlug, t.grader, t.grade)],
);