-- Users Table for Authentication System
-- Run this SQL to create the users table

-- ---------------------------------------------
-- ตาราง sys_seq สำหรับ sequence number
-- ---------------------------------------------
CREATE TABLE IF NOT EXISTS `sys_seq` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `seq` BIGINT NOT NULL DEFAULT 0,
    `table_name` VARCHAR(50) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert initial sequence for users table
INSERT INTO `sys_seq` (`id`, `seq`, `table_name`) VALUES (1, 0, 'users');

-- ---------------------------------------------
-- ตาราง users หลัก
-- ---------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `US_id` VARCHAR(30) NOT NULL UNIQUE,
    `username` VARCHAR(50) NOT NULL UNIQUE,
    `email` VARCHAR(100) NOT NULL UNIQUE,
    `password` VARCHAR(255) NOT NULL,
    `role` ENUM('admin', 'user') DEFAULT 'user',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `last_login` TIMESTAMP NULL,
    `is_active` TINYINT(1) DEFAULT 1,
    INDEX `idx_US_id` (`US_id`),
    INDEX `idx_username` (`username`),
    INDEX `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------
-- Trigger สำหรับสร้าง US_id อัตโนมัติ
-- ---------------------------------------------
DELIMITER $$
CREATE TRIGGER `trg_users_before_insert` BEFORE INSERT ON `users` FOR EACH ROW BEGIN
    DECLARE v_time BIGINT;
    DECLARE v_seq BIGINT;

    SET v_time = FLOOR(UNIX_TIMESTAMP(NOW(3)) * 1000);
    UPDATE sys_seq SET seq = seq + 1 WHERE id = 1;
    SELECT seq INTO v_seq FROM sys_seq WHERE id = 1;

    SET NEW.US_id = CONCAT('US_', v_time + (v_seq MOD 1000));
END
$$
DELIMITER ;

-- Create default admin user (password: Admin@123)
-- You should change this password after first login
INSERT INTO `users` (`username`, `email`, `password`, `role`) VALUES 
('admin', 'admin@muanjai.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin');

-- Note: The default password hash above is for 'Admin@123'
-- You can generate a new hash using: password_hash('YourPassword', PASSWORD_DEFAULT)
