-- ============================================================================
-- SISTEMA DE GESTIÓN DE CONTRATOS DIGITALES CON FIRMA ELECTRÓNICA
-- Schema inicial de base de datos
-- ============================================================================

CREATE DATABASE IF NOT EXISTS contratos_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE contratos_db;

SET sql_mode = '';

-- ============================================================================
-- Tabla de administradores
-- ============================================================================
CREATE TABLE admins (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    nombre VARCHAR(255) NOT NULL,
    activo TINYINT(1) DEFAULT 1,
    ultimo_login TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_activo (activo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Tabla de contratos
-- ============================================================================
CREATE TABLE contracts (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    admin_id INT UNSIGNED NOT NULL,
    titulo VARCHAR(255) NOT NULL,
    contenido TEXT NOT NULL,
    token VARCHAR(64) NOT NULL UNIQUE,

    -- Datos del cliente
    cliente_nombre VARCHAR(255),
    cliente_email VARCHAR(255),
    cliente_telefono VARCHAR(50),

    -- Estado y expiración
    estado ENUM('pendiente', 'firmado', 'expirado', 'cancelado') DEFAULT 'pendiente',
    expira_at DATETIME NOT NULL,

    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE,
    INDEX idx_token (token),
    INDEX idx_estado (estado),
    INDEX idx_expira (expira_at),
    INDEX idx_admin_estado (admin_id, estado),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Tabla de firmas (evidencias de firma digital)
-- ============================================================================
CREATE TABLE signatures (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    contract_id INT UNSIGNED NOT NULL,

    -- Archivos capturados
    firma_imagen_path VARCHAR(500) NOT NULL,
    selfie_path VARCHAR(500) NOT NULL,
    documento_frontal_path VARCHAR(500) NOT NULL,
    documento_trasero_path VARCHAR(500) NOT NULL,

    -- Datos del documento
    tipo_documento ENUM('CC', 'TI', 'CE', 'PP', 'DNI', 'RUT', 'PAS') NOT NULL,
    numero_documento VARCHAR(50) NOT NULL,

    -- Datos de auditoría
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT,
    ubicacion_lat DECIMAL(10, 8),
    ubicacion_lng DECIMAL(11, 8),
    ubicacion_precision INT,

    -- PDF generado
    pdf_path VARCHAR(500),
    pdf_hash VARCHAR(64),

    -- Timestamps
    firmado_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- Metadata adicional (JSON)
    metadata JSON,

    FOREIGN KEY (contract_id) REFERENCES contracts(id) ON DELETE CASCADE,
    INDEX idx_contract (contract_id),
    INDEX idx_documento (numero_documento),
    INDEX idx_firmado (firmado_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Tabla de intentos de firma (seguridad y auditoría)
-- ============================================================================
CREATE TABLE signature_attempts (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    contract_id INT UNSIGNED NOT NULL,

    -- Datos del intento
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT,
    exitoso TINYINT(1) DEFAULT 0,
    razon_fallo VARCHAR(255),
    paso_fallido VARCHAR(50),

    -- Timestamp
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (contract_id) REFERENCES contracts(id) ON DELETE CASCADE,
    INDEX idx_contract_ip (contract_id, ip_address),
    INDEX idx_created (created_at),
    INDEX idx_exitoso (exitoso)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Tabla de logs de acceso y actividad
-- ============================================================================
CREATE TABLE access_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

    -- Tipo de log
    tipo ENUM('admin', 'firma', 'api', 'security', 'error') NOT NULL,

    -- Identificadores
    usuario_id INT UNSIGNED,
    contract_id INT UNSIGNED,

    -- Acción realizada
    accion VARCHAR(100) NOT NULL,
    descripcion TEXT,

    -- Datos de conexión
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT,

    -- Detalles adicionales
    detalles TEXT,
    nivel ENUM('debug', 'info', 'warning', 'error', 'critical') DEFAULT 'info',

    -- Timestamp
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_tipo_created (tipo, created_at),
    INDEX idx_ip (ip_address),
    INDEX idx_accion (accion),
    INDEX idx_nivel (nivel),
    INDEX idx_usuario (usuario_id),
    INDEX idx_contract (contract_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Tabla de sesiones de administradores
-- ============================================================================
CREATE TABLE sessions (
    id VARCHAR(128) PRIMARY KEY,
    admin_id INT UNSIGNED NOT NULL,

    -- Datos de sesión
    token VARCHAR(255) NOT NULL UNIQUE,
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT,

    -- Timestamps
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE,
    INDEX idx_admin (admin_id),
    INDEX idx_token (token),
    INDEX idx_last_activity (last_activity),
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Tabla de tokens de recuperación de contraseña
-- ============================================================================
CREATE TABLE password_resets (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    admin_id INT UNSIGNED NOT NULL,
    token VARCHAR(255) NOT NULL,
    usado TINYINT(1) DEFAULT 0,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE,
    INDEX idx_token (token),
    INDEX idx_expires (expires_at),
    INDEX idx_admin_usado (admin_id, usado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Tabla de configuraciones del sistema
-- ============================================================================
CREATE TABLE system_settings (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    clave VARCHAR(100) NOT NULL UNIQUE,
    valor TEXT,
    tipo ENUM('string', 'integer', 'boolean', 'json') DEFAULT 'string',
    descripcion TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_clave (clave)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Insertar datos iniciales
-- ============================================================================

-- Admin por defecto
-- Email: admin@sistema.com
-- Password: Admin123!
-- IMPORTANTE: Si cambias esta contraseña, ejecuta: php scripts/create-admin.php
INSERT INTO admins (email, password, nombre) VALUES
('admin@sistema.com', '$2y$12$rl77lsxXQUdyKvd3jlVR5ePxH6DmfsFgy6y/JuOo36xR/wt3dB34.', 'Administrador');

-- Configuraciones por defecto
INSERT INTO system_settings (clave, valor, tipo, descripcion) VALUES
('site_name', 'Sistema de Contratos Digitales', 'string', 'Nombre del sitio'),
('contract_expiration_default', '24', 'integer', 'Expiración por defecto de contratos (horas)'),
('max_signature_attempts', '5', 'integer', 'Máximo de intentos de firma fallidos'),
('require_geolocation', 'true', 'boolean', 'Requerir geolocalización en la firma'),
('email_notifications', 'true', 'boolean', 'Enviar notificaciones por email'),
('maintenance_mode', 'false', 'boolean', 'Modo de mantenimiento');

-- ============================================================================
-- Triggers de auditoría
-- ============================================================================

DELIMITER $$

-- Trigger para registrar cambios de estado en contratos
CREATE TRIGGER contract_status_change
AFTER UPDATE ON contracts
FOR EACH ROW
BEGIN
    IF OLD.estado != NEW.estado THEN
        INSERT INTO access_logs (tipo, contract_id, accion, descripcion, ip_address, nivel)
        VALUES ('admin', NEW.id, 'contract_status_change',
                CONCAT('Estado cambiado de ', OLD.estado, ' a ', NEW.estado),
                '127.0.0.1', 'info');
    END IF;
END$$

-- Trigger para actualizar estado a expirado
CREATE EVENT expire_contracts
ON SCHEDULE EVERY 1 HOUR
DO
    UPDATE contracts
    SET estado = 'expirado'
    WHERE estado = 'pendiente'
    AND expira_at < NOW();

DELIMITER ;

-- ============================================================================
-- Vistas útiles
-- ============================================================================

-- Vista de contratos con información del admin
CREATE VIEW v_contracts_full AS
SELECT
    c.*,
    a.nombre as admin_nombre,
    a.email as admin_email,
    s.id as signature_id,
    s.firmado_at,
    s.tipo_documento,
    s.numero_documento,
    s.pdf_path,
    CASE
        WHEN c.estado = 'firmado' THEN 'Firmado'
        WHEN c.estado = 'expirado' THEN 'Expirado'
        WHEN c.estado = 'cancelado' THEN 'Cancelado'
        WHEN c.expira_at < NOW() THEN 'Expirado'
        ELSE 'Pendiente'
    END as estado_display
FROM contracts c
INNER JOIN admins a ON c.admin_id = a.id
LEFT JOIN signatures s ON c.id = s.contract_id;

-- Vista de estadísticas por admin
CREATE VIEW v_admin_stats AS
SELECT
    a.id,
    a.nombre,
    a.email,
    COUNT(c.id) as total_contratos,
    SUM(CASE WHEN c.estado = 'firmado' THEN 1 ELSE 0 END) as contratos_firmados,
    SUM(CASE WHEN c.estado = 'pendiente' THEN 1 ELSE 0 END) as contratos_pendientes,
    SUM(CASE WHEN c.estado = 'expirado' THEN 1 ELSE 0 END) as contratos_expirados,
    SUM(CASE WHEN c.estado = 'cancelado' THEN 1 ELSE 0 END) as contratos_cancelados
FROM admins a
LEFT JOIN contracts c ON a.id = c.admin_id
GROUP BY a.id, a.nombre, a.email;

-- ============================================================================
-- Mensaje de finalización
-- ============================================================================

SELECT 'Base de datos creada exitosamente' as mensaje,
       'Usuario: admin@sistema.com' as usuario,
       'Password: Admin123!' as password,
       'IMPORTANTE: Cambiar la contraseña después del primer login' as nota;
