

-- Tabla de clientes
CREATE TABLE IF NOT EXISTS `clients` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(100) NOT NULL,
    `email` varchar(100) NOT NULL,
    `company` varchar(150) DEFAULT NULL,
    `phone` varchar(20) DEFAULT NULL,
    `address` text DEFAULT NULL,
    `is_active` tinyint(1) NOT NULL DEFAULT 1,
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_email` (`email`),
    KEY `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de licencias
CREATE TABLE IF NOT EXISTS `licenses` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `client_id` int(11) NOT NULL,
    `license_key` varchar(20) NOT NULL UNIQUE,
    `product_name` varchar(100) NOT NULL DEFAULT 'Sistema de Reportes',
    `status` enum('Active','Suspended','Expired','Cancelled') NOT NULL DEFAULT 'Active',
    `domains` text DEFAULT NULL COMMENT 'JSON array de dominios autorizados',
    `max_domains` int(11) NOT NULL DEFAULT 1,
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `expires_at` timestamp NULL DEFAULT NULL,
    `last_check` timestamp NULL DEFAULT NULL,
    `check_count` int(11) NOT NULL DEFAULT 0,
    `notes` text DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_client_id` (`client_id`),
    KEY `idx_status` (`status`),
    KEY `idx_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de logs de verificación
CREATE TABLE IF NOT EXISTS `license_logs` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `license_key` varchar(20) NOT NULL,
    `domain` varchar(255) NOT NULL,
    `ip_address` varchar(45) NOT NULL,
    `user_agent` text DEFAULT NULL,
    `status` varchar(20) NOT NULL,
    `response` text DEFAULT NULL,
    `error_message` text DEFAULT NULL,
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_license_key` (`license_key`),
    KEY `idx_domain` (`domain`),
    KEY `idx_status` (`status`),
    KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de configuraciones del sistema
CREATE TABLE IF NOT EXISTS `settings` (
    `setting_key` varchar(100) NOT NULL,
    `setting_value` text DEFAULT NULL,
    `description` text DEFAULT NULL,
    `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de claves locales (para funcionamiento offline)
CREATE TABLE IF NOT EXISTS `local_keys` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `license_key` varchar(20) NOT NULL,
    `domain` varchar(255) NOT NULL,
    `local_key_data` text NOT NULL,
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `expires_at` timestamp NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_license_domain` (`license_key`, `domain`),
    KEY `idx_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @sql = IF(@FK_EXISTS = 0, 
    'ALTER TABLE `licenses` ADD CONSTRAINT `fk_licenses_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE',
    'SELECT "Foreign key already exists"');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Insertar configuraciones por defecto (solo si no existen)
INSERT IGNORE INTO `settings` (`setting_key`, `setting_value`, `description`) VALUES
('system_name', 'Sistema de Licencias GD', 'Nombre del sistema'),
('license_prefix', 'gd-', 'Prefijo para las claves de licencia'),
('license_length', '8', 'Longitud de la parte variable de la licencia'),
('default_expiry_days', '365', 'Días por defecto para expiración de licencias'),
('allow_unlimited_domains', '0', 'Permitir dominios ilimitados por defecto'),
('api_rate_limit', '100', 'Límite de peticiones por hora por IP'),
('log_retention_days', '90', 'Días para mantener logs de verificación'),
('system_timezone', 'America/Caracas', 'Zona horaria del sistema'),
('maintenance_mode', '0', 'Modo mantenimiento (0=off, 1=on)');

SET @sql = IF(@INDEX_EXISTS = 0, 
    'CREATE INDEX `idx_logs_date_license` ON `license_logs` (`created_at`, `license_key`)',
    'SELECT "Index already exists"');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql2 = IF(@INDEX_EXISTS2 = 0, 
    'CREATE INDEX `idx_licenses_status_expires` ON `licenses` (`status`, `expires_at`)',
    'SELECT "Index already exists"');

PREPARE stmt2 FROM @sql2;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;

-- Crear vistas útiles (eliminar si existe y recrear)
DROP VIEW IF EXISTS `active_licenses_view`;
CREATE VIEW `active_licenses_view` AS
SELECT 
    l.license_key,
    l.product_name,
    l.status,
    c.name as client_name,
    c.email as client_email,
    l.created_at,
    l.expires_at,
    l.last_check,
    l.check_count
FROM `licenses` l
JOIN `clients` c ON l.client_id = c.id
WHERE l.status = 'Active' AND c.is_active = 1;

-- Procedimiento para limpiar logs antiguos (eliminar si existe y recrear)
DROP PROCEDURE IF EXISTS `CleanOldLogs`;
DELIMITER //
CREATE PROCEDURE `CleanOldLogs`()
BEGIN
    DECLARE retention_days INT DEFAULT 90;
    
    SELECT setting_value INTO retention_days 
    FROM settings 
    WHERE setting_key = 'log_retention_days';
    
    DELETE FROM license_logs 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL retention_days DAY);
    
    DELETE FROM local_keys 
    WHERE expires_at < NOW();
END //
DELIMITER ;

-- Trigger para actualizar el contador de verificaciones (eliminar si existe y recrear)
DROP TRIGGER IF EXISTS `update_license_check_count`;
DELIMITER //
CREATE TRIGGER `update_license_check_count` 
AFTER INSERT ON license_logs
FOR EACH ROW
BEGIN
    UPDATE licenses 
    SET 
        check_count = check_count + 1,
        last_check = NEW.created_at
    WHERE license_key = NEW.license_key;
END //
DELIMITER ; 