-- ============================================================
-- quoERP — Módulo de Inventario y Bodega
-- Migración: 004_inventory_module.sql
-- Versión: 1.0.0 | Fecha: 2026-06-04
-- ============================================================
-- Requiere: 001_create_all_tables.sql, 003_sales_module.sql
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ─────────────────────────────────────────────────────────────
-- AJUSTE 1: products — campos de soporte adicionales
-- ─────────────────────────────────────────────────────────────
ALTER TABLE products
    ADD COLUMN IF NOT EXISTS reorder_qty    INT UNSIGNED  DEFAULT 0
        COMMENT 'Cantidad mínima para orden de reposición',
    ADD COLUMN IF NOT EXISTS notes          TEXT          DEFAULT NULL
        COMMENT 'Notas internas del producto',
    ADD COLUMN IF NOT EXISTS is_purchasable TINYINT(1)    DEFAULT 1
        COMMENT '1 = se compra a proveedores',
    ADD COLUMN IF NOT EXISTS is_sellable    TINYINT(1)    DEFAULT 1
        COMMENT '1 = se vende a clientes';


-- ─────────────────────────────────────────────────────────────
-- TABLA 1: product_supplier_prices — Historial de precios de compra
--
-- Registra cada precio observado de un producto por proveedor.
-- Se pobla desde:
--   a) Registro manual en el módulo de Inventario
--   b) Recepción de Órdenes de Compra (módulo Compras, futuro)
--
-- Permite calcular por proveedor:
--   - Precio mínimo / máximo / promedio histórico
--   - Tendencia (sube/baja)
--   - Puntuación de competitividad vs el mejor precio
-- Esto facilita calificar proveedores y tomar decisiones de compra.
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS product_supplier_prices (
    id                  INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id          INT UNSIGNED    NOT NULL,
    product_id          INT UNSIGNED    NOT NULL,

    -- Proveedor: puede ser del catálogo (supplier_id) o libre (supplier_name_snap)
    supplier_id         INT UNSIGNED    DEFAULT NULL
        COMMENT 'FK a suppliers. NULL si el proveedor no está en el catálogo',
    supplier_name_snap  VARCHAR(200)    DEFAULT NULL
        COMMENT 'Nombre libre cuando supplier_id es NULL',

    -- Precio registrado
    unit_price          DECIMAL(12,4)   NOT NULL
        COMMENT 'Precio unitario de compra en moneda local',
    currency            VARCHAR(3)      NOT NULL DEFAULT 'USD',

    -- Origen del registro
    source              ENUM('manual','purchase_order','quote') NOT NULL DEFAULT 'manual'
        COMMENT 'manual=ingreso directo, purchase_order=desde OC recibida, quote=cotización',
    reference           VARCHAR(255)    DEFAULT NULL
        COMMENT 'N° factura, N° cotización, N° OC, etc.',
    notes               TEXT            DEFAULT NULL,

    -- Fecha en que este precio fue válido/observado
    price_date          DATE            NOT NULL,

    created_by          INT UNSIGNED    DEFAULT NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    INDEX idx_company   (company_id),
    INDEX idx_product   (product_id),
    INDEX idx_supplier  (supplier_id),
    INDEX idx_date      (price_date),

    CONSTRAINT fk_psp_company  FOREIGN KEY (company_id)  REFERENCES companies(id)  ON DELETE RESTRICT,
    CONSTRAINT fk_psp_product  FOREIGN KEY (product_id)  REFERENCES products(id)   ON DELETE CASCADE,
    CONSTRAINT fk_psp_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id)  ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Historial de precios de compra por producto y proveedor. Base para analítica de proveedores.';


-- ─────────────────────────────────────────────────────────────
-- AJUSTE 2: purchase_orders — agregar campo de precio unitario en líneas
-- (para cuando el módulo de Compras registre recepciones y poblar la
--  tabla product_supplier_prices automáticamente)
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS purchase_order_lines (
    id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    order_id        INT UNSIGNED    NOT NULL,
    product_id      INT UNSIGNED    DEFAULT NULL,
    description     VARCHAR(255)    NOT NULL,
    qty             DECIMAL(12,4)   NOT NULL DEFAULT 1,
    qty_received    DECIMAL(12,4)   NOT NULL DEFAULT 0,
    unit_price      DECIMAL(12,4)   NOT NULL DEFAULT 0,
    tax_rate        DECIMAL(5,2)    NOT NULL DEFAULT 0,
    subtotal        DECIMAL(12,2)   NOT NULL DEFAULT 0,

    PRIMARY KEY (id),
    INDEX idx_order   (order_id),
    INDEX idx_product (product_id),

    CONSTRAINT fk_pol_order   FOREIGN KEY (order_id)   REFERENCES purchase_orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_pol_product FOREIGN KEY (product_id) REFERENCES products(id)        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Líneas de órdenes de compra. qty_received se actualiza al recibir mercadería.';


SET FOREIGN_KEY_CHECKS = 1;

-- ─────────────────────────────────────────────────────────────
-- FIN DE MIGRACIÓN
-- Tabla nueva: product_supplier_prices, purchase_order_lines
-- ALTER TABLE: products (reorder_qty, notes, is_purchasable, is_sellable)
-- ─────────────────────────────────────────────────────────────