/// <reference path="../pb_data/types.d.ts" />
//
// Initial PocketBase schema for Bettersight — MVP (Analyse tier) scope only.
// Discover/Intelligence-only collections (signal_events, competitor_people,
// wayback_snapshots, tenant_custom_fields, etc.) are intentionally excluded
// per CLAUDE.md §27 "What NOT to Build" — add them in a later migration
// when those tiers are actually built.
//
// Written against PocketBase's JS migration API (v0.23+). Collections are
// created in dependency order so relation fields can reference the
// already-created target collection's id directly.

migrate((app) => {
  // ── small helpers to keep 15 collection definitions readable ──────────
  const text = (name, opts = {}) => ({ name, type: 'text', ...opts });
  const url = (name, opts = {}) => ({ name, type: 'url', ...opts });
  const num = (name, opts = {}) => ({ name, type: 'number', ...opts });
  const boolean = (name, opts = {}) => ({ name, type: 'bool', ...opts });
  const json = (name, opts = {}) => ({ name, type: 'json', ...opts });
  const date = (name, opts = {}) => ({ name, type: 'date', ...opts });
  const autodate = (name, onCreate, onUpdate = false) => ({
    name, type: 'autodate', onCreate, onUpdate,
  });
  const select = (name, values, opts = {}) => ({
    name, type: 'select', maxSelect: 1, values, ...opts,
  });
  const rel = (name, collection, opts = {}) => ({
    name, type: 'relation', collectionId: collection.id,
    cascadeDelete: false, minSelect: 0, maxSelect: 1, ...opts,
  });

  const mk = (name, fields, listRule = null, indexes = []) => {
    const collection = new Collection({
      type: 'base',
      name,
      fields,
      indexes,
      // Tenant-scoped data — API access enforced via the Flask backend's
      // X-API-Key + tenant_id filtering, not PocketBase's own auth rules.
      // Leaving rules null restricts direct API access to admin-only,
      // which is correct since only the Flask service account talks to
      // PocketBase directly.
      listRule,
      viewRule: listRule,
      createRule: null,
      updateRule: null,
      deleteRule: null,
    });
    // app.save() throws on failure and returns undefined on success (it's
    // not a boolean-returning call in PocketBase's JS VM) — returning
    // `app.save(collection) && collection` always evaluated to undefined,
    // breaking every rel() call further down that referenced an
    // earlier-created collection.
    app.save(collection);
    return collection;
  };

  // ── tenants ─────────────────────────────────────────────────────────
  const tenants = mk('tenants', [
    text('name', { required: true }),
    text('email', { required: true }),
    text('domain', { required: true }),
    select('tier', ['analyse', 'discover', 'intelligence', 'enterprise'], { required: true }),
    select('status', ['trial', 'active', 'inactive'], { required: true }),
    date('trial_ends_at'),
    num('max_seats', { required: true }),
    text('timezone'),
    text('stripe_customer_id'),
    text('stripe_subscription_id'),
    json('onboarding_checklist'),
    text('data_region'),
    autodate('created', true),
  ]);

  // ── tenant_seats ────────────────────────────────────────────────────
  mk('tenant_seats', [
    rel('tenant_id', tenants, { required: true }),
    text('email', { required: true }),
    boolean('active'),
    date('last_accessed'),
    text('added_by'),
    autodate('created', true),
  ]);

  // ── competitors ─────────────────────────────────────────────────────
  const competitors = mk('competitors', [
    rel('tenant_id', tenants, { required: true }),
    text('name', { required: true }),
    url('website', { required: true }),
    url('catalogue_url', { required: true }),
    text('primary_market', { required: true }),
    boolean('change_detected'),
    date('change_detected_at'),
    boolean('active'),
    date('last_scraped'),
    autodate('created', true),
  ]);

  // ── products ────────────────────────────────────────────────────────
  const products = mk('products', [
    rel('tenant_id', tenants, { required: true }),
    rel('competitor_id', competitors, { required: true }),
    text('trip_name'),
    url('url'),
    text('destination'),
    num('duration_days'),
    num('majority_price_usd'),
    num('price_low_usd'),
    num('price_high_usd'),
    num('aud_price'),
    num('cad_price'),
    num('eur_price'),
    num('gbp_price'),
    text('date_used'),
    text('seasonality'),
    num('group_size'),
    num('meals'),
    text('service_level'),
    text('activities'),
    text('start_location'),
    text('end_location'),
    text('target_audience'),
    text('hotels'),
    text('comments'),
    num('relevancy'),
    text('trip_code'),
    boolean('is_new'),
    autodate('first_seen', true),
    date('last_seen'),
    boolean('is_active'),
    json('embedding'),
  ]);

  // ── products_ngs_meta ───────────────────────────────────────────────
  mk('products_ngs_meta', [
    rel('product_id', products, { required: true }),
    text('exclusive_access'),
    text('group_leader'),
    text('sustainability'),
  ]);

  // ── price_history ───────────────────────────────────────────────────
  mk('price_history', [
    rel('tenant_id', tenants, { required: true }),
    rel('product_id', products, { required: true }),
    num('majority_price_usd'),
    num('price_low_usd'),
    num('price_high_usd'),
    num('aud_price'),
    num('cad_price'),
    num('eur_price'),
    num('gbp_price'),
    text('date_used'),
    text('change_field'),
    num('change_amount'),
    num('change_percent'),
    autodate('scraped_at', true),
  ]);

  // ── scrape_runs ─────────────────────────────────────────────────────
  // Note: duration_seconds is not in CLAUDE.md §6's scrape_runs schema
  // block, but §29 Component 2 (job duration alerting) explicitly writes
  // scrape_repository.update(job_id, {'duration_seconds': duration}) —
  // added here so that hook has somewhere to write.
  //
  // content_hash/content_length/hash_algorithm (CLAUDE.md §4/§10) back
  // the content-hash change-detection system that replaced Firecrawl
  // Monitor — analysis_service.run_competitor_analysis() writes a
  // dedicated per-competitor row here on every refresh scrape purely to
  // persist the hash for the next comparison.
  mk('scrape_runs', [
    rel('tenant_id', tenants, { required: true }),
    rel('competitor_id', competitors, { required: false }),
    select('triggered_by', ['cron', 'webhook', 'manual', 'sheet'], { required: true }),
    select('status', ['pending', 'running', 'complete', 'failed'], { required: true }),
    num('competitors_total'),
    num('competitors_done'),
    json('results'),
    text('error_log'),
    num('duration_seconds'),
    text('content_hash'),
    num('content_length'),
    text('hash_algorithm'),
    autodate('started_at', true),
    date('completed_at'),
  ], null, [
    // CLAUDE.md §6 documents content_hash as "indexed" — the daily
    // scrape cron calls get_last_hash_for_url(competitor_id) once per
    // competitor per run (and would be once per hour if a second daily
    // slot is ever added), always filtering by competitor_id and
    // sorting by started_at desc. This composite index covers that
    // exact access pattern rather than indexing content_hash in
    // isolation, which wouldn't help the competitor-scoped lookup.
    'CREATE INDEX idx_scrape_runs_competitor_hash ON scrape_runs (competitor_id, content_hash, started_at)',
  ]);

  // ── alerts ──────────────────────────────────────────────────────────
  mk('alerts', [
    rel('tenant_id', tenants, { required: true }),
    rel('competitor_id', competitors, { required: true }),
    rel('product_id', products, { required: false }),
    select('alert_type', ['price_change', 'new_product', 'product_removed', 'new_comparable'], { required: true }),
    text('message'),
    num('change_amount'),
    num('change_percent'),
    boolean('delivered_dashboard'),
    boolean('delivered_email'),
    boolean('delivered_slack'),
    boolean('delivered_teams'),
    boolean('delivered_whatsapp'),
    boolean('read'),
    autodate('created', true),
  ]);

  // ── battlecards ─────────────────────────────────────────────────────
  mk('battlecards', [
    rel('tenant_id', tenants, { required: true }),
    rel('competitor_id', competitors, { required: true }),
    text('content'),
    json('content_json'),
    autodate('generated_at', true),
  ]);

  // ── comparable_matches ──────────────────────────────────────────────
  mk('comparable_matches', [
    rel('tenant_id', tenants, { required: true }),
    text('client_product'),
    rel('competitor_product', products, { required: true }),
    num('similarity_score'),
    num('price_difference'),
    num('duration_difference'),
    autodate('first_matched', true),
    date('last_matched'),
    boolean('dismissed'),
  ]);

  // ── tenant_notifications ────────────────────────────────────────────
  // Slack/Teams/WhatsApp fields are schema-present per CLAUDE.md §6 but
  // unused in the Analyse-tier MVP — those integrations are Discover
  // tier only (§27). email_digest fields are the only ones written to
  // until Discover tier work begins.
  mk('tenant_notifications', [
    rel('tenant_id', tenants, { required: true }),
    text('slack_webhook_url'),
    text('teams_webhook_url'),
    text('whatsapp_number'),
    boolean('email_digest'),
    text('email_digest_time'),
    autodate('created', true),
  ]);

  // ── digest_logs ─────────────────────────────────────────────────────
  mk('digest_logs', [
    rel('tenant_id', tenants, { required: true }),
    select('digest_type', ['weekly', 'monthly'], { required: true }),
    text('pdf_path'),
    autodate('sent_at', true),
    text('recipient_email'),
    select('status', ['sent', 'failed'], { required: true }),
  ]);

  // ── proxy_overrides ─────────────────────────────────────────────────
  mk('proxy_overrides', [
    rel('tenant_id', tenants, { required: false }),
    text('domain', { required: true }),
    select('provider', ['webshare', 'brightdata'], { required: true }),
    num('blocked_count'),
    date('last_blocked'),
    autodate('created', true),
  ]);

  // ── client_trips ────────────────────────────────────────────────────
  mk('client_trips', [
    rel('tenant_id', tenants, { required: true }),
    text('trip_name'),
    text('destination'),
    num('duration_days'),
    num('price_usd'),
    text('activities'),
    json('embedding'),
    autodate('created', true),
  ]);

  // ── monitoring_events ───────────────────────────────────────────────
  // Owner-only (Gotify) monitoring history — never tenant-scoped.
  mk('monitoring_events', [
    select('event_type', [
      'queue_depth', 'job_duration', 'api_health', 'worker_health',
      'cron_duration', 'failure_rate', 'fast_path_ratio', 'weekly_summary',
    ], { required: true }),
    select('severity', ['info', 'advisory', 'warning', 'critical'], { required: true }),
    num('value'),
    num('threshold'),
    text('message'),
    boolean('alert_sent'),
    autodate('created', true),
  ]);

}, (app) => {
  // ── revert: delete in reverse dependency order ─────────────────────
  const names = [
    'monitoring_events', 'client_trips', 'proxy_overrides', 'digest_logs',
    'tenant_notifications', 'comparable_matches', 'battlecards', 'alerts',
    'scrape_runs', 'price_history', 'products_ngs_meta', 'products',
    'competitors', 'tenant_seats', 'tenants',
  ];
  for (const name of names) {
    const collection = app.findCollectionByNameOrId(name);
    if (collection) app.delete(collection);
  }
});