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

/** Full valuation record with method breakdown (§115, §191). */
export const valuations = pgTable(
  'valuations',
  {
    id: text('id').primaryKey(),
    assetId: text('asset_id').notNull(),
    variantId: text('variant_id'),
    computedAt: ts('computed_at').notNull(),
    rivUsd: money('riv_usd'),
    lowUsd: money('low_usd'),
    highUsd: money('high_usd'),
    confidence: ratio('confidence').notNull().default(0),
    confidenceLabel: text('confidence_label').notNull().default('insufficient'), // high | medium | low | insufficient
    sampleSize: integer('sample_size').notNull().default(0),
    windowDays: integer('window_days').notNull().default(365),
    methods: jsonObject<Record<string, number | null>>('methods'),
    salesUsed: textArray('sales_used'),
    observationsUsed: integer('observations_used').notNull().default(0),
    method: text('method').notNull().default('ensemble_v1'),
    notes: textArray('notes'),
  },
  (t) => [index('valuations_asset_idx').on(t.assetId, t.computedAt), index('valuations_variant_idx').on(t.variantId, t.computedAt)],
);

/** Daily price series per asset/variant (repeat-sales friendly; §126 charts). */
export const priceSnapshots = pgTable(
  'price_snapshots',
  {
    assetId: text('asset_id').notNull(),
    variantId: text('variant_id').notNull().default(''),
    date: date('date').notNull(),
    rivUsd: money('riv_usd'),
    latestSaleUsd: money('latest_sale_usd'),
    medianUsd: money('median_usd'),
    salesCount: integer('sales_count').notNull().default(0),
    volumeUsd: money('volume_usd'),
    listingsCount: integer('listings_count').notNull().default(0),
    minAskUsd: money('min_ask_usd'),
    observationUsd: money('observation_usd'),
  },
  (t) => [primaryKey({ columns: [t.assetId, t.variantId, t.date] }), index('price_snapshots_date_idx').on(t.date)],
);

/** Index definitions (§3). */
export const indices = pgTable('indices', {
  id: text('id').primaryKey(),
  ticker: text('ticker').notNull().unique(),
  name: text('name').notNull(),
  description: text('description'),
  categorySlugs: textArray('category_slugs'),
  familySlugs: textArray('family_slugs'),
  parentTicker: text('parent_ticker'),
  methodology: text('methodology').notNull().default('chain_linked_equal_weight_v1'),
  weighting: text('weighting').notNull().default('equal'), // equal | liquidity | value
  baseDate: date('base_date').notNull(),
  baseValue: real('base_value').notNull().default(1000),
  minConstituents: integer('min_constituents').notNull().default(10),
  active: boolean('active').notNull().default(true),
  isFlagship: boolean('is_flagship').notNull().default(false),
  color: text('color'),
  createdAt: createdAt(),
});

export const indexValues = pgTable(
  'index_values',
  {
    indexId: text('index_id').notNull(),
    date: date('date').notNull(),
    value: real('value').notNull(),
    constituentsCount: integer('constituents_count').notNull().default(0),
    transactions: integer('transactions').notNull().default(0),
    volumeUsd: money('volume_usd'),
    medianSaleUsd: money('median_sale_usd'),
    avgSaleUsd: money('avg_sale_usd'),
    marketCapEstUsd: money('market_cap_est_usd'),
    marketCapConfidence: text('market_cap_confidence'),
    liquidityScore: real('liquidity_score'),
    momentum: real('momentum'),
    breadth: integer('breadth'),
    trackedAssets: integer('tracked_assets'),
    /** fraction of constituents with a fresh valuation that day */
    coverage: real('coverage'),
  },
  (t) => [primaryKey({ columns: [t.indexId, t.date] })],
);

export const indexConstituents = pgTable(
  'index_constituents',
  {
    indexId: text('index_id').notNull(),
    assetId: text('asset_id').notNull(),
    variantId: text('variant_id').notNull().default(''),
    weight: real('weight').notNull().default(1),
    addedAt: date('added_at').notNull(),
    removedAt: date('removed_at'),
    reason: text('reason'),
  },
  (t) => [primaryKey({ columns: [t.indexId, t.assetId, t.variantId, t.addedAt] }), index('index_constituents_asset_idx').on(t.assetId)],
);

/** Category-level daily aggregates for market pages & heatmap (§125, §151). */
export const categorySnapshots = pgTable(
  'category_snapshots',
  {
    categorySlug: text('category_slug').notNull(),
    date: date('date').notNull(),
    indexValue: real('index_value'),
    trackedAssets: integer('tracked_assets').notNull().default(0),
    assetsWithValuation: integer('assets_with_valuation').notNull().default(0),
    sales: integer('sales').notNull().default(0),
    volumeUsd: money('volume_usd'),
    medianSaleUsd: money('median_sale_usd'),
    activeListings: integer('active_listings').notNull().default(0),
    marketCapEstUsd: money('market_cap_est_usd'),
    liquidityScore: real('liquidity_score'),
    change1d: ratio('change_1d'),
    change7d: ratio('change_7d'),
    change30d: ratio('change_30d'),
    change1y: ratio('change_1y'),
  },
  (t) => [primaryKey({ columns: [t.categorySlug, t.date] })],
);

/** Rare Radar findings (§155) and record sales (§154) are derived views persisted for speed. */
export const radarFindings = pgTable(
  'radar_findings',
  {
    id: text('id').primaryKey(),
    assetId: text('asset_id').notNull(),
    kind: text('kind').notNull(), // first_listing_in_years | ultra_low_population | rare_auction_appearance | price_discrepancy | undocumented_variation | record_sale
    score: real('score').notNull(),
    evidence: jsonb('evidence').$type<Record<string, unknown>>().notNull().default({}),
    entityType: text('entity_type'),
    entityId: text('entity_id'),
    detectedAt: ts('detected_at').notNull(),
    expiresAt: ts('expires_at'),
  },
  (t) => [index('radar_kind_idx').on(t.kind, t.detectedAt), uniqueIndex('radar_entity_uq').on(t.kind, t.entityType, t.entityId)],
);

/** Correlation matrix cache (§186). */
export const correlations = pgTable(
  'correlations',
  {
    a: text('a').notNull(),
    b: text('b').notNull(),
    windowDays: integer('window_days').notNull(),
    coefficient: real('coefficient').notNull(),
    observations: integer('observations').notNull(),
    computedAt: ts('computed_at').notNull(),
  },
  (t) => [primaryKey({ columns: [t.a, t.b, t.windowDays] })],
);

/** Benchmark series (S&P 500, gold, BTC, CPI) fetched from official/free datasets (§187). */
export const benchmarks = pgTable(
  'benchmarks',
  {
    ticker: text('ticker').notNull(),
    date: date('date').notNull(),
    value: real('value').notNull(),
    source: text('source').notNull(),
  },
  (t) => [primaryKey({ columns: [t.ticker, t.date] })],
);