SET NAMES utf8mb4;
SET time_zone = '+00:00';
SET FOREIGN_KEY_CHECKS = 0;

-- =====================================================
-- ROLES
-- =====================================================
CREATE TABLE IF NOT EXISTS roles (
    id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(255) NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- USUARIOS INTERNOS
-- =====================================================
CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_id SMALLINT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    mfa_enabled TINYINT(1) NOT NULL DEFAULT 0,
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- CANALES
-- =====================================================
CREATE TABLE IF NOT EXISTS channels (
    id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(255) NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TIPOS DE TITULAR
-- =====================================================
CREATE TABLE IF NOT EXISTS subject_types (
    id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(255) NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TIPOS DE DERECHO
-- =====================================================
CREATE TABLE IF NOT EXISTS right_types (
    id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(150) NOT NULL,
    legal_reference VARCHAR(255) NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- ESTADOS DE SOLICITUD
-- =====================================================
CREATE TABLE IF NOT EXISTS request_statuses (
    id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(255) NULL,
    category VARCHAR(50) NOT NULL DEFAULT 'gestion',
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TITULARES DE DATOS
-- =====================================================
CREATE TABLE IF NOT EXISTS data_subjects (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    subject_type_id SMALLINT UNSIGNED NOT NULL,
    full_name VARCHAR(200) NOT NULL,
    document_type VARCHAR(30) NULL,
    document_number_hash CHAR(64) NULL,
    document_number_encrypted VARBINARY(1000) NULL,
    email VARCHAR(190) NULL,
    email_hash CHAR(64) NULL,
    phone VARCHAR(50) NULL,
    external_code VARCHAR(100) NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_data_subjects_document_hash (document_number_hash),
    KEY idx_data_subjects_email_hash (email_hash),
    CONSTRAINT fk_data_subjects_type FOREIGN KEY (subject_type_id) REFERENCES subject_types(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- SOLICITUDES DE DERECHOS
-- =====================================================
CREATE TABLE IF NOT EXISTS rights_requests (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid CHAR(36) NOT NULL UNIQUE,
    unique_code VARCHAR(30) NOT NULL UNIQUE,
    data_subject_id BIGINT UNSIGNED NOT NULL,
    channel_id SMALLINT UNSIGNED NOT NULL,
    right_type_id SMALLINT UNSIGNED NOT NULL,
    status_id SMALLINT UNSIGNED NOT NULL,
    description TEXT NULL,
    requested_data TEXT NULL,
    risk_level ENUM('bajo','medio','alto','muy_alto') NULL,
    criticality ENUM('normal','alta','critica') NULL,
    assigned_to BIGINT UNSIGNED NULL,
    received_at DATETIME NOT NULL,
    ack_sent_at DATETIME NULL,
    authenticated_at DATETIME NULL,
    classified_at DATETIME NULL,
    initial_deadline_at DATETIME NOT NULL,
    extension_requested_at DATETIME NULL,
    extension_approved_at DATETIME NULL,
    extended_deadline_at DATETIME NULL,
    response_due_at DATETIME NULL,
    response_sent_at DATETIME NULL,
    decision VARCHAR(50) NULL,
    denial_reason_id SMALLINT UNSIGNED NULL,
    execution_status VARCHAR(50) NULL,
    closed_at DATETIME NULL,
    archived_at DATETIME NULL,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_rights_requests_status (status_id),
    KEY idx_rights_requests_received_at (received_at),
    KEY idx_rights_requests_initial_deadline (initial_deadline_at),
    KEY idx_rights_requests_response_due (response_due_at),
    CONSTRAINT fk_rights_requests_subject FOREIGN KEY (data_subject_id) REFERENCES data_subjects(id),
    CONSTRAINT fk_rights_requests_channel FOREIGN KEY (channel_id) REFERENCES channels(id),
    CONSTRAINT fk_rights_requests_right_type FOREIGN KEY (right_type_id) REFERENCES right_types(id),
    CONSTRAINT fk_rights_requests_status FOREIGN KEY (status_id) REFERENCES request_statuses(id),
    CONSTRAINT fk_rights_requests_assigned_user FOREIGN KEY (assigned_to) REFERENCES users(id),
    CONSTRAINT fk_rights_requests_created_user FOREIGN KEY (created_by) REFERENCES users(id),
    CONSTRAINT fk_rights_requests_updated_user FOREIGN KEY (updated_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- EVENTOS / TRAZABILIDAD
-- =====================================================
CREATE TABLE IF NOT EXISTS request_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    request_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NULL,
    action VARCHAR(100) NOT NULL,
    previous_status_id SMALLINT UNSIGNED NULL,
    new_status_id SMALLINT UNSIGNED NULL,
    payload_json JSON NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    event_hash CHAR(64) NOT NULL,
    previous_event_hash CHAR(64) NULL,
    created_at_utc DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_request_events_request (request_id),
    KEY idx_request_events_user (user_id),
    CONSTRAINT fk_request_events_request FOREIGN KEY (request_id) REFERENCES rights_requests(id),
    CONSTRAINT fk_request_events_user FOREIGN KEY (user_id) REFERENCES users(id),
    CONSTRAINT fk_request_events_previous_status FOREIGN KEY (previous_status_id) REFERENCES request_statuses(id),
    CONSTRAINT fk_request_events_new_status FOREIGN KEY (new_status_id) REFERENCES request_statuses(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TIPOS DE EVIDENCIA
-- =====================================================
CREATE TABLE IF NOT EXISTS evidence_types (
    id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(200) NOT NULL,
    legal_reference VARCHAR(255) NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- EVIDENCIAS DE SOLICITUDES
-- =====================================================
CREATE TABLE IF NOT EXISTS request_evidences (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    request_id BIGINT UNSIGNED NOT NULL,
    evidence_type_id SMALLINT UNSIGNED NOT NULL,
    file_name VARCHAR(255) NULL,
    file_path VARCHAR(500) NULL,
    file_hash_sha256 CHAR(64) NULL,
    mime_type VARCHAR(150) NULL,
    file_size BIGINT UNSIGNED NULL,
    text_content LONGTEXT NULL,
    description TEXT NULL,
    captured_at DATETIME NOT NULL,
    captured_by BIGINT UNSIGNED NULL,
    is_immutable TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_request_evidences_request (request_id),
    KEY idx_request_evidences_type (evidence_type_id),
    CONSTRAINT fk_request_evidences_request FOREIGN KEY (request_id) REFERENCES rights_requests(id),
    CONSTRAINT fk_request_evidences_type FOREIGN KEY (evidence_type_id) REFERENCES evidence_types(id),
    CONSTRAINT fk_request_evidences_user FOREIGN KEY (captured_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- AUDITORÍA GENERAL
-- =====================================================
CREATE TABLE IF NOT EXISTS audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(100) NULL,
    entity_id BIGINT UNSIGNED NULL,
    old_values_json JSON NULL,
    new_values_json JSON NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    created_at_utc DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_audit_logs_user (user_id),
    KEY idx_audit_logs_entity (entity_type, entity_id),
    CONSTRAINT fk_audit_logs_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- CONFIGURACIÓN
-- =====================================================
CREATE TABLE IF NOT EXISTS settings (
    id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT NULL,
    description VARCHAR(255) NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- SEED: ROLES
-- =====================================================
INSERT INTO roles (code, name, description) VALUES
('admin', 'Administrador plataforma', 'Administra usuarios, configuración y soporte técnico'),
('dpo', 'DPO / Encargado de Privacidad', 'Dueño del proceso de privacidad'),
('analyst', 'Analista de privacidad', 'Gestiona casos y solicitudes'),
('legal', 'Legal', 'Aprueba respuestas complejas y denegaciones'),
('compliance', 'Compliance / Fiscalía', 'Administrador del canal'),
('hr', 'Recursos Humanos', 'Soporte de autenticación y datos de trabajadores'),
('customer_service', 'Servicio al Cliente', 'Atiende solicitudes de clientes'),
('data_owner', 'Dueño de datos', 'Responsable de búsqueda y ejecución en bases específicas'),
('it', 'TI', 'Soporte tecnológico'),
('auditor', 'Auditor', 'Acceso de solo lectura'),
('committee', 'Comité de Privacidad', 'Supervisión y reportes');

-- =====================================================
-- SEED: CANALES
-- =====================================================
INSERT INTO channels (code, name, description) VALUES
('web_publico', 'Formulario web público', 'Canal principal público'),
('intranet', 'Formulario intranet', 'Canal para trabajadores'),
('correo', 'Correo electrónico', 'Canal subsidiario'),
('papel', 'Papel', 'Carta o formulario físico'),
('telefono', 'Teléfono', 'Solo si queda respaldo o grabación'),
('presencial', 'Presencial', 'Atención presencial'),
('otro', 'Otro medio equivalente', 'Medio alternativo autorizado');

-- =====================================================
-- SEED: TIPOS DE TITULAR
-- =====================================================
INSERT INTO subject_types (code, name, description) VALUES
('trabajador', 'Trabajador', 'Trabajador vigente'),
('extrabajador', 'Extrabajador', 'Ex colaborador'),
('postulante', 'Postulante', 'Persona en proceso de selección'),
('cliente', 'Cliente o consumidor', 'Cliente, usuario o consumidor'),
('proveedor', 'Proveedor o contratista', 'Persona natural vinculada a proveedor'),
('representante_legal', 'Representante legal o contacto comercial', 'Representante de persona jurídica'),
('visitante', 'Visitante o usuario de plataformas', 'Visitante presencial o digital'),
('otro', 'Otra persona natural', 'Cualquier otra persona cuyos datos sean tratados');

-- =====================================================
-- SEED: DERECHOS
-- =====================================================
INSERT INTO right_types (code, name, legal_reference) VALUES
('acceso', 'Acceso', 'Ley 21.719'),
('rectificacion', 'Rectificación', 'Ley 21.719'),
('supresion', 'Supresión', 'Ley 21.719'),
('oposicion', 'Oposición', 'Ley 21.719'),
('portabilidad', 'Portabilidad', 'Ley 21.719'),
('bloqueo_temporal', 'Bloqueo temporal', 'Ley 21.719'),
('retiro_consentimiento', 'Retiro del consentimiento', 'Ley 21.719'),
('decision_automatizada', 'Revisión de decisiones automatizadas', 'Ley 21.719');

-- =====================================================
-- SEED: ESTADOS
-- =====================================================
INSERT INTO request_statuses (code, name, description, category) VALUES
('recibido', 'Recibido', 'Solicitud recibida', 'inicial'),
('acuse_enviado', 'Acuse enviado', 'Acuse de recibo enviado', 'inicial'),
('pendiente_autenticacion', 'Pendiente autenticación', 'Falta validar identidad', 'gestion'),
('autenticado', 'Autenticado', 'Identidad validada', 'gestion'),
('en_clasificacion', 'En clasificación', 'Clasificando derecho y riesgo', 'gestion'),
('clasificado', 'Clasificado', 'Derecho clasificado', 'gestion'),
('busqueda_interna', 'Búsqueda interna', 'Buscando datos en áreas responsables', 'gestion'),
('evaluacion_juridica', 'Evaluación jurídica', 'Análisis legal en curso', 'gestion'),
('prorroga_solicitada', 'Prórroga solicitada', 'Prórroga en evaluación', 'gestion'),
('prorroga_aprobada', 'Prórroga aprobada', 'Prórroga vigente', 'gestion'),
('respuesta_en_preparacion', 'Respuesta en preparación', 'Redactando respuesta', 'gestion'),
('respuesta_aprobada', 'Respuesta aprobada', 'Respuesta aprobada para envío', 'gestion'),
('respuesta_enviada', 'Respuesta enviada', 'Respuesta enviada al titular', 'gestion'),
('ejecucion_pendiente', 'Ejecución pendiente', 'Falta ejecutar el derecho', 'gestion'),
('ejecucion_en_curso', 'Ejecución en curso', 'Ejecución en proceso', 'gestion'),
('ejecutado', 'Ejecutado', 'Derecho ejecutado', 'gestion'),
('cerrado', 'Cerrado', 'Caso cerrado con respaldo', 'cerrado'),
('denegado_fundado', 'Denegado fundado', 'Denegación total o parcial fundada', 'cerrado'),
('inexistencia_datos', 'Inexistencia de datos', 'No se encontraron datos del titular', 'cerrado'),
('archivado', 'Archivado', 'Caso archivado', 'cerrado');

-- =====================================================
-- SEED: EVIDENCIAS OBLIGATORIAS
-- =====================================================
INSERT INTO evidence_types (code, name, legal_reference) VALUES
('EV-01', 'Solicitud original', 'Canal Institucional de Derechos'),
('EV-02', 'Fecha y canal de recepción', 'Canal Institucional de Derechos'),
('EV-03', 'Evidencia de autenticación', 'Canal Institucional de Derechos'),
('EV-04', 'Acuse de recibo', 'Canal Institucional de Derechos'),
('EV-05', 'Áreas consultadas', 'Canal Institucional de Derechos'),
('EV-06', 'Búsquedas realizadas', 'Canal Institucional de Derechos'),
('EV-07', 'Datos identificados', 'Canal Institucional de Derechos'),
('EV-08', 'Análisis jurídico', 'Canal Institucional de Derechos'),
('EV-09', 'Decisión adoptada', 'Canal Institucional de Derechos'),
('EV-10', 'Aprobaciones', 'Canal Institucional de Derechos'),
('EV-11', 'Respuesta enviada', 'Canal Institucional de Derechos'),
('EV-12', 'Comprobante de recepción o despacho', 'Canal Institucional de Derechos'),
('EV-13', 'Evidencia de ejecución', 'Canal Institucional de Derechos'),
('EV-14', 'Justificación de prórroga, rechazo o inexistencia', 'Canal Institucional de Derechos');

-- =====================================================
-- SEED: CONFIGURACIÓN INICIAL
-- =====================================================
INSERT INTO settings (setting_key, setting_value, description) VALUES
('legal_deadline_days', '30', 'Plazo legal de respuesta en días corridos'),
('extension_max_days', '30', 'Días máximos de prórroga'),
('max_extensions', '1', 'Cantidad máxima de prórrogas'),
('ack_deadline_hours', '24', 'Plazo objetivo para acuse de recibo en horas'),
('timezone', 'America/Santiago', 'Zona horaria institucional'),
('dpo_email', 'privacidad@example.com', 'Correo del Encargado de Privacidad / DPO'),
('agency_name', 'Agencia de Protección de Datos Personales', 'Nombre de la autoridad de control'),
('case_classification', 'Interno, no apto para su distribución', 'Clasificación de la información del canal'),
('retention_years', '5', 'Años sugeridos de retención del expediente, sujeto a definición legal');

SET FOREIGN_KEY_CHECKS = 1;

