-- Base de données pour le système de gestion des clés Pedazone

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

USE `pedazone_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',
    `max_installations` INT DEFAULT 1,
    `installations_count` INT DEFAULT 0,
    `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),
    `notes` TEXT,
    `created_by` INT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `expires_at` DATE NOT NULL,
    `used_at` DATETIME NULL,
    FOREIGN KEY (`created_by`) REFERENCES `administrators`(`id`) ON DELETE CASCADE
);

-- Installations réalisées
CREATE TABLE `installations` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `key_id` INT NOT NULL,
    `domain` VARCHAR(255) NOT NULL,
    `server_ip` VARCHAR(45),
    `admin_email` VARCHAR(150) NOT NULL,
    `pedazone_version` VARCHAR(20) NOT NULL,
    `php_version` VARCHAR(10) NOT NULL,
    `database_type` VARCHAR(20) DEFAULT 'MySQL',
    `web_server` VARCHAR(50) DEFAULT 'Apache',
    `installed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `last_check` TIMESTAMP NULL,
    `status` ENUM('active', 'suspended', 'uninstalled') DEFAULT 'active',
    `installation_data` JSON,
    FOREIGN KEY (`key_id`) REFERENCES `installation_keys`(`id`) ON DELETE CASCADE
);

-- Logs d'audit
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,
    FOREIGN KEY (`admin_id`) REFERENCES `administrators`(`id`) ON DELETE SET NULL
);

-- Paramètres 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
);

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);

-- Clé 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));

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;

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