-- ============================================================
-- FashCognitive Apparel MES - Phase 2: Planning Board & Capacity
-- Shifts, machines, operator assignments, daily capacity plans.
-- ============================================================

CREATE TABLE IF NOT EXISTS production_shifts (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  unit_id int unsigned DEFAULT NULL COMMENT 'master_contractors.id',
  line_id int unsigned DEFAULT NULL COMMENT 'master_production_lines.id',
  shift_name varchar(50) NOT NULL,
  start_time time DEFAULT '08:00:00',
  end_time time DEFAULT '17:00:00',
  planned_operators int unsigned DEFAULT 0,
  status enum('active','inactive') NOT NULL DEFAULT 'active',
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY company_id (company_id),
  KEY unit_id (unit_id),
  KEY line_id (line_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS production_machines (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  unit_id int unsigned DEFAULT NULL COMMENT 'master_contractors.id',
  line_id int unsigned DEFAULT NULL COMMENT 'master_production_lines.id',
  work_center_id int unsigned DEFAULT NULL COMMENT 'master_work_centers.id',
  machine_code varchar(50) NOT NULL,
  machine_name varchar(150) NOT NULL,
  machine_type varchar(100) DEFAULT NULL,
  status enum('available','in_use','maintenance','breakdown') NOT NULL DEFAULT 'available',
  hourly_rate decimal(10,2) DEFAULT 0,
  notes text,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY machine_code (machine_code, company_id),
  KEY company_id (company_id),
  KEY unit_id (unit_id),
  KEY line_id (line_id),
  KEY work_center_id (work_center_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS production_operator_assignments (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  prod_order_id int unsigned DEFAULT NULL COMMENT 'production_orders.id',
  operation_id int unsigned DEFAULT NULL COMMENT 'production_order_operations.id',
  operator_id int unsigned DEFAULT NULL COMMENT 'users.id',
  operator_name varchar(150) DEFAULT NULL,
  line_id int unsigned DEFAULT NULL COMMENT 'master_production_lines.id',
  shift_id int unsigned DEFAULT NULL COMMENT 'production_shifts.id',
  machine_id int unsigned DEFAULT NULL COMMENT 'production_machines.id',
  assigned_date date DEFAULT NULL,
  start_time time DEFAULT NULL,
  end_time time DEFAULT NULL,
  role varchar(50) DEFAULT NULL,
  status enum('assigned','in_progress','completed','released') NOT NULL DEFAULT 'assigned',
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY company_id (company_id),
  KEY prod_order_id (prod_order_id),
  KEY operation_id (operation_id),
  KEY operator_id (operator_id),
  KEY line_id (line_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS production_capacity_plans (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  plan_date date NOT NULL,
  unit_id int unsigned DEFAULT NULL COMMENT 'master_contractors.id',
  line_id int unsigned DEFAULT NULL COMMENT 'master_production_lines.id',
  production_order_id int unsigned DEFAULT NULL COMMENT 'production_orders.id',
  operation_id int unsigned DEFAULT NULL COMMENT 'production_order_operations.id',
  planned_pcs int unsigned DEFAULT 0,
  planned_hours decimal(8,2) DEFAULT 0,
  load_pct decimal(6,2) DEFAULT 0,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY company_id (company_id),
  KEY plan_date (plan_date),
  KEY line_id (line_id),
  KEY production_order_id (production_order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
