-- ============================================================
-- AGG-UAE ERP — Migration 44 — Phase M7-p2
--   Production-Schedule projection + audit
-- ============================================================
--
-- Per spec tab 02:
--   - JOBFORM_SCHEDULE_DAY = per-(JF, glass, stage, day) projection.
--     Wiped-and-rewritten on every scheduler recompute (only the
--     latest run is "live").
--   - SCHEDULE_RUN = append-only audit row per recompute event.
--     Answers "when did the forecast last change?" + carries a
--     small summary JSON so post-hoc forensics are possible
--     without re-storing every prior projection.
--
-- Both new InnoDB tables — no row-size posture concern. Standard
-- AUTO_INCREMENT PKs. Idempotent via CREATE TABLE IF NOT EXISTS.

CREATE TABLE IF NOT EXISTS SCHEDULE_RUN (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    computed_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    computed_by     VARCHAR(100) NULL,
    jf_count        INT NOT NULL DEFAULT 0,
    status          ENUM('ok','warn','error') NOT NULL DEFAULT 'ok',
    summary_json    MEDIUMTEXT NULL,
    INDEX idx_computed_at (computed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS JOBFORM_SCHEDULE_DAY (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    run_id              INT NOT NULL,
    jobform_id          INT NOT NULL,
    jobform_glass_id    INT NULL,
    stage               ENUM(
                            'Glass Cutting','Film Cutting','PC Cutting',
                            'Glass Bending','Ceramic Printing','Primer Printing',
                            'Printing','Lamination','Autoclave','Final Inspection'
                        ) NOT NULL,
    section             VARCHAR(50) NULL,
    day_date            DATE NOT NULL,
    shift_or_cycle_no   VARCHAR(20) NULL,
    qty_allocated       INT NOT NULL DEFAULT 0,
    est_minutes         INT NOT NULL DEFAULT 0,
    is_stock_pull_pill  TINYINT(1) NOT NULL DEFAULT 0,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_run_day   (run_id, day_date),
    INDEX idx_jf_day    (jobform_id, day_date),
    INDEX idx_stage_day (stage, day_date),
    CONSTRAINT fk_jsd_run FOREIGN KEY (run_id) REFERENCES SCHEDULE_RUN (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;