-- quinn_macsync database initialization
-- Raw mirror of Quinn's iCloud data (iMessage, Mail, Calendar, Contacts, Photos, Albums,
-- Reminders, Notes). Single-writer (mac-sync server), read-only for processors elsewhere.
-- Service identifier: quinn.db.pg.macsync (formerly quinn.db.pg.icloud).

CREATE SCHEMA IF NOT EXISTS macsync;

-- Migration tracking table — entity migrations record themselves here.
CREATE TABLE IF NOT EXISTS _migrations (
  id TEXT PRIMARY KEY,
  applied_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Supporting tables not owned by any single entity.
CREATE TABLE IF NOT EXISTS macsync.send_queue (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  conversation_id UUID NOT NULL,
  body TEXT,
  status TEXT DEFAULT 'pending',
  sent_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS macsync.sync_checkpoints (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  device_id UUID NOT NULL,
  module TEXT NOT NULL,
  last_checkpoint TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- All other tables (devices, contacts, conversations, messages, albums, photos, calendars,
-- events, reminders, notes, *_send_queue, search_cache, sync_history, message_embedding,
-- prospect) are created by the mac-sync server's entity migrations on first boot.