-- =============================================
-- BD CENTRAL (SERVIDOR)
-- Ejecutar en la base de datos remota
-- Todas las tablas llevan sucursal_id + local_id
-- para identificar origen y evitar duplicados
-- =============================================

-- Sucursales (compartida, sin local_id)
CREATE TABLE IF NOT EXISTS sucursales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    direccion VARCHAR(255) DEFAULT '',
    telefono VARCHAR(20) DEFAULT '',
    activo TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_nombre (nombre)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- CATÁLOGOS
-- =============================================

CREATE TABLE IF NOT EXISTS categorias_productos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sucursal_id INT NOT NULL,
    local_id INT NOT NULL,
    nombre VARCHAR(100),
    descripcion TEXT,
    color VARCHAR(20) DEFAULT '#f97316',
    orden INT DEFAULT 0,
    activo TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_suc_local (sucursal_id, local_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS productos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sucursal_id INT NOT NULL,
    local_id INT NOT NULL,
    nombre VARCHAR(150),
    descripcion TEXT,
    precio DECIMAL(10,2) DEFAULT 0,
    categoria_id INT,
    unidad VARCHAR(20) DEFAULT 'pza',
    stock DECIMAL(10,2) DEFAULT 0,
    stock_minimo DECIMAL(10,2) DEFAULT 0,
    codigo VARCHAR(50),
    imagen VARCHAR(255),
    activo TINYINT(1) DEFAULT 1,
    tiene_receta TINYINT(1) DEFAULT 0,
    envasado_origen TINYINT(1) DEFAULT 0,
    contabilizar_corte TINYINT(1) DEFAULT 1,
    contar_al_inicio TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_suc_local (sucursal_id, local_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS categorias_ingredientes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sucursal_id INT NOT NULL,
    local_id INT NOT NULL,
    nombre VARCHAR(100),
    descripcion TEXT,
    activo TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_suc_local (sucursal_id, local_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS unidades_medida (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sucursal_id INT NOT NULL,
    local_id INT NOT NULL,
    nombre VARCHAR(50),
    abreviatura VARCHAR(10),
    activo TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_suc_local (sucursal_id, local_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ingredientes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sucursal_id INT NOT NULL,
    local_id INT NOT NULL,
    nombre VARCHAR(150),
    cantidad DECIMAL(10,2) DEFAULT 0,
    unidad VARCHAR(20),
    precio DECIMAL(10,2) DEFAULT 0,
    proveedor VARCHAR(150),
    stock_minimo DECIMAL(10,2) DEFAULT 0,
    categoria VARCHAR(100) DEFAULT 'Otros',
    activo TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_suc_local (sucursal_id, local_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS clientes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sucursal_id INT NOT NULL,
    local_id INT NOT NULL,
    nombre VARCHAR(150),
    telefono VARCHAR(20),
    email VARCHAR(150),
    direccion TEXT,
    activo TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_suc_local (sucursal_id, local_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- RECETAS
-- =============================================

CREATE TABLE IF NOT EXISTS producto_ingredientes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sucursal_id INT NOT NULL,
    local_id INT NOT NULL,
    producto_id INT NOT NULL,
    ingrediente_id INT NOT NULL,
    cantidad DECIMAL(10,4) NOT NULL DEFAULT 0,
    unidad VARCHAR(20) DEFAULT 'pza',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_suc_local (sucursal_id, local_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS producto_componentes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sucursal_id INT NOT NULL,
    local_id INT NOT NULL,
    producto_id INT NOT NULL,
    componente_id INT NOT NULL,
    cantidad DECIMAL(10,4) NOT NULL DEFAULT 1,
    unidad VARCHAR(20) DEFAULT 'pza',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_suc_local (sucursal_id, local_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- USUARIOS Y PERFILES
-- =============================================

CREATE TABLE IF NOT EXISTS permisos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sucursal_id INT NOT NULL,
    local_id INT NOT NULL,
    codigo VARCHAR(50),
    nombre VARCHAR(100),
    descripcion VARCHAR(255),
    modulo VARCHAR(50),
    activo TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_suc_local (sucursal_id, local_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS perfiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sucursal_id INT NOT NULL,
    local_id INT NOT NULL,
    nombre VARCHAR(100),
    descripcion VARCHAR(255),
    activo TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_suc_local (sucursal_id, local_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS perfil_permisos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sucursal_id INT NOT NULL,
    local_id INT NOT NULL,
    perfil_id INT,
    permiso_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_suc_local (sucursal_id, local_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS usuarios (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sucursal_id INT NOT NULL,
    local_id INT NOT NULL,
    username VARCHAR(50),
    password VARCHAR(255),
    nombre VARCHAR(150),
    email VARCHAR(150),
    telefono VARCHAR(20),
    perfil_id INT,
    activo TINYINT(1) DEFAULT 1,
    ultimo_acceso TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_suc_local (sucursal_id, local_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- PEDIDOS PAUSADOS
-- =============================================

CREATE TABLE IF NOT EXISTS pedidos_pausados (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sucursal_id INT NOT NULL,
    local_id INT NOT NULL,
    cliente_id INT,
    items JSON,
    total DECIMAL(10,2) DEFAULT 0,
    notas TEXT,
    usuario_id INT,
    sucursal VARCHAR(100),
    caja VARCHAR(50),
    estado VARCHAR(20) DEFAULT 'activo',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_suc_local (sucursal_id, local_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- VENTAS
-- =============================================

CREATE TABLE IF NOT EXISTS ventas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sucursal_id INT NOT NULL,
    local_id INT NOT NULL,
    folio VARCHAR(50),
    subtotal DECIMAL(10,2) DEFAULT 0,
    total DECIMAL(10,2) DEFAULT 0,
    pago DECIMAL(10,2) DEFAULT 0,
    cambio DECIMAL(10,2) DEFAULT 0,
    metodo_pago VARCHAR(30),
    cliente_id INT,
    usuario_id INT,
    sucursal VARCHAR(100),
    caja VARCHAR(50),
    notas TEXT,
    estado VARCHAR(20) DEFAULT 'completada',
    fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_suc_local (sucursal_id, local_id),
    INDEX idx_fecha (fecha),
    INDEX idx_sucursal_id (sucursal_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS venta_detalle (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sucursal_id INT NOT NULL,
    local_id INT NOT NULL,
    venta_id INT,
    venta_local_id INT,
    producto_id INT,
    producto_nombre VARCHAR(150),
    cantidad DECIMAL(10,2),
    precio_unitario DECIMAL(10,2),
    subtotal DECIMAL(10,2),
    UNIQUE KEY uk_suc_local (sucursal_id, local_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- COMPRAS
-- =============================================

CREATE TABLE IF NOT EXISTS compras (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sucursal_id INT NOT NULL,
    local_id INT NOT NULL,
    proveedor VARCHAR(150),
    metodo_pago VARCHAR(30),
    total DECIMAL(10,2) DEFAULT 0,
    observaciones TEXT,
    fecha DATE,
    usuario_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_suc_local (sucursal_id, local_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS compra_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sucursal_id INT NOT NULL,
    local_id INT NOT NULL,
    compra_id INT,
    compra_local_id INT,
    tipo_item VARCHAR(20),
    item_id INT,
    cantidad DECIMAL(10,2),
    unidad VARCHAR(20),
    precio_unitario DECIMAL(10,2),
    subtotal DECIMAL(10,2),
    UNIQUE KEY uk_suc_local (sucursal_id, local_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- PRODUCCIONES
-- =============================================

CREATE TABLE IF NOT EXISTS producciones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sucursal_id INT NOT NULL,
    local_id INT NOT NULL,
    producto_id INT,
    cantidad_producida DECIMAL(10,2),
    unidad VARCHAR(20) DEFAULT 'pza',
    fecha DATE,
    hora TIME,
    usuario_id INT,
    notas TEXT,
    estado VARCHAR(20) DEFAULT 'completada',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_suc_local (sucursal_id, local_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS produccion_ingredientes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sucursal_id INT NOT NULL,
    local_id INT NOT NULL,
    produccion_id INT,
    produccion_local_id INT,
    ingrediente_id INT,
    cantidad_usada DECIMAL(10,4),
    cantidad_receta DECIMAL(10,4),
    unidad VARCHAR(20) DEFAULT 'pza',
    costo_unitario DECIMAL(10,2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_suc_local (sucursal_id, local_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS produccion_componentes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sucursal_id INT NOT NULL,
    local_id INT NOT NULL,
    produccion_id INT,
    produccion_local_id INT,
    componente_id INT,
    cantidad_usada DECIMAL(10,4),
    cantidad_receta DECIMAL(10,4),
    unidad VARCHAR(20) DEFAULT 'pza',
    costo_unitario DECIMAL(10,2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_suc_local (sucursal_id, local_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS produccion_ajustes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sucursal_id INT NOT NULL,
    local_id INT NOT NULL,
    produccion_id INT,
    produccion_local_id INT,
    peso_inicial DECIMAL(10,2),
    peso_final DECIMAL(10,2),
    diferencia DECIMAL(10,2),
    notas TEXT,
    usuario_id INT,
    fecha DATE,
    hora TIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_suc_local (sucursal_id, local_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS produccion_ajuste_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sucursal_id INT NOT NULL,
    local_id INT NOT NULL,
    ajuste_id INT,
    ajuste_local_id INT,
    tipo VARCHAR(20),
    item_id INT,
    cantidad DECIMAL(10,4),
    unidad VARCHAR(20),
    UNIQUE KEY uk_suc_local (sucursal_id, local_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- MERMAS
-- =============================================

CREATE TABLE IF NOT EXISTS mermas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sucursal_id INT NOT NULL,
    local_id INT NOT NULL,
    tipo_item VARCHAR(20),
    item_id INT,
    cantidad DECIMAL(10,2),
    unidad VARCHAR(20),
    motivo VARCHAR(50),
    observaciones TEXT,
    fecha DATE,
    usuario_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_suc_local (sucursal_id, local_id),
    INDEX idx_fecha (fecha)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- CORTES DE CAJA
-- =============================================

CREATE TABLE IF NOT EXISTS cortes_caja (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sucursal_id INT NOT NULL,
    local_id INT NOT NULL,
    usuario_id INT,
    sucursal VARCHAR(100),
    caja VARCHAR(50),
    fecha_inicio DATETIME,
    fecha_fin DATETIME,
    ventas_efectivo DECIMAL(10,2) DEFAULT 0,
    ventas_tarjeta DECIMAL(10,2) DEFAULT 0,
    ventas_total DECIMAL(10,2) DEFAULT 0,
    num_ventas INT DEFAULT 0,
    efectivo_sistema DECIMAL(10,2) DEFAULT 0,
    efectivo_fisico DECIMAL(10,2) DEFAULT 0,
    diferencia DECIMAL(10,2) DEFAULT 0,
    fondo_caja DECIMAL(10,2) DEFAULT 0,
    detalle_arqueo JSON,
    notas TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_suc_local (sucursal_id, local_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS inventario_cortes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sucursal_id INT NOT NULL,
    local_id INT NOT NULL,
    corte_id INT,
    corte_local_id INT,
    producto_id INT,
    producto_nombre VARCHAR(150),
    stock_esperado DECIMAL(10,2),
    stock_fisico DECIMAL(10,2),
    diferencia DECIMAL(10,2),
    unidad VARCHAR(20),
    UNIQUE KEY uk_suc_local (sucursal_id, local_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- TRANSFERENCIAS (cross-branch)
-- =============================================

CREATE TABLE IF NOT EXISTS transferencias (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sucursal_id INT NOT NULL,
    local_id INT NOT NULL,
    tipo ENUM('requisicion', 'envio_directo') NOT NULL,
    sucursal_origen_id INT NOT NULL,
    sucursal_origen_nombre VARCHAR(100) NOT NULL,
    sucursal_destino_id INT NOT NULL,
    sucursal_destino_nombre VARCHAR(100) NOT NULL,
    estado ENUM('pendiente', 'aprobada', 'rechazada', 'enviada', 'recibida', 'cancelada') DEFAULT 'pendiente',
    observaciones TEXT,
    fecha DATE NOT NULL,
    solicitado_por INT,
    aprobado_por INT,
    enviado_por INT,
    recibido_por INT,
    fecha_aprobacion DATETIME,
    fecha_envio DATETIME,
    fecha_recepcion DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_suc_local (sucursal_id, local_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS transferencia_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sucursal_id INT NOT NULL,
    local_id INT NOT NULL,
    transferencia_id INT,
    transferencia_local_id INT,
    tipo_item ENUM('producto', 'ingrediente') NOT NULL,
    item_id INT NOT NULL,
    item_nombre VARCHAR(150) NOT NULL,
    cantidad_solicitada DECIMAL(10,2) NOT NULL DEFAULT 0,
    cantidad_enviada DECIMAL(10,2) DEFAULT 0,
    cantidad_recibida DECIMAL(10,2) DEFAULT 0,
    unidad VARCHAR(20) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_suc_local (sucursal_id, local_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- TURNOS
-- =============================================

CREATE TABLE IF NOT EXISTS turnos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sucursal_id INT NOT NULL,
    local_id INT NOT NULL,
    usuario_id INT,
    sucursal VARCHAR(100),
    caja VARCHAR(50),
    fondo_caja DECIMAL(10,2) DEFAULT 0,
    fecha_inicio DATETIME,
    fecha_fin DATETIME NULL,
    estado ENUM('activo', 'cerrado') DEFAULT 'activo',
    corte_id INT NULL,
    notas TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_suc_local (sucursal_id, local_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS turno_inventario (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sucursal_id INT NOT NULL,
    local_id INT NOT NULL,
    turno_id INT,
    turno_local_id INT,
    producto_id INT,
    producto_nombre VARCHAR(150),
    stock_sistema DECIMAL(10,2) DEFAULT 0,
    stock_fisico DECIMAL(10,2) DEFAULT 0,
    diferencia DECIMAL(10,2) DEFAULT 0,
    unidad VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_suc_local (sucursal_id, local_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
