-- Base de datos para Sistema de Certificados de Residencia
CREATE DATABASE IF NOT EXISTS certificados_residencia CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE certificados_residencia;

-- Tabla de usuarios administradores
CREATE TABLE IF NOT EXISTS usuarios (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    nombre_completo VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    cargo VARCHAR(100) DEFAULT 'Administrador',
    activo TINYINT(1) DEFAULT 1,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ultimo_acceso TIMESTAMP NULL,
    INDEX idx_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de solicitudes de certificados
CREATE TABLE IF NOT EXISTS solicitudes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    numero_solicitud VARCHAR(20) NOT NULL UNIQUE,
    nombre_completo VARCHAR(150) NOT NULL,
    rut VARCHAR(12) NOT NULL,
    direccion TEXT NOT NULL,
    telefono VARCHAR(20) NOT NULL,
    email VARCHAR(100) NOT NULL,
    motivo TEXT NOT NULL,
    estado ENUM('pendiente', 'en_proceso', 'aprobada', 'rechazada', 'enviada') DEFAULT 'pendiente',
    fecha_solicitud TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_procesada TIMESTAMP NULL,
    usuario_procesado INT NULL,
    observaciones TEXT NULL,
    ip_solicitante VARCHAR(45) NULL,
    INDEX idx_estado (estado),
    INDEX idx_fecha (fecha_solicitud),
    INDEX idx_rut (rut),
    INDEX idx_numero (numero_solicitud),
    FOREIGN KEY (usuario_procesado) REFERENCES usuarios(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de adjuntos
CREATE TABLE IF NOT EXISTS adjuntos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    solicitud_id INT NOT NULL,
    nombre_archivo VARCHAR(255) NOT NULL,
    ruta_archivo VARCHAR(500) NOT NULL,
    tipo_mime VARCHAR(100) NOT NULL,
    tamano_bytes INT NOT NULL,
    fecha_subida TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_solicitud (solicitud_id),
    FOREIGN KEY (solicitud_id) REFERENCES solicitudes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de certificados generados
CREATE TABLE IF NOT EXISTS certificados (
    id INT AUTO_INCREMENT PRIMARY KEY,
    solicitud_id INT NOT NULL,
    numero_certificado VARCHAR(20) NOT NULL UNIQUE,
    ruta_pdf VARCHAR(500) NOT NULL,
    hash_documento VARCHAR(64) NOT NULL,
    firmado TINYINT(1) DEFAULT 0,
    fecha_generacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_firma TIMESTAMP NULL,
    usuario_generador INT NOT NULL,
    enviado_correo TINYINT(1) DEFAULT 0,
    fecha_envio_correo TIMESTAMP NULL,
    INDEX idx_solicitud (solicitud_id),
    INDEX idx_numero (numero_certificado),
    FOREIGN KEY (solicitud_id) REFERENCES solicitudes(id) ON DELETE CASCADE,
    FOREIGN KEY (usuario_generador) REFERENCES usuarios(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de logs de actividad
CREATE TABLE IF NOT EXISTS logs_actividad (
    id INT AUTO_INCREMENT PRIMARY KEY,
    usuario_id INT NULL,
    solicitud_id INT NULL,
    accion VARCHAR(100) NOT NULL,
    descripcion TEXT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    fecha_hora TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_usuario (usuario_id),
    INDEX idx_solicitud (solicitud_id),
    INDEX idx_fecha (fecha_hora),
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE SET NULL,
    FOREIGN KEY (solicitud_id) REFERENCES solicitudes(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de configuración del sistema
CREATE TABLE IF NOT EXISTS configuracion (
    id INT AUTO_INCREMENT PRIMARY KEY,
    clave VARCHAR(100) NOT NULL UNIQUE,
    valor TEXT NOT NULL,
    descripcion TEXT NULL,
    tipo ENUM('texto', 'numero', 'booleano', 'json') DEFAULT 'texto',
    fecha_modificacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insertar usuario administrador por defecto (password: admin123)
-- Hash generado con: password_hash('admin123', PASSWORD_BCRYPT)
INSERT INTO usuarios (username, password, nombre_completo, email, cargo) 
VALUES ('admin', '$2y$10$eP8F5qGJxGMOZKEXvLlKQ.nLLh9s5OGLcpZPHKxQVJa7OxW6Y8KUq', 'Administrador del Sistema', 'admin@municipio.cl', 'Secretario Municipal');

-- Insertar configuraciones iniciales
INSERT INTO configuracion (clave, valor, descripcion, tipo) VALUES
('nombre_municipio', 'Ilustre Municipalidad de Loncoche', 'Nombre de la municipalidad', 'texto'),
('direccion_municipio', 'O\'Higgins 560, Loncoche', 'Dirección de la municipalidad', 'texto'),
('comuna', 'Loncoche', 'Comuna', 'texto'),
('region', 'Región de La Araucanía', 'Región', 'texto'),
('telefono_municipio', '+56 45 2491100', 'Teléfono de contacto', 'texto'),
('email_municipio', 'contacto@municipalidadloncoche.cl', 'Email de contacto', 'texto'),
('smtp_host', 'smtp.gmail.com', 'Servidor SMTP', 'texto'),
('smtp_port', '587', 'Puerto SMTP', 'numero'),
('smtp_user', '', 'Usuario SMTP', 'texto'),
('smtp_password', '', 'Contraseña SMTP', 'texto'),
('smtp_secure', 'tls', 'Seguridad SMTP (tls/ssl)', 'texto'),
('smtp_from_email', 'noreply@municipalidadloncoche.cl', 'Email remitente', 'texto'),
('smtp_from_name', 'Municipalidad de Loncoche', 'Nombre remitente', 'texto'),
('max_file_size', '5242880', 'Tamaño máximo de archivo en bytes (5MB)', 'numero'),
('allowed_extensions', 'pdf,jpg,jpeg,png', 'Extensiones de archivo permitidas', 'texto'),
('captcha_site_key', '', 'Google reCAPTCHA Site Key', 'texto'),
('captcha_secret_key', '', 'Google reCAPTCHA Secret Key', 'texto'),
('certificado_vigencia_dias', '90', 'Días de vigencia del certificado', 'numero');
