"""Add normalized identities and fixed platform roles without copying legacy passwords."""

from alembic import op


revision = "20260716_10"
down_revision = "20260716_03"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute(
        """
        DO $$
        BEGIN
            IF EXISTS (
                SELECT 1 FROM information_schema.columns
                WHERE table_schema = 'public' AND table_name = 'users'
                  AND column_name = 'password'
            ) AND NOT EXISTS (
                SELECT 1 FROM information_schema.tables
                WHERE table_schema = 'public' AND table_name = 'users_legacy'
            ) THEN
                ALTER TABLE public.users RENAME TO users_legacy;
                ALTER INDEX IF EXISTS public.idx_users_username
                    RENAME TO idx_users_legacy_username;
            END IF;
        END $$;

        CREATE TABLE IF NOT EXISTS public.roles (
            id UUID PRIMARY KEY,
            name VARCHAR(32) NOT NULL UNIQUE,
            description VARCHAR(200) NOT NULL,
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS public.users (
            id UUID PRIMARY KEY,
            username VARCHAR(64) NOT NULL UNIQUE,
            display_name VARCHAR(100),
            password_hash TEXT NOT NULL,
            status VARCHAR(20) NOT NULL DEFAULT 'active'
                CHECK (status IN ('active', 'disabled')),
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            last_login_at TIMESTAMPTZ
        );

        CREATE TABLE IF NOT EXISTS public.user_roles (
            user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
            role_id UUID NOT NULL REFERENCES public.roles(id) ON DELETE CASCADE,
            assigned_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
            assigned_by UUID REFERENCES public.users(id) ON DELETE SET NULL,
            PRIMARY KEY (user_id, role_id)
        );

        CREATE TABLE IF NOT EXISTS public.auth_audit_events (
            id BIGSERIAL PRIMARY KEY,
            user_id UUID REFERENCES public.users(id) ON DELETE SET NULL,
            username VARCHAR(64),
            event_type VARCHAR(40) NOT NULL,
            success BOOLEAN NOT NULL,
            ip_address VARCHAR(64),
            user_agent VARCHAR(300),
            detail VARCHAR(500),
            created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
        );
        CREATE INDEX IF NOT EXISTS idx_auth_audit_created_at
            ON public.auth_audit_events(created_at DESC);
        CREATE INDEX IF NOT EXISTS idx_auth_audit_username
            ON public.auth_audit_events(username);

        INSERT INTO public.roles (id, name, description) VALUES
            ('01900000-0000-7000-8000-000000000001', 'admin', '平台管理员'),
            ('01900000-0000-7000-8000-000000000002', 'editor', '治理编辑者'),
            ('01900000-0000-7000-8000-000000000003', 'viewer', '只读查看者')
        ON CONFLICT (name) DO NOTHING;
        """
    )


def downgrade() -> None:
    # Identity rollback is intentionally data-preserving.
    pass