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

/** A data source (marketplace, auction house, price guide…) — §107, §143. */
export const sources = pgTable('sources', {
  id: text('id').primaryKey(), // slug-like, e.g. "scryfall", "psa"
  name: text('name').notNull(),
  sourceType: text('source_type').notNull(),
  url: text('url'),
  countries: textArray('countries'),
  languages: textArray('languages'),
  currencies: textArray('currencies'),
  /** 0–1 (§143) */
  trustScore: ratio('trust_score').notNull().default(0.5),
  trustFactors: jsonObject<Record<string, number>>('trust_factors'),
  attributionRequired: boolean('attribution_required').notNull().default(true),
  termsUrl: text('terms_url'),
  active: boolean('active').notNull().default(true),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
});

/** Connector registry mirror (connectors/registry.json is the source of truth for metadata; DB holds state). */
export const connectors = pgTable('connectors', {
  id: text('id').primaryKey(),
  sourceId: text('source_id').notNull(),
  displayName: text('display_name').notNull(),
  enginePriority: textArray('engine_priority'),
  categories: textArray('categories'),
  regions: textArray('regions'),
  languages: textArray('languages'),
  currency: textArray('currency'),
  supportsListings: boolean('supports_listings').notNull().default(false),
  supportsSold: boolean('supports_sold').notNull().default(false),
  supportsAuctions: boolean('supports_auctions').notNull().default(false),
  supportsImages: boolean('supports_images').notNull().default(true),
  supportsCatalog: boolean('supports_catalog').notNull().default(false),
  supportsPopulation: boolean('supports_population').notNull().default(false),
  refreshFrequencyMinutes: integer('refresh_frequency_minutes').notNull().default(1440),
  priority: text('priority').notNull().default('medium'), // high | medium | low (§141)
  status: text('status').notNull().default('active'), // active | paused | disabled
  schemaVersion: text('schema_version').notNull().default('1.0'),
  connectorVersion: text('connector_version').notNull().default('1.0.0'),
  config: jsonObject<Record<string, unknown>>('config'),
  /** full registry entry (capabilities, domain, country, acquisitionMethod, requires, refreshClass…) mirrored by the seed */
  meta: jsonObject<Record<string, unknown>>('meta'),
  lastRunAt: ts('last_run_at'),
  lastSuccessAt: ts('last_success_at'),
  nextRunAt: ts('next_run_at'),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
});

/**
 * Resumable historical backfills (SPEC §9). One row per backfill campaign; the crawler resumes from
 * last_cursor after any interruption instead of restarting from zero. `percent` is only set when the
 * connector knows the total (pages or date range).
 */
export const connectorBackfills = pgTable(
  'connector_backfills',
  {
    id: text('id').primaryKey(),
    connectorId: text('connector_id').notNull(),
    status: text('status').notNull().default('running'), // running | paused | completed | failed
    startedAt: ts('started_at').notNull(),
    updatedAt: updatedAt(),
    finishedAt: ts('finished_at'),
    /** date range the campaign targets (source-side dates) */
    backfillStartDate: date('backfill_start_date'),
    backfillEndDate: date('backfill_end_date'),
    /** oldest source date reached so far */
    reachedDate: date('reached_date'),
    pagesProcessed: integer('pages_processed').notNull().default(0),
    totalPages: integer('total_pages'),
    itemsProcessed: integer('items_processed').notNull().default(0),
    lastCursor: jsonObject<Record<string, unknown>>('last_cursor'),
    lastSuccessfulPage: integer('last_successful_page'),
    runs: integer('runs').notNull().default(0),
    errors: integer('errors').notNull().default(0),
    retryCount: integer('retry_count').notNull().default(0),
    lastError: text('last_error'),
    percent: real('percent'),
  },
  (t) => [index('connector_backfills_connector_idx').on(t.connectorId, t.status)],
);

/** Daily field-presence counters per connector for schema-drift detection (SPEC §13). */
export const connectorFieldStats = pgTable(
  'connector_field_stats',
  {
    connectorId: text('connector_id').notNull(),
    day: date('day').notNull(),
    field: text('field').notNull(),
    total: integer('total').notNull().default(0),
    nulls: integer('nulls').notNull().default(0),
  },
  (t) => [primaryKey({ columns: [t.connectorId, t.day, t.field] })],
);

/**
 * Certification numbers seen across sources (SPEC §22): one row per (grader, cert); every sale or
 * listing that carries the cert becomes a sighting → provenance graph over time.
 */
export const certificates = pgTable(
  'certificates',
  {
    id: text('id').primaryKey(),
    grader: text('grader').notNull(),
    certNumber: text('cert_number').notNull(),
    assetId: text('asset_id'),
    variantId: text('variant_id'),
    grade: text('grade'),
    qualifier: text('qualifier'),
    verifyUrl: text('verify_url'),
    firstSeenAt: ts('first_seen_at').notNull(),
    lastSeenAt: ts('last_seen_at').notNull(),
    sightings: integer('sightings').notNull().default(0),
    sourceIds: textArray('source_ids'),
    lastSourceUrl: text('last_source_url'),
    lastPriceUsd: money('last_price_usd'),
    /** verified against the grader's public lookup (cert_lookup connectors) */
    verifiedAt: ts('verified_at'),
    verification: jsonObject<Record<string, unknown>>('verification'),
    createdAt: createdAt(),
  },
  (t) => [uniqueIndex('certificates_grader_cert_uq').on(t.grader, t.certNumber), index('certificates_asset_idx').on(t.assetId), index('certificates_last_seen_idx').on(t.lastSeenAt)],
);

export const certificateSightings = pgTable(
  'certificate_sightings',
  {
    id: text('id').primaryKey(),
    certificateId: text('certificate_id').notNull(),
    kind: text('kind').notNull(), // sale | listing | auction_lot | lookup
    targetId: text('target_id'),
    sourceId: text('source_id').notNull(),
    connectorId: text('connector_id'),
    sourceUrl: text('source_url'),
    priceUsd: money('price_usd'),
    currency: text('currency'),
    price: money('price'),
    observedAt: ts('observed_at').notNull(),
    createdAt: createdAt(),
  },
  (t) => [index('certificate_sightings_cert_idx').on(t.certificateId, t.observedAt), uniqueIndex('certificate_sightings_uq').on(t.certificateId, t.kind, t.targetId)],
);

export const connectorRuns = pgTable(
  'connector_runs',
  {
    id: text('id').primaryKey(),
    connectorId: text('connector_id').notNull(),
    trigger: text('trigger').notNull().default('schedule'), // schedule | manual | backfill | repair
    startedAt: ts('started_at').notNull(),
    finishedAt: ts('finished_at'),
    status: text('status').notNull().default('running'), // running | success | partial | failed
    pagesAttempted: integer('pages_attempted').notNull().default(0),
    pagesSuccess: integer('pages_success').notNull().default(0),
    recordsRaw: integer('records_raw').notNull().default(0),
    recordsNormalized: integer('records_normalized').notNull().default(0),
    recordsDuplicate: integer('records_duplicate').notNull().default(0),
    recordsRejected: integer('records_rejected').notNull().default(0),
    /** per engine: {firecrawl:{attempts,success,credits}, scrapfly:{…}} */
    engineStats: jsonObject<Record<string, { attempts: number; success: number; credits: number; ms: number }>>('engine_stats'),
    anomalies: jsonb('anomalies').$type<string[]>().notNull().default([]),
    error: text('error'),
    costCredits: real('cost_credits').notNull().default(0),
    costUsdEst: real('cost_usd_est').notNull().default(0),
    cursor: jsonObject<Record<string, unknown>>('cursor'),
  },
  (t) => [index('connector_runs_connector_started_idx').on(t.connectorId, t.startedAt)],
);

/** Latest computed health snapshot per connector (§105). */
export const connectorHealth = pgTable('connector_health', {
  connectorId: text('connector_id').primaryKey(),
  computedAt: ts('computed_at').notNull(),
  status: text('status').notNull(),
  health: jsonObject<Record<string, unknown>>('health'),
});

/** Per-URL crawl budget state (§170). */
export const crawlState = pgTable(
  'crawl_state',
  {
    urlHash: text('url_hash').primaryKey(),
    connectorId: text('connector_id').notNull(),
    url: text('url').notNull(),
    etag: text('etag'),
    lastModified: text('last_modified'),
    contentHash: text('content_hash'),
    lastFetchedAt: ts('last_fetched_at'),
    lastChangedAt: ts('last_changed_at'),
    fetchCount: integer('fetch_count').notNull().default(0),
    changeCount: integer('change_count').notNull().default(0),
    /** estimated hours between changes */
    changeIntervalHours: real('change_interval_hours'),
    nextFetchAt: ts('next_fetch_at'),
    lastStatus: integer('last_status'),
    failures: integer('failures').notNull().default(0),
  },
  (t) => [index('crawl_state_next_idx').on(t.connectorId, t.nextFetchAt)],
);

/** Cost ledger for crawlers and AI (§169). */
export const costs = pgTable(
  'costs',
  {
    id: text('id').primaryKey(),
    occurredAt: ts('occurred_at').notNull(),
    kind: text('kind').notNull(), // firecrawl | scrapfly | ai | storage
    provider: text('provider'),
    connectorId: text('connector_id'),
    categorySlug: text('category_slug'),
    endpoint: text('endpoint'),
    userId: text('user_id'),
    units: real('units').notNull().default(1),
    credits: real('credits').notNull().default(0),
    usdEst: real('usd_est').notNull().default(0),
    metadata: jsonObject<Record<string, unknown>>('metadata'),
  },
  (t) => [index('costs_occurred_idx').on(t.occurredAt), index('costs_connector_idx').on(t.connectorId)],
);