-- =============================================================================
-- BeKind Backend — Auth Schema v3
-- Tables: users, user_auth, roles, permissions,
--         user_roles, role_permissions, user_sessions, password_reset_tokens, email_verify_tokens,
--         auth_audit_logs, refresh_token_audit_logs
-- Note: auth_audit_logs and refresh_token_audit_logs are partitioned by RANGE(created_at)
--       Partitions must be created manually or via scheduled job
--
-- Architect-level improvements:
-- - Split generic tokens table into specific token types (password_reset, email_verify, magic_link)
-- - Access tokens are stateless JWT, not persisted (except blacklist use-case)
-- - Refresh tokens stored in user_sessions, not generic tokens table
-- - user_sessions status ENUM instead of duplicate is_active + is_revoked
-- - roles support multi-tenant with tenant_id, is_system_role, scope
-- - Email validation moved to app layer, removed strict DB regex
-- - FK changed from CASCADE to RESTRICT to preserve audit chain
-- =============================================================================

-- =============================================================================
-- EXTENSIONS
-- =============================================================================

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "citext";

-- =============================================================================
-- ENUMS
-- =============================================================================

DO $$ BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'user_status') THEN
        CREATE TYPE user_status AS ENUM ('active', 'inactive', 'suspended', 'pending_verification');
    END IF;
END $$;

DO $$ BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'session_status') THEN
        CREATE TYPE session_status AS ENUM ('active', 'revoked', 'expired', 'logged_out');
    END IF;
END $$;

DO $$ BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'platform_type') THEN
        CREATE TYPE platform_type AS ENUM ('web', 'mobile', 'zalo_miniapp', 'internal');
    END IF;
END $$;

-- =============================================================================
-- TABLES
-- =============================================================================

CREATE TABLE IF NOT EXISTS users (
    id         UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email      CITEXT NOT NULL,
    username   CITEXT UNIQUE,
    first_name VARCHAR(100),
    last_name  VARCHAR(100),
    phone      VARCHAR(20),
    avatar_url TEXT,
    status     user_status DEFAULT 'pending_verification',
    created_by UUID REFERENCES users(id),
    updated_by UUID REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP WITH TIME ZONE,

    CONSTRAINT users_phone_format CHECK (phone IS NULL OR phone ~* '^\+?[0-9\s\-\(\)]+$')
);

-- DB-02: ON DELETE RESTRICT - Prevents accidental user deletion while auth record exists
CREATE TABLE IF NOT EXISTS user_auth (
    id                  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id             UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    password_hash       VARCHAR(255) NOT NULL,
    last_login_at       TIMESTAMP WITH TIME ZONE,
    password_changed_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    login_attempts      INTEGER DEFAULT 0,
    locked_until        TIMESTAMP WITH TIME ZONE,
    twofa_secret        VARCHAR(255),
    twofa_enabled       BOOLEAN DEFAULT FALSE,
    twofa_method        VARCHAR(20) DEFAULT 'totp',
    created_at          TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT user_auth_twofa_method CHECK (twofa_method IN ('totp', 'sms', 'email')),
    UNIQUE (user_id)
);

CREATE TABLE IF NOT EXISTS roles (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name        VARCHAR(50) NOT NULL,
    description TEXT,
    tenant_id   UUID,
    is_system_role BOOLEAN DEFAULT FALSE,
    scope       VARCHAR(100),
    created_by  UUID REFERENCES users(id),
    updated_by  UUID REFERENCES users(id),
    created_at  TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    UNIQUE (tenant_id, name)
);

CREATE TABLE IF NOT EXISTS permissions (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    key         VARCHAR(150) UNIQUE NOT NULL,
    resource    VARCHAR(100) NOT NULL,
    action      VARCHAR(50) NOT NULL,
    name        VARCHAR(100),
    description TEXT,
    created_by  UUID REFERENCES users(id),
    updated_by  UUID REFERENCES users(id),
    created_at  TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    UNIQUE (resource, action)
);

-- DB-02: ON DELETE RESTRICT - Prevents accidental user deletion while role assignments exist
-- DB-02: ON DELETE RESTRICT - Prevents accidental role deletion while user assignments exist
CREATE TABLE IF NOT EXISTS user_roles (
    id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    role_id     UUID NOT NULL REFERENCES roles(id) ON DELETE RESTRICT,
    assigned_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    assigned_by UUID REFERENCES users(id),
    is_primary  BOOLEAN DEFAULT FALSE,
    created_by  UUID REFERENCES users(id),
    updated_by  UUID REFERENCES users(id),
    created_at  TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    UNIQUE (user_id, role_id)
);

-- DB-02: ON DELETE CASCADE - Role deletion should cascade to role-permission associations
-- DB-02: ON DELETE CASCADE - Permission deletion should cascade to role-permission associations
CREATE TABLE IF NOT EXISTS role_permissions (
    id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    role_id       UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    permission_id UUID NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
    created_at    TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_by    UUID REFERENCES users(id),
    updated_by    UUID REFERENCES users(id),
    updated_at    TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    UNIQUE (role_id, permission_id)
);

-- DB-02: ON DELETE RESTRICT - Prevents accidental user deletion while sessions exist
CREATE TABLE IF NOT EXISTS user_sessions (
    id                      UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id                 UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    token_version           INTEGER DEFAULT 1,
    status                  session_status DEFAULT 'active',
    device_info             JSONB,
    ip_address              INET,
    user_agent              TEXT,
    expires_at              TIMESTAMP WITH TIME ZONE NOT NULL,
    refresh_expires_at      TIMESTAMP WITH TIME ZONE NOT NULL,
    created_at              TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at              TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_activity_at        TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    platform                platform_type,
    device_id               VARCHAR(255),
    device_name             VARCHAR(255),
    revoked_at              TIMESTAMP WITH TIME ZONE,
    logged_out_at           TIMESTAMP WITH TIME ZONE,
    tenant_id               UUID,
    refresh_token_hash      VARCHAR(64),
    access_token_hash       VARCHAR(64)
);

COMMENT ON COLUMN user_sessions.device_info             IS 'Device information: OS, browser, type (for login history)';
COMMENT ON COLUMN user_sessions.status                  IS 'Session status: active, revoked, expired, logged_out';
COMMENT ON COLUMN user_sessions.platform                IS 'Platform type: web, mobile, zalo_miniapp, internal';
COMMENT ON COLUMN user_sessions.device_id               IS 'Unique device identifier';
COMMENT ON COLUMN user_sessions.device_name             IS 'Human-readable device name';
COMMENT ON COLUMN user_sessions.revoked_at              IS 'Timestamp when session was revoked (security event)';
COMMENT ON COLUMN user_sessions.logged_out_at           IS 'Timestamp when user logged out';
COMMENT ON COLUMN user_sessions.tenant_id               IS 'Tenant ID for multi-tenant support';
COMMENT ON COLUMN user_sessions.refresh_token_hash      IS 'SHA-256 hash of plain refresh token for O(1) lookup during token rotation';
COMMENT ON COLUMN user_sessions.access_token_hash       IS 'SHA-256 hash of plain access token for O(1) session validation';

-- Password reset tokens (for password reset flow)
-- DB-02: ON DELETE RESTRICT - Prevents accidental user deletion while tokens exist
CREATE TABLE IF NOT EXISTS password_reset_tokens (
    id         UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id    UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    token_hash CHAR(64) UNIQUE NOT NULL,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    used_at    TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);


-- Email verification tokens (for email verification flow)
-- DB-02: ON DELETE RESTRICT - Prevents accidental user deletion while tokens exist
CREATE TABLE IF NOT EXISTS email_verify_tokens (
    id         UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id    UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    token_hash CHAR(64) UNIQUE NOT NULL,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    used_at    TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);


-- DB-02: ON DELETE SET NULL - Preserves audit trail even if user is deleted
CREATE TABLE IF NOT EXISTS auth_audit_logs (
    id             UUID DEFAULT uuid_generate_v4(),
    user_id        UUID REFERENCES users(id) ON DELETE SET NULL,
    action         VARCHAR(50) NOT NULL,
    ip_address     INET,
    user_agent     TEXT,
    device_info    JSONB,
    success        BOOLEAN DEFAULT TRUE,
    failure_reason TEXT,
    session_id     UUID REFERENCES user_sessions(id) ON DELETE SET NULL,
    created_at     TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- DB-02: ON DELETE CASCADE - User deletion should cascade to their refresh token audit logs
CREATE TABLE IF NOT EXISTS refresh_token_audit_logs (
    id                  UUID DEFAULT uuid_generate_v4(),
    user_id             UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    session_id          UUID REFERENCES user_sessions(id) ON DELETE SET NULL,
    action              VARCHAR(50) NOT NULL,
    ip_address          INET,
    user_agent          TEXT,
    platform            platform_type,
    device_id           VARCHAR(255),
    previous_token_hash VARCHAR(255),
    new_token_hash      VARCHAR(255),
    reason              TEXT,
    created_at          TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- =============================================================================
-- INDEXES
-- =============================================================================

CREATE UNIQUE INDEX IF NOT EXISTS ux_users_email_active
ON users(email)
WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_users_status     ON users(status);
CREATE INDEX IF NOT EXISTS idx_users_created_at ON users(created_at);
CREATE INDEX IF NOT EXISTS idx_users_deleted_at ON users(deleted_at);

CREATE INDEX IF NOT EXISTS idx_user_auth_user_id      ON user_auth(user_id);
CREATE INDEX IF NOT EXISTS idx_user_auth_locked_until ON user_auth(locked_until);


-- 2FA backup codes indexes
-- Future: See future/auth-2fa-backup-codes.sql
-- CREATE INDEX IF NOT EXISTS idx_user_2fa_backup_codes_user_id ON user_2fa_backup_codes(user_id);
-- CREATE INDEX IF NOT EXISTS idx_user_2fa_backup_codes_used_at ON user_2fa_backup_codes(used_at);


CREATE INDEX IF NOT EXISTS idx_roles_name           ON roles(name);
CREATE INDEX IF NOT EXISTS idx_permissions_name     ON permissions(name);
CREATE INDEX IF NOT EXISTS idx_permissions_resource ON permissions(resource);
CREATE UNIQUE INDEX IF NOT EXISTS idx_permissions_key ON permissions(key);

CREATE INDEX IF NOT EXISTS idx_user_roles_user_id             ON user_roles(user_id);
CREATE INDEX IF NOT EXISTS idx_user_roles_role_id             ON user_roles(role_id);
CREATE UNIQUE INDEX IF NOT EXISTS ux_user_primary_role
ON user_roles(user_id)
WHERE is_primary = TRUE;
CREATE INDEX IF NOT EXISTS idx_role_permissions_role_id       ON role_permissions(role_id);
CREATE INDEX IF NOT EXISTS idx_role_permissions_permission_id ON role_permissions(permission_id);

CREATE INDEX IF NOT EXISTS idx_user_sessions_user_id               ON user_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_user_sessions_expires_at            ON user_sessions(expires_at);
CREATE INDEX IF NOT EXISTS idx_user_sessions_status               ON user_sessions(status);
CREATE INDEX IF NOT EXISTS idx_user_sessions_token_version         ON user_sessions(token_version);
CREATE INDEX IF NOT EXISTS idx_user_sessions_device_id             ON user_sessions(device_id);
CREATE INDEX IF NOT EXISTS idx_user_sessions_tenant_id             ON user_sessions(tenant_id);
CREATE INDEX IF NOT EXISTS idx_user_sessions_user_platform_active  ON user_sessions(user_id, platform, status) WHERE status = 'active';
CREATE INDEX IF NOT EXISTS idx_user_sessions_user_platform_revoked ON user_sessions(user_id, platform, status) WHERE status = 'revoked';
CREATE UNIQUE INDEX IF NOT EXISTS idx_user_sessions_refresh_token_hash    ON user_sessions(refresh_token_hash) WHERE status = 'active';
CREATE UNIQUE INDEX IF NOT EXISTS idx_user_sessions_access_token_hash     ON user_sessions(access_token_hash) WHERE status = 'active' AND access_token_hash IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_password_reset_tokens_user_id    ON password_reset_tokens(user_id);
CREATE INDEX IF NOT EXISTS idx_password_reset_tokens_expires_at ON password_reset_tokens(expires_at);

CREATE INDEX IF NOT EXISTS idx_email_verify_tokens_user_id    ON email_verify_tokens(user_id);
CREATE INDEX IF NOT EXISTS idx_email_verify_tokens_expires_at ON email_verify_tokens(expires_at);


CREATE INDEX IF NOT EXISTS idx_auth_audit_user_id    ON auth_audit_logs(user_id);
CREATE INDEX IF NOT EXISTS idx_auth_audit_action     ON auth_audit_logs(action);
CREATE INDEX IF NOT EXISTS idx_auth_audit_created_at ON auth_audit_logs(created_at);
CREATE INDEX IF NOT EXISTS idx_auth_audit_success    ON auth_audit_logs(success);

CREATE INDEX IF NOT EXISTS idx_refresh_audit_user_id    ON refresh_token_audit_logs(user_id);
CREATE INDEX IF NOT EXISTS idx_refresh_audit_session_id ON refresh_token_audit_logs(session_id);
CREATE INDEX IF NOT EXISTS idx_refresh_audit_action     ON refresh_token_audit_logs(action);
CREATE INDEX IF NOT EXISTS idx_refresh_audit_created_at ON refresh_token_audit_logs(created_at DESC);

-- =============================================================================
-- TRIGGER FUNCTIONS (shared — used by file and notification schemas too)
-- =============================================================================

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

-- =============================================================================
-- TRIGGERS
-- =============================================================================

CREATE TRIGGER update_user_auth_updated_at
    BEFORE UPDATE ON user_auth
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_roles_updated_at
    BEFORE UPDATE ON roles
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_user_roles_updated_at
    BEFORE UPDATE ON user_roles
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_role_permissions_updated_at
    BEFORE UPDATE ON role_permissions
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();


-- =============================================================================
-- VIEWS
-- =============================================================================

CREATE OR REPLACE VIEW active_users AS
SELECT
    u.id,
    u.email,
    u.username,
    u.first_name,
    u.last_name,
    primary_role.name AS primary_role_name,
    ua.last_login_at,
    u.created_at
FROM users u
LEFT JOIN user_auth ua ON u.id = ua.user_id
LEFT JOIN user_roles ur ON u.id = ur.user_id AND ur.is_primary = TRUE
LEFT JOIN roles primary_role ON ur.role_id = primary_role.id
WHERE u.status = 'active'
  AND u.deleted_at IS NULL;

CREATE OR REPLACE VIEW user_role_details AS
SELECT
    u.id           AS user_id,
    u.email,
    u.username,
    r.id           AS role_id,
    r.name         AS role_name,
    r.description  AS role_description,
    ur.is_primary,
    ur.assigned_at,
    ur.assigned_by,
    assigner.email AS assigned_by_email
FROM users u
JOIN user_roles ur ON u.id = ur.user_id
JOIN roles r ON ur.role_id = r.id
LEFT JOIN users assigner ON ur.assigned_by = assigner.id
WHERE u.deleted_at IS NULL
ORDER BY u.id, ur.is_primary DESC, ur.assigned_at ASC;

CREATE OR REPLACE VIEW user_sessions_detailed AS
SELECT
    us.id,
    us.user_id,
    us.token_version,
    us.status,
    us.device_info,
    us.ip_address,
    us.user_agent,
    us.expires_at,
    us.refresh_expires_at,
    us.created_at,
    us.updated_at,
    us.last_activity_at,
    us.platform,
    us.device_id,
    us.device_name,
    us.revoked_at,
    us.logged_out_at,
    us.tenant_id,
    us.refresh_token_hash,
    us.access_token_hash,
    u.email,
    u.username,
    u.first_name,
    u.last_name,
    u.status as user_status
FROM user_sessions us
LEFT JOIN users u ON us.user_id = u.id;

-- =============================================================================
-- FUNCTIONS
-- =============================================================================

CREATE OR REPLACE FUNCTION cleanup_expired_sessions()
RETURNS INTEGER AS $$
DECLARE
    updated_count INTEGER;
BEGIN
    UPDATE user_sessions
    SET status = 'expired',
        updated_at = CURRENT_TIMESTAMP
    WHERE status = 'active'
      AND (expires_at < CURRENT_TIMESTAMP OR refresh_expires_at < CURRENT_TIMESTAMP);
    GET DIAGNOSTICS updated_count = ROW_COUNT;
    RETURN updated_count;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION cleanup_expired_password_reset_tokens()
RETURNS INTEGER AS $$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM password_reset_tokens WHERE expires_at < CURRENT_TIMESTAMP;
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION cleanup_expired_email_verify_tokens()
RETURNS INTEGER AS $$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM email_verify_tokens WHERE expires_at < CURRENT_TIMESTAMP;
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION is_user_locked(user_email VARCHAR(255))
RETURNS BOOLEAN AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM users u
        JOIN user_auth ua ON u.id = ua.user_id
        WHERE u.email = user_email
          AND ua.locked_until IS NOT NULL
          AND ua.locked_until > CURRENT_TIMESTAMP
    );
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION invalidate_user_sessions(user_uuid UUID)
RETURNS INTEGER AS $$
DECLARE
    updated_count INTEGER;
BEGIN
    UPDATE user_sessions
    SET token_version = token_version + 1,
        status        = 'revoked',
        revoked_at    = CURRENT_TIMESTAMP,
        updated_at    = CURRENT_TIMESTAMP
    WHERE user_id = user_uuid AND status = 'active';
    GET DIAGNOSTICS updated_count = ROW_COUNT;
    RETURN updated_count;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION log_auth_event(
    p_user_id        UUID,
    p_action         VARCHAR(50),
    p_ip_address     INET,
    p_user_agent     TEXT,
    p_device_info    JSONB,
    p_success        BOOLEAN,
    p_failure_reason TEXT DEFAULT NULL,
    p_session_id     UUID DEFAULT NULL
)
RETURNS UUID AS $$
DECLARE
    log_id UUID;
BEGIN
    INSERT INTO auth_audit_logs
        (user_id, action, ip_address, user_agent, device_info, success, failure_reason, session_id)
    VALUES
        (p_user_id, p_action, p_ip_address, p_user_agent, p_device_info, p_success, p_failure_reason, p_session_id)
    RETURNING id INTO log_id;
    RETURN log_id;
END;
$$ LANGUAGE plpgsql;
