import type { Migration } from '@/shared/db';

export const searchCacheMigrations: readonly Migration[] = [
  {
    id: '2026-04-25_search_cache_table',
    sql: `
      CREATE TABLE IF NOT EXISTS macsync.search_cache (
        cache_key   TEXT PRIMARY KEY,
        hits        JSONB NOT NULL,
        total       INTEGER NOT NULL,
        fetched_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
        expires_at  TIMESTAMPTZ NOT NULL DEFAULT now() + INTERVAL '30 days'
      );

      CREATE INDEX IF NOT EXISTS idx_search_cache_expires
        ON macsync.search_cache(expires_at);
    `,
  },
  {
    id: '2026-04-25_search_cache_epoch',
    sql: `
      CREATE TABLE IF NOT EXISTS macsync.search_cache_epoch (
        id                  INTEGER PRIMARY KEY DEFAULT 1,
        last_invalidated_at TIMESTAMPTZ NOT NULL DEFAULT '1970-01-01',
        CONSTRAINT search_cache_epoch_singleton CHECK (id = 1)
      );

      INSERT INTO macsync.search_cache_epoch (id, last_invalidated_at)
      VALUES (1, '1970-01-01')
      ON CONFLICT DO NOTHING;
    `,
  },
];
