-- OEE Module — install.sql
-- Creates tables: llx_oee_downtime, llx_oee_shiftplan, llx_oee_reason_category
-- Safe to re-run: uses CREATE TABLE IF NOT EXISTS, INSERT IGNORE

-- ============================================================
-- TABLE: llx_oee_reason_category
-- ============================================================
CREATE TABLE IF NOT EXISTS llx_oee_reason_category (
  rowid      INT          NOT NULL AUTO_INCREMENT,
  code       VARCHAR(30)  NOT NULL,
  label      VARCHAR(100) NOT NULL,
  color      VARCHAR(10)  DEFAULT '#888888',
  is_planned TINYINT(1)   NOT NULL DEFAULT 0 COMMENT '1=planned loss excluded from Availability',
  entity     INT          NOT NULL DEFAULT 1,
  PRIMARY KEY (rowid),
  UNIQUE KEY uq_oee_reason_code (code, entity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed default reason categories
INSERT IGNORE INTO llx_oee_reason_category (code, label, color, is_planned, entity)
VALUES
  ('BREAKDOWN',   'Machine Breakdown',    '#e74c3c', 0, 1),
  ('CHANGEOVER',  'Setup / Changeover',   '#f39c12', 1, 1),
  ('NO_MATERIAL', 'No Material',          '#e67e22', 0, 1),
  ('NO_OPERATOR', 'No Operator',          '#9b59b6', 0, 1),
  ('PLAN_MAINT',  'Planned Maintenance',  '#3498db', 1, 1),
  ('OTHER',       'Other',                '#95a5a6', 0, 1);

-- ============================================================
-- TABLE: llx_oee_downtime
-- ============================================================
CREATE TABLE IF NOT EXISTS llx_oee_downtime (
  rowid          INT          NOT NULL AUTO_INCREMENT,
  fk_workstation INT          NOT NULL,
  reason_code    VARCHAR(30)  NOT NULL,
  reason_detail  TEXT         DEFAULT NULL,
  dt_start       DATETIME     NOT NULL,
  dt_end         DATETIME     DEFAULT NULL COMMENT 'NULL = ongoing',
  duration_min   INT          DEFAULT NULL COMMENT 'TIMESTAMPDIFF(MINUTE, dt_start, dt_end)',
  fk_user_creat  INT          DEFAULT NULL,
  fk_mo          INT          DEFAULT NULL COMMENT 'Optional link to MO',
  entity         INT          NOT NULL DEFAULT 1,
  tms            TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (rowid),
  KEY idx_oee_dt_ws      (fk_workstation),
  KEY idx_oee_dt_start   (dt_start),
  KEY idx_oee_dt_entity  (entity),
  KEY idx_oee_dt_mo      (fk_mo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: llx_oee_shiftplan
-- ============================================================
CREATE TABLE IF NOT EXISTS llx_oee_shiftplan (
  rowid               INT          NOT NULL AUTO_INCREMENT,
  fk_workstation      INT          NOT NULL,
  shift_label         VARCHAR(50)  NOT NULL DEFAULT 'Morning',
  plan_date           DATE         NOT NULL,
  planned_minutes     INT          NOT NULL DEFAULT 480,
  target_qty_per_shift DOUBLE      NOT NULL DEFAULT 0,
  fk_user_creat       INT          DEFAULT NULL,
  entity              INT          NOT NULL DEFAULT 1,
  tms                 TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (rowid),
  KEY idx_oee_sp_ws     (fk_workstation),
  KEY idx_oee_sp_date   (plan_date),
  KEY idx_oee_sp_entity (entity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
