CREATE TABLE IF NOT EXISTS accounts (
    id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL PRIMARY KEY,
    display_name VARCHAR(120) NOT NULL DEFAULT '',
    password_hash VARCHAR(255) NOT NULL,
    status ENUM('pending_email', 'active', 'suspended', 'deleted') NOT NULL DEFAULT 'pending_email',
    password_changed_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_sso_accounts_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS account_emails (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    account_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    email VARCHAR(191) NOT NULL,
    email_normalized VARCHAR(191) NOT NULL,
    status ENUM('pending', 'verified', 'retired') NOT NULL DEFAULT 'pending',
    is_primary TINYINT(1) NOT NULL DEFAULT 0,
    verified_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_sso_account_email_normalized (email_normalized),
    UNIQUE KEY uq_sso_account_email_pair (account_id, email_normalized),
    INDEX idx_sso_account_emails_owner (account_id, status, is_primary),
    CONSTRAINT fk_sso_account_emails_account
        FOREIGN KEY (account_id) REFERENCES accounts (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS account_action_tokens (
    token_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL PRIMARY KEY,
    account_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    email_id BIGINT UNSIGNED NULL,
    purpose ENUM('verify_email', 'reset_password') NOT NULL,
    expires_at DATETIME NOT NULL,
    used_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_sso_action_tokens_owner (account_id, purpose, expires_at),
    INDEX idx_sso_action_tokens_expiry (expires_at, used_at),
    CONSTRAINT fk_sso_action_tokens_account
        FOREIGN KEY (account_id) REFERENCES accounts (id) ON DELETE CASCADE,
    CONSTRAINT fk_sso_action_tokens_email
        FOREIGN KEY (email_id) REFERENCES account_emails (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS auth_sessions (
    session_token_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL PRIMARY KEY,
    account_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_seen_at DATETIME NOT NULL,
    expires_at DATETIME NOT NULL,
    revoked_at DATETIME NULL,
    user_agent_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NULL,
    ip_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NULL,
    INDEX idx_sso_sessions_account (account_id, revoked_at, expires_at),
    INDEX idx_sso_sessions_expiry (expires_at),
    CONSTRAINT fk_sso_sessions_account
        FOREIGN KEY (account_id) REFERENCES accounts (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS oauth_clients (
    client_id CHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL PRIMARY KEY,
    owner_account_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    name VARCHAR(120) NOT NULL,
    description VARCHAR(1000) NOT NULL DEFAULT '',
    homepage_url VARCHAR(2048) NULL,
    client_type ENUM('confidential', 'public') NOT NULL,
    sector_key VARCHAR(128) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    status ENUM('pending', 'active', 'suspended', 'revoked') NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_sso_oauth_clients_owner (owner_account_id, status, created_at),
    INDEX idx_sso_oauth_clients_sector (sector_key, status),
    CONSTRAINT fk_sso_oauth_clients_owner
        FOREIGN KEY (owner_account_id) REFERENCES accounts (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS oauth_client_redirect_uris (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    client_id CHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    redirect_uri VARCHAR(2048) NOT NULL,
    redirect_uri_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_sso_oauth_redirect_hash (client_id, redirect_uri_hash),
    INDEX idx_sso_oauth_redirect_client (client_id),
    CONSTRAINT fk_sso_oauth_redirect_client
        FOREIGN KEY (client_id) REFERENCES oauth_clients (client_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS oauth_client_secrets (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    client_id CHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    secret_prefix CHAR(12) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    secret_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NULL,
    revoked_at DATETIME NULL,
    UNIQUE KEY uq_sso_oauth_client_secret (client_id, secret_hash),
    INDEX idx_sso_oauth_client_secret_active (client_id, revoked_at, expires_at),
    CONSTRAINT fk_sso_oauth_client_secrets_client
        FOREIGN KEY (client_id) REFERENCES oauth_clients (client_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS oauth_grants (
    account_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    client_id CHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    scope VARCHAR(512) NOT NULL,
    granted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    revoked_at DATETIME NULL,
    PRIMARY KEY (account_id, client_id),
    INDEX idx_sso_oauth_grants_client (client_id, revoked_at),
    CONSTRAINT fk_sso_oauth_grants_account
        FOREIGN KEY (account_id) REFERENCES accounts (id) ON DELETE CASCADE,
    CONSTRAINT fk_sso_oauth_grants_client
        FOREIGN KEY (client_id) REFERENCES oauth_clients (client_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS oauth_authorization_codes (
    code_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL PRIMARY KEY,
    client_id CHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    account_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    redirect_uri_id BIGINT UNSIGNED NOT NULL,
    scope VARCHAR(512) NOT NULL,
    code_challenge CHAR(43) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    code_challenge_method ENUM('S256') NOT NULL,
    nonce VARCHAR(255) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    consumed_at DATETIME NULL,
    INDEX idx_sso_oauth_codes_client_expiry (client_id, expires_at, consumed_at),
    INDEX idx_sso_oauth_codes_account (account_id, created_at),
    CONSTRAINT fk_sso_oauth_codes_client
        FOREIGN KEY (client_id) REFERENCES oauth_clients (client_id) ON DELETE CASCADE,
    CONSTRAINT fk_sso_oauth_codes_account
        FOREIGN KEY (account_id) REFERENCES accounts (id) ON DELETE CASCADE,
    CONSTRAINT fk_sso_oauth_codes_redirect
        FOREIGN KEY (redirect_uri_id) REFERENCES oauth_client_redirect_uris (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS oauth_refresh_tokens (
    token_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL PRIMARY KEY,
    family_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    client_id CHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    account_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    scope VARCHAR(512) NOT NULL,
    issued_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    consumed_at DATETIME NULL,
    revoked_at DATETIME NULL,
    INDEX idx_sso_refresh_family (family_id, revoked_at),
    INDEX idx_sso_refresh_owner (account_id, client_id, revoked_at, expires_at),
    CONSTRAINT fk_sso_refresh_client
        FOREIGN KEY (client_id) REFERENCES oauth_clients (client_id) ON DELETE CASCADE,
    CONSTRAINT fk_sso_refresh_account
        FOREIGN KEY (account_id) REFERENCES accounts (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS oidc_signing_keys (
    kid VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL PRIMARY KEY,
    algorithm VARCHAR(16) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    public_key_pem TEXT NOT NULL,
    private_key_ref VARCHAR(255) NOT NULL,
    status ENUM('active', 'verify_only', 'revoked') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    not_before DATETIME NOT NULL,
    retired_at DATETIME NULL,
    INDEX idx_sso_signing_keys_status (status, not_before, retired_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_events (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    actor_account_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NULL,
    event_type VARCHAR(80) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    target_type VARCHAR(80) CHARACTER SET ascii COLLATE ascii_bin NULL,
    target_id VARCHAR(128) CHARACTER SET ascii COLLATE ascii_bin NULL,
    request_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NULL,
    metadata_json LONGTEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_sso_audit_actor_time (actor_account_id, created_at),
    INDEX idx_sso_audit_target_time (target_type, target_id, created_at),
    INDEX idx_sso_audit_event_time (event_type, created_at),
    CONSTRAINT fk_sso_audit_actor
        FOREIGN KEY (actor_account_id) REFERENCES accounts (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
