import { familyAccessMigrations } from './migrationsFamilyAccess';
import { identityMigrations } from './migrationsIdentity';
import { paymentMigrations } from './migrationsPayments';
import { paymentPolicyMigrations } from './migrationsPaymentPolicy';
import { photoPrivacyMigrations } from './migrationsPhotoPrivacy';
import { supportMigrations } from './migrationsSupport';
import { notificationMigrations } from './migrationsNotifications';
import { adminActionMigrations } from './migrationsAdminActions';
import { certificateMigrations } from './migrationsCertificates';
import { accessLogMigrations } from './migrationsAccessLogs';
import { googleAuthMigrations } from './migrationsGoogleAuth';

const baseMigrations = [
  {
    id: '001_shared_journey',
    sql: `
      ALTER TABLE users ADD COLUMN IF NOT EXISTS token_version INTEGER NOT NULL DEFAULT 0;

      CREATE TABLE IF NOT EXISTS profiles (
        user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
        display_name TEXT NOT NULL,
        city TEXT NOT NULL DEFAULT '',
        bio TEXT NOT NULL DEFAULT '',
        birth_year INTEGER,
        is_published BOOLEAN NOT NULL DEFAULT FALSE,
        version INTEGER NOT NULL DEFAULT 1,
        updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        CONSTRAINT profiles_birth_year_range CHECK (birth_year BETWEEN 1900 AND 2100)
      );

      CREATE TABLE IF NOT EXISTS engagement_requests (
        id UUID PRIMARY KEY,
        sender_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        recipient_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        message TEXT NOT NULL DEFAULT '',
        status TEXT NOT NULL DEFAULT 'pending'
          CHECK (status IN ('pending', 'accepted', 'declined', 'withdrawn')),
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        CONSTRAINT engagement_requests_distinct_users CHECK (sender_user_id <> recipient_user_id)
      );

      CREATE UNIQUE INDEX IF NOT EXISTS engagement_requests_active_pair_idx
        ON engagement_requests (
          LEAST(sender_user_id, recipient_user_id),
          GREATEST(sender_user_id, recipient_user_id)
        ) WHERE status IN ('pending', 'accepted');
      CREATE INDEX IF NOT EXISTS engagement_requests_inbox_idx
        ON engagement_requests (recipient_user_id, created_at DESC);
      CREATE INDEX IF NOT EXISTS engagement_requests_outbox_idx
        ON engagement_requests (sender_user_id, created_at DESC);

      CREATE TABLE IF NOT EXISTS journeys (
        id UUID PRIMARY KEY,
        request_id UUID NOT NULL UNIQUE REFERENCES engagement_requests(id) ON DELETE CASCADE,
        first_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        second_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'closed')),
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        CONSTRAINT journeys_distinct_users CHECK (first_user_id <> second_user_id)
      );
      CREATE INDEX IF NOT EXISTS journeys_first_user_idx ON journeys (first_user_id, created_at DESC);
      CREATE INDEX IF NOT EXISTS journeys_second_user_idx ON journeys (second_user_id, created_at DESC);
    `,
  },
  {
    id: '002_family_room_and_decisions',
    sql: `
      CREATE TABLE IF NOT EXISTS family_invitations (
        id UUID PRIMARY KEY,
        journey_id UUID NOT NULL REFERENCES journeys(id) ON DELETE CASCADE,
        invited_by UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        code_hash TEXT NOT NULL UNIQUE,
        role_label TEXT NOT NULL,
        permission TEXT NOT NULL CHECK (permission IN ('viewer', 'contributor')),
        status TEXT NOT NULL DEFAULT 'pending'
          CHECK (status IN ('pending', 'accepted', 'revoked')),
        expires_at TIMESTAMPTZ NOT NULL,
        accepted_by UUID REFERENCES users(id) ON DELETE SET NULL,
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        accepted_at TIMESTAMPTZ
      );
      CREATE INDEX IF NOT EXISTS family_invitations_journey_idx
        ON family_invitations (journey_id, created_at DESC);

      CREATE TABLE IF NOT EXISTS journey_memberships (
        journey_id UUID NOT NULL REFERENCES journeys(id) ON DELETE CASCADE,
        user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        invitation_id UUID NOT NULL UNIQUE REFERENCES family_invitations(id) ON DELETE CASCADE,
        role_label TEXT NOT NULL,
        permission TEXT NOT NULL CHECK (permission IN ('viewer', 'contributor')),
        is_active BOOLEAN NOT NULL DEFAULT TRUE,
        joined_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        PRIMARY KEY (journey_id, user_id)
      );

      CREATE TABLE IF NOT EXISTS journey_messages (
        id UUID PRIMARY KEY,
        journey_id UUID NOT NULL REFERENCES journeys(id) ON DELETE CASCADE,
        author_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        client_nonce UUID NOT NULL,
        body TEXT NOT NULL CHECK (length(body) BETWEEN 1 AND 2000),
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        UNIQUE (journey_id, author_user_id, client_nonce)
      );
      CREATE INDEX IF NOT EXISTS journey_messages_order_idx
        ON journey_messages (journey_id, created_at DESC, id DESC);

      CREATE TABLE IF NOT EXISTS meeting_proposals (
        id UUID PRIMARY KEY,
        journey_id UUID NOT NULL REFERENCES journeys(id) ON DELETE CASCADE,
        proposed_by UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        starts_at TIMESTAMPTZ NOT NULL,
        time_zone TEXT NOT NULL,
        status TEXT NOT NULL DEFAULT 'proposed'
          CHECK (status IN ('proposed', 'confirmed', 'completed', 'cancelled')),
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
      );
      CREATE UNIQUE INDEX IF NOT EXISTS meeting_proposals_active_idx
        ON meeting_proposals (journey_id)
        WHERE status IN ('proposed', 'confirmed');

      CREATE TABLE IF NOT EXISTS meeting_attendance (
        meeting_id UUID NOT NULL REFERENCES meeting_proposals(id) ON DELETE CASCADE,
        user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        confirmed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        PRIMARY KEY (meeting_id, user_id)
      );

      CREATE TABLE IF NOT EXISTS decision_rounds (
        id UUID PRIMARY KEY,
        journey_id UUID NOT NULL REFERENCES journeys(id) ON DELETE CASCADE,
        meeting_id UUID NOT NULL UNIQUE REFERENCES meeting_proposals(id) ON DELETE CASCADE,
        status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'resolved')),
        outcome TEXT CHECK (outcome IN ('continue', 'second_session', 'more_time', 'closed')),
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        resolved_at TIMESTAMPTZ
      );
      CREATE UNIQUE INDEX IF NOT EXISTS decision_rounds_open_idx
        ON decision_rounds (journey_id) WHERE status='open';

      CREATE TABLE IF NOT EXISTS participant_decisions (
        round_id UUID NOT NULL REFERENCES decision_rounds(id) ON DELETE CASCADE,
        user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        choice TEXT NOT NULL CHECK (choice IN ('continue', 'second_session', 'extend_time', 'apologize')),
        private_notes TEXT NOT NULL DEFAULT '',
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        PRIMARY KEY (round_id, user_id)
      );
    `,
  },
  {
    id: '003_mutual_contact_consent',
    sql: `
      CREATE TABLE IF NOT EXISTS journey_contact_grants (
        journey_id UUID NOT NULL REFERENCES journeys(id) ON DELETE CASCADE,
        user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        field TEXT NOT NULL CHECK (field IN ('email', 'phone')),
        value TEXT NOT NULL CHECK (length(value) BETWEEN 3 AND 160),
        granted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        PRIMARY KEY (journey_id, user_id, field)
      );
    `,
  },
  {
    id: '004_safety_reports',
    sql: `
      CREATE TABLE IF NOT EXISTS safety_reports (
        id UUID PRIMARY KEY,
        journey_id UUID NOT NULL REFERENCES journeys(id) ON DELETE CASCADE,
        reporter_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        category TEXT NOT NULL CHECK (category IN ('conduct', 'privacy', 'impersonation', 'other')),
        description TEXT NOT NULL CHECK (length(description) BETWEEN 20 AND 2000),
        status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'reviewing', 'resolved', 'dismissed')),
        admin_note TEXT NOT NULL DEFAULT '',
        reviewed_by UUID REFERENCES users(id) ON DELETE SET NULL,
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
      );
      CREATE INDEX IF NOT EXISTS safety_reports_status_idx ON safety_reports (status, created_at DESC);
      CREATE INDEX IF NOT EXISTS safety_reports_reporter_idx ON safety_reports (reporter_user_id, created_at DESC);
    `,
  },
  {
    id: '005_match_profile',
    sql: `
      ALTER TABLE profiles ADD COLUMN IF NOT EXISTS gender TEXT;
      ALTER TABLE profiles ADD COLUMN IF NOT EXISTS country TEXT NOT NULL DEFAULT '';
      ALTER TABLE profiles ADD COLUMN IF NOT EXISTS marital_status TEXT;
      ALTER TABLE profiles ADD COLUMN IF NOT EXISTS has_children BOOLEAN;
      ALTER TABLE profiles ADD COLUMN IF NOT EXISTS education TEXT;
      ALTER TABLE profiles ADD COLUMN IF NOT EXISTS occupation TEXT NOT NULL DEFAULT '';
      ALTER TABLE profiles ADD COLUMN IF NOT EXISTS values_text TEXT NOT NULL DEFAULT '';
      ALTER TABLE profiles ADD COLUMN IF NOT EXISTS partner_description TEXT NOT NULL DEFAULT '';
      ALTER TABLE profiles ADD COLUMN IF NOT EXISTS preferred_gender TEXT;
      ALTER TABLE profiles ADD COLUMN IF NOT EXISTS preferred_min_age INTEGER;
      ALTER TABLE profiles ADD COLUMN IF NOT EXISTS preferred_max_age INTEGER;
      ALTER TABLE profiles ADD COLUMN IF NOT EXISTS preferred_country TEXT;
      ALTER TABLE profiles ADD COLUMN IF NOT EXISTS preferred_marital_status TEXT;
      ALTER TABLE profiles ADD COLUMN IF NOT EXISTS preferred_education TEXT;
      ALTER TABLE profiles ADD COLUMN IF NOT EXISTS accepts_partner_children BOOLEAN;
      ALTER TABLE profiles ADD COLUMN IF NOT EXISTS is_complete BOOLEAN NOT NULL DEFAULT FALSE;
      CREATE INDEX IF NOT EXISTS profiles_discovery_idx
        ON profiles (is_published, is_complete, updated_at DESC)
        WHERE is_published AND is_complete;
    `,
  },
  {
    id: '006_profile_review_and_appearance',
    sql: `
      ALTER TABLE profiles ADD COLUMN IF NOT EXISTS birth_place TEXT NOT NULL DEFAULT '';
      ALTER TABLE profiles ADD COLUMN IF NOT EXISTS children_count INTEGER;
      ALTER TABLE profiles ADD COLUMN IF NOT EXISTS skin_tone TEXT;
      ALTER TABLE profiles ADD COLUMN IF NOT EXISTS eye_color TEXT;
      ALTER TABLE profiles ADD COLUMN IF NOT EXISTS hair_color TEXT;
      ALTER TABLE profiles ADD COLUMN IF NOT EXISTS height_cm INTEGER;
      ALTER TABLE profiles ADD COLUMN IF NOT EXISTS weight_kg INTEGER;
      ALTER TABLE profiles ADD COLUMN IF NOT EXISTS body_build TEXT;
      ALTER TABLE profiles ADD COLUMN IF NOT EXISTS appearance_note TEXT NOT NULL DEFAULT '';
      ALTER TABLE profiles ADD COLUMN IF NOT EXISTS publication_requested BOOLEAN NOT NULL DEFAULT FALSE;
      ALTER TABLE profiles ADD COLUMN IF NOT EXISTS approval_status TEXT NOT NULL DEFAULT 'pending';
      ALTER TABLE profiles ADD COLUMN IF NOT EXISTS reviewed_by UUID REFERENCES users(id) ON DELETE SET NULL;
      ALTER TABLE profiles ADD COLUMN IF NOT EXISTS reviewed_at TIMESTAMPTZ;
      ALTER TABLE profiles ADD COLUMN IF NOT EXISTS review_note TEXT NOT NULL DEFAULT '';
      UPDATE profiles
      SET publication_requested = is_published,
          is_published = FALSE,
          is_complete = FALSE,
          marital_status = CASE WHEN marital_status = 'other' THEN NULL ELSE marital_status END;
      ALTER TABLE profiles ADD CONSTRAINT profiles_children_count_range
        CHECK (children_count BETWEEN 0 AND 5);
      ALTER TABLE profiles ADD CONSTRAINT profiles_height_range
        CHECK (height_cm BETWEEN 120 AND 230);
      ALTER TABLE profiles ADD CONSTRAINT profiles_weight_range
        CHECK (weight_kg BETWEEN 30 AND 300);
      ALTER TABLE profiles ADD CONSTRAINT profiles_approval_status_value
        CHECK (approval_status IN ('pending', 'approved', 'rejected'));
      CREATE INDEX IF NOT EXISTS profiles_review_queue_idx
        ON profiles (updated_at DESC) WHERE publication_requested AND approval_status = 'pending';
    `,
  },
  {
    id: '007_introductions_and_blocks',
    sql: `
      ALTER TABLE engagement_requests ADD COLUMN IF NOT EXISTS intro_reason TEXT NOT NULL DEFAULT '';
      ALTER TABLE engagement_requests ADD COLUMN IF NOT EXISTS family_view TEXT NOT NULL DEFAULT '';
      ALTER TABLE engagement_requests ADD COLUMN IF NOT EXISTS meeting_goal TEXT NOT NULL DEFAULT '';
      CREATE TABLE IF NOT EXISTS user_blocks (
        blocker_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        blocked_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        PRIMARY KEY (blocker_user_id, blocked_user_id),
        CONSTRAINT user_blocks_distinct_users CHECK (blocker_user_id <> blocked_user_id)
      );
      CREATE INDEX IF NOT EXISTS user_blocks_target_idx ON user_blocks (blocked_user_id);
    `,
  },
  {
    id: '008_binary_gender',
    sql: `
      DO $migration$
      BEGIN
        IF EXISTS (
          SELECT 1 FROM profiles
          WHERE gender = 'other' OR preferred_gender IN ('other', 'any')
        ) THEN
          RAISE EXCEPTION 'Existing profiles use unsupported gender values; review them before migration';
        END IF;
      END
      $migration$;
      ALTER TABLE profiles ADD CONSTRAINT profiles_gender_binary
        CHECK (gender IS NULL OR gender IN ('man', 'woman'));
      ALTER TABLE profiles ADD CONSTRAINT profiles_preferred_gender_binary
        CHECK (preferred_gender IS NULL OR preferred_gender IN ('man', 'woman'));
    `,
  },
  {
    id: '009_profile_corrections_and_family_calls',
    sql: `
      CREATE TABLE profile_corrections (
        id UUID PRIMARY KEY,
        user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        profile_version INTEGER NOT NULL,
        field TEXT NOT NULL,
        proposed_value JSONB NOT NULL,
        reason TEXT NOT NULL CHECK (length(reason) BETWEEN 20 AND 1000),
        status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected')),
        admin_note TEXT NOT NULL DEFAULT '',
        reviewed_by UUID REFERENCES users(id) ON DELETE SET NULL,
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        reviewed_at TIMESTAMPTZ
      );
      CREATE UNIQUE INDEX profile_corrections_one_pending_idx
        ON profile_corrections (user_id) WHERE status='pending';
      CREATE INDEX profile_corrections_queue_idx
        ON profile_corrections (status, created_at DESC);

      CREATE TABLE journey_call_guardians (
        journey_id UUID NOT NULL REFERENCES journeys(id) ON DELETE CASCADE,
        participant_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        guardian_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        PRIMARY KEY (journey_id, participant_user_id),
        UNIQUE (journey_id, guardian_user_id),
        CHECK (participant_user_id <> guardian_user_id)
      );
      CREATE TABLE meeting_call_sessions (
        session_id UUID PRIMARY KEY,
        meeting_id UUID NOT NULL REFERENCES meeting_proposals(id) ON DELETE CASCADE,
        user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        joined_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        left_at TIMESTAMPTZ
      );
      CREATE UNIQUE INDEX meeting_call_active_user_idx
        ON meeting_call_sessions (meeting_id, user_id) WHERE left_at IS NULL;
      CREATE INDEX meeting_call_presence_idx
        ON meeting_call_sessions (meeting_id, last_seen_at DESC);
      CREATE TABLE meeting_call_signals (
        id BIGSERIAL PRIMARY KEY,
        meeting_id UUID NOT NULL REFERENCES meeting_proposals(id) ON DELETE CASCADE,
        sender_session_id UUID NOT NULL REFERENCES meeting_call_sessions(session_id) ON DELETE CASCADE,
        recipient_session_id UUID NOT NULL REFERENCES meeting_call_sessions(session_id) ON DELETE CASCADE,
        signal_type TEXT NOT NULL CHECK (signal_type IN ('offer', 'answer', 'ice')),
        payload JSONB NOT NULL,
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
      );
      CREATE INDEX meeting_call_signals_recipient_idx
        ON meeting_call_signals (recipient_session_id, id);
      ALTER TABLE meeting_proposals ADD COLUMN video_required BOOLEAN NOT NULL DEFAULT FALSE;
    `,
  },
  {
    id: '010_family_call_media_links',
    sql: `
      CREATE TABLE meeting_call_links (
        meeting_id UUID NOT NULL REFERENCES meeting_proposals(id) ON DELETE CASCADE,
        lower_session_id UUID NOT NULL REFERENCES meeting_call_sessions(session_id) ON DELETE CASCADE,
        upper_session_id UUID NOT NULL REFERENCES meeting_call_sessions(session_id) ON DELETE CASCADE,
        lower_confirmed BOOLEAN NOT NULL DEFAULT FALSE,
        upper_confirmed BOOLEAN NOT NULL DEFAULT FALSE,
        updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        PRIMARY KEY (lower_session_id, upper_session_id),
        CHECK (lower_session_id <> upper_session_id)
      );
      CREATE INDEX meeting_call_links_meeting_idx ON meeting_call_links (meeting_id);
    `,
  },
  {
    id: '011_guardian_verification',
    sql: `
      ALTER TABLE users ADD COLUMN username TEXT;
      ALTER TABLE users ADD COLUMN account_type TEXT NOT NULL DEFAULT 'candidate';
      ALTER TABLE users ADD CONSTRAINT users_account_type_value
        CHECK (account_type IN ('candidate', 'guardian'));
      CREATE UNIQUE INDEX users_username_unique_idx
        ON users (LOWER(username)) WHERE username IS NOT NULL;

      CREATE TABLE guardian_invitations (
        id UUID PRIMARY KEY,
        candidate_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        code_hash TEXT NOT NULL UNIQUE,
        status TEXT NOT NULL DEFAULT 'pending'
          CHECK (status IN ('pending', 'registered', 'verified', 'failed', 'revoked')),
        accepted_by UUID REFERENCES users(id) ON DELETE SET NULL,
        common_contact_count INTEGER,
        candidate_synced_at TIMESTAMPTZ,
        guardian_synced_at TIMESTAMPTZ,
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        expires_at TIMESTAMPTZ NOT NULL,
        completed_at TIMESTAMPTZ,
        CHECK (common_contact_count IS NULL OR common_contact_count >= 0)
      );
      CREATE INDEX guardian_invitations_candidate_idx
        ON guardian_invitations (candidate_user_id, created_at DESC);
      CREATE INDEX guardian_invitations_guardian_idx
        ON guardian_invitations (accepted_by, created_at DESC);
      CREATE UNIQUE INDEX guardian_invitations_one_open_idx
        ON guardian_invitations (candidate_user_id)
        WHERE status IN ('pending', 'registered', 'verified');

      CREATE TABLE guardian_contact_hashes (
        invitation_id UUID NOT NULL REFERENCES guardian_invitations(id) ON DELETE CASCADE,
        user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        contact_digest TEXT NOT NULL,
        PRIMARY KEY (invitation_id, user_id, contact_digest)
      );
      CREATE INDEX guardian_contact_hashes_compare_idx
        ON guardian_contact_hashes (invitation_id, contact_digest);

      CREATE TABLE guardian_exceptions (
        id UUID PRIMARY KEY,
        candidate_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        phone_encrypted TEXT NOT NULL,
        phone_last_four TEXT NOT NULL,
        status TEXT NOT NULL DEFAULT 'pending'
          CHECK (status IN ('pending', 'approved', 'rejected', 'revoked')),
        admin_note TEXT NOT NULL DEFAULT '',
        reviewed_by UUID REFERENCES users(id) ON DELETE SET NULL,
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
        reviewed_at TIMESTAMPTZ
      );
      CREATE INDEX guardian_exceptions_queue_idx
        ON guardian_exceptions (status, created_at);
      CREATE UNIQUE INDEX guardian_exceptions_one_active_idx
        ON guardian_exceptions (candidate_user_id)
        WHERE status IN ('pending', 'approved');

      ALTER TABLE journey_call_guardians ALTER COLUMN guardian_user_id DROP NOT NULL;
      ALTER TABLE journey_call_guardians ADD COLUMN selection_mode TEXT NOT NULL DEFAULT 'guardian';
      ALTER TABLE journey_call_guardians ADD COLUMN verification_id UUID REFERENCES guardian_invitations(id) ON DELETE RESTRICT;
      ALTER TABLE journey_call_guardians ADD COLUMN exception_id UUID REFERENCES guardian_exceptions(id) ON DELETE RESTRICT;
      ALTER TABLE journey_call_guardians ADD CONSTRAINT journey_call_guardians_selection_mode
        CHECK (selection_mode IN ('guardian', 'exception'));
      ALTER TABLE journey_call_guardians ADD CONSTRAINT journey_call_guardians_verified_source
        CHECK (
          (selection_mode='guardian' AND guardian_user_id IS NOT NULL AND verification_id IS NOT NULL AND exception_id IS NULL)
          OR
          (selection_mode='exception' AND guardian_user_id IS NULL AND verification_id IS NULL AND exception_id IS NOT NULL)
        ) NOT VALID;
    `,
  },
  {
    id: '012_guardian_exception_contact_confirmation',
    sql: `
      ALTER TABLE guardian_exceptions ADD COLUMN contact_confirmed_at TIMESTAMPTZ;
      UPDATE guardian_exceptions SET status='pending', reviewed_by=NULL, reviewed_at=NULL
      WHERE status='approved' AND contact_confirmed_at IS NULL;
    `,
  },
  {
    id: '013_guardian_contact_fingerprint_v2',
    sql: `
      DELETE FROM guardian_contact_hashes;
      UPDATE guardian_invitations
      SET candidate_synced_at=NULL, guardian_synced_at=NULL
      WHERE status IN ('pending', 'registered');
    `,
  },
] as const;

export const migrations = [
  ...baseMigrations,
  ...familyAccessMigrations,
  ...paymentMigrations,
  ...identityMigrations,
  ...paymentPolicyMigrations,
  ...photoPrivacyMigrations,
  ...supportMigrations,
  ...notificationMigrations,
  ...adminActionMigrations,
  ...certificateMigrations,
  ...accessLogMigrations,
  ...googleAuthMigrations,
] as const;