-- Premium PHP MVC Framework Database Setup

CREATE DATABASE IF NOT EXISTS `premium_mvc` 
DEFAULT CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE `premium_mvc`;

-- Users Table
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255) UNIQUE NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `status` ENUM('active', 'suspended') DEFAULT 'active',
    `last_login` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX `idx_email` (`email`),
    INDEX `idx_status` (`status`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- User Roles Junction Table
CREATE TABLE IF NOT EXISTS `user_roles` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `role` ENUM('user', 'moderator', 'admin') NOT NULL,
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    UNIQUE KEY `unique_user_role` (`user_id`, `role`),
    
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_role` (`role`),
    INDEX `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert Sample Users (password is 'password' hashed)
INSERT INTO `users` (`name`, `email`, `password`, `status`) VALUES
('Administrator User', 'admin@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'active'),
('Moderator User', 'moderator@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'active'),
('Regular User', 'user@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'active'),
('Suspended User', 'suspended@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'suspended');

-- Assign Multiple Roles to Users
INSERT INTO `user_roles` (`user_id`, `role`) VALUES
(1, 'user'),
(1, 'moderator'),
(1, 'admin'),
(2, 'user'),
(2, 'moderator'),
(3, 'user'),
(4, 'user');

-- Additional test users
INSERT INTO `users` (`name`, `email`, `password`, `status`) VALUES
('Multi Role User 1', 'multi1@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'active'),
('Multi Role User 2', 'multi2@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'active');

INSERT INTO `user_roles` (`user_id`, `role`) VALUES
(5, 'user'),
(5, 'moderator'),
(6, 'user'),
(6, 'admin');

SELECT '=============================================' AS '';
SELECT 'Premium PHP MVC Framework Database Setup Complete!' AS '';
SELECT '=============================================' AS '';
SELECT 'Sample login credentials:' AS '';
SELECT '---------------------------------------------' AS '';
SELECT 'Admin: admin@example.com / password' AS '';
SELECT 'Moderator: moderator@example.com / password' AS '';
SELECT 'User: user@example.com / password' AS '';
SELECT 'Multi-role User: multi1@example.com / password' AS '';
SELECT '=============================================' AS '';