-- llx_planning_* DDL (Dolibarr custom module)
-- Notes:
-- - Uses standard Dolibarr fields: entity, tms, fk_user_creat, fk_user_modif
-- - Storage engine: InnoDB, charset: utf8mb4
-- - Day boundary logic (06:30 -> 06:30) is handled in application code; planning_day_date stores the "planning day" date key.

SET NAMES utf8mb4;

-- -----------------------------------------------------------------------------
-- Planning jobs (Forming/Trimming). MVP fields for Forming A..U are included.
-- Trimming-specific fields can be added later (either extra columns or a JSON payload).
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS llx_planning_job (
  rowid                     INT UNSIGNED NOT NULL AUTO_INCREMENT,
  entity                    INT NOT NULL DEFAULT 1,

  fk_workstation            INT UNSIGNED NOT NULL,
  group_code                VARCHAR(16) NOT NULL DEFAULT 'forming',  -- forming|trimming (extendable)
  status                    VARCHAR(24) NOT NULL DEFAULT 'planned',  -- planned|needs_ruleset|incomplete_data|converted|cancelled

  fk_ruleset                INT UNSIGNED NULL,
  fk_mo                     INT UNSIGNED NULL,

  sort_order                INT NOT NULL DEFAULT 0,

  date_start                DATETIME NULL,
  date_end                  DATETIME NULL,

  estimated_hours           DECIMAL(10,2) NULL,
  remaining_hours_override  DECIMAL(10,2) NULL,

  -- Shared link keys (no hard FK between jobs; link via business keys)
  fg                        VARCHAR(64) NULL,
  part_no                   VARCHAR(128) NULL,
  tool_no                   VARCHAR(64) NULL,
  metal_code                VARCHAR(64) NULL,
  parts_per_sheet           VARCHAR(64) NULL,
  setup_dia_clamp_req       VARCHAR(255) NULL,
  works_order_no            VARCHAR(64) NULL,
  pack_number               VARCHAR(64) NULL,
  metal_cut                 VARCHAR(64) NULL,
  form_fixture              VARCHAR(64) NULL,
  customer_no               VARCHAR(64) NULL,
  customer                  VARCHAR(255) NULL,
  description               VARCHAR(255) NULL,
  batch_qty                 VARCHAR(64) NULL,
  qty_per_hr                VARCHAR(64) NULL,
  total_time                VARCHAR(64) NULL,
  laser_stillage            VARCHAR(64) NULL,
  metal_called_off          VARCHAR(8) NULL,
  metal_at_worc             VARCHAR(8) NULL,
  job_wallet                VARCHAR(8) NULL,
  notes                     TEXT NULL,

  tms                       TIMESTAMP NULL DEFAULT NULL,
  fk_user_creat             INT UNSIGNED NULL,
  fk_user_modif             INT UNSIGNED NULL,

  PRIMARY KEY (rowid),

  KEY idx_planning_job_entity (entity),
  KEY idx_planning_job_ws (entity, fk_workstation),
  KEY idx_planning_job_group (entity, group_code),
  KEY idx_planning_job_status (entity, status),
  KEY idx_planning_job_order (entity, fk_workstation, sort_order),
  KEY idx_planning_job_linkkeys (entity, works_order_no, pack_number, fg),
  KEY idx_planning_job_dates (entity, fk_workstation, date_start, date_end)

  -- Optional FKs (enable if you want strict constraints and table names match your Dolibarr schema):
  -- ,CONSTRAINT fk_planning_job_workstation FOREIGN KEY (fk_workstation) REFERENCES llx_workstation_workstation(rowid)
  -- ,CONSTRAINT fk_planning_job_mo FOREIGN KEY (fk_mo) REFERENCES llx_mrp_mo(rowid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- -----------------------------------------------------------------------------
-- Default capacity per machine (hours per planning day)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS llx_planning_capacity (
  rowid                    INT UNSIGNED NOT NULL AUTO_INCREMENT,
  entity                   INT NOT NULL DEFAULT 1,

  fk_workstation           INT UNSIGNED NOT NULL,
  capacity_hours_per_day   DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  active                   TINYINT NOT NULL DEFAULT 1,

  tms                      TIMESTAMP NULL DEFAULT NULL,
  fk_user_creat            INT UNSIGNED NULL,
  fk_user_modif            INT UNSIGNED NULL,

  PRIMARY KEY (rowid),
  UNIQUE KEY uk_planning_capacity_ws (entity, fk_workstation),

  KEY idx_planning_capacity_entity (entity),
  KEY idx_planning_capacity_active (entity, active)

  -- Optional FK:
  -- ,CONSTRAINT fk_planning_capacity_workstation FOREIGN KEY (fk_workstation) REFERENCES llx_workstation_workstation(rowid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- -----------------------------------------------------------------------------
-- Capacity override per planning day (date key, 06:30 boundary handled in code)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS llx_planning_capacity_override (
  rowid             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  entity            INT NOT NULL DEFAULT 1,

  fk_workstation    INT UNSIGNED NOT NULL,
  planning_day_date DATE NOT NULL,                    -- "planning day" key (not calendar day boundary)
  capacity_hours    DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  note              VARCHAR(255) NULL,

  tms               TIMESTAMP NULL DEFAULT NULL,
  fk_user_creat     INT UNSIGNED NULL,
  fk_user_modif     INT UNSIGNED NULL,

  PRIMARY KEY (rowid),
  UNIQUE KEY uk_planning_cap_ovr (entity, fk_workstation, planning_day_date),

  KEY idx_planning_cap_ovr_entity (entity),
  KEY idx_planning_cap_ovr_ws (entity, fk_workstation),
  KEY idx_planning_cap_ovr_day (entity, planning_day_date)

  -- Optional FK:
  -- ,CONSTRAINT fk_planning_cap_ovr_workstation FOREIGN KEY (fk_workstation) REFERENCES llx_workstation_workstation(rowid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- -----------------------------------------------------------------------------
-- Downtime events (from-to) for machines
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS llx_planning_downtime (
  rowid            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  entity           INT NOT NULL DEFAULT 1,

  fk_workstation   INT UNSIGNED NOT NULL,
  date_start       DATETIME NOT NULL,
  date_end         DATETIME NOT NULL,
  type             VARCHAR(16) NOT NULL DEFAULT 'planned', -- planned|unplanned (extendable)
  note             VARCHAR(255) NULL,

  tms              TIMESTAMP NULL DEFAULT NULL,
  fk_user_creat    INT UNSIGNED NULL,
  fk_user_modif    INT UNSIGNED NULL,

  PRIMARY KEY (rowid),

  KEY idx_planning_dt_entity (entity),
  KEY idx_planning_dt_ws (entity, fk_workstation),
  KEY idx_planning_dt_range (entity, fk_workstation, date_start, date_end)

  -- Optional FK:
  -- ,CONSTRAINT fk_planning_dt_workstation FOREIGN KEY (fk_workstation) REFERENCES llx_workstation_workstation(rowid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
