-- ============================================================
-- quoERP — Modulo de Produccion / MRP
-- Migracion: 032_production_mrp.sql
-- ============================================================
-- BOM (Lista de Materiales), Ordenes de Produccion,
-- Centros de Trabajo, y calculo MRP.
-- Integra con: products, inventory_stock, inventory_movements,
--              journal_entries (costeo).
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ─────────────────────────────────────────────────────────────
-- CENTROS DE TRABAJO — maquinas o areas de produccion
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS work_centers (
    id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id      INT UNSIGNED    NOT NULL,
    code            VARCHAR(20)     NOT NULL,
    name            VARCHAR(150)    NOT NULL,
    type            ENUM('machine','assembly','quality','storage','other')
                    NOT NULL DEFAULT 'assembly',
    capacity_per_day DECIMAL(10,2)  DEFAULT NULL
        COMMENT 'Capacidad de produccion por dia (unidades)',
    cost_per_hour   DECIMAL(12,4)   NOT NULL DEFAULT 0
        COMMENT 'Costo operativo por hora (maquina + operario)',
    is_active       TINYINT(1)      NOT NULL DEFAULT 1,
    description     TEXT            DEFAULT NULL,
    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_code (company_id, code),
    INDEX idx_company (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────
-- BOM HEADERS — encabezado de la lista de materiales
-- Un producto terminado puede tener una BOM activa
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS bom_headers (
    id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id      INT UNSIGNED    NOT NULL,
    product_id      INT UNSIGNED    NOT NULL
        COMMENT 'Producto terminado que se fabrica',
    version         VARCHAR(20)     NOT NULL DEFAULT '1.0'
        COMMENT 'Version de la BOM (1.0, 1.1, 2.0...)',
    name            VARCHAR(150)    DEFAULT NULL
        COMMENT 'Nombre descriptivo de la BOM',
    qty_base        DECIMAL(12,4)   NOT NULL DEFAULT 1.0000
        COMMENT 'Cantidad base de produccion (normalmente 1)',
    unit            VARCHAR(20)     DEFAULT NULL
        COMMENT 'Unidad del producto terminado',
    status          ENUM('draft','active','obsolete') NOT NULL DEFAULT 'draft',
    std_time_hours  DECIMAL(8,2)    DEFAULT NULL
        COMMENT 'Tiempo estandar de produccion en horas',
    notes           TEXT            DEFAULT NULL,
    created_by      INT UNSIGNED    DEFAULT NULL,
    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME        DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_company (company_id),
    INDEX idx_product (product_id),
    INDEX idx_status  (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────
-- BOM LINES — componentes / materiales de la BOM
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS bom_lines (
    id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    bom_id          INT UNSIGNED    NOT NULL,
    component_id    INT UNSIGNED    NOT NULL
        COMMENT 'Producto/materia prima componente',
    quantity        DECIMAL(12,4)   NOT NULL DEFAULT 1.0000
        COMMENT 'Cantidad requerida por qty_base del producto terminado',
    unit            VARCHAR(20)     DEFAULT NULL,
    waste_pct       DECIMAL(5,2)    NOT NULL DEFAULT 0.00
        COMMENT 'Porcentaje de merma/desperdicio esperado',
    work_center_id  INT UNSIGNED    DEFAULT NULL
        COMMENT 'Centro de trabajo donde se usa este componente',
    operation_time  DECIMAL(8,2)    DEFAULT NULL
        COMMENT 'Tiempo de operacion en minutos para este componente',
    notes           VARCHAR(300)    DEFAULT NULL,
    sort_order      TINYINT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    INDEX idx_bom       (bom_id),
    INDEX idx_component (component_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────
-- ORDENES DE PRODUCCION
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS production_orders (
    id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id      INT UNSIGNED    NOT NULL,
    number          VARCHAR(30)     NOT NULL
        COMMENT 'Numero interno: OP-YYYY-NNNNN',
    product_id      INT UNSIGNED    NOT NULL
        COMMENT 'Producto terminado a fabricar',
    product_name    VARCHAR(200)    NOT NULL COMMENT 'Snapshot',
    bom_id          INT UNSIGNED    NOT NULL,
    qty_planned     DECIMAL(12,4)   NOT NULL DEFAULT 1.0000
        COMMENT 'Cantidad planificada a producir',
    qty_produced    DECIMAL(12,4)   NOT NULL DEFAULT 0.0000
        COMMENT 'Cantidad realmente producida',
    qty_wasted      DECIMAL(12,4)   NOT NULL DEFAULT 0.0000
        COMMENT 'Cantidad con defectos / desechada',
    unit            VARCHAR(20)     DEFAULT NULL,
    warehouse_id    INT UNSIGNED    DEFAULT NULL
        COMMENT 'Bodega origen de materiales',
    output_warehouse_id INT UNSIGNED DEFAULT NULL
        COMMENT 'Bodega destino del producto terminado',
    planned_start   DATE            DEFAULT NULL,
    planned_end     DATE            DEFAULT NULL,
    actual_start    DATETIME        DEFAULT NULL,
    actual_end      DATETIME        DEFAULT NULL,
    status          ENUM('draft','planned','in_progress','completed','cancelled')
                    NOT NULL DEFAULT 'draft',
    -- Costos (calculados al completar)
    cost_materials  DECIMAL(14,4)   NOT NULL DEFAULT 0
        COMMENT 'Costo real de materiales consumidos',
    cost_labor      DECIMAL(14,4)   NOT NULL DEFAULT 0
        COMMENT 'Costo de mano de obra',
    cost_overhead   DECIMAL(14,4)   NOT NULL DEFAULT 0
        COMMENT 'Costos indirectos',
    cost_total      DECIMAL(14,4)   NOT NULL DEFAULT 0,
    cost_per_unit   DECIMAL(14,4)   NOT NULL DEFAULT 0,
    -- Contabilidad
    journal_entry_id INT UNSIGNED   DEFAULT NULL,
    -- Trazabilidad
    sale_order_id   INT UNSIGNED    DEFAULT NULL
        COMMENT 'OV que origino esta OP (opcional)',
    notes           TEXT            DEFAULT NULL,
    created_by      INT UNSIGNED    DEFAULT NULL,
    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME        DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_number (company_id, number),
    INDEX idx_company (company_id),
    INDEX idx_product (product_id),
    INDEX idx_status  (status),
    INDEX idx_planned (planned_start)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────
-- LINEAS DE CONSUMO DE LA ORDEN DE PRODUCCION
-- Materiales planificados y realmente consumidos
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS production_order_lines (
    id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    order_id        INT UNSIGNED    NOT NULL,
    component_id    INT UNSIGNED    NOT NULL,
    component_name  VARCHAR(200)    NOT NULL COMMENT 'Snapshot',
    unit            VARCHAR(20)     DEFAULT NULL,
    qty_planned     DECIMAL(12,4)   NOT NULL DEFAULT 0
        COMMENT 'Cantidad planificada (de la BOM x qty_planned)',
    qty_consumed    DECIMAL(12,4)   NOT NULL DEFAULT 0
        COMMENT 'Cantidad realmente consumida',
    qty_returned    DECIMAL(12,4)   NOT NULL DEFAULT 0
        COMMENT 'Sobrante devuelto al stock',
    unit_cost       DECIMAL(14,4)   NOT NULL DEFAULT 0
        COMMENT 'Costo unitario al momento de consumo',
    total_cost      DECIMAL(14,4)   NOT NULL DEFAULT 0,
    work_center_id  INT UNSIGNED    DEFAULT NULL,
    sort_order      TINYINT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    INDEX idx_order     (order_id),
    INDEX idx_component (component_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────
-- Columna para marcar productos como productos terminados (con BOM)
-- ─────────────────────────────────────────────────────────────
ALTER TABLE products
    ADD COLUMN IF NOT EXISTS is_manufactured TINYINT(1) NOT NULL DEFAULT 0
        COMMENT '1 = producto fabricado en planta (tiene BOM)',
    ADD COLUMN IF NOT EXISTS active_bom_id   INT UNSIGNED DEFAULT NULL
        COMMENT 'BOM activa para este producto';

-- ─────────────────────────────────────────────────────────────
-- Cuentas contables para produccion en company_account_map
-- ─────────────────────────────────────────────────────────────
ALTER TABLE company_account_map
    ADD COLUMN IF NOT EXISTS acc_wip          INT UNSIGNED DEFAULT NULL
        COMMENT 'Productos en proceso (Work in Progress)',
    ADD COLUMN IF NOT EXISTS acc_finished_goods INT UNSIGNED DEFAULT NULL
        COMMENT 'Productos terminados',
    ADD COLUMN IF NOT EXISTS acc_production_variance INT UNSIGNED DEFAULT NULL
        COMMENT 'Variacion de costos de produccion';

SET FOREIGN_KEY_CHECKS = 1;

-- ─────────────────────────────────────────────────────────────
-- FIN DE MIGRACION
-- Tablas nuevas: work_centers, bom_headers, bom_lines,
--                production_orders, production_order_lines
-- ALTER: products (is_manufactured, active_bom_id)
-- ALTER: company_account_map (acc_wip, acc_finished_goods)
-- ─────────────────────────────────────────────────────────────
