-- =====================================================================
-- Migration: Add Admin and SuperAdmin tables
-- Date: 2026-08-15
-- Description: Creates separate logical tables for Admin and SuperAdmin
--              accounts with proper indexes and constraints.
-- =====================================================================

-- ===== CREATE ADMIN TABLE =====
CREATE TABLE `Admin` (
    `id` VARCHAR(191) NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,
    `adminId` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `username` VARCHAR(191) NOT NULL,
    `email` VARCHAR(191) NOT NULL,
    `phone` VARCHAR(191) NULL,
    `passwordHash` VARCHAR(191) NOT NULL,
    `roleId` VARCHAR(191) NULL,
    `status` ENUM('ACTIVE', 'INACTIVE', 'SUSPENDED', 'BANNED') NOT NULL DEFAULT 'ACTIVE',
    `isActive` BOOLEAN NOT NULL DEFAULT true,
    `isBanned` BOOLEAN NOT NULL DEFAULT false,
    `twoFactorEnabled` BOOLEAN NOT NULL DEFAULT false,
    `lastLoginAt` DATETIME(3) NULL,
    `lastLogoutAt` DATETIME(3) NULL,

    UNIQUE INDEX `Admin_adminId_key`(`adminId`),
    UNIQUE INDEX `Admin_username_key`(`username`),
    UNIQUE INDEX `Admin_email_key`(`email`),
    INDEX `Admin_status_idx`(`status`),
    INDEX `Admin_isBanned_idx`(`isBanned`),
    INDEX `Admin_email_idx`(`email`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- ===== CREATE SUPER ADMIN TABLE =====
CREATE TABLE `SuperAdmin` (
    `id` VARCHAR(191) NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,
    `superAdminId` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `username` VARCHAR(191) NOT NULL,
    `email` VARCHAR(191) NOT NULL,
    `phone` VARCHAR(191) NULL,
    `passwordHash` VARCHAR(191) NOT NULL,
    `status` ENUM('ACTIVE', 'INACTIVE', 'SUSPENDED', 'BANNED') NOT NULL DEFAULT 'ACTIVE',
    `isActive` BOOLEAN NOT NULL DEFAULT true,
    `twoFactorEnabled` BOOLEAN NOT NULL DEFAULT false,
    `lastLoginAt` DATETIME(3) NULL,
    `lastLogoutAt` DATETIME(3) NULL,

    UNIQUE INDEX `SuperAdmin_superAdminId_key`(`superAdminId`),
    UNIQUE INDEX `SuperAdmin_username_key`(`username`),
    UNIQUE INDEX `SuperAdmin_email_key`(`email`),
    INDEX `SuperAdmin_status_idx`(`status`),
    INDEX `SuperAdmin_isActive_idx`(`isActive`),
    INDEX `SuperAdmin_email_idx`(`email`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- ===== FOREIGN KEYS =====

-- Admin references Role
ALTER TABLE `Admin` ADD CONSTRAINT `Admin_roleId_fkey` FOREIGN KEY (`roleId`) REFERENCES `Role`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- =====================================================================
-- SEED DATA
-- =====================================================================

-- Seed default ADMIN account (password: Admin@12345)
-- Password is hashed with bcrypt rounds=10
INSERT INTO `Admin` (`id`, `adminId`, `name`, `username`, `email`, `passwordHash`, `status`, `isActive`, `createdAt`, `updatedAt`)
VALUES (
    'admin-001-fasecmo-platform',
    'ADM-001',
    'System Admin',
    'admin',
    'admin@fasecmo.com',
    '$2b$10$rQZ8k3L9pV2wX7yH5nJ0eO1iU4tY6aS8dF0gH2jK4lM7nP9qR2sT5uV8wX1yZ3',
    'ACTIVE',
    true,
    NOW(3),
    NOW(3)
) ON DUPLICATE KEY UPDATE `email` = `email`;

-- Seed default SUPER_ADMIN account (password: SuperAdmin@12345)
-- Password is hashed with bcrypt rounds=10
INSERT INTO `SuperAdmin` (`id`, `superAdminId`, `name`, `username`, `email`, `passwordHash`, `status`, `isActive`, `createdAt`, `updatedAt`)
VALUES (
    'superadmin-001-fasecmo-platform',
    'SADM-001',
    'Super Administrator',
    'superadmin',
    'superadmin@fasecmo.com',
    '$2b$10$mN3bV6cX9dF2gH5jK8lP0qR3sT6uV9wX2yZ5aB8cD1eF4gH7jK0lM3nP6qS9',
    'ACTIVE',
    true,
    NOW(3),
    NOW(3)
) ON DUPLICATE KEY UPDATE `email` = `email`;

-- =====================================================================
-- END OF MIGRATION
-- =====================================================================