-- =========================================================
-- 030_spk_foundation.sql
-- Sprint 12: SPK module foundation
-- =========================================================

CREATE TABLE IF NOT EXISTS production_departments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  code VARCHAR(40) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_production_departments_location_code (location_id, code),
  UNIQUE KEY uq_production_departments_location_name (location_id, name),
  KEY idx_production_departments_location_active (location_id, is_active),
  CONSTRAINT fk_production_departments_location FOREIGN KEY (location_id) REFERENCES locations(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS production_department_categories (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  department_id BIGINT UNSIGNED NOT NULL,
  category_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_production_department_category_pair (department_id, category_id),
  KEY idx_production_department_categories_category (category_id),
  CONSTRAINT fk_production_department_categories_department FOREIGN KEY (department_id) REFERENCES production_departments(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_production_department_categories_category FOREIGN KEY (category_id) REFERENCES categories(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS spk (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  id_order BIGINT UNSIGNED NULL,
  no_spk VARCHAR(60) NOT NULL,
  tanggal DATE NOT NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_by_name_snapshot VARCHAR(120) NULL,
  customer_id BIGINT UNSIGNED NULL,
  customer_name_snapshot VARCHAR(180) NULL,
  jenis_customer_snapshot VARCHAR(40) NULL,
  nama_folder VARCHAR(180) NOT NULL,
  note TEXT NULL,
  pengambilan ENUM('diambil','dikirim') NOT NULL,
  catatan_pengambilan VARCHAR(255) NULL,
  status_spk ENUM('antrian','proses','selesai','canceled') NOT NULL DEFAULT 'antrian',
  status_serah ENUM('belum','sudah') NOT NULL DEFAULT 'belum',
  penyerah_id BIGINT UNSIGNED NULL,
  penyerah_name_snapshot VARCHAR(120) NULL,
  jam_serah DATETIME NULL,
  landing_token VARCHAR(80) NOT NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_spk_location_no_spk (location_id, no_spk),
  UNIQUE KEY uq_spk_landing_token (landing_token),
  KEY idx_spk_location_tanggal (location_id, tanggal),
  KEY idx_spk_customer (customer_id),
  KEY idx_spk_status (location_id, status_spk, status_serah),
  KEY idx_spk_order (id_order),
  CONSTRAINT fk_spk_location FOREIGN KEY (location_id) REFERENCES locations(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_spk_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_spk_order FOREIGN KEY (id_order) REFERENCES orders(id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS spk_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  spk_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NULL,
  product_name_snapshot VARCHAR(180) NOT NULL,
  category_id BIGINT UNSIGNED NULL,
  category_name_snapshot VARCHAR(120) NULL,
  department_id BIGINT UNSIGNED NULL,
  department_name_snapshot VARCHAR(120) NULL,
  ukuran VARCHAR(120) NULL,
  qty VARCHAR(120) NOT NULL DEFAULT '',
  finishing VARCHAR(180) NULL,
  note VARCHAR(255) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_spk_items_spk (spk_id),
  KEY idx_spk_items_product (product_id),
  KEY idx_spk_items_category (category_id),
  KEY idx_spk_items_department (department_id),
  CONSTRAINT fk_spk_items_spk FOREIGN KEY (spk_id) REFERENCES spk(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_spk_items_product FOREIGN KEY (product_id) REFERENCES products(id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_spk_items_category FOREIGN KEY (category_id) REFERENCES categories(id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_spk_items_department FOREIGN KEY (department_id) REFERENCES production_departments(id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS spk_department_progress (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  spk_id BIGINT UNSIGNED NOT NULL,
  department_id BIGINT UNSIGNED NOT NULL,
  status ENUM('antrian','proses','selesai') NOT NULL DEFAULT 'antrian',
  started_at DATETIME NULL,
  finished_at DATETIME NULL,
  updated_by_user_id BIGINT UNSIGNED NULL,
  updated_by_name_snapshot VARCHAR(120) NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_spk_department_progress_pair (spk_id, department_id),
  KEY idx_spk_department_progress_status (spk_id, status),
  CONSTRAINT fk_spk_department_progress_spk FOREIGN KEY (spk_id) REFERENCES spk(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_spk_department_progress_department FOREIGN KEY (department_id) REFERENCES production_departments(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;