-- Sistema de Inventario Forestal de Guatemala V1.0
-- Esquema de Base de Datos MySQL
-- Creado para cumplir con los estándares del INAB

-- Configuración inicial
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

-- Crear base de datos si no existe
CREATE DATABASE IF NOT EXISTS `sif_guatemala` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `sif_guatemala`;

-- =====================================================
-- TABLA: usuarios
-- Gestión de usuarios con roles y autenticación
-- =====================================================
CREATE TABLE `usuarios` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(100) NOT NULL,
    `email` VARCHAR(100) NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `rol` ENUM('Administrador', 'Regente', 'Técnico de Campo') NOT NULL,
    `fecha_creacion` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `activo` BOOLEAN DEFAULT TRUE,
    `ultimo_acceso` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `email_unique` (`email`),
    KEY `idx_rol` (`rol`),
    KEY `idx_activo` (`activo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABLA: proyectos
-- Proyectos forestales con información requerida por INAB
-- =====================================================
CREATE TABLE `proyectos` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `fecha_creacion` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `nombre_proyecto` VARCHAR(200) NOT NULL,
    `nombre_finca` VARCHAR(200) NOT NULL,
    `departamento` VARCHAR(100) NOT NULL,
    `municipio` VARCHAR(100) NOT NULL,
    `aldea` VARCHAR(100) NOT NULL,
    `nombre_propietario` VARCHAR(200) NOT NULL,
    `nombre_regente_forestal` VARCHAR(200) NOT NULL,
    `doc_tenencia_path` VARCHAR(500) NULL,
    `doc_regencia_path` VARCHAR(500) NULL,
    `usuario_id` INT(11) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_nombre_proyecto` (`nombre_proyecto`),
    KEY `idx_departamento` (`departamento`),
    KEY `idx_municipio` (`municipio`),
    KEY `fk_proyectos_usuario` (`usuario_id`),
    CONSTRAINT `fk_proyectos_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABLA: rodales
-- Rodales dentro de proyectos con área en hectáreas
-- =====================================================
CREATE TABLE `rodales` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `proyecto_id` INT(11) NOT NULL,
    `nombre_rodal` VARCHAR(100) NOT NULL,
    `area_ha` DECIMAL(10,4) NOT NULL,
    `fecha_creacion` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_proyecto_id` (`proyecto_id`),
    KEY `idx_nombre_rodal` (`nombre_rodal`),
    CONSTRAINT `fk_rodales_proyecto` FOREIGN KEY (`proyecto_id`) REFERENCES `proyectos` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `chk_area_positiva` CHECK (`area_ha` > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABLA: especies
-- Catálogo de especies forestales de Guatemala
-- =====================================================
CREATE TABLE `especies` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `nombre_cientifico` VARCHAR(200) NOT NULL,
    `nombre_comun` VARCHAR(200) NOT NULL,
    `activa` BOOLEAN DEFAULT TRUE,
    `fecha_creacion` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_nombre_cientifico` (`nombre_cientifico`),
    KEY `idx_nombre_comun` (`nombre_comun`),
    KEY `idx_activa` (`activa`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABLA: datos_campo
-- Registros de árboles capturados en campo con GPS
-- =====================================================
CREATE TABLE `datos_campo` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `rodal_id` INT(11) NOT NULL,
    `numero_parcela` INT(11) NOT NULL,
    `fecha_captura` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `latitud` DECIMAL(10, 8) NOT NULL,
    `longitud` DECIMAL(11, 8) NOT NULL,
    `especie_id` INT(11) NOT NULL,
    `dap_cm` DECIMAL(6,2) NOT NULL,
    `altura_m` DECIMAL(6,2) NOT NULL,
    `condicion_arbol` ENUM('Sano', 'Bifurcado', 'Plagado', 'Muerto en Pie') NOT NULL,
    `estado_sincronizacion` ENUM('Pendiente', 'Sincronizado') DEFAULT 'Pendiente',
    `usuario_id` INT(11) NOT NULL,
    `fecha_sincronizacion` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    KEY `idx_rodal_id` (`rodal_id`),
    KEY `idx_numero_parcela` (`numero_parcela`),
    KEY `idx_fecha_captura` (`fecha_captura`),
    KEY `idx_estado_sincronizacion` (`estado_sincronizacion`),
    KEY `idx_especie_id` (`especie_id`),
    KEY `fk_datos_campo_usuario` (`usuario_id`),
    CONSTRAINT `fk_datos_campo_rodal` FOREIGN KEY (`rodal_id`) REFERENCES `rodales` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_datos_campo_especie` FOREIGN KEY (`especie_id`) REFERENCES `especies` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `fk_datos_campo_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `chk_dap_positivo` CHECK (`dap_cm` > 0),
    CONSTRAINT `chk_altura_positiva` CHECK (`altura_m` > 0),
    CONSTRAINT `chk_parcela_positiva` CHECK (`numero_parcela` > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABLA: configuracion
-- Configuraciones del sistema
-- =====================================================
CREATE TABLE `configuracion` (
    `clave` VARCHAR(100) NOT NULL,
    `valor` TEXT NOT NULL,
    `descripcion` TEXT NULL,
    `fecha_modificacion` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`clave`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- DATOS INICIALES
-- =====================================================

-- Insertar especies forestales comunes de Guatemala
INSERT INTO `especies` (`nombre_cientifico`, `nombre_comun`, `activa`) VALUES
('Pinus maximinoi', 'Pino Candelillo', TRUE),
('Cupressus lusitanica', 'Ciprés Común', TRUE),
('Pinus oocarpa', 'Pino Ocote', TRUE),
('Pinus pseudostrobus', 'Pino Blanco', TRUE),
('Quercus sp.', 'Encino', TRUE),
('Alnus acuminata', 'Aliso', TRUE),
('Liquidambar styraciflua', 'Liquidámbar', TRUE),
('Cedrela odorata', 'Cedro', TRUE),
('Swietenia macrophylla', 'Caoba', TRUE),
('Cordia alliodora', 'Laurel', TRUE),
('Tabebuia rosea', 'Matilisguate', TRUE),
('Ceiba pentandra', 'Ceiba', TRUE),
('Terminalia amazonia', 'Volador', TRUE),
('Vochysia guatemalensis', 'San Juan', TRUE),
('Dalbergia retusa', 'Cocobolo', TRUE);

-- Configuraciones iniciales del sistema
INSERT INTO `configuracion` (`clave`, `valor`, `descripcion`) VALUES
('sistema_version', '1.0.0', 'Versión actual del sistema'),
('max_file_size', '5242880', 'Tamaño máximo de archivo en bytes (5MB)'),
('allowed_file_types', 'application/pdf', 'Tipos de archivo permitidos para documentos'),
('session_timeout', '86400', 'Tiempo de expiración de sesión en segundos (24 horas)'),
('backup_frequency', '7', 'Frecuencia de respaldo automático en días'),
('gps_precision', '6', 'Precisión decimal para coordenadas GPS'),
('dap_max_alert', '200', 'Valor máximo de DAP antes de mostrar alerta (cm)'),
('altura_max_alert', '50', 'Valor máximo de altura antes de mostrar alerta (m)'),
('sync_batch_size', '100', 'Cantidad de registros por lote en sincronización'),
('offline_storage_days', '30', 'Días de retención de datos offline');

-- =====================================================
-- ÍNDICES ADICIONALES PARA OPTIMIZACIÓN
-- =====================================================

-- Índice compuesto para búsquedas frecuentes
CREATE INDEX `idx_datos_campo_rodal_fecha` ON `datos_campo` (`rodal_id`, `fecha_captura`);
CREATE INDEX `idx_datos_campo_sincronizacion` ON `datos_campo` (`estado_sincronizacion`, `fecha_captura`);
CREATE INDEX `idx_proyectos_usuario_fecha` ON `proyectos` (`usuario_id`, `fecha_creacion`);

-- =====================================================
-- TRIGGERS PARA AUDITORÍA
-- =====================================================

-- Trigger para actualizar fecha de sincronización
DELIMITER $$
CREATE TRIGGER `tr_datos_campo_sync_update` 
BEFORE UPDATE ON `datos_campo`
FOR EACH ROW
BEGIN
    IF NEW.estado_sincronizacion = 'Sincronizado' AND OLD.estado_sincronizacion = 'Pendiente' THEN
        SET NEW.fecha_sincronizacion = CURRENT_TIMESTAMP;
    END IF;
END$$
DELIMITER ;

-- =====================================================
-- VISTAS PARA REPORTES
-- =====================================================

-- Vista para resumen de proyectos
CREATE VIEW `vista_resumen_proyectos` AS
SELECT 
    p.id,
    p.nombre_proyecto,
    p.nombre_finca,
    p.departamento,
    p.municipio,
    p.nombre_propietario,
    p.nombre_regente_forestal,
    u.nombre as usuario_creador,
    COUNT(DISTINCT r.id) as total_rodales,
    COALESCE(SUM(r.area_ha), 0) as area_total_ha,
    COUNT(DISTINCT dc.id) as total_registros_campo,
    p.fecha_creacion
FROM proyectos p
LEFT JOIN usuarios u ON p.usuario_id = u.id
LEFT JOIN rodales r ON p.id = r.proyecto_id
LEFT JOIN datos_campo dc ON r.id = dc.rodal_id
GROUP BY p.id, p.nombre_proyecto, p.nombre_finca, p.departamento, p.municipio, 
         p.nombre_propietario, p.nombre_regente_forestal, u.nombre, p.fecha_creacion;

-- Vista para estadísticas por rodal
CREATE VIEW `vista_estadisticas_rodal` AS
SELECT 
    r.id as rodal_id,
    r.nombre_rodal,
    r.area_ha,
    p.nombre_proyecto,
    COUNT(dc.id) as total_arboles,
    COALESCE(AVG(dc.dap_cm), 0) as dap_promedio,
    COALESCE(AVG(dc.altura_m), 0) as altura_promedio,
    COALESCE(MAX(dc.dap_cm), 0) as dap_maximo,
    COALESCE(MAX(dc.altura_m), 0) as altura_maxima,
    COUNT(DISTINCT dc.numero_parcela) as total_parcelas,
    COUNT(DISTINCT dc.especie_id) as total_especies,
    SUM(CASE WHEN dc.estado_sincronizacion = 'Sincronizado' THEN 1 ELSE 0 END) as registros_sincronizados,
    SUM(CASE WHEN dc.estado_sincronizacion = 'Pendiente' THEN 1 ELSE 0 END) as registros_pendientes
FROM rodales r
LEFT JOIN proyectos p ON r.proyecto_id = p.id
LEFT JOIN datos_campo dc ON r.id = dc.rodal_id
GROUP BY r.id, r.nombre_rodal, r.area_ha, p.nombre_proyecto;

COMMIT;

-- =====================================================
-- VERIFICACIÓN DE INTEGRIDAD
-- =====================================================

-- Verificar que todas las tablas fueron creadas
SELECT 
    TABLE_NAME as 'Tabla Creada',
    TABLE_ROWS as 'Registros',
    CREATE_TIME as 'Fecha Creación'
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'sif_guatemala' 
ORDER BY TABLE_NAME;

-- Verificar especies insertadas
SELECT COUNT(*) as 'Total Especies Insertadas' FROM especies WHERE activa = TRUE;

-- Verificar configuraciones insertadas
SELECT COUNT(*) as 'Total Configuraciones' FROM configuracion;