-- Water Sort Game Admin Panel Database
-- Clean, optimized database structure

-- Create database
CREATE DATABASE IF NOT EXISTS `water_sort_admin` 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE `water_sort_admin`;

-- Game levels table
CREATE TABLE `levels` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `level_number` INT NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `description` TEXT,
    `difficulty` ENUM('Easy', 'Medium', 'Hard', 'Expert') DEFAULT 'Easy',
    `bottles_count` INT DEFAULT 4,
    `colors_count` INT DEFAULT 2,
    `bottle_capacity` INT DEFAULT 4,
    `initial_state` JSON NOT NULL,
    `solution_moves` INT DEFAULT 0,
    `time_limit` INT DEFAULT 0,
    `reward_coins` INT DEFAULT 10,
    `is_active` BOOLEAN DEFAULT TRUE,
    `play_count` INT DEFAULT 0,
    `completion_count` INT DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_level_number` (`level_number`),
    KEY `idx_difficulty` (`difficulty`),
    KEY `idx_is_active` (`is_active`),
    KEY `idx_play_count` (`play_count`),
    KEY `idx_levels_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Game plays/sessions table
CREATE TABLE `game_plays` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT(11) UNSIGNED NOT NULL,
    `level_id` INT(11) UNSIGNED NOT NULL,
    `moves_count` INT DEFAULT 0,
    `time_spent` INT DEFAULT 0,
    `status` ENUM('playing', 'won', 'lost', 'quit') DEFAULT 'playing',
    `coins_earned` INT DEFAULT 0,
    `hints_used` INT DEFAULT 0,
    `undos_used` INT DEFAULT 0,
    `completion_percentage` DECIMAL(5,2) DEFAULT 0.00,
    `started_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `completed_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_level_id` (`level_id`),
    KEY `idx_status` (`status`),
    KEY `idx_started_at` (`started_at`),
    KEY `idx_completed_at` (`completed_at`),
    KEY `idx_user_level` (`user_id`, `level_id`),
    CONSTRAINT `fk_game_plays_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_game_plays_level` FOREIGN KEY (`level_id`) REFERENCES `levels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Activity logs table
CREATE TABLE `activity_logs` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `admin_id` INT(11) UNSIGNED NULL,
    `action` VARCHAR(50) NOT NULL,
    `table_name` VARCHAR(50) NULL,
    `record_id` INT NULL,
    `old_values` JSON NULL,
    `new_values` JSON NULL,
    `ip_address` VARCHAR(45) NULL,
    `user_agent` TEXT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_admin_id` (`admin_id`),
    KEY `idx_action` (`action`),
    KEY `idx_table_name` (`table_name`),
    KEY `idx_created_at` (`created_at`),
    KEY `idx_activity_logs_created` (`created_at`),
    CONSTRAINT `fk_activity_logs_admin` FOREIGN KEY (`admin_id`) REFERENCES `admins` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- System settings table
CREATE TABLE `settings` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `setting_key` VARCHAR(100) NOT NULL,
    `setting_value` TEXT,
    `setting_type` ENUM('string', 'number', 'boolean', 'json') DEFAULT 'string',
    `description` TEXT,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert sample levels
INSERT INTO `levels` (`level_number`, `name`, `description`, `difficulty`, `bottles_count`, `colors_count`, `bottle_capacity`, `initial_state`, `solution_moves`, `reward_coins`) VALUES
(1, 'Getting Started', 'Learn the basics of water sorting', 'Easy', 4, 2, 4, 
 '[["red","red","blue","blue"], ["blue","red","blue","red"], [], []]', 3, 10),
(2, 'Mixing Colors', 'Three colors make it more challenging', 'Easy', 5, 3, 4, 
 '[["red","red","blue","blue"], ["blue","green","green","red"], ["green","blue","red","green"], [], []]', 5, 15),
(3, 'Triple Challenge', 'More bottles, more complexity', 'Medium', 6, 3, 4, 
 '[["red","green","blue","red"], ["blue","red","green","blue"], ["green","blue","red","green"], [], [], []]', 7, 20),
(4, 'Four Colors', 'Introducing a fourth color', 'Medium', 7, 4, 4, 
 '[["red","green","blue","yellow"], ["yellow","red","green","blue"], ["blue","yellow","red","green"], ["green","blue","yellow","red"], [], [], []]', 9, 25),
(5, 'Expert Level', 'Test your skills', 'Hard', 8, 4, 4, 
 '[["red","green","blue","yellow"], ["yellow","red","green","blue"], ["blue","yellow","red","green"], ["green","blue","yellow","red"], ["red","blue","green","yellow"], [], [], []]', 12, 35);

-- Insert sample game plays
INSERT INTO `game_plays` (`user_id`, `level_id`, `moves_count`, `time_spent`, `status`, `coins_earned`, `completed_at`) VALUES
(1, 1, 3, 120, 'won', 10, NOW() - INTERVAL 1 DAY),
(2, 1, 5, 180, 'won', 8, NOW() - INTERVAL 2 DAY),
(3, 2, 7, 300, 'won', 15, NOW() - INTERVAL 3 HOUR),
(1, 2, 0, 0, 'playing', 0, NULL);

-- Insert default settings
INSERT INTO `settings` (`setting_key`, `setting_value`, `setting_type`, `description`) VALUES
('site_name', 'Water Sort Game Admin', 'string', 'Site name for admin panel'),
('session_timeout', '3600', 'number', 'Session timeout in seconds'),
('max_login_attempts', '5', 'number', 'Maximum login attempts before lockout'),
('lockout_duration', '900', 'number', 'Account lockout duration in seconds'),
('default_coins', '100', 'number', 'Default coins for new users'),
('default_hints', '5', 'number', 'Default hints for new users'),
('default_undos', '10', 'number', 'Default undos for new users');

-- Create view for level statistics
CREATE VIEW `level_stats` AS
SELECT 
    l.id,
    l.level_number,
    l.name,
    l.difficulty,
    l.play_count,
    l.completion_count,
    CASE 
        WHEN l.play_count > 0 THEN ROUND((l.completion_count / l.play_count) * 100, 2)
        ELSE 0
    END as completion_rate,
    COALESCE(AVG(gp.moves_count), 0) as avg_moves,
    COALESCE(AVG(gp.time_spent), 0) as avg_time
FROM levels l
LEFT JOIN game_plays gp ON l.id = gp.level_id AND gp.status = 'won'
GROUP BY l.id;

-- Database setup complete
SELECT 'Water Sort Game Admin Panel database setup completed successfully!' as status;
