-- labSoft — Schema reconstruido
-- Fuente: BD/laboratorios_myAdmin.sql (2010-03-27) + correcciones del código
-- Cambios respecto al original:
--   · Charset latin1 → utf8mb4
--   · usuario.pass → password_hash VARCHAR(255)
--   · muestra: añade idcliente (faltaba en original, presente en código)
--   · analisis: añade campos_resultado JSON (generador dinámico, Día 12)
--   · ventas.idmuestra VARCHAR eliminado → tabla venta_muestra (junction)
--   · Nueva tabla resultado_detalle (reemplaza archivos XML)
--   · Tablas de prueba removidas: employees, mate_columns
--   · consecutivo_c fusionada con consecutivo (mismo propósito)

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET FOREIGN_KEY_CHECKS = 0;

CREATE DATABASE IF NOT EXISTS `laboratorios_bello`
    DEFAULT CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE `laboratorios_bello`;

-- ─────────────────────────────────────────────────────────────────────────────
-- sucursal  (va primero, otras tablas la referencian)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `sucursal` (
    `idsucursal` int          NOT NULL AUTO_INCREMENT,
    `nombre`     varchar(100) NOT NULL,
    `direccion`  varchar(150) DEFAULT NULL,
    `telefono`   varchar(25)  DEFAULT NULL,
    PRIMARY KEY (`idsucursal`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- empleado
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `empleado` (
    `idempleado` int          NOT NULL AUTO_INCREMENT,
    `nombre`     varchar(100) NOT NULL,
    `dir`        varchar(150) DEFAULT NULL,
    `correo`     varchar(100) DEFAULT NULL,
    `tel`        varchar(25)  DEFAULT NULL,
    `idsucursal` int          DEFAULT NULL,
    PRIMARY KEY (`idempleado`),
    CONSTRAINT `fk_emp_suc` FOREIGN KEY (`idsucursal`) REFERENCES `sucursal` (`idsucursal`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- usuario  (pass → password_hash, se elimina campo codigo sin uso real)
-- accesslevel: 0=admin  1=laboratorista  2=empleado
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `usuario` (
    `idusuario`     int          NOT NULL AUTO_INCREMENT,
    `login`         varchar(45)  NOT NULL,
    `password_hash` varchar(255) NOT NULL,
    `idempleado`    int          DEFAULT NULL,
    `accesslevel`   tinyint      NOT NULL DEFAULT 2,
    `activo`        tinyint      NOT NULL DEFAULT 1,
    PRIMARY KEY (`idusuario`),
    UNIQUE KEY `uk_login` (`login`),
    CONSTRAINT `fk_usr_emp` FOREIGN KEY (`idempleado`) REFERENCES `empleado` (`idempleado`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- cliente  (huella eliminada — módulo biométrico fuera de scope)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `cliente` (
    `idcliente`    int           NOT NULL AUTO_INCREMENT,
    `nombre`       varchar(100)  NOT NULL,
    `fnacimiento`  date          DEFAULT NULL,
    `dir`          varchar(150)  DEFAULT NULL,
    `mail`         varchar(100)  DEFAULT NULL,
    `tel`          varchar(20)   DEFAULT NULL,
    `cel`          varchar(20)   DEFAULT NULL,
    `sexo`         enum('F','M') DEFAULT NULL,
    `monedero`     varchar(45)   NOT NULL DEFAULT 'no tiene',
    `puntos`       int           NOT NULL DEFAULT 0,
    `tipo_cliente` varchar(25)   NOT NULL DEFAULT 'General',
    `status`       tinyint       NOT NULL DEFAULT 1,
    PRIMARY KEY (`idcliente`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- analisis  (+ campos_resultado JSON para generador dinámico)
-- descripcion = categoría (HEMATOLOGIA, QUIMICA CLINICA, etc.)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `analisis` (
    `idanalisis`       int          NOT NULL AUTO_INCREMENT,
    `nombre`           varchar(100) NOT NULL,
    `descripcion`      varchar(50)  DEFAULT NULL,
    `precio`           decimal(9,2) NOT NULL DEFAULT 0.00,
    `puntos`           int          NOT NULL DEFAULT 0,
    `plantilla`        int          NOT NULL DEFAULT 0,
    `descuento`        int          NOT NULL DEFAULT 0,
    `tipo_muestra`     varchar(5)   NOT NULL DEFAULT '',
    `campos_resultado` json         DEFAULT NULL,
    `activo`           tinyint      NOT NULL DEFAULT 1,
    PRIMARY KEY (`idanalisis`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- precios  (4 tipos de cliente — nombres son columnas tal cual en código original)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `precios` (
    `idprecio`  int          NOT NULL AUTO_INCREMENT,
    `idanalisis` int         NOT NULL,
    `General`   decimal(9,2) NOT NULL DEFAULT 0.00,
    `GeneralD`  decimal(9,2) NOT NULL DEFAULT 0.00,
    `Especial`  decimal(9,2) NOT NULL DEFAULT 0.00,
    `EspecialD` decimal(9,2) NOT NULL DEFAULT 0.00,
    PRIMARY KEY (`idprecio`),
    UNIQUE KEY `uk_analisis` (`idanalisis`),
    CONSTRAINT `fk_precio_analisis` FOREIGN KEY (`idanalisis`) REFERENCES `analisis` (`idanalisis`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- consecutivo  (folio por sucursal — UNIQUE en idsucursal evita duplicados)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `consecutivo` (
    `idconsecutivo` int NOT NULL AUTO_INCREMENT,
    `idsucursal`    int NOT NULL,
    `consecutivo`   int NOT NULL DEFAULT 1,
    PRIMARY KEY (`idconsecutivo`),
    UNIQUE KEY `uk_sucursal` (`idsucursal`),
    CONSTRAINT `fk_cons_suc` FOREIGN KEY (`idsucursal`) REFERENCES `sucursal` (`idsucursal`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- doctores
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `doctores` (
    `correodoctor` varchar(100) NOT NULL,
    `nombre`       varchar(100) DEFAULT NULL,
    `institucion`  varchar(100) DEFAULT NULL,
    PRIMARY KEY (`correodoctor`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- muestra  (+ idcliente que faltaba en el SQL original pero existe en código)
-- status: 0=pendiente, 1=resultado capturado
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `muestra` (
    `idmuestra`    int        NOT NULL AUTO_INCREMENT,
    `cdb`          varchar(45) DEFAULT NULL,
    `status`       tinyint    NOT NULL DEFAULT 0,
    `idusuario`    int        DEFAULT NULL,
    `idcliente`    int        DEFAULT NULL,
    `idanalisis`   int        DEFAULT NULL,
    `tipo`         varchar(5) NOT NULL DEFAULT '',
    `fecha`        date       DEFAULT NULL,
    `fechaCaptura` date       DEFAULT NULL,
    PRIMARY KEY (`idmuestra`),
    CONSTRAINT `fk_mta_usr`      FOREIGN KEY (`idusuario`)  REFERENCES `usuario`  (`idusuario`),
    CONSTRAINT `fk_mta_cliente`  FOREIGN KEY (`idcliente`)  REFERENCES `cliente`  (`idcliente`),
    CONSTRAINT `fk_mta_analisis` FOREIGN KEY (`idanalisis`) REFERENCES `analisis` (`idanalisis`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- resultado_detalle  (NUEVA — reemplaza archivos XML en expedientesXml/)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `resultado_detalle` (
    `id`        int         NOT NULL AUTO_INCREMENT,
    `idmuestra` int         NOT NULL,
    `campo`     varchar(50) NOT NULL,
    `valor`     text        DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_idmuestra` (`idmuestra`),
    CONSTRAINT `fk_rd_mta` FOREIGN KEY (`idmuestra`) REFERENCES `muestra` (`idmuestra`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- ventas  (se elimina idmuestra VARCHAR — reemplazado por venta_muestra)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `ventas` (
    `idventas`      int          NOT NULL AUTO_INCREMENT,
    `idcliente`     int          DEFAULT NULL,
    `idusuario`     int          DEFAULT NULL,
    `idsucursal`    int          DEFAULT NULL,
    `idconsecutivo` int          DEFAULT NULL,
    `precio`        decimal(9,2) NOT NULL DEFAULT 0.00,
    `fecha`         date         NOT NULL,
    `correodoctor`  varchar(100) DEFAULT NULL,
    `status`        varchar(10)  NOT NULL DEFAULT '0',
    PRIMARY KEY (`idventas`),
    CONSTRAINT `fk_vta_cliente`  FOREIGN KEY (`idcliente`)    REFERENCES `cliente`  (`idcliente`),
    CONSTRAINT `fk_vta_usuario`  FOREIGN KEY (`idusuario`)    REFERENCES `usuario`  (`idusuario`),
    CONSTRAINT `fk_vta_sucursal` FOREIGN KEY (`idsucursal`)   REFERENCES `sucursal` (`idsucursal`),
    CONSTRAINT `fk_vta_doctor`   FOREIGN KEY (`correodoctor`) REFERENCES `doctores` (`correodoctor`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- venta_muestra  (junction — reemplaza ventas.idmuestra VARCHAR con dash-IDs)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `venta_muestra` (
    `id`        int NOT NULL AUTO_INCREMENT,
    `idventa`   int NOT NULL,
    `idmuestra` int NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_venta_mta` (`idventa`, `idmuestra`),
    CONSTRAINT `fk_vm_venta` FOREIGN KEY (`idventa`)   REFERENCES `ventas`  (`idventas`),
    CONSTRAINT `fk_vm_mta`   FOREIGN KEY (`idmuestra`) REFERENCES `muestra` (`idmuestra`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- abonar
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `abonar` (
    `id_abonar` int          NOT NULL AUTO_INCREMENT,
    `fecha`     date         NOT NULL,
    `abonar`    decimal(9,2) NOT NULL DEFAULT 0.00,
    `id_venta`  int          NOT NULL,
    PRIMARY KEY (`id_abonar`),
    CONSTRAINT `fk_abono_vta` FOREIGN KEY (`id_venta`) REFERENCES `ventas` (`idventas`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- maximos  (configuración del programa de puntos — una sola fila)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `maximos` (
    `id`            int NOT NULL DEFAULT 1,
    `puntos_max`    int NOT NULL DEFAULT 30,
    `descuento_max` int NOT NULL DEFAULT 10,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;