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

export const messageMigrations: readonly Migration[] = [
  {
    id: '2026-04-18_message_initial',
    sql: `
      CREATE TABLE IF NOT EXISTS macsync.messages (
        id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        device_id         UUID NOT NULL REFERENCES macsync.devices(id) ON DELETE CASCADE,
        conversation_id   UUID NOT NULL REFERENCES macsync.conversations(id) ON DELETE CASCADE,
        external_id       TEXT NOT NULL,
        provider          TEXT NOT NULL DEFAULT 'imessage',
        from_handle       TEXT NOT NULL DEFAULT '',
        body              TEXT NOT NULL DEFAULT '',
        is_from_me        BOOLEAN NOT NULL DEFAULT false,
        has_attachments   BOOLEAN NOT NULL DEFAULT false,
        attachment_count  INTEGER NOT NULL DEFAULT 0,
        sent_at           TIMESTAMPTZ NOT NULL,
        delivered_at      TIMESTAMPTZ,
        read_at           TIMESTAMPTZ,
        synced_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
        created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
        updated_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
        UNIQUE(device_id, external_id, provider)
      );
      CREATE INDEX IF NOT EXISTS idx_msg_conv ON macsync.messages(conversation_id);
      CREATE INDEX IF NOT EXISTS idx_msg_sent ON macsync.messages(sent_at DESC);
      CREATE INDEX IF NOT EXISTS idx_msg_device ON macsync.messages(device_id);
    `,
  },
  {
    id: '2026-04-22_message_fts_tsvector',
    sql: `
      ALTER TABLE macsync.messages
      ADD COLUMN IF NOT EXISTS body_tsv tsvector
      GENERATED ALWAYS AS (to_tsvector('english', coalesce(body, ''))) STORED;

      CREATE INDEX IF NOT EXISTS idx_messages_body_tsv
      ON macsync.messages USING GIN(body_tsv);
    `,
  },
  {
    id: '2026-04-23_message_rich_metadata',
    sql: `
      ALTER TABLE macsync.messages
        ADD COLUMN IF NOT EXISTS attributed_body          BYTEA,
        ADD COLUMN IF NOT EXISTS associated_message_type  INTEGER,
        ADD COLUMN IF NOT EXISTS associated_message_guid  TEXT,
        ADD COLUMN IF NOT EXISTS expressive_send_style_id TEXT,
        ADD COLUMN IF NOT EXISTS balloon_bundle_id        TEXT,
        ADD COLUMN IF NOT EXISTS is_audio_message         BOOLEAN NOT NULL DEFAULT false,
        ADD COLUMN IF NOT EXISTS reply_to_guid            TEXT,
        ADD COLUMN IF NOT EXISTS thread_originator_guid   TEXT,
        ADD COLUMN IF NOT EXISTS group_title              TEXT,
        ADD COLUMN IF NOT EXISTS service                  TEXT,
        ADD COLUMN IF NOT EXISTS sender_identifier        TEXT,
        ADD COLUMN IF NOT EXISTS sender_display_name      TEXT,
        ADD COLUMN IF NOT EXISTS sender_phone_number      TEXT,
        ADD COLUMN IF NOT EXISTS sender_email             TEXT;

      -- Index for reaction lookups (reactions reference their target via associated_message_guid)
      CREATE INDEX IF NOT EXISTS idx_msg_associated_guid
        ON macsync.messages(associated_message_guid)
        WHERE associated_message_guid IS NOT NULL;
      CREATE INDEX IF NOT EXISTS idx_msg_reply_guid
        ON macsync.messages(reply_to_guid)
        WHERE reply_to_guid IS NOT NULL;
      CREATE INDEX IF NOT EXISTS idx_msg_thread_origin
        ON macsync.messages(thread_originator_guid)
        WHERE thread_originator_guid IS NOT NULL;
    `,
  },
  {
    id: '2026-04-25_message_from_handle_index',
    sql: `
      CREATE INDEX IF NOT EXISTS idx_messages_from_handle_sent_at
        ON macsync.messages (from_handle, sent_at DESC)
        WHERE from_handle != '';
    `,
  },
  {
    id: '2026-04-22_message_notify_trigger',
    sql: `
      CREATE OR REPLACE FUNCTION macsync.notify_message_inserted()
      RETURNS TRIGGER AS $$
      BEGIN
        PERFORM pg_notify('message_inserted', json_build_object(
          'id', NEW.id,
          'from_handle', NEW.from_handle,
          'conversation_id', NEW.conversation_id,
          'device_id', NEW.device_id,
          'is_from_me', NEW.is_from_me,
          'sent_at', NEW.sent_at,
          'body', NEW.body
        )::text);
        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;

      DROP TRIGGER IF EXISTS trg_notify_message_inserted ON macsync.messages;
      CREATE TRIGGER trg_notify_message_inserted
      AFTER INSERT ON macsync.messages
      FOR EACH ROW
      EXECUTE FUNCTION macsync.notify_message_inserted();
    `,
  },
  {
    id: '2026-05-13_message_body_decoded_and_fts',
    sql: `
      -- Decoded plain-text shadow column populated by the typedstream
      -- decoder. Falls back to body when decoder is unavailable.
      ALTER TABLE macsync.messages
        ADD COLUMN IF NOT EXISTS body_decoded TEXT;

      -- Extend FTS to cover decoded rich content. Drop and recreate the
      -- generated column because PG cannot ALTER GENERATED expressions.
      ALTER TABLE macsync.messages DROP COLUMN IF EXISTS body_tsv;
      ALTER TABLE macsync.messages
        ADD COLUMN body_tsv tsvector
        GENERATED ALWAYS AS (
          to_tsvector('english',
            coalesce(body, '') || ' ' || coalesce(body_decoded, ''))
        ) STORED;
      CREATE INDEX IF NOT EXISTS idx_messages_body_tsv
        ON macsync.messages USING GIN(body_tsv);
    `,
  },
];
