-- =============================================================================
-- 07 — STOCK + RECETAS: insumos, recetas de cada producto, movimientos
-- (El corazón del back office de la pizzería)
-- =============================================================================

-- Unidades de medida (kg, gr, lt, ml, unidad, etc.)
CREATE TABLE IF NOT EXISTS unidades_medida (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    codigo VARCHAR(10) UNIQUE NOT NULL,
    nombre VARCHAR(40) NOT NULL,
    tipo   ENUM('peso','volumen','unidad','tiempo') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO unidades_medida (codigo, nombre, tipo) VALUES
    ('kg',  'Kilogramo', 'peso'),
    ('gr',  'Gramo',     'peso'),
    ('lt',  'Litro',     'volumen'),
    ('ml',  'Mililitro', 'volumen'),
    ('un',  'Unidad',    'unidad'),
    ('doc', 'Docena',    'unidad'),
    ('cj',  'Caja',      'unidad'),
    ('bol', 'Bolsa',     'unidad');

-- INSUMOS: lo que comprás (harina, queso muzzarella, salsa de tomate, etc.)
-- A diferencia de "ingredientes" (que era lo que el cliente ve como adicional),
-- estos son los items reales de inventario.
CREATE TABLE IF NOT EXISTS insumos (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    codigo          VARCHAR(40) UNIQUE,             -- código interno o SKU
    nombre          VARCHAR(120) NOT NULL,          -- "Muzzarella primera"
    descripcion     TEXT,
    id_categoria_insumo INT NULL,
    id_unidad_compra    INT NOT NULL,               -- en qué unidad comprás (ej. 'kg')
    id_unidad_uso       INT NOT NULL,               -- en qué unidad usás en receta (ej. 'gr')
    factor_conversion   DECIMAL(10,4) DEFAULT 1,    -- 1 kg = 1000 gr → factor 1000
    activo          TINYINT DEFAULT 1,
    es_perecedero   TINYINT DEFAULT 0,
    dias_vencimiento INT NULL,                      -- estimado en días desde compra
    FOREIGN KEY (id_unidad_compra) REFERENCES unidades_medida(id),
    FOREIGN KEY (id_unidad_uso)    REFERENCES unidades_medida(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS categorias_insumos (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(60) NOT NULL,             -- 'Quesos', 'Harinas', 'Embutidos', 'Salsas', 'Bebidas insumo'
    orden  INT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO categorias_insumos (nombre, orden) VALUES
    ('Quesos',          1),
    ('Harinas y masas', 2),
    ('Embutidos',       3),
    ('Salsas',          4),
    ('Verduras',        5),
    ('Bebidas',         6),
    ('Empaque',         7),
    ('Limpieza',        8);

-- STOCK ACTUAL: snapshot del stock disponible de cada insumo
CREATE TABLE IF NOT EXISTS stock_actual (
    id_insumo           INT PRIMARY KEY,
    cantidad            DECIMAL(12,3) NOT NULL DEFAULT 0,      -- en unidad de uso (gr, ml, un)
    cantidad_minima     DECIMAL(12,3) DEFAULT 0,               -- punto de reorden
    cantidad_ideal      DECIMAL(12,3) DEFAULT 0,               -- nivel óptimo
    costo_promedio_unit DECIMAL(12,4) DEFAULT 0,               -- costo unitario promedio ponderado
    updated_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_insumo) REFERENCES insumos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- KARDEX: historial de TODOS los movimientos de stock (entrada, salida, ajuste, merma)
CREATE TABLE IF NOT EXISTS stock_movimientos (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    id_insumo       INT NOT NULL,
    tipo_mov        ENUM('entrada_compra','salida_venta','ajuste_manual','merma','transferencia','inicial') NOT NULL,
    cantidad        DECIMAL(12,3) NOT NULL,        -- positiva si entrada, negativa si salida
    costo_unit      DECIMAL(12,4),                 -- snapshot del costo al momento del mov
    saldo_post      DECIMAL(12,3),                 -- cuánto quedó después del mov
    id_referencia   INT NULL,                       -- id_compra, id_pedido, id_ajuste, etc.
    tipo_referencia VARCHAR(40),
    motivo          VARCHAR(255),
    id_usuario      INT NULL,
    fecha           TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_insumo) REFERENCES insumos(id),
    INDEX (id_insumo, fecha),
    INDEX (tipo_mov, fecha)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- RECETAS: qué insumos lleva cada producto + tamaño
-- Esto permite que al vender 1 pizza muzzarella grande, el sistema descuente
-- automáticamente: 250gr harina, 200gr muzzarella, 80ml salsa, etc.
CREATE TABLE IF NOT EXISTS recetas (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    id_producto     INT NOT NULL,
    id_tamanio      INT NULL,                  -- NULL si la receta es la misma para todos los tamaños
    nombre_receta   VARCHAR(120),              -- opcional, ej. "Muzzarella grande v2"
    rendimiento     INT DEFAULT 1,             -- cuántas unidades produce la receta
    notas_cocina    TEXT,
    version         INT DEFAULT 1,
    activa          TINYINT DEFAULT 1,
    fecha_alta      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_prod_tam_act (id_producto, id_tamanio, version),
    FOREIGN KEY (id_producto) REFERENCES productos(id) ON DELETE CASCADE,
    FOREIGN KEY (id_tamanio)  REFERENCES tamanios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS recetas_items (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    id_receta       INT NOT NULL,
    id_insumo       INT NOT NULL,
    cantidad        DECIMAL(12,3) NOT NULL,    -- en unidad de USO del insumo (gr, ml, un)
    es_opcional     TINYINT DEFAULT 0,         -- 1 si el cliente puede sacarlo
    es_extra        TINYINT DEFAULT 0,         -- 1 si solo se agrega como adicional
    orden           INT DEFAULT 0,
    FOREIGN KEY (id_receta) REFERENCES recetas(id) ON DELETE CASCADE,
    FOREIGN KEY (id_insumo) REFERENCES insumos(id),
    INDEX (id_receta, orden)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ALERTAS de stock bajo (se generan automáticamente)
CREATE TABLE IF NOT EXISTS stock_alertas (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    id_insumo       INT NOT NULL,
    tipo            ENUM('stock_bajo','sin_stock','vencimiento_proximo') NOT NULL,
    nivel_actual    DECIMAL(12,3),
    nivel_minimo    DECIMAL(12,3),
    fecha_generada  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_resuelta  TIMESTAMP NULL,
    resuelta        TINYINT DEFAULT 0,
    FOREIGN KEY (id_insumo) REFERENCES insumos(id),
    INDEX (resuelta, fecha_generada)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- MERMAS (lo que se desperdicia, vence, se cae al piso, etc.)
-- Ya está como tipo_mov en stock_movimientos, pero esta tabla guarda el detalle
CREATE TABLE IF NOT EXISTS mermas (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    id_insumo       INT NOT NULL,
    cantidad        DECIMAL(12,3) NOT NULL,
    motivo          ENUM('vencimiento','rotura','quemado','contaminacion','otros') NOT NULL,
    detalle         TEXT,
    costo_perdido   DECIMAL(12,2),
    id_usuario      INT,
    fecha           TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_insumo) REFERENCES insumos(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
