-- Demandes de nettoyage de compte ambassadeur (archivage)
-- Soumises par le franchisé, validées par le super_admin

CREATE TABLE IF NOT EXISTS cleanup_requests (
    id                 SERIAL PRIMARY KEY,
    ambassador_user_id INTEGER      NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    requested_by_id    INTEGER      NOT NULL REFERENCES users(id),
    reason             TEXT,
    status             VARCHAR(20)  NOT NULL DEFAULT 'pending'
                           CHECK (status IN ('pending', 'approved', 'rejected')),
    reviewed_by_id     INTEGER      REFERENCES users(id),
    reviewed_at        TIMESTAMPTZ,
    created_at         TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

-- Empêche deux demandes "pending" sur le même compte
CREATE UNIQUE INDEX IF NOT EXISTS idx_cleanup_requests_one_pending
    ON cleanup_requests(ambassador_user_id)
    WHERE status = 'pending';

-- Index pour la file admin
CREATE INDEX IF NOT EXISTS idx_cleanup_requests_status ON cleanup_requests(status);
