-- Esquema de base de datos — Sistema de Diagnostico de Tramites y Servicios Municipales
-- Sector Turistico, Valladolid e Izamal (ver seccion 6 del Documento Tecnico de Arquitectura)
-- MySQL 8.0 / MariaDB 10.4+

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS municipios (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    UNIQUE KEY uq_municipios_nombre (nombre)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tramites (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    codigo VARCHAR(10) NOT NULL,
    nombre VARCHAR(150) NOT NULL,
    UNIQUE KEY uq_tramites_codigo (codigo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS usuarios (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(150) NOT NULL,
    correo VARCHAR(150) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    rol ENUM('Administrador', 'Consultor de procesos', 'Analista de captura') NOT NULL,
    intentos_fallidos TINYINT UNSIGNED NOT NULL DEFAULT 0,
    bloqueado_hasta DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_usuarios_correo (correo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS diagnosticos (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    municipio_id INT UNSIGNED NOT NULL,
    tramite_id INT UNSIGNED NOT NULL,
    estado ENUM('en captura', 'en validacion', 'cerrado') NOT NULL DEFAULT 'en captura',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_diagnosticos_municipio_tramite (municipio_id, tramite_id),
    CONSTRAINT fk_diagnosticos_municipio FOREIGN KEY (municipio_id) REFERENCES municipios(id),
    CONSTRAINT fk_diagnosticos_tramite FOREIGN KEY (tramite_id) REFERENCES tramites(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS fichas_retys (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    diagnostico_id INT UNSIGNED NOT NULL,
    requisitos TEXT NULL,
    tiempo_normado_dias DECIMAL(6,2) NULL,
    costo_derechos DECIMAL(10,2) NULL,
    responsable VARCHAR(150) NULL,
    fundamento_juridico TEXT NULL,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_fichas_retys_diagnostico FOREIGN KEY (diagnostico_id) REFERENCES diagnosticos(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS procesos_asis (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    diagnostico_id INT UNSIGNED NOT NULL,
    orden SMALLINT UNSIGNED NOT NULL,
    actor VARCHAR(150) NOT NULL,
    paso VARCHAR(255) NOT NULL,
    tiempo_promedio_horas DECIMAL(8,2) NULL,
    punto_friccion BOOLEAN NOT NULL DEFAULT FALSE,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_procesos_asis_diagnostico FOREIGN KEY (diagnostico_id) REFERENCES diagnosticos(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS indicadores_ca (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    codigo VARCHAR(10) NOT NULL,
    nombre VARCHAR(255) NOT NULL,
    unidad VARCHAR(50) NOT NULL,
    formula TEXT NULL,
    umbral_verde VARCHAR(100) NULL,
    umbral_amarillo VARCHAR(100) NULL,
    umbral_rojo VARCHAR(100) NULL,
    plantilla_interpretativa TEXT NULL,
    UNIQUE KEY uq_indicadores_ca_codigo (codigo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS diagnostico_indicadores (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    diagnostico_id INT UNSIGNED NOT NULL,
    indicador_id INT UNSIGNED NOT NULL,
    valor VARCHAR(255) NULL,
    fuente VARCHAR(255) NULL,
    metodo VARCHAR(255) NULL,
    semaforo ENUM('verde', 'amarillo', 'rojo') NULL,
    -- Snapshot de formula/umbrales vigentes al momento del calculo (seccion 7.3):
    -- preserva la trazabilidad si el catalogo se edita despues.
    formula_snapshot TEXT NULL,
    umbrales_snapshot VARCHAR(255) NULL,
    calculado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_diagnostico_indicador (diagnostico_id, indicador_id),
    CONSTRAINT fk_diag_ind_diagnostico FOREIGN KEY (diagnostico_id) REFERENCES diagnosticos(id),
    CONSTRAINT fk_diag_ind_indicador FOREIGN KEY (indicador_id) REFERENCES indicadores_ca(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS hallazgos (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    diagnostico_id INT UNSIGNED NOT NULL,
    clasificacion ENUM('critico', 'moderado', 'menor') NOT NULL,
    descripcion TEXT NOT NULL,
    evidencia TEXT NULL,
    causa_raiz TEXT NULL,
    impacto_ciudadano TEXT NULL,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_hallazgos_diagnostico FOREIGN KEY (diagnostico_id) REFERENCES diagnosticos(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS quick_wins (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    diagnostico_id INT UNSIGNED NOT NULL,
    accion VARCHAR(255) NOT NULL,
    impacto ENUM('alto', 'medio', 'bajo') NOT NULL,
    tiempo_implementacion VARCHAR(100) NULL,
    responsable VARCHAR(150) NULL,
    estado ENUM('propuesto', 'aceptado', 'implementado') NOT NULL DEFAULT 'propuesto',
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_quick_wins_diagnostico FOREIGN KEY (diagnostico_id) REFERENCES diagnosticos(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS agenda_simplificacion (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    diagnostico_id INT UNSIGNED NOT NULL,
    tipo ENUM('reforma normativa', 'cambio operativo', 'digitalizacion') NOT NULL,
    descripcion TEXT NOT NULL,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_agenda_diagnostico FOREIGN KEY (diagnostico_id) REFERENCES diagnosticos(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS evidencias (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    diagnostico_id INT UNSIGNED NOT NULL,
    archivo VARCHAR(255) NOT NULL,
    tipo VARCHAR(50) NULL,
    fuente_dato VARCHAR(255) NULL,
    consentimiento_informado BOOLEAN NOT NULL DEFAULT FALSE,
    usuario_id INT UNSIGNED NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_evidencias_diagnostico FOREIGN KEY (diagnostico_id) REFERENCES diagnosticos(id),
    CONSTRAINT fk_evidencias_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS reportes_generados (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    diagnostico_id INT UNSIGNED NULL,
    usuario_id INT UNSIGNED NOT NULL,
    formato ENUM('docx', 'pdf') NOT NULL,
    ruta_archivo VARCHAR(255) NOT NULL,
    generado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_reportes_diagnostico FOREIGN KEY (diagnostico_id) REFERENCES diagnosticos(id),
    CONSTRAINT fk_reportes_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bitacora (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    usuario_id INT UNSIGNED NOT NULL,
    tabla_afectada VARCHAR(100) NOT NULL,
    registro_id INT UNSIGNED NOT NULL,
    accion VARCHAR(50) NOT NULL,
    valor_anterior TEXT NULL,
    valor_nuevo TEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_bitacora_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;