import { pgTable, text, integer, boolean, real, index, jsonb } from 'drizzle-orm/pg-core';
import { createdAt, updatedAt, ts, jsonObject, textArray, ratio } 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'),
  lastRunAt: ts('last_run_at'),
  lastSuccessAt: ts('last_success_at'),
  nextRunAt: ts('next_run_at'),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
});

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)],
);