-- Planning Schedule — day-level hour allocation per MO per workstation
-- One row = "MO X is planned for N hours on date D at workstation W"
-- PHASE 1 — created 2026-03-26

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS llx_planning_schedule (
    rowid          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    fk_mo          INT UNSIGNED NOT NULL,              -- llx_mrp_mo.rowid
    fk_ws          INT UNSIGNED NOT NULL,              -- llx_workstation_workstation.rowid
    schedule_date  DATE NOT NULL,                      -- the planned day
    planned_hours  DECIMAL(5,2) NOT NULL DEFAULT 0,   -- hours allocated (0 = remove from plan)
    qty_planned    DECIMAL(10,2) NULL,                 -- derived: planned_hours * qty_per_hr (optional cache)
    source         VARCHAR(20) NOT NULL DEFAULT 'manual', -- 'manual' | 'excel' | 'api'
    note           VARCHAR(255) NULL,
    fk_user        INT UNSIGNED NULL,
    date_sync      DATETIME NULL,
    tms            TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    entity         INT NOT NULL DEFAULT 1,

    PRIMARY KEY (rowid),
    UNIQUE KEY uq_ps_mo_date_ws (fk_mo, schedule_date, fk_ws, entity),
    KEY idx_ps_entity      (entity),
    KEY idx_ps_ws_date     (entity, fk_ws, schedule_date),
    KEY idx_ps_mo          (entity, fk_mo),
    KEY idx_ps_date_range  (entity, fk_ws, schedule_date, planned_hours)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
