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

export const contactMigrations: readonly Migration[] = [
  {
    id: '2026-04-18_contact_initial',
    sql: `
      CREATE TABLE IF NOT EXISTS macsync.contacts (
        id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        device_id     UUID NOT NULL REFERENCES macsync.devices(id) ON DELETE CASCADE,
        handle        TEXT NOT NULL,
        display_name  TEXT NOT NULL DEFAULT '',
        phone_numbers JSONB NOT NULL DEFAULT '[]'::jsonb,
        emails        JSONB NOT NULL DEFAULT '[]'::jsonb,
        raw_data      JSONB NOT NULL DEFAULT '{}'::jsonb,
        synced_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
        created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
        updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
      );
      CREATE INDEX IF NOT EXISTS idx_contacts_device ON macsync.contacts(device_id);
      CREATE INDEX IF NOT EXISTS idx_contacts_handle ON macsync.contacts(handle);
    `,
  },
  {
    id: '2026-04-25_contact_summary',
    sql: `
      ALTER TABLE macsync.contacts
        ADD COLUMN IF NOT EXISTS summary_data         JSONB,
        ADD COLUMN IF NOT EXISTS summary_generated_at TIMESTAMPTZ;
    `,
  },
  {
    id: '2026-04-23_contact_dedupe_natural_key',
    sql: `
      -- Deduplicate existing rows: keep the most-recently-updated per (device_id, handle).
      DELETE FROM macsync.contacts a USING macsync.contacts b
      WHERE a.device_id = b.device_id
        AND a.handle = b.handle
        AND (a.updated_at, a.id) < (b.updated_at, b.id);

      -- Natural-key uniqueness so the upsert can ON CONFLICT(device_id, handle).
      CREATE UNIQUE INDEX IF NOT EXISTS uq_contacts_device_handle
        ON macsync.contacts(device_id, handle);
    `,
  },
];
