-- =========================================================
-- Sistema de Consultas - Base de Datos
-- =========================================================
-- Ejecutar este script en phpMyAdmin (pestaña SQL) o con:
--   mysql -u root -p < schema.sql
-- =========================================================

CREATE DATABASE IF NOT EXISTS sistema_consultas
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE sistema_consultas;

-- ---------------------------------------------------------
-- Tabla de usuarios (login del sistema)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS usuarios (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    usuario         VARCHAR(50)  NOT NULL UNIQUE,
    password_hash   VARCHAR(255) NOT NULL,
    nombre_completo VARCHAR(150) NOT NULL,
    rol             ENUM('ADMIN','CONSULTA') NOT NULL DEFAULT 'CONSULTA',
    activo          TINYINT(1)   NOT NULL DEFAULT 1,
    ultimo_acceso   DATETIME     NULL,
    creado_en       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Tabla principal de expedientes / ordenes
-- Concentra los 3 archivos: Robo de vehiculo, Alto impacto,
-- Resto de delitos, en una sola tabla con columna "categoria"
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS expedientes (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    categoria       ENUM('ROBO_DE_VEHICULO','ALTO_IMPACTO','RESTO_DE_DELITOS') NOT NULL,
    expediente      VARCHAR(100) NULL COMMENT 'Causa / Carpeta Administrativa / Expediente',
    tipo_orden      VARCHAR(100) NULL,
    fecha_orden     DATE NULL,
    juzgado         VARCHAR(150) NULL,
    nombre          VARCHAR(200) NOT NULL,
    municipio       VARCHAR(150) NULL COMMENT 'Municipio posible localizacion validado',
    delito          VARCHAR(255) NULL COMMENT 'Desglose resto de delitos',
    creado_en       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_nombre (nombre),
    FULLTEXT INDEX ftx_nombre (nombre)
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Bitacora de consultas (opcional, auditoria de busquedas)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS bitacora_consultas (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    usuario_id      INT NOT NULL,
    termino_buscado VARCHAR(200) NOT NULL,
    resultados      INT NOT NULL DEFAULT 0,
    fecha           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Usuario administrador por defecto
-- Usuario: admin   Password: Admin2025*
-- (el hash se genera con password_hash() de PHP - ver nota abajo)
-- ---------------------------------------------------------
-- Este INSERT se hace tambien desde crear_admin.php para asegurar
-- que el hash sea compatible con la version de PHP del servidor.
-- Si prefieres hacerlo manual, genera el hash con:
--   php -r "echo password_hash('Admin2025*', PASSWORD_DEFAULT);"
-- y reemplaza abajo:
-- INSERT INTO usuarios (usuario, password_hash, nombre_completo, rol)
-- VALUES ('admin', '$2y$10$REEMPLAZAR_HASH_AQUI', 'Administrador', 'ADMIN');