-- Complaint & Rework Management for the existing invoice/invoice_detail workflow.
-- Safe to run repeatedly. Existing transaction and SPK rows are never altered.
CREATE TABLE IF NOT EXISTS complaint_permissions (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT, id_level INT NOT NULL, permission VARCHAR(40) NOT NULL,
  created_at DATETIME NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_complaint_permission (id_level, permission)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS complaints (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT, complaint_no VARCHAR(30) NOT NULL,
  transaction_id INT NOT NULL, customer_id INT NOT NULL, original_spk_detail_id INT NULL,
  complaint_date DATE NOT NULL, complaint_type VARCHAR(60) NOT NULL, description TEXT NOT NULL,
  customer_request TEXT NULL, internal_note TEXT NULL, order_qty DECIMAL(15,2) NOT NULL DEFAULT 0,
  complaint_qty DECIMAL(15,2) NOT NULL DEFAULT 0, rework_qty DECIMAL(15,2) NOT NULL DEFAULT 0,
  priority ENUM('LOW','NORMAL','HIGH','URGENT') NOT NULL DEFAULT 'NORMAL',
  status ENUM('OPEN','VERIFICATION','APPROVED','REWORK','QC','READY','DELIVERED','CLOSED','REJECTED','CANCELLED') NOT NULL DEFAULT 'OPEN',
  pic_user_id INT NULL, root_cause_department VARCHAR(40) NULL, root_cause_user_id INT NULL,
  root_cause_category VARCHAR(80) NULL, root_cause_note TEXT NULL,
  resolution_type VARCHAR(40) NULL, resolution_note TEXT NULL, approved_by INT NULL, approved_at DATETIME NULL,
  created_by INT NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, closed_at DATETIME NULL,
  PRIMARY KEY (id), UNIQUE KEY uk_complaint_no (complaint_no), KEY idx_complaint_transaction (transaction_id),
  KEY idx_complaint_status_date (status, complaint_date), KEY idx_complaint_customer (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS complaint_items (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT, complaint_id INT UNSIGNED NOT NULL, transaction_detail_id INT NOT NULL,
  product_id INT NULL, product_name VARCHAR(190) NOT NULL, order_qty DECIMAL(15,2) NOT NULL DEFAULT 0,
  complaint_qty DECIMAL(15,2) NOT NULL DEFAULT 0, rework_qty DECIMAL(15,2) NOT NULL DEFAULT 0,
  issue_description TEXT NULL, PRIMARY KEY (id), UNIQUE KEY uk_complaint_item (complaint_id, transaction_detail_id),
  KEY idx_complaint_item_complaint (complaint_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS complaint_files (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT, complaint_id INT UNSIGNED NOT NULL, file_name VARCHAR(190) NOT NULL,
  original_name VARCHAR(190) NOT NULL, file_path VARCHAR(255) NOT NULL, file_type VARCHAR(80) NOT NULL,
  file_size INT UNSIGNED NOT NULL DEFAULT 0, uploaded_by INT NOT NULL, created_at DATETIME NOT NULL,
  PRIMARY KEY (id), KEY idx_complaint_file (complaint_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS complaint_reworks (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT, complaint_id INT UNSIGNED NOT NULL, original_spk_detail_id INT NOT NULL,
  rework_no VARCHAR(30) NOT NULL, rework_spk_no VARCHAR(45) NOT NULL, qty DECIMAL(15,2) NOT NULL,
  status ENUM('PENDING','IN_PROGRESS','QC','DONE','CANCELLED') NOT NULL DEFAULT 'PENDING',
  started_at DATETIME NULL, finished_at DATETIME NULL, created_by INT NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL,
  PRIMARY KEY (id), UNIQUE KEY uk_rework_no (rework_no), UNIQUE KEY uk_rework_spk_no (rework_spk_no),
  KEY idx_rework_complaint (complaint_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS complaint_rework_processes (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT, rework_id INT UNSIGNED NOT NULL, process_type VARCHAR(40) NOT NULL,
  process_name VARCHAR(80) NOT NULL, sequence_no SMALLINT UNSIGNED NOT NULL,
  assigned_to INT NULL, status ENUM('PENDING','IN_PROGRESS','DONE','SKIPPED','CANCELLED') NOT NULL DEFAULT 'PENDING',
  started_at DATETIME NULL, finished_at DATETIME NULL, notes TEXT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL,
  PRIMARY KEY (id), KEY idx_rework_process (rework_id, sequence_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS complaint_costs (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT, complaint_id INT UNSIGNED NOT NULL, rework_id INT UNSIGNED NOT NULL,
  cost_type VARCHAR(40) NOT NULL, description VARCHAR(255) NOT NULL, qty DECIMAL(15,2) NOT NULL,
  unit VARCHAR(30) NOT NULL, unit_cost DECIMAL(18,2) NOT NULL, total_cost DECIMAL(18,2) NOT NULL,
  created_by INT NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id), KEY idx_complaint_cost (complaint_id, rework_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS complaint_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, complaint_id INT UNSIGNED NOT NULL, user_id INT NOT NULL,
  action VARCHAR(50) NOT NULL, old_status VARCHAR(30) NULL, new_status VARCHAR(30) NULL,
  description TEXT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id), KEY idx_complaint_log (complaint_id, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO menuadmin (idparent,id_level,nama_menu,link,target,link_on,treeview,classes,classicon,icon,aktif,level,urutan)
SELECT 260,'1,2,3,4,5,6','Manajemen Komplain','komplain','_self','Y','treeview',NULL,'Y','fa-comments','Y',NULL,54
WHERE NOT EXISTS (SELECT 1 FROM menuadmin WHERE link='komplain');

-- Resolve role IDs from the project's own role slugs; no numeric role ID is hardcoded.
INSERT IGNORE INTO complaint_permissions (id_level,permission,created_at)
SELECT h.id_level,p.permission,NOW() FROM hak_akses h
JOIN (SELECT 'complaint.*' permission) p WHERE h.level IN ('admin','owner');
INSERT IGNORE INTO complaint_permissions (id_level,permission,created_at)
SELECT h.id_level,p.permission,NOW() FROM hak_akses h
JOIN (SELECT 'complaint.view' permission UNION ALL SELECT 'complaint.create') p WHERE h.level='kasir';
INSERT IGNORE INTO complaint_permissions (id_level,permission,created_at)
SELECT h.id_level,p.permission,NOW() FROM hak_akses h
JOIN (SELECT 'complaint.view' permission UNION ALL SELECT 'complaint.cost' UNION ALL SELECT 'complaint.report') p WHERE h.level='keu';
INSERT IGNORE INTO complaint_permissions (id_level,permission,created_at)
SELECT h.id_level,p.permission,NOW() FROM hak_akses h
JOIN (SELECT 'complaint.view' permission UNION ALL SELECT 'complaint.rework') p WHERE h.level IN ('desain','operator');
