-- Ensi.lk Database Schema
-- This script creates the necessary tables for the application

-- Create users table
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `email` VARCHAR(255) NOT NULL UNIQUE,
    `username` VARCHAR(50) NULL UNIQUE,
    `password_hash` VARCHAR(255) NULL,
    `profile_picture` TEXT NULL,
    `user_level` ENUM('guest', 'registered', 'pro', 'vip') DEFAULT 'guest',
    `is_verified` BOOLEAN DEFAULT FALSE,
    `is_deleted` BOOLEAN DEFAULT FALSE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_email` (`email`),
    INDEX `idx_username` (`username`),
    INDEX `idx_user_level` (`user_level`),
    INDEX `idx_is_verified` (`is_verified`),
    INDEX `idx_is_deleted` (`is_deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create verification_codes table
CREATE TABLE IF NOT EXISTS `verification_codes` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `email` VARCHAR(255) NOT NULL,
    `code` VARCHAR(10) NOT NULL,
    `type` ENUM('email_verification', 'password_reset', 'account_deletion') NOT NULL,
    `expires_at` TIMESTAMP NOT NULL,
    `used` BOOLEAN DEFAULT FALSE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_email_code` (`email`, `code`),
    INDEX `idx_type` (`type`),
    INDEX `idx_expires_at` (`expires_at`),
    INDEX `idx_used` (`used`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create user_sessions table (for future use)
CREATE TABLE IF NOT EXISTS `user_sessions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `session_token` VARCHAR(255) NOT NULL UNIQUE,
    `expires_at` TIMESTAMP NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_session_token` (`session_token`),
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_expires_at` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create user_activity_log table (for future use)
CREATE TABLE IF NOT EXISTS `user_activity_log` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NULL,
    `activity_type` VARCHAR(50) NOT NULL,
    `description` TEXT NULL,
    `ip_address` VARCHAR(45) NULL,
    `user_agent` TEXT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_activity_type` (`activity_type`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create app_settings table (for future use)
CREATE TABLE IF NOT EXISTS `app_settings` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `setting_key` VARCHAR(100) NOT NULL UNIQUE,
    `setting_value` TEXT NULL,
    `description` TEXT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default app settings
INSERT IGNORE INTO `app_settings` (`setting_key`, `setting_value`, `description`) VALUES
('app_name', 'Ensi.lk', 'Application name'),
('app_version', '1.0.0', 'Current application version'),
('maintenance_mode', '0', 'Maintenance mode flag (0=off, 1=on)'),
('max_verification_attempts', '5', 'Maximum verification attempts per hour'),
('verification_code_expiry', '10', 'Verification code expiry time in minutes');

-- Clean up old verification codes (run this periodically)
-- DELETE FROM verification_codes WHERE expires_at < NOW() OR used = TRUE;

-- Example queries for common operations:

-- Check if user exists
-- SELECT id, email, is_verified FROM users WHERE email = ? AND is_deleted = FALSE;

-- Create verification code
-- INSERT INTO verification_codes (email, code, type, expires_at) VALUES (?, ?, ?, DATE_ADD(NOW(), INTERVAL 10 MINUTE));

-- Verify code
-- SELECT * FROM verification_codes WHERE email = ? AND code = ? AND type = ? AND expires_at > NOW() AND used = FALSE;

-- Mark code as used
-- UPDATE verification_codes SET used = TRUE WHERE email = ? AND code = ? AND type = ?;