-- ================================================================================================
-- ARCHIVÉ - Ce script n'est plus nécessaire.
-- Son contenu a été intégré dans 01_create_database.sql (version consolidée).
-- ================================================================================================
-- Ce fichier est conservé à titre de référence historique uniquement.
-- Purpose:
-- 1) Add guest sessions + merge audit tables
-- 2) Add actor_type / actor_user_id / actor_guest_id columns on business aggregates
-- 3) Backfill AUTH_USER actor values for legacy rows
-- 4) Add indexes, foreign keys and actor consistency checks
--
-- Run with:
-- psql -h localhost -U postgres -d campus_safe -f scripts/04_guest_mode_full_migration.sql
-- ================================================================================================

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS guest_sessions (
    id CHAR(36) NOT NULL PRIMARY KEY,
    statut VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    telephone_hash VARCHAR(255) NOT NULL,
    telephone_chiffre VARCHAR(255) NULL,
    device_fingerprint VARCHAR(500) NULL,
    date_creation TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    date_modification TIMESTAMPTZ NULL,
    last_seen_at TIMESTAMPTZ NULL,
    merged_into_user_id CHAR(36) NULL,
    merged_at TIMESTAMPTZ NULL,
    CONSTRAINT fk_guest_sessions_merged_into_user
        FOREIGN KEY (merged_into_user_id) REFERENCES utilisateurs(id)
        ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_guest_sessions_telephone_hash ON guest_sessions (telephone_hash);
CREATE INDEX IF NOT EXISTS idx_guest_sessions_statut ON guest_sessions (statut);
CREATE INDEX IF NOT EXISTS idx_guest_sessions_merged_into_user_id ON guest_sessions (merged_into_user_id);

CREATE TABLE IF NOT EXISTS guest_merge_audit (
    id CHAR(36) NOT NULL PRIMARY KEY,
    guest_id CHAR(36) NOT NULL,
    user_id CHAR(36) NOT NULL,
    merge_request_id VARCHAR(100) NOT NULL UNIQUE,
    summary_json JSONB NOT NULL DEFAULT '[]'::jsonb,
    date_creation TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_guest_merge_audit_guest
        FOREIGN KEY (guest_id) REFERENCES guest_sessions(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_guest_merge_audit_user
        FOREIGN KEY (user_id) REFERENCES utilisateurs(id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_guest_merge_audit_guest_id ON guest_merge_audit (guest_id);
CREATE INDEX IF NOT EXISTS idx_guest_merge_audit_user_id ON guest_merge_audit (user_id);
CREATE INDEX IF NOT EXISTS idx_guest_merge_audit_merge_request_id ON guest_merge_audit (merge_request_id);

ALTER TABLE signalements ADD COLUMN IF NOT EXISTS actor_type VARCHAR(20) NOT NULL DEFAULT 'AUTH_USER';
ALTER TABLE signalements ADD COLUMN IF NOT EXISTS actor_user_id CHAR(36) NULL;
ALTER TABLE signalements ADD COLUMN IF NOT EXISTS actor_guest_id CHAR(36) NULL;

ALTER TABLE signalement_timeline ADD COLUMN IF NOT EXISTS actor_type VARCHAR(20) NOT NULL DEFAULT 'AUTH_USER';
ALTER TABLE signalement_timeline ADD COLUMN IF NOT EXISTS actor_user_id CHAR(36) NULL;
ALTER TABLE signalement_timeline ADD COLUMN IF NOT EXISTS actor_guest_id CHAR(36) NULL;

ALTER TABLE notes_internes ADD COLUMN IF NOT EXISTS actor_type VARCHAR(20) NOT NULL DEFAULT 'AUTH_USER';
ALTER TABLE notes_internes ADD COLUMN IF NOT EXISTS actor_user_id CHAR(36) NULL;
ALTER TABLE notes_internes ADD COLUMN IF NOT EXISTS actor_guest_id CHAR(36) NULL;

ALTER TABLE discussions ADD COLUMN IF NOT EXISTS actor_type VARCHAR(20) NOT NULL DEFAULT 'AUTH_USER';
ALTER TABLE discussions ADD COLUMN IF NOT EXISTS actor_user_id CHAR(36) NULL;
ALTER TABLE discussions ADD COLUMN IF NOT EXISTS actor_guest_id CHAR(36) NULL;
ALTER TABLE discussions ALTER COLUMN utilisateur_id DROP NOT NULL;

ALTER TABLE messages ADD COLUMN IF NOT EXISTS actor_type VARCHAR(20) NOT NULL DEFAULT 'AUTH_USER';
ALTER TABLE messages ADD COLUMN IF NOT EXISTS actor_user_id CHAR(36) NULL;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS actor_guest_id CHAR(36) NULL;

UPDATE signalements
SET actor_type = 'AUTH_USER',
    actor_user_id = COALESCE(actor_user_id, utilisateur_id),
    actor_guest_id = NULL
WHERE actor_type IS NULL
   OR (actor_type = 'AUTH_USER' AND actor_user_id IS NULL AND utilisateur_id IS NOT NULL);

UPDATE signalement_timeline
SET actor_type = 'AUTH_USER',
    actor_user_id = COALESCE(actor_user_id, effectue_par_id),
    actor_guest_id = NULL
WHERE actor_type IS NULL
   OR (actor_type = 'AUTH_USER' AND actor_user_id IS NULL AND effectue_par_id IS NOT NULL);

UPDATE notes_internes
SET actor_type = 'AUTH_USER',
    actor_user_id = COALESCE(actor_user_id, auteur_id),
    actor_guest_id = NULL
WHERE actor_type IS NULL
   OR (actor_type = 'AUTH_USER' AND actor_user_id IS NULL AND auteur_id IS NOT NULL);

UPDATE discussions
SET actor_type = 'AUTH_USER',
    actor_user_id = COALESCE(actor_user_id, utilisateur_id),
    actor_guest_id = NULL
WHERE actor_type IS NULL
   OR (actor_type = 'AUTH_USER' AND actor_user_id IS NULL AND utilisateur_id IS NOT NULL);

UPDATE messages
SET actor_type = 'AUTH_USER',
    actor_user_id = COALESCE(actor_user_id, auteur_id),
    actor_guest_id = NULL
WHERE actor_type IS NULL
   OR (actor_type = 'AUTH_USER' AND actor_user_id IS NULL AND auteur_id IS NOT NULL);

CREATE INDEX IF NOT EXISTS idx_signalements_actor_type ON signalements (actor_type);
CREATE INDEX IF NOT EXISTS idx_signalements_actor_user_id ON signalements (actor_user_id);
CREATE INDEX IF NOT EXISTS idx_signalements_actor_guest_id ON signalements (actor_guest_id);
CREATE INDEX IF NOT EXISTS idx_signalement_timeline_actor_type ON signalement_timeline (actor_type);
CREATE INDEX IF NOT EXISTS idx_signalement_timeline_actor_user_id ON signalement_timeline (actor_user_id);
CREATE INDEX IF NOT EXISTS idx_signalement_timeline_actor_guest_id ON signalement_timeline (actor_guest_id);
CREATE INDEX IF NOT EXISTS idx_notes_internes_actor_type ON notes_internes (actor_type);
CREATE INDEX IF NOT EXISTS idx_notes_internes_actor_user_id ON notes_internes (actor_user_id);
CREATE INDEX IF NOT EXISTS idx_notes_internes_actor_guest_id ON notes_internes (actor_guest_id);
CREATE INDEX IF NOT EXISTS idx_discussions_actor_type ON discussions (actor_type);
CREATE INDEX IF NOT EXISTS idx_discussions_actor_user_id ON discussions (actor_user_id);
CREATE INDEX IF NOT EXISTS idx_discussions_actor_guest_id ON discussions (actor_guest_id);
CREATE INDEX IF NOT EXISTS idx_messages_actor_type ON messages (actor_type);
CREATE INDEX IF NOT EXISTS idx_messages_actor_user_id ON messages (actor_user_id);
CREATE INDEX IF NOT EXISTS idx_messages_actor_guest_id ON messages (actor_guest_id);

ALTER TABLE signalements DROP CONSTRAINT IF EXISTS fk_signalements_actor_user;
ALTER TABLE signalements DROP CONSTRAINT IF EXISTS fk_signalements_actor_guest;
ALTER TABLE signalements DROP CONSTRAINT IF EXISTS ck_signalements_actor_consistency;
ALTER TABLE signalements
    ADD CONSTRAINT fk_signalements_actor_user
    FOREIGN KEY (actor_user_id) REFERENCES utilisateurs(id)
    ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE signalements
    ADD CONSTRAINT fk_signalements_actor_guest
    FOREIGN KEY (actor_guest_id) REFERENCES guest_sessions(id)
    ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE signalements
    ADD CONSTRAINT ck_signalements_actor_consistency
    CHECK (
        (actor_type = 'AUTH_USER' AND actor_guest_id IS NULL)
        OR
        (actor_type = 'GUEST' AND actor_user_id IS NULL AND actor_guest_id IS NOT NULL)
    );

ALTER TABLE signalement_timeline DROP CONSTRAINT IF EXISTS fk_signalement_timeline_actor_user;
ALTER TABLE signalement_timeline DROP CONSTRAINT IF EXISTS fk_signalement_timeline_actor_guest;
ALTER TABLE signalement_timeline DROP CONSTRAINT IF EXISTS ck_signalement_timeline_actor_consistency;
ALTER TABLE signalement_timeline
    ADD CONSTRAINT fk_signalement_timeline_actor_user
    FOREIGN KEY (actor_user_id) REFERENCES utilisateurs(id)
    ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE signalement_timeline
    ADD CONSTRAINT fk_signalement_timeline_actor_guest
    FOREIGN KEY (actor_guest_id) REFERENCES guest_sessions(id)
    ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE signalement_timeline
    ADD CONSTRAINT ck_signalement_timeline_actor_consistency
    CHECK (
        (actor_type = 'AUTH_USER' AND actor_guest_id IS NULL)
        OR
        (actor_type = 'GUEST' AND actor_user_id IS NULL AND actor_guest_id IS NOT NULL)
    );

ALTER TABLE notes_internes DROP CONSTRAINT IF EXISTS fk_notes_internes_actor_user;
ALTER TABLE notes_internes DROP CONSTRAINT IF EXISTS fk_notes_internes_actor_guest;
ALTER TABLE notes_internes DROP CONSTRAINT IF EXISTS ck_notes_internes_actor_consistency;
ALTER TABLE notes_internes
    ADD CONSTRAINT fk_notes_internes_actor_user
    FOREIGN KEY (actor_user_id) REFERENCES utilisateurs(id)
    ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE notes_internes
    ADD CONSTRAINT fk_notes_internes_actor_guest
    FOREIGN KEY (actor_guest_id) REFERENCES guest_sessions(id)
    ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE notes_internes
    ADD CONSTRAINT ck_notes_internes_actor_consistency
    CHECK (
        (actor_type = 'AUTH_USER' AND actor_guest_id IS NULL)
        OR
        (actor_type = 'GUEST' AND actor_user_id IS NULL AND actor_guest_id IS NOT NULL)
    );

ALTER TABLE discussions DROP CONSTRAINT IF EXISTS fk_discussions_actor_user;
ALTER TABLE discussions DROP CONSTRAINT IF EXISTS fk_discussions_actor_guest;
ALTER TABLE discussions DROP CONSTRAINT IF EXISTS ck_discussions_actor_consistency;
ALTER TABLE discussions
    ADD CONSTRAINT fk_discussions_actor_user
    FOREIGN KEY (actor_user_id) REFERENCES utilisateurs(id)
    ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE discussions
    ADD CONSTRAINT fk_discussions_actor_guest
    FOREIGN KEY (actor_guest_id) REFERENCES guest_sessions(id)
    ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE discussions
    ADD CONSTRAINT ck_discussions_actor_consistency
    CHECK (
        (actor_type = 'AUTH_USER' AND actor_guest_id IS NULL)
        OR
        (actor_type = 'GUEST' AND actor_user_id IS NULL AND actor_guest_id IS NOT NULL)
    );

ALTER TABLE messages DROP CONSTRAINT IF EXISTS fk_messages_actor_user;
ALTER TABLE messages DROP CONSTRAINT IF EXISTS fk_messages_actor_guest;
ALTER TABLE messages DROP CONSTRAINT IF EXISTS ck_messages_actor_consistency;
ALTER TABLE messages
    ADD CONSTRAINT fk_messages_actor_user
    FOREIGN KEY (actor_user_id) REFERENCES utilisateurs(id)
    ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE messages
    ADD CONSTRAINT fk_messages_actor_guest
    FOREIGN KEY (actor_guest_id) REFERENCES guest_sessions(id)
    ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE messages
    ADD CONSTRAINT ck_messages_actor_consistency
    CHECK (
        (actor_type = 'AUTH_USER' AND actor_guest_id IS NULL)
        OR
        (actor_type = 'GUEST' AND actor_user_id IS NULL AND actor_guest_id IS NOT NULL)
    );

SELECT 'Guest full migration applied successfully' AS status;