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

/**
 * Logical read-state (Handoff 04). There is **no safe public macOS API** to mark
 * a Messages conversation read, and writing `is_read`/`date_read` into Apple's
 * live `chat.db` risks corruption — so macsync tracks "handled/read" in its OWN
 * store and the inbox/app reads from here. Apple's database is never touched.
 *
 * A handle is "read through" a timestamp: it is considered read iff it has no
 * inbound message newer than `read_through`. A later inbound naturally
 * re-surfaces it as unread. `read_through` only ever advances (GREATEST on
 * upsert), so re-marking is an idempotent no-op.
 */
export const conversationReadMigrations: readonly Migration[] = [
  {
    id: '2026-06-28_conversation_read_initial',
    sql: `
      CREATE TABLE IF NOT EXISTS macsync.conversation_read (
        id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        device_id     UUID NOT NULL REFERENCES macsync.devices(id) ON DELETE CASCADE,
        -- The lead's handle (phone/email), matching outbox.recipient and the
        -- inbound messages.from_handle — the consumer-facing thread identity.
        handle        TEXT NOT NULL,
        read_through  TIMESTAMPTZ NOT NULL,
        -- manual | outbox_send | all_replied — provenance for tuning/audit.
        source        TEXT NOT NULL DEFAULT 'manual',
        read_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
        created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
        updated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
        UNIQUE(device_id, handle)
      );
      CREATE INDEX IF NOT EXISTS idx_conv_read_device
        ON macsync.conversation_read(device_id);
    `,
  },
];
