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

export const eventMigrations: readonly Migration[] = [
  {
    id: '2026-04-18_event_initial',
    sql: `
      CREATE TABLE IF NOT EXISTS macsync.events (
        id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        device_id         UUID NOT NULL REFERENCES macsync.devices(id) ON DELETE CASCADE,
        calendar_id       UUID REFERENCES macsync.calendars(id) ON DELETE SET NULL,
        external_id       TEXT NOT NULL,
        title             TEXT NOT NULL DEFAULT '',
        notes             TEXT,
        location          TEXT,
        url               TEXT,
        is_all_day        BOOLEAN NOT NULL DEFAULT false,
        start_date        TIMESTAMPTZ NOT NULL,
        end_date          TIMESTAMPTZ NOT NULL,
        status            TEXT NOT NULL DEFAULT 'none',
        availability      TEXT NOT NULL DEFAULT 'none',
        is_detached       BOOLEAN NOT NULL DEFAULT false,
        has_recurrence    BOOLEAN NOT NULL DEFAULT false,
        recurrence_rule   TEXT,
        organizer_email   TEXT,
        organizer_name    TEXT,
        attendee_count    INTEGER NOT NULL DEFAULT 0,
        created_at        TIMESTAMPTZ,
        modified_at       TIMESTAMPTZ,
        synced_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
        updated_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
        UNIQUE(device_id, external_id)
      );
      CREATE INDEX IF NOT EXISTS idx_events_device ON macsync.events(device_id);
      CREATE INDEX IF NOT EXISTS idx_events_calendar ON macsync.events(calendar_id);
      CREATE INDEX IF NOT EXISTS idx_events_start ON macsync.events(start_date ASC);
    `,
  },
  {
    id: '2026-04-24_event_fts',
    sql: `
      ALTER TABLE macsync.events
      ADD COLUMN IF NOT EXISTS search_tsv tsvector
      GENERATED ALWAYS AS (
        to_tsvector('english',
          coalesce(title, '') || ' ' ||
          coalesce(notes, '') || ' ' ||
          coalesce(location, '')
        )
      ) STORED;

      CREATE INDEX IF NOT EXISTS idx_events_search_tsv
      ON macsync.events USING GIN(search_tsv);
    `,
  },
];
