-- Standalone User Dashboard / Central Auth Database
-- Create a new database first, example: smith_auth_db. Then run this SQL in that database.

CREATE TABLE IF NOT EXISTS roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_key VARCHAR(80) NOT NULL UNIQUE,
    role_name VARCHAR(150) NOT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NULL,
    updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(190) NULL,
    password_hash VARCHAR(255) NOT NULL,
    role_id BIGINT UNSIGNED NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    mfa_enabled TINYINT(1) NOT NULL DEFAULT 1,
    mfa_email VARCHAR(190) NULL,
    force_password_change TINYINT(1) NOT NULL DEFAULT 0,
    password_changed_at DATETIME NULL,
    last_login_at DATETIME NULL,
    last_mfa_at DATETIME NULL,
    created_at DATETIME NULL,
    updated_at DATETIME NULL,
    INDEX idx_users_role (role_id),
    CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS programs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    program_code VARCHAR(80) NOT NULL UNIQUE,
    program_name VARCHAR(150) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NULL,
    updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    program_id BIGINT UNSIGNED NOT NULL,
    permission_key VARCHAR(150) NOT NULL,
    permission_name VARCHAR(190) NOT NULL,
    permission_group VARCHAR(120) NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NULL,
    updated_at DATETIME NULL,
    UNIQUE KEY uq_program_permission (program_id, permission_key),
    CONSTRAINT fk_permissions_program FOREIGN KEY (program_id) REFERENCES programs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS role_permissions (
    role_id BIGINT UNSIGNED NOT NULL,
    permission_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    CONSTRAINT fk_role_permissions_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    CONSTRAINT fk_role_permissions_permission FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_permissions (
    user_id BIGINT UNSIGNED NOT NULL,
    permission_id BIGINT UNSIGNED NOT NULL,
    effect ENUM('allow','deny') NOT NULL DEFAULT 'allow',
    PRIMARY KEY (user_id, permission_id),
    CONSTRAINT fk_user_permissions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_permissions_permission FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS program_user_access (
    user_id BIGINT UNSIGNED NOT NULL,
    program_id BIGINT UNSIGNED NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, program_id),
    CONSTRAINT fk_program_user_access_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_program_user_access_program FOREIGN KEY (program_id) REFERENCES programs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS login_attempts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(150) NULL,
    user_id BIGINT UNSIGNED NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    status ENUM('success','failed','locked') NOT NULL,
    reason VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_login_username (username), INDEX idx_login_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mfa_challenges (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    otp_hash VARCHAR(255) NOT NULL,
    purpose VARCHAR(50) NOT NULL DEFAULT 'login',
    attempts INT NOT NULL DEFAULT 0,
    expires_at DATETIME NOT NULL,
    verified_at DATETIME NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_mfa_user (user_id), INDEX idx_mfa_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_trusted_devices (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    selector VARCHAR(64) NOT NULL UNIQUE,
    validator_hash VARCHAR(255) NOT NULL,
    device_name VARCHAR(150) NULL,
    ip_address VARCHAR(45) NULL,
    user_agent_hash VARCHAR(255) NULL,
    expires_at DATETIME NOT NULL,
    last_used_at DATETIME NULL,
    revoked_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_trusted_user (user_id), INDEX idx_trusted_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_recovery_codes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    code_hash VARCHAR(255) NOT NULL,
    used_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_recovery_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS email_oauth_tokens (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    provider VARCHAR(50) NOT NULL DEFAULT 'google',
    email VARCHAR(190) NULL,
    access_token TEXT NULL,
    refresh_token TEXT NULL,
    expires_at DATETIME NULL,
    scopes TEXT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NULL,
    updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS email_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    to_email VARCHAR(190) NULL,
    subject VARCHAR(255) NULL,
    status VARCHAR(50) NULL,
    error_message TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    action VARCHAR(100) NOT NULL,
    module VARCHAR(100) NULL,
    description TEXT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_user (user_id), INDEX idx_audit_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO roles (role_key, role_name, status, created_at, updated_at) VALUES
('super_admin','Super Admin','active',NOW(),NOW()),
('admin','Admin','active',NOW(),NOW()),
('staff','Staff','active',NOW(),NOW()),
('viewer','Viewer','active',NOW(),NOW())
ON DUPLICATE KEY UPDATE role_name=VALUES(role_name), status=VALUES(status), updated_at=NOW();

INSERT INTO programs (program_code, program_name, is_active, created_at, updated_at) VALUES
('ACCESS_DASHBOARD','Access Dashboard',1,NOW(),NOW())
ON DUPLICATE KEY UPDATE program_name=VALUES(program_name), is_active=1, updated_at=NOW();

SET @access_program_id := (SELECT id FROM programs WHERE program_code='ACCESS_DASHBOARD' LIMIT 1);

INSERT INTO permissions (program_id, permission_key, permission_name, permission_group, sort_order, is_active, created_at, updated_at) VALUES
(@access_program_id,'dashboard_view','View Dashboard','Dashboard',10,1,NOW(),NOW()),
(@access_program_id,'users_view','View Users','Users',100,1,NOW(),NOW()),
(@access_program_id,'users_create','Create Users','Users',110,1,NOW(),NOW()),
(@access_program_id,'users_edit','Edit Users','Users',120,1,NOW(),NOW()),
(@access_program_id,'users_manage_permissions','Manage User Program Access','Users',130,1,NOW(),NOW()),
(@access_program_id,'roles_view','View Roles','Roles',200,1,NOW(),NOW()),
(@access_program_id,'roles_create','Create Roles','Roles',210,1,NOW(),NOW()),
(@access_program_id,'roles_edit','Edit Roles','Roles',220,1,NOW(),NOW()),
(@access_program_id,'roles_permissions_manage','Manage Role Permissions','Roles',230,1,NOW(),NOW()),
(@access_program_id,'programs_view','View Programs','Programs',300,1,NOW(),NOW()),
(@access_program_id,'programs_create','Create Programs','Programs',310,1,NOW(),NOW()),
(@access_program_id,'programs_edit','Edit Programs','Programs',320,1,NOW(),NOW()),
(@access_program_id,'security_manage','Manage Security','Security',400,1,NOW(),NOW()),
(@access_program_id,'gmail_oauth_manage','Manage Google OTP OAuth','Security',410,1,NOW(),NOW()),
(@access_program_id,'audit_logs_view','View Audit Logs','Audit Logs',500,1,NOW(),NOW())
ON DUPLICATE KEY UPDATE permission_name=VALUES(permission_name), permission_group=VALUES(permission_group), sort_order=VALUES(sort_order), is_active=1, updated_at=NOW();

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r CROSS JOIN permissions p WHERE r.role_key='super_admin';

INSERT IGNORE INTO users (name, username, email, password_hash, role_id, status, mfa_enabled, mfa_email, force_password_change, created_at, updated_at)
SELECT 'Super Admin', 'admin', 'admin@example.com', '$2y$10$54esXNNNwlLiMwEnEcLJ1u7cKsvti5QvQEZns5F1ujmL9l9CpHsBK', r.id, 'active', 0, 'admin@example.com', 1, NOW(), NOW()
FROM roles r WHERE r.role_key='super_admin'
AND NOT EXISTS (SELECT 1 FROM users WHERE username='admin');

INSERT IGNORE INTO program_user_access (user_id, program_id, is_active, created_at)
SELECT u.id, p.id, 1, NOW() FROM users u CROSS JOIN programs p WHERE u.username='admin' AND p.program_code='ACCESS_DASHBOARD';

-- Default admin password is: Admin@12345
-- Change immediately after first login.