-- =============================================
-- BASE DE DONNÉES: pedazone_keys_manager
-- Système de gestion des clés d'installation
-- =============================================

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

USE `pedazone_keys_manager`;

-- =============================================
-- TABLE: administrators - Gestion des admins
-- =============================================
CREATE TABLE `administrators` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `username` VARCHAR(50) UNIQUE NOT NULL,
    `email` VARCHAR(100) UNIQUE NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `full_name` VARCHAR(100) NOT NULL,
    `role` ENUM('superadmin', 'admin') DEFAULT 'admin',
    `is_active` BOOLEAN DEFAULT TRUE,
    `last_login` DATETIME NULL,
    `login_attempts` INT DEFAULT 0,
    `last_attempt` DATETIME NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- =============================================
-- TABLE: installation_keys - Clés d'installation
-- =============================================
CREATE TABLE `installation_keys` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `key_value` VARCHAR(50) UNIQUE NOT NULL COMMENT 'Clé unique générée',
    `key_name` VARCHAR(100) NOT NULL COMMENT 'Nom identifiant la clé',
    `status` ENUM('active', 'used', 'revoked', 'expired') DEFAULT 'active',
    
    -- Limitations
    `max_installations` INT DEFAULT 1,
    `installations_count` INT DEFAULT 0,
    
    -- Informations client
    `client_name` VARCHAR(200) NOT NULL,
    `client_email` VARCHAR(150) NOT NULL,
    `client_phone` VARCHAR(20),
    `institution_type` ENUM('universite', 'ecole', 'college', 'lycee', 'autre') DEFAULT 'universite',
    `institution_name` VARCHAR(200),
    
    -- Métadonnées
    `notes` TEXT,
    `created_by` INT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `expires_at` DATE NOT NULL,
    `used_at` DATETIME NULL,
    
    -- Index
    INDEX `idx_status` (`status`),
    INDEX `idx_expires` (`expires_at`),
    INDEX `idx_created_by` (`created_by`),
    
    FOREIGN KEY (`created_by`) REFERENCES `administrators`(`id`) ON DELETE CASCADE
);

-- =============================================
-- TABLE: installations - Suivi des installations
-- =============================================
CREATE TABLE `installations` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `key_id` INT NOT NULL,
    
    -- Informations installation
    `domain` VARCHAR(255) NOT NULL,
    `server_ip` VARCHAR(45) NOT NULL,
    `admin_email` VARCHAR(150) NOT NULL,
    
    -- Versions et environnement
    `pedazone_version` VARCHAR(20) NOT NULL,
    `php_version` VARCHAR(10) NOT NULL,
    `database_type` VARCHAR(20) DEFAULT 'MySQL',
    `web_server` VARCHAR(50) DEFAULT 'Apache',
    
    -- Statuts et dates
    `installed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `last_check` TIMESTAMP NULL,
    `status` ENUM('active', 'suspended', 'uninstalled') DEFAULT 'active',
    
    -- Données techniques
    `installation_data` JSON,
    
    -- Index
    INDEX `idx_key_id` (`key_id`),
    INDEX `idx_domain` (`domain`),
    INDEX `idx_installed` (`installed_at`),
    INDEX `idx_status` (`status`),
    
    FOREIGN KEY (`key_id`) REFERENCES `installation_keys`(`id`) ON DELETE CASCADE
);

-- =============================================
-- TABLE: api_requests - Logs des requêtes API
-- =============================================
CREATE TABLE `api_requests` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `api_key` VARCHAR(50) NOT NULL,
    `endpoint` VARCHAR(100) NOT NULL,
    `installation_key` VARCHAR(50) NULL,
    `ip_address` VARCHAR(45) NOT NULL,
    `user_agent` TEXT,
    `request_method` VARCHAR(10) NOT NULL,
    `response_code` INT NOT NULL,
    `response_time` FLOAT NOT NULL COMMENT 'Temps en secondes',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX `idx_ip` (`ip_address`),
    INDEX `idx_created` (`created_at`),
    INDEX `idx_installation_key` (`installation_key`)
);

-- =============================================
-- TABLE: audit_logs - Logs de sécurité
-- =============================================
CREATE TABLE `audit_logs` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `admin_id` INT NULL,
    `action` VARCHAR(100) NOT NULL,
    `description` TEXT NOT NULL,
    `ip_address` VARCHAR(45) NOT NULL,
    `user_agent` TEXT,
    `affected_table` VARCHAR(50),
    `affected_id` INT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX `idx_admin` (`admin_id`),
    INDEX `idx_action` (`action`),
    INDEX `idx_created` (`created_at`),
    
    FOREIGN KEY (`admin_id`) REFERENCES `administrators`(`id`) ON DELETE SET NULL
);

-- =============================================
-- TABLE: settings - Configuration système
-- =============================================
CREATE TABLE `settings` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `setting_key` VARCHAR(100) UNIQUE NOT NULL,
    `setting_value` TEXT,
    `setting_type` ENUM('string', 'integer', 'boolean', 'json') DEFAULT 'string',
    `description` TEXT,
    `is_public` BOOLEAN DEFAULT FALSE,
    `updated_by` INT NULL,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`updated_by`) REFERENCES `administrators`(`id`) ON DELETE SET NULL
);

-- =============================================
-- DONNÉES INITIALES
-- =============================================

-- Administrateur principal (mot de passe: Admin123!)
INSERT INTO `administrators` 
(`username`, `email`, `password_hash`, `full_name`, `role`) 
VALUES 
('admin', 'admin@pedazone.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Administrateur Principal', 'superadmin');

-- Paramètres par défaut
INSERT INTO `settings` (`setting_key`, `setting_value`, `setting_type`, `description`, `is_public`) VALUES
('site_name', 'Pedazone Keys Manager', 'string', 'Nom de l application', TRUE),
('site_url', 'https://keys.pedazone.com', 'string', 'URL de l application', TRUE),
('key_expiry_days', '30', 'integer', 'Durée de validité des clés en jours', FALSE),
('max_validation_attempts', '3', 'integer', 'Tentatives max de validation', FALSE),
('api_rate_limit', '100', 'integer', 'Limite de requêtes API par heure', FALSE),
('auto_revoke_expired', '1', 'boolean', 'Révoquer automatiquement les clés expirées', FALSE),
('notification_email', 'no_reply@pedazone.com', 'string', 'Email des notifications', FALSE),
('support_email', 'support@pedazone.com', 'string', 'Email du support', TRUE);

-- Clé d'exemple (à usage de test)
INSERT INTO `installation_keys` 
(`key_value`, `key_name`, `client_name`, `client_email`, `institution_type`, `institution_name`, `created_by`, `expires_at`) 
VALUES 
('PEDAZONE-TEST-1234-5678', 'Clé de Test Université Paris', 'Université Paris Test', 'contact@universite-paris.fr', 'universite', 'Université de Paris', 1, DATE_ADD(CURDATE(), INTERVAL 30 DAY));

-- =============================================
-- VUES POUR RAPPORTS
-- =============================================

CREATE VIEW `view_keys_summary` AS
SELECT 
    COUNT(*) as total_keys,
    SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_keys,
    SUM(CASE WHEN status = 'used' THEN 1 ELSE 0 END) as used_keys,
    SUM(CASE WHEN status = 'revoked' THEN 1 ELSE 0 END) as revoked_keys,
    SUM(CASE WHEN status = 'expired' THEN 1 ELSE 0 END) as expired_keys,
    SUM(installations_count) as total_installations
FROM `installation_keys`;

CREATE VIEW `view_recent_activity` AS
SELECT 
    'key_created' as activity_type,
    key_name as description,
    created_at
FROM `installation_keys`
UNION ALL
SELECT 
    'installation' as activity_type,
    CONCAT('Installation sur ', domain) as description,
    installed_at
FROM `installations`
ORDER BY created_at DESC
LIMIT 50;

-- =============================================
-- INDEX OPTIMISATION
-- =============================================

CREATE INDEX `idx_keys_status_expiry` ON `installation_keys` (`status`, `expires_at`);
CREATE INDEX `idx_installations_domain_status` ON `installations` (`domain`, `status`);
CREATE INDEX `idx_audit_admin_date` ON `audit_logs` (`admin_id`, `created_at`);
CREATE INDEX `idx_api_requests_date` ON `api_requests` (`created_at`, `response_code`);

-- Table pour les tokens "Se souvenir de moi"
CREATE TABLE IF NOT EXISTS `remember_me_tokens` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `admin_id` INT NOT NULL,
    `token` VARCHAR(64) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `expires_at` DATETIME NOT NULL,
    
    INDEX `idx_token` (`token`),
    INDEX `idx_admin` (`admin_id`),
    INDEX `idx_expires` (`expires_at`),
    
    FOREIGN KEY (`admin_id`) REFERENCES `administrators`(`id`) ON DELETE CASCADE
);

-- Nettoyage automatique des tokens expirés
CREATE EVENT IF NOT EXISTS `cleanup_expired_tokens`
ON SCHEDULE EVERY 1 DAY
DO
DELETE FROM `remember_me_tokens` WHERE `expires_at` < NOW();


-- =============================================
-- FIN DU SCRIPT
-- =============================================

SELECT 'Base de données pedazone_keys_manager créée avec succès!' as message;