-- ============================================================
-- quoERP — MRP Avanzado: Planificacion, Capacidad, Scheduling
-- Migracion: 033_mrp_advanced.sql
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ─────────────────────────────────────────────────────────────
-- Ampliar products con campos MRP
-- ─────────────────────────────────────────────────────────────
ALTER TABLE products
    ADD COLUMN IF NOT EXISTS lead_time_days   SMALLINT UNSIGNED NOT NULL DEFAULT 0
        COMMENT 'Dias de entrega del proveedor (tiempo de espera)',
    ADD COLUMN IF NOT EXISTS safety_stock     DECIMAL(12,4) NOT NULL DEFAULT 0
        COMMENT 'Stock de seguridad minimo a mantener siempre',
    ADD COLUMN IF NOT EXISTS reorder_point    DECIMAL(12,4) NOT NULL DEFAULT 0
        COMMENT 'Punto de reorden: cantidad en la que se dispara alerta de compra',
    ADD COLUMN IF NOT EXISTS lot_size         DECIMAL(12,4) NOT NULL DEFAULT 1
        COMMENT 'Tamano de lote minimo de produccion o compra',
    ADD COLUMN IF NOT EXISTS mrp_policy       ENUM('lot_for_lot','fixed_lot','min_max')
                                              NOT NULL DEFAULT 'lot_for_lot'
        COMMENT 'Politica MRP: lot_for_lot=exacto, fixed_lot=lote fijo, min_max=min-max';

-- ─────────────────────────────────────────────────────────────
-- Turnos de trabajo para centros de trabajo
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS work_center_shifts (
    id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    work_center_id  INT UNSIGNED    NOT NULL,
    name            VARCHAR(60)     NOT NULL COMMENT 'Turno manana, tarde, noche...',
    day_of_week     TINYINT UNSIGNED NOT NULL
        COMMENT '0=Lun,1=Mar,2=Mie,3=Jue,4=Vie,5=Sab,6=Dom',
    start_time      TIME            NOT NULL,
    end_time        TIME            NOT NULL,
    effective_hours DECIMAL(4,2)    GENERATED ALWAYS AS
        (TIME_TO_SEC(TIMEDIFF(end_time, start_time)) / 3600.0) STORED
        COMMENT 'Horas efectivas del turno',
    is_active       TINYINT(1)      NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    INDEX idx_wc (work_center_id),
    INDEX idx_day (day_of_week)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────
-- Ordenes planificadas MRP (sugerencias automaticas)
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS mrp_planned_orders (
    id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id      INT UNSIGNED    NOT NULL,
    product_id      INT UNSIGNED    NOT NULL,
    product_name    VARCHAR(200)    NOT NULL COMMENT 'Snapshot',
    order_type      ENUM('production','purchase') NOT NULL DEFAULT 'production',
    qty_required    DECIMAL(12,4)   NOT NULL DEFAULT 0 COMMENT 'Necesidad neta calculada',
    qty_planned     DECIMAL(12,4)   NOT NULL DEFAULT 0 COMMENT 'Cantidad del lote sugerido',
    need_date       DATE            NOT NULL COMMENT 'Fecha en que se necesita',
    start_date      DATE            NOT NULL COMMENT 'Fecha de inicio sugerida (backward scheduling)',
    level           TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Nivel en la explosion de la BOM (0=producto final)',
    parent_order_id INT UNSIGNED    DEFAULT NULL COMMENT 'OP padre que origino esta necesidad',
    source_demand   VARCHAR(200)    DEFAULT NULL COMMENT 'Origen de la demanda (OV, OP, manual)',
    status          ENUM('suggested','approved','converted','cancelled') NOT NULL DEFAULT 'suggested',
    converted_to_id INT UNSIGNED    DEFAULT NULL COMMENT 'ID de la OP o OC generada',
    run_id          VARCHAR(36)     NOT NULL COMMENT 'UUID del run de MRP que genero esta orden',
    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_company   (company_id),
    INDEX idx_product   (product_id),
    INDEX idx_run       (run_id),
    INDEX idx_status    (status),
    INDEX idx_need_date (need_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────
-- Scheduling de produccion — slots asignados por OP y centro
-- ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS production_schedule_slots (
    id              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    company_id      INT UNSIGNED    NOT NULL,
    order_id        INT UNSIGNED    NOT NULL COMMENT 'FK production_orders',
    work_center_id  INT UNSIGNED    DEFAULT NULL,
    scheduled_start DATETIME        NOT NULL,
    scheduled_end   DATETIME        NOT NULL,
    hours_required  DECIMAL(8,2)    NOT NULL DEFAULT 0,
    sequence        SMALLINT UNSIGNED NOT NULL DEFAULT 0
        COMMENT 'Prioridad/secuencia dentro del centro de trabajo',
    status          ENUM('scheduled','in_progress','done','cancelled') NOT NULL DEFAULT 'scheduled',
    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_order      (order_id),
    INDEX idx_wc         (work_center_id),
    INDEX idx_scheduled  (scheduled_start, scheduled_end)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ─────────────────────────────────────────────────────────────
-- FIN DE MIGRACION
-- ALTER: products (lead_time_days, safety_stock, reorder_point, lot_size, mrp_policy)
-- Tablas nuevas: work_center_shifts, mrp_planned_orders, production_schedule_slots
-- ─────────────────────────────────────────────────────────────