-- ============================================================
-- OptiCore SaaS - Sistema para Ópticas Multi-tenant
-- Schema MySQL 8.0 | Versión 1.0.0
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

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

USE `opticore`;

-- ============================================================
-- 1. planes
-- ============================================================
CREATE TABLE IF NOT EXISTS `planes` (
  `id`                INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nombre`            VARCHAR(100) NOT NULL,
  `descripcion`       TEXT,
  `precio_mensual`    DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `precio_anual`      DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `max_sucursales`    INT NOT NULL DEFAULT 1,
  `max_usuarios`      INT NOT NULL DEFAULT 5,
  `max_productos`     INT NOT NULL DEFAULT 100,
  `max_pacientes`     INT NOT NULL DEFAULT 500,
  `tiene_laboratorio` TINYINT(1) NOT NULL DEFAULT 0,
  `tiene_agenda`      TINYINT(1) NOT NULL DEFAULT 0,
  `tiene_reportes`    TINYINT(1) NOT NULL DEFAULT 1,
  `tiene_api`         TINYINT(1) NOT NULL DEFAULT 0,
  `estado`            ENUM('activo','inactivo') NOT NULL DEFAULT 'activo',
  `created_at`        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 2. empresas
-- ============================================================
CREATE TABLE IF NOT EXISTS `empresas` (
  `id`             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `plan_id`        INT UNSIGNED NOT NULL,
  `nombre`         VARCHAR(200) NOT NULL,
  `razon_social`   VARCHAR(200),
  `rut`            VARCHAR(20),
  `email`          VARCHAR(150),
  `telefono`       VARCHAR(30),
  `direccion`      VARCHAR(300),
  `ciudad`         VARCHAR(100),
  `pais`           VARCHAR(100) DEFAULT 'Chile',
  `moneda`         VARCHAR(10) DEFAULT 'CLP',
  `logo`           VARCHAR(300),
  `color_primario` VARCHAR(10) DEFAULT '#0d6efd',
  `estado`         ENUM('activa','suspendida','expirada','prueba') NOT NULL DEFAULT 'prueba',
  `created_at`     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_plan_id` (`plan_id`),
  KEY `idx_estado` (`estado`),
  CONSTRAINT `fk_empresas_plan` FOREIGN KEY (`plan_id`) REFERENCES `planes` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 3. licencias
-- ============================================================
CREATE TABLE IF NOT EXISTS `licencias` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `empresa_id`    INT UNSIGNED NOT NULL,
  `plan_id`       INT UNSIGNED NOT NULL,
  `tipo`          ENUM('mensual','anual','perpetua','prueba') NOT NULL DEFAULT 'mensual',
  `fecha_inicio`  DATE NOT NULL,
  `fecha_fin`     DATE,
  `monto`         DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `estado`        ENUM('activa','suspendida','expirada','cancelada') NOT NULL DEFAULT 'activa',
  `observaciones` TEXT,
  `created_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_empresa_id` (`empresa_id`),
  KEY `idx_plan_id` (`plan_id`),
  KEY `idx_estado` (`estado`),
  KEY `idx_fecha_fin` (`fecha_fin`),
  CONSTRAINT `fk_licencias_empresa` FOREIGN KEY (`empresa_id`) REFERENCES `empresas` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_licencias_plan` FOREIGN KEY (`plan_id`) REFERENCES `planes` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 4. sucursales
-- ============================================================
CREATE TABLE IF NOT EXISTS `sucursales` (
  `id`                    INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `empresa_id`            INT UNSIGNED NOT NULL,
  `nombre`                VARCHAR(150) NOT NULL,
  `codigo`                VARCHAR(20),
  `direccion`             VARCHAR(300),
  `telefono`              VARCHAR(30),
  `email`                 VARCHAR(150),
  `horario`               VARCHAR(200),
  `correlativo_ventas`    INT UNSIGNED NOT NULL DEFAULT 1,
  `correlativo_ordenes`   INT UNSIGNED NOT NULL DEFAULT 1,
  `correlativo_caja`      INT UNSIGNED NOT NULL DEFAULT 1,
  `es_principal`          TINYINT(1) NOT NULL DEFAULT 0,
  `estado`                ENUM('activa','inactiva') NOT NULL DEFAULT 'activa',
  `created_at`            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_empresa_id` (`empresa_id`),
  KEY `idx_estado` (`estado`),
  CONSTRAINT `fk_sucursales_empresa` FOREIGN KEY (`empresa_id`) REFERENCES `empresas` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 5. roles
-- ============================================================
CREATE TABLE IF NOT EXISTS `roles` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `empresa_id`  INT UNSIGNED,
  `nombre`      VARCHAR(100) NOT NULL,
  `slug`        VARCHAR(100) NOT NULL,
  `descripcion` TEXT,
  `es_sistema`  TINYINT(1) NOT NULL DEFAULT 0,
  `estado`      ENUM('activo','inactivo') NOT NULL DEFAULT 'activo',
  `created_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_empresa_id` (`empresa_id`),
  KEY `idx_slug` (`slug`),
  CONSTRAINT `fk_roles_empresa` FOREIGN KEY (`empresa_id`) REFERENCES `empresas` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 6. permisos
-- ============================================================
CREATE TABLE IF NOT EXISTS `permisos` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `modulo`      VARCHAR(100) NOT NULL,
  `nombre`      VARCHAR(100) NOT NULL,
  `descripcion` VARCHAR(200),
  `created_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_nombre` (`nombre`),
  KEY `idx_modulo` (`modulo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 7. rol_permisos
-- ============================================================
CREATE TABLE IF NOT EXISTS `rol_permisos` (
  `rol_id`     INT UNSIGNED NOT NULL,
  `permiso_id` INT UNSIGNED NOT NULL,
  `granted`    TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`rol_id`, `permiso_id`),
  KEY `idx_permiso_id` (`permiso_id`),
  CONSTRAINT `fk_rp_rol` FOREIGN KEY (`rol_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rp_permiso` FOREIGN KEY (`permiso_id`) REFERENCES `permisos` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 8. usuarios
-- ============================================================
CREATE TABLE IF NOT EXISTS `usuarios` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `empresa_id`    INT UNSIGNED,
  `sucursal_id`   INT UNSIGNED,
  `rol_id`        INT UNSIGNED NOT NULL,
  `nombre`        VARCHAR(100) NOT NULL,
  `apellido`      VARCHAR(100) NOT NULL,
  `email`         VARCHAR(150) NOT NULL,
  `password`      VARCHAR(255) NOT NULL,
  `telefono`      VARCHAR(30),
  `avatar`        VARCHAR(300),
  `es_superadmin` TINYINT(1) NOT NULL DEFAULT 0,
  `estado`        ENUM('activo','inactivo','bloqueado') NOT NULL DEFAULT 'activo',
  `ultimo_login`  TIMESTAMP NULL,
  `token_reset`   VARCHAR(100),
  `token_expira`  TIMESTAMP NULL,
  `created_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_email` (`email`),
  KEY `idx_empresa_id` (`empresa_id`),
  KEY `idx_sucursal_id` (`sucursal_id`),
  KEY `idx_rol_id` (`rol_id`),
  KEY `idx_estado` (`estado`),
  CONSTRAINT `fk_usuarios_empresa` FOREIGN KEY (`empresa_id`) REFERENCES `empresas` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_usuarios_sucursal` FOREIGN KEY (`sucursal_id`) REFERENCES `sucursales` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_usuarios_rol` FOREIGN KEY (`rol_id`) REFERENCES `roles` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 9. pacientes
-- ============================================================
CREATE TABLE IF NOT EXISTS `pacientes` (
  `id`               INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `empresa_id`       INT UNSIGNED NOT NULL,
  `sucursal_id`      INT UNSIGNED,
  `nombre`           VARCHAR(100) NOT NULL,
  `apellido`         VARCHAR(100) NOT NULL,
  `rut`              VARCHAR(20),
  `documento`        VARCHAR(30),
  `fecha_nacimiento` DATE,
  `genero`           ENUM('M','F','otro'),
  `telefono`         VARCHAR(30),
  `email`            VARCHAR(150),
  `direccion`        VARCHAR(300),
  `ciudad`           VARCHAR(100),
  `ocupacion`        VARCHAR(100),
  `observaciones`    TEXT,
  `estado`           ENUM('activo','inactivo') NOT NULL DEFAULT 'activo',
  `created_at`       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_empresa_id` (`empresa_id`),
  KEY `idx_sucursal_id` (`sucursal_id`),
  KEY `idx_nombre` (`nombre`, `apellido`),
  KEY `idx_rut` (`rut`),
  KEY `idx_telefono` (`telefono`),
  CONSTRAINT `fk_pacientes_empresa` FOREIGN KEY (`empresa_id`) REFERENCES `empresas` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_pacientes_sucursal` FOREIGN KEY (`sucursal_id`) REFERENCES `sucursales` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 10. recetas
-- ============================================================
CREATE TABLE IF NOT EXISTS `recetas` (
  `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `empresa_id`   INT UNSIGNED NOT NULL,
  `sucursal_id`  INT UNSIGNED,
  `paciente_id`  INT UNSIGNED NOT NULL,
  `usuario_id`   INT UNSIGNED NOT NULL,
  `fecha`        DATE NOT NULL,
  `vigencia`     DATE,
  `tipo`         ENUM('cerca','lejos','progresivo','bifocal') NOT NULL DEFAULT 'lejos',
  `od_esfera`    DECIMAL(5,2),
  `od_cilindro`  DECIMAL(5,2),
  `od_eje`       INT,
  `od_adicion`   DECIMAL(5,2),
  `od_dp`        DECIMAL(5,2),
  `od_av`        VARCHAR(20),
  `oi_esfera`    DECIMAL(5,2),
  `oi_cilindro`  DECIMAL(5,2),
  `oi_eje`       INT,
  `oi_adicion`   DECIMAL(5,2),
  `oi_dp`        DECIMAL(5,2),
  `oi_av`        VARCHAR(20),
  `dp_total`     DECIMAL(5,2),
  `observaciones` TEXT,
  `adjunto`      VARCHAR(300),
  `estado`       ENUM('activa','vencida','anulada') NOT NULL DEFAULT 'activa',
  `created_at`   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_empresa_id` (`empresa_id`),
  KEY `idx_sucursal_id` (`sucursal_id`),
  KEY `idx_paciente_id` (`paciente_id`),
  KEY `idx_usuario_id` (`usuario_id`),
  KEY `idx_fecha` (`fecha`),
  CONSTRAINT `fk_recetas_empresa` FOREIGN KEY (`empresa_id`) REFERENCES `empresas` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_recetas_sucursal` FOREIGN KEY (`sucursal_id`) REFERENCES `sucursales` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_recetas_paciente` FOREIGN KEY (`paciente_id`) REFERENCES `pacientes` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_recetas_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 11. categorias
-- ============================================================
CREATE TABLE IF NOT EXISTS `categorias` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `empresa_id`  INT UNSIGNED NOT NULL,
  `nombre`      VARCHAR(100) NOT NULL,
  `descripcion` TEXT,
  `estado`      ENUM('activa','inactiva') NOT NULL DEFAULT 'activa',
  `created_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_empresa_id` (`empresa_id`),
  CONSTRAINT `fk_categorias_empresa` FOREIGN KEY (`empresa_id`) REFERENCES `empresas` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 12. marcas
-- ============================================================
CREATE TABLE IF NOT EXISTS `marcas` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `empresa_id`  INT UNSIGNED NOT NULL,
  `nombre`      VARCHAR(100) NOT NULL,
  `descripcion` TEXT,
  `estado`      ENUM('activa','inactiva') NOT NULL DEFAULT 'activa',
  `created_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_empresa_id` (`empresa_id`),
  CONSTRAINT `fk_marcas_empresa` FOREIGN KEY (`empresa_id`) REFERENCES `empresas` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 13. proveedores
-- ============================================================
CREATE TABLE IF NOT EXISTS `proveedores` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `empresa_id`  INT UNSIGNED NOT NULL,
  `nombre`      VARCHAR(200) NOT NULL,
  `rut`         VARCHAR(20),
  `contacto`    VARCHAR(100),
  `telefono`    VARCHAR(30),
  `email`       VARCHAR(150),
  `direccion`   VARCHAR(300),
  `tipo`        ENUM('proveedor','laboratorio','ambos') NOT NULL DEFAULT 'proveedor',
  `estado`      ENUM('activo','inactivo') NOT NULL DEFAULT 'activo',
  `created_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_empresa_id` (`empresa_id`),
  CONSTRAINT `fk_proveedores_empresa` FOREIGN KEY (`empresa_id`) REFERENCES `empresas` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 14. productos
-- ============================================================
CREATE TABLE IF NOT EXISTS `productos` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `empresa_id`    INT UNSIGNED NOT NULL,
  `categoria_id`  INT UNSIGNED,
  `marca_id`      INT UNSIGNED,
  `proveedor_id`  INT UNSIGNED,
  `sku`           VARCHAR(100),
  `codigo_barras` VARCHAR(100),
  `nombre`        VARCHAR(200) NOT NULL,
  `descripcion`   TEXT,
  `tipo`          ENUM('marco','lente','solucion','accesorio','servicio','otro') NOT NULL DEFAULT 'otro',
  `precio_costo`  DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `precio_venta`  DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `impuesto`      DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  `stock_minimo`  INT NOT NULL DEFAULT 0,
  `imagen`        VARCHAR(300),
  `estado`        ENUM('activo','inactivo') NOT NULL DEFAULT 'activo',
  `created_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_empresa_id` (`empresa_id`),
  KEY `idx_categoria_id` (`categoria_id`),
  KEY `idx_marca_id` (`marca_id`),
  KEY `idx_sku` (`sku`),
  KEY `idx_codigo_barras` (`codigo_barras`),
  KEY `idx_tipo` (`tipo`),
  CONSTRAINT `fk_productos_empresa` FOREIGN KEY (`empresa_id`) REFERENCES `empresas` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_productos_categoria` FOREIGN KEY (`categoria_id`) REFERENCES `categorias` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_productos_marca` FOREIGN KEY (`marca_id`) REFERENCES `marcas` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_productos_proveedor` FOREIGN KEY (`proveedor_id`) REFERENCES `proveedores` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 15. stock
-- ============================================================
CREATE TABLE IF NOT EXISTS `stock` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `empresa_id`  INT UNSIGNED NOT NULL,
  `sucursal_id` INT UNSIGNED NOT NULL,
  `producto_id` INT UNSIGNED NOT NULL,
  `cantidad`    INT NOT NULL DEFAULT 0,
  `updated_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_sucursal_producto` (`sucursal_id`, `producto_id`),
  KEY `idx_empresa_id` (`empresa_id`),
  KEY `idx_producto_id` (`producto_id`),
  CONSTRAINT `fk_stock_empresa` FOREIGN KEY (`empresa_id`) REFERENCES `empresas` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_stock_sucursal` FOREIGN KEY (`sucursal_id`) REFERENCES `sucursales` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_stock_producto` FOREIGN KEY (`producto_id`) REFERENCES `productos` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 16. movimientos_stock (Kardex)
-- ============================================================
CREATE TABLE IF NOT EXISTS `movimientos_stock` (
  `id`                INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `empresa_id`        INT UNSIGNED NOT NULL,
  `sucursal_id`       INT UNSIGNED NOT NULL,
  `producto_id`       INT UNSIGNED NOT NULL,
  `usuario_id`        INT UNSIGNED,
  `tipo`              ENUM('entrada','salida','ajuste','traspaso_entrada','traspaso_salida','venta','devolucion') NOT NULL,
  `cantidad`          INT NOT NULL,
  `cantidad_anterior` INT NOT NULL DEFAULT 0,
  `cantidad_nueva`    INT NOT NULL DEFAULT 0,
  `costo_unitario`    DECIMAL(12,2) DEFAULT 0.00,
  `referencia_tipo`   VARCHAR(50),
  `referencia_id`     INT UNSIGNED,
  `observaciones`     TEXT,
  `fecha`             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_empresa_id` (`empresa_id`),
  KEY `idx_sucursal_id` (`sucursal_id`),
  KEY `idx_producto_id` (`producto_id`),
  KEY `idx_tipo` (`tipo`),
  KEY `idx_fecha` (`fecha`),
  CONSTRAINT `fk_mov_empresa` FOREIGN KEY (`empresa_id`) REFERENCES `empresas` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_mov_sucursal` FOREIGN KEY (`sucursal_id`) REFERENCES `sucursales` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_mov_producto` FOREIGN KEY (`producto_id`) REFERENCES `productos` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 17. formas_pago
-- ============================================================
CREATE TABLE IF NOT EXISTS `formas_pago` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `empresa_id` INT UNSIGNED NOT NULL,
  `nombre`     VARCHAR(100) NOT NULL,
  `tipo`       ENUM('efectivo','tarjeta','transferencia','cheque','credito','otro') NOT NULL DEFAULT 'efectivo',
  `estado`     ENUM('activa','inactiva') NOT NULL DEFAULT 'activa',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_empresa_id` (`empresa_id`),
  CONSTRAINT `fk_fp_empresa` FOREIGN KEY (`empresa_id`) REFERENCES `empresas` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 18. ventas
-- ============================================================
CREATE TABLE IF NOT EXISTS `ventas` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `empresa_id`    INT UNSIGNED NOT NULL,
  `sucursal_id`   INT UNSIGNED NOT NULL,
  `usuario_id`    INT UNSIGNED NOT NULL,
  `paciente_id`   INT UNSIGNED,
  `correlativo`   VARCHAR(20) NOT NULL,
  `fecha`         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `subtotal`      DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `descuento`     DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `impuesto`      DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `total`         DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `estado`        ENUM('cotizacion','confirmada','anulada') NOT NULL DEFAULT 'confirmada',
  `observaciones` TEXT,
  `created_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_empresa_id` (`empresa_id`),
  KEY `idx_sucursal_id` (`sucursal_id`),
  KEY `idx_usuario_id` (`usuario_id`),
  KEY `idx_paciente_id` (`paciente_id`),
  KEY `idx_correlativo` (`correlativo`),
  KEY `idx_fecha` (`fecha`),
  KEY `idx_estado` (`estado`),
  CONSTRAINT `fk_ventas_empresa` FOREIGN KEY (`empresa_id`) REFERENCES `empresas` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ventas_sucursal` FOREIGN KEY (`sucursal_id`) REFERENCES `sucursales` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_ventas_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_ventas_paciente` FOREIGN KEY (`paciente_id`) REFERENCES `pacientes` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 19. venta_items
-- ============================================================
CREATE TABLE IF NOT EXISTS `venta_items` (
  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `venta_id`        INT UNSIGNED NOT NULL,
  `producto_id`     INT UNSIGNED NOT NULL,
  `nombre_producto` VARCHAR(200) NOT NULL,
  `cantidad`        INT NOT NULL DEFAULT 1,
  `precio_unitario` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `descuento`       DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `impuesto`        DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `subtotal`        DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `idx_venta_id` (`venta_id`),
  KEY `idx_producto_id` (`producto_id`),
  CONSTRAINT `fk_vi_venta` FOREIGN KEY (`venta_id`) REFERENCES `ventas` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_vi_producto` FOREIGN KEY (`producto_id`) REFERENCES `productos` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 20. venta_pagos
-- ============================================================
CREATE TABLE IF NOT EXISTS `venta_pagos` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `venta_id`      INT UNSIGNED NOT NULL,
  `forma_pago_id` INT UNSIGNED NOT NULL,
  `monto`         DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `referencia`    VARCHAR(100),
  PRIMARY KEY (`id`),
  KEY `idx_venta_id` (`venta_id`),
  KEY `idx_forma_pago_id` (`forma_pago_id`),
  CONSTRAINT `fk_vp_venta` FOREIGN KEY (`venta_id`) REFERENCES `ventas` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_vp_forma_pago` FOREIGN KEY (`forma_pago_id`) REFERENCES `formas_pago` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 21. caja_sesiones
-- ============================================================
CREATE TABLE IF NOT EXISTS `caja_sesiones` (
  `id`                    INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `empresa_id`            INT UNSIGNED NOT NULL,
  `sucursal_id`           INT UNSIGNED NOT NULL,
  `usuario_id`            INT UNSIGNED NOT NULL,
  `correlativo`           VARCHAR(20),
  `fecha_apertura`        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `monto_apertura`        DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `fecha_cierre`          TIMESTAMP NULL,
  `monto_cierre_real`     DECIMAL(12,2),
  `monto_cierre_sistema`  DECIMAL(12,2),
  `diferencia`            DECIMAL(12,2),
  `estado`                ENUM('abierta','cerrada') NOT NULL DEFAULT 'abierta',
  `observaciones`         TEXT,
  `created_at`            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_empresa_id` (`empresa_id`),
  KEY `idx_sucursal_id` (`sucursal_id`),
  KEY `idx_usuario_id` (`usuario_id`),
  KEY `idx_estado` (`estado`),
  KEY `idx_fecha_apertura` (`fecha_apertura`),
  CONSTRAINT `fk_cs_empresa` FOREIGN KEY (`empresa_id`) REFERENCES `empresas` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cs_sucursal` FOREIGN KEY (`sucursal_id`) REFERENCES `sucursales` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_cs_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 22. caja_movimientos
-- ============================================================
CREATE TABLE IF NOT EXISTS `caja_movimientos` (
  `id`               INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `empresa_id`       INT UNSIGNED NOT NULL,
  `sucursal_id`      INT UNSIGNED NOT NULL,
  `sesion_id`        INT UNSIGNED NOT NULL,
  `usuario_id`       INT UNSIGNED,
  `tipo`             ENUM('ingreso','egreso') NOT NULL,
  `concepto`         VARCHAR(200) NOT NULL,
  `monto`            DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `forma_pago_id`    INT UNSIGNED,
  `referencia_tipo`  VARCHAR(50),
  `referencia_id`    INT UNSIGNED,
  `fecha`            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_empresa_id` (`empresa_id`),
  KEY `idx_sucursal_id` (`sucursal_id`),
  KEY `idx_sesion_id` (`sesion_id`),
  KEY `idx_tipo` (`tipo`),
  KEY `idx_fecha` (`fecha`),
  CONSTRAINT `fk_cm_empresa` FOREIGN KEY (`empresa_id`) REFERENCES `empresas` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cm_sucursal` FOREIGN KEY (`sucursal_id`) REFERENCES `sucursales` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cm_sesion` FOREIGN KEY (`sesion_id`) REFERENCES `caja_sesiones` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 23. ordenes_laboratorio
-- ============================================================
CREATE TABLE IF NOT EXISTS `ordenes_laboratorio` (
  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `empresa_id`      INT UNSIGNED NOT NULL,
  `sucursal_id`     INT UNSIGNED NOT NULL,
  `venta_id`        INT UNSIGNED,
  `receta_id`       INT UNSIGNED,
  `paciente_id`     INT UNSIGNED,
  `proveedor_id`    INT UNSIGNED,
  `usuario_id`      INT UNSIGNED NOT NULL,
  `correlativo`     VARCHAR(20),
  `estado`          ENUM('recibido','en_proceso','listo','entregado','cancelado') NOT NULL DEFAULT 'recibido',
  `fecha_recepcion` DATE NOT NULL,
  `fecha_estimada`  DATE,
  `fecha_entrega`   DATE,
  `costo`           DECIMAL(12,2) DEFAULT 0.00,
  `descripcion`     TEXT,
  `observaciones`   TEXT,
  `created_at`      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_empresa_id` (`empresa_id`),
  KEY `idx_sucursal_id` (`sucursal_id`),
  KEY `idx_venta_id` (`venta_id`),
  KEY `idx_receta_id` (`receta_id`),
  KEY `idx_estado` (`estado`),
  CONSTRAINT `fk_ol_empresa` FOREIGN KEY (`empresa_id`) REFERENCES `empresas` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ol_sucursal` FOREIGN KEY (`sucursal_id`) REFERENCES `sucursales` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_ol_venta` FOREIGN KEY (`venta_id`) REFERENCES `ventas` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_ol_receta` FOREIGN KEY (`receta_id`) REFERENCES `recetas` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_ol_proveedor` FOREIGN KEY (`proveedor_id`) REFERENCES `proveedores` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 24. citas (Agenda)
-- ============================================================
CREATE TABLE IF NOT EXISTS `citas` (
  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `empresa_id`      INT UNSIGNED NOT NULL,
  `sucursal_id`     INT UNSIGNED NOT NULL,
  `paciente_id`     INT UNSIGNED,
  `usuario_id`      INT UNSIGNED,
  `fecha`           DATE NOT NULL,
  `hora`            TIME NOT NULL,
  `duracion`        INT NOT NULL DEFAULT 30,
  `tipo`            ENUM('optometria','entrega','control','otro') NOT NULL DEFAULT 'optometria',
  `estado`          ENUM('pendiente','confirmada','atendida','cancelada','no_asistio') NOT NULL DEFAULT 'pendiente',
  `nombre_paciente` VARCHAR(200),
  `telefono`        VARCHAR(30),
  `observaciones`   TEXT,
  `created_at`      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_empresa_id` (`empresa_id`),
  KEY `idx_sucursal_id` (`sucursal_id`),
  KEY `idx_paciente_id` (`paciente_id`),
  KEY `idx_fecha` (`fecha`),
  KEY `idx_estado` (`estado`),
  CONSTRAINT `fk_citas_empresa` FOREIGN KEY (`empresa_id`) REFERENCES `empresas` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_citas_sucursal` FOREIGN KEY (`sucursal_id`) REFERENCES `sucursales` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_citas_paciente` FOREIGN KEY (`paciente_id`) REFERENCES `pacientes` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 25. audit_logs
-- ============================================================
CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id`               INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `empresa_id`       INT UNSIGNED,
  `usuario_id`       INT UNSIGNED,
  `accion`           VARCHAR(100) NOT NULL,
  `tabla`            VARCHAR(100),
  `registro_id`      INT UNSIGNED,
  `datos_anteriores` JSON,
  `datos_nuevos`     JSON,
  `ip`               VARCHAR(45),
  `user_agent`       VARCHAR(300),
  `fecha`            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_empresa_id` (`empresa_id`),
  KEY `idx_usuario_id` (`usuario_id`),
  KEY `idx_accion` (`accion`),
  KEY `idx_tabla` (`tabla`),
  KEY `idx_fecha` (`fecha`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
