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

/** Verified/observed transactions (§110). Prices keep native currency + USD at historical FX (§136). */
export const sales = pgTable(
  'sales',
  {
    id: text('id').primaryKey(),
    assetId: text('asset_id').notNull(),
    variantId: text('variant_id'),
    sourceId: text('source_id').notNull(),
    connectorId: text('connector_id').notNull(),
    rawRecordId: text('raw_record_id'),
    normalizedRecordId: text('normalized_record_id'),
    sourceUrl: text('source_url').notNull(),
    externalId: text('external_id'),
    saleType: text('sale_type').notNull().default('unknown'),
    saleDate: ts('sale_date').notNull(),
    price: money('price').notNull(),
    currency: text('currency').notNull(),
    priceUsd: money('price_usd').notNull(),
    fxRate: real('fx_rate'),
    fxDate: date('fx_date'),
    buyerPremiumIncluded: boolean('buyer_premium_included'),
    quantity: integer('quantity').notNull().default(1),
    isBundle: boolean('is_bundle').notNull().default(false),
    condition: text('condition'),
    grader: text('grader'),
    grade: text('grade'),
    certificationNumber: text('certification_number'),
    location: text('location'),
    auctionHouse: text('auction_house'),
    lotNumber: text('lot_number'),
    imageUrls: jsonb('image_urls').$type<string[]>().notNull().default([]),
    rawTitle: text('raw_title').notNull(),
    confidence: ratio('confidence').notNull().default(0.8),
    dataQuality: real('data_quality').notNull().default(0),
    /** valid | flagged | excluded (never deleted, §116) */
    status: text('status').notNull().default('valid'),
    flags: textArray('flags'),
    dedupeKey: text('dedupe_key').notNull(),
    createdAt: createdAt(),
  },
  (t) => [
    uniqueIndex('sales_dedupe_uq').on(t.dedupeKey),
    index('sales_asset_date_idx').on(t.assetId, t.saleDate),
    index('sales_variant_date_idx').on(t.variantId, t.saleDate),
    index('sales_source_idx').on(t.sourceId, t.saleDate),
    index('sales_date_idx').on(t.saleDate),
    index('sales_price_idx').on(t.priceUsd),
  ],
);

/** Current and historical listings (§111). Listing price is never treated as market value. */
export const listings = pgTable(
  'listings',
  {
    id: text('id').primaryKey(),
    assetId: text('asset_id').notNull(),
    variantId: text('variant_id'),
    sourceId: text('source_id').notNull(),
    connectorId: text('connector_id').notNull(),
    rawRecordId: text('raw_record_id'),
    sourceUrl: text('source_url').notNull(),
    externalId: text('external_id').notNull(),
    listingType: text('listing_type').notNull().default('unknown'),
    price: money('price'),
    currency: text('currency'),
    priceUsd: money('price_usd'),
    seller: text('seller'),
    sellerReputation: text('seller_reputation'),
    location: text('location'),
    shippingCost: money('shipping_cost'),
    quantity: integer('quantity'),
    condition: text('condition'),
    grader: text('grader'),
    grade: text('grade'),
    certificationNumber: text('certification_number'),
    imageUrls: jsonb('image_urls').$type<string[]>().notNull().default([]),
    rawTitle: text('raw_title').notNull(),
    description: text('description'),
    listedAt: ts('listed_at'),
    endsAt: ts('ends_at'),
    availability: text('availability').notNull().default('available'),
    bidCount: integer('bid_count'),
    firstSeenAt: ts('first_seen_at').notNull(),
    lastSeenAt: ts('last_seen_at').notNull(),
    priceChangedAt: ts('price_changed_at'),
    crossListingGroupId: text('cross_listing_group_id'),
    confidence: ratio('confidence').notNull().default(0.8),
    dataQuality: real('data_quality').notNull().default(0),
    flags: textArray('flags'),
    /** value opportunity vs RIV, computed (§123) */
    discountToRiv: ratio('discount_to_riv'),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [
    uniqueIndex('listings_source_external_uq').on(t.sourceId, t.externalId),
    index('listings_asset_avail_idx').on(t.assetId, t.availability),
    index('listings_avail_price_idx').on(t.availability, t.priceUsd),
    index('listings_ends_idx').on(t.endsAt),
    index('listings_last_seen_idx').on(t.lastSeenAt),
  ],
);

export const listingEvents = pgTable(
  'listing_events',
  {
    id: text('id').primaryKey(),
    listingId: text('listing_id').notNull(),
    eventType: text('event_type').notNull(), // new | price_changed | sold | removed | relisted | auction_ended
    oldPrice: money('old_price'),
    newPrice: money('new_price'),
    currency: text('currency'),
    occurredAt: ts('occurred_at').notNull(),
  },
  (t) => [index('listing_events_listing_idx').on(t.listingId, t.occurredAt)],
);

export const auctions = pgTable(
  'auctions',
  {
    id: text('id').primaryKey(),
    sourceId: text('source_id').notNull(),
    auctionHouse: text('auction_house').notNull(),
    name: text('name').notNull(),
    url: text('url').notNull(),
    startsAt: ts('starts_at'),
    endsAt: ts('ends_at'),
    location: text('location'),
    categorySlugs: textArray('category_slugs'),
    lotCount: integer('lot_count'),
    status: text('status').notNull().default('upcoming'), // upcoming | live | ended
    currency: text('currency'),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('auctions_url_uq').on(t.url), index('auctions_ends_idx').on(t.endsAt)],
);

export const auctionLots = pgTable(
  'auction_lots',
  {
    id: text('id').primaryKey(),
    auctionId: text('auction_id').notNull(),
    assetId: text('asset_id'),
    variantId: text('variant_id'),
    sourceId: text('source_id').notNull(),
    lotNumber: text('lot_number'),
    title: text('title').notNull(),
    url: text('url').notNull(),
    estimateLow: money('estimate_low'),
    estimateHigh: money('estimate_high'),
    currentBid: money('current_bid'),
    hammerPrice: money('hammer_price'),
    currency: text('currency'),
    bidCount: integer('bid_count'),
    startsAt: ts('starts_at'),
    endsAt: ts('ends_at'),
    status: text('status').notNull().default('upcoming'),
    imageUrls: jsonb('image_urls').$type<string[]>().notNull().default([]),
    grader: text('grader'),
    grade: text('grade'),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex('auction_lots_url_uq').on(t.url), index('auction_lots_auction_idx').on(t.auctionId), index('auction_lots_asset_idx').on(t.assetId), index('auction_lots_ends_idx').on(t.endsAt)],
);

/** Price-guide observations (market/low/mid/high) — informative, weighted below transactions. */
export const priceObservations = pgTable(
  'price_observations',
  {
    id: text('id').primaryKey(),
    assetId: text('asset_id').notNull(),
    variantId: text('variant_id'),
    sourceId: text('source_id').notNull(),
    connectorId: text('connector_id').notNull(),
    rawRecordId: text('raw_record_id'),
    sourceUrl: text('source_url').notNull(),
    priceKind: text('price_kind').notNull(),
    price: money('price').notNull(),
    currency: text('currency').notNull(),
    priceUsd: money('price_usd').notNull(),
    observationDate: date('observation_date').notNull(),
    sampleSize: integer('sample_size'),
    dedupeKey: text('dedupe_key').notNull(),
    createdAt: createdAt(),
  },
  (t) => [uniqueIndex('price_observations_dedupe_uq').on(t.dedupeKey), index('price_observations_asset_date_idx').on(t.assetId, t.observationDate)],
);

export const crossListingGroups = pgTable('cross_listing_groups', {
  id: text('id').primaryKey(),
  assetId: text('asset_id'),
  signals: jsonObject<Record<string, unknown>>('signals'),
  createdAt: createdAt(),
});

/** Daily FX rates from an official dataset (ECB via frankfurter); base USD (§136). */
export const fxRates = pgTable(
  'fx_rates',
  {
    date: date('date').notNull(),
    base: text('base').notNull(),
    quote: text('quote').notNull(),
    rate: real('rate').notNull(),
    source: text('source').notNull().default('ecb'),
  },
  (t) => [uniqueIndex('fx_rates_uq').on(t.date, t.base, t.quote)],
);

export const news = pgTable(
  'news',
  {
    id: text('id').primaryKey(),
    sourceId: text('source_id').notNull(),
    url: text('url').notNull(),
    title: text('title').notNull(),
    summary: text('summary'),
    aiSummary: text('ai_summary'),
    publishedAt: ts('published_at'),
    categorySlugs: textArray('category_slugs'),
    newsType: text('news_type'), // auction_results | record_sales | grading | releases | trends | discoveries | events
    imageUrl: text('image_url'),
    fetchedAt: ts('fetched_at').notNull(),
  },
  (t) => [uniqueIndex('news_url_uq').on(t.url), index('news_published_idx').on(t.publishedAt)],
);