-- =============================================================================
-- Migration 006: Superuser Hierarchy
-- =============================================================================
-- Implements:
-- 1. SUPERUSER role - god-like permissions
-- 2. is_initial_superuser flag - marks the founder (cannot be deleted)
-- 3. Initial superuser can only transfer their status (self-transfer only)
-- 4. Superusers can promote/demote other superusers (but not initial)
-- 5. Admins cannot touch superusers
-- =============================================================================

-- Add SUPERUSER to the user_role enum
ALTER TYPE user_role ADD VALUE IF NOT EXISTS 'SUPERUSER';

-- Add is_initial_superuser column to users table
-- This marks the very first user who gets god-like, non-removable status
ALTER TABLE users ADD COLUMN IF NOT EXISTS is_initial_superuser BOOLEAN DEFAULT false;

-- Add is_protected column to prevent deletion of critical users
ALTER TABLE users ADD COLUMN IF NOT EXISTS is_protected BOOLEAN DEFAULT false;

-- Create index for quick lookup of initial superuser
CREATE INDEX IF NOT EXISTS idx_users_initial_superuser ON users(is_initial_superuser) WHERE is_initial_superuser = true;

-- Create superuser_transfers table to track initial superuser transfers
-- This is an audit trail for when the initial superuser status is transferred
CREATE TABLE IF NOT EXISTS superuser_transfers (
    id SERIAL PRIMARY KEY,
    from_user_id INTEGER NOT NULL REFERENCES users(id),
    to_user_id INTEGER NOT NULL REFERENCES users(id),
    transferred_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    reason TEXT,
    -- Constraint: from_user must have been initial superuser
    CONSTRAINT valid_transfer CHECK (from_user_id != to_user_id)
);

-- Create index on superuser_transfers
CREATE INDEX IF NOT EXISTS idx_superuser_transfers_from ON superuser_transfers(from_user_id);
CREATE INDEX IF NOT EXISTS idx_superuser_transfers_to ON superuser_transfers(to_user_id);

-- Create trigger to ensure only one initial superuser exists at a time
CREATE OR REPLACE FUNCTION ensure_single_initial_superuser()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.is_initial_superuser = true THEN
        -- Check if there's already an initial superuser (other than this user)
        IF EXISTS (
            SELECT 1 FROM users
            WHERE is_initial_superuser = true
            AND id != NEW.id
        ) THEN
            RAISE EXCEPTION 'Only one initial superuser can exist at a time. Transfer must be explicit.';
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_initial_superuser
    BEFORE INSERT OR UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION ensure_single_initial_superuser();

-- Create trigger to prevent deletion of initial superuser
CREATE OR REPLACE FUNCTION prevent_initial_superuser_deletion()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.is_initial_superuser = true THEN
        RAISE EXCEPTION 'Cannot delete the initial superuser. Transfer ownership first.';
    END IF;
    IF OLD.is_protected = true THEN
        RAISE EXCEPTION 'Cannot delete a protected user.';
    END IF;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_delete_initial_superuser
    BEFORE DELETE ON users
    FOR EACH ROW
    EXECUTE FUNCTION prevent_initial_superuser_deletion();

-- Create trigger to prevent removal of SUPERUSER role from initial superuser
CREATE OR REPLACE FUNCTION prevent_initial_superuser_role_removal()
RETURNS TRIGGER AS $$
DECLARE
    is_initial BOOLEAN;
BEGIN
    -- Check if the user being demoted is the initial superuser
    SELECT is_initial_superuser INTO is_initial FROM users WHERE id = OLD.user_id;

    IF is_initial = true AND OLD.role = 'SUPERUSER' THEN
        RAISE EXCEPTION 'Cannot remove SUPERUSER role from the initial superuser. Transfer ownership first.';
    END IF;

    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_initial_superuser_demotion
    BEFORE DELETE ON user_roles
    FOR EACH ROW
    EXECUTE FUNCTION prevent_initial_superuser_role_removal();

-- Create function to transfer initial superuser status
-- This MUST be called by the current initial superuser
CREATE OR REPLACE FUNCTION transfer_initial_superuser(
    current_su_id INTEGER,
    new_su_id INTEGER,
    transfer_reason TEXT DEFAULT NULL
)
RETURNS BOOLEAN AS $$
DECLARE
    is_current_initial BOOLEAN;
    new_user_exists BOOLEAN;
BEGIN
    -- Verify current user is the initial superuser
    SELECT is_initial_superuser INTO is_current_initial
    FROM users WHERE id = current_su_id;

    IF is_current_initial IS NULL OR is_current_initial = false THEN
        RAISE EXCEPTION 'Only the current initial superuser can transfer this status';
    END IF;

    -- Verify new user exists
    SELECT EXISTS(SELECT 1 FROM users WHERE id = new_su_id) INTO new_user_exists;
    IF new_user_exists = false THEN
        RAISE EXCEPTION 'Target user does not exist';
    END IF;

    -- Cannot transfer to self
    IF current_su_id = new_su_id THEN
        RAISE EXCEPTION 'Cannot transfer to yourself';
    END IF;

    -- Begin transfer
    -- 1. Remove initial_superuser flag from current user
    UPDATE users SET is_initial_superuser = false WHERE id = current_su_id;

    -- 2. Set initial_superuser flag on new user
    UPDATE users SET is_initial_superuser = true, is_protected = true WHERE id = new_su_id;

    -- 3. Ensure new user has SUPERUSER role
    INSERT INTO user_roles (user_id, role, created_at)
    VALUES (new_su_id, 'SUPERUSER', NOW())
    ON CONFLICT (user_id, role) DO NOTHING;

    -- 4. Record the transfer
    INSERT INTO superuser_transfers (from_user_id, to_user_id, reason)
    VALUES (current_su_id, new_su_id, transfer_reason);

    RETURN true;
END;
$$ LANGUAGE plpgsql;

-- Comment explaining the hierarchy
COMMENT ON COLUMN users.is_initial_superuser IS
'Marks the founding superuser. Cannot be deleted, can only transfer status to another user via self-initiated transfer.';

COMMENT ON COLUMN users.is_protected IS
'Protected users cannot be deleted. Initial superuser is always protected.';

COMMENT ON TABLE superuser_transfers IS
'Audit trail for initial superuser ownership transfers. Only the initial superuser can initiate a transfer.';