-- =============================================
-- DATABASE : perawatan  |  Versi 4.0
-- TABLES   : data_pr, data_pegawai, data_cc
-- =============================================
CREATE DATABASE IF NOT EXISTS perawatan CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE perawatan;

-- ─────────────────────────────────────────────
-- 1. data_pegawai
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS data_pegawai (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  NIK_Pegawai   VARCHAR(30)  NOT NULL UNIQUE,
  Password      VARCHAR(255) NOT NULL,
  Nama_Pegawai  VARCHAR(100),
  Jabatan       VARCHAR(100),
  Departemen    VARCHAR(100),
  Email         VARCHAR(100),
  Role          VARCHAR(30)  DEFAULT 'planner',
  is_active     TINYINT(1)   DEFAULT 1,
  created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

ALTER TABLE data_pegawai ADD COLUMN IF NOT EXISTS Role VARCHAR(30) DEFAULT 'planner' AFTER Email;

-- NIK Planner grup A : approval 1=31385, 2=30662, 3=31196
-- NIK Planner grup B : approval 1=31385, 2=30909, 3=31196
-- NIK Approver       : 31385, 30662, 30909, 31196
INSERT IGNORE INTO data_pegawai (NIK_Pegawai, Password, Nama_Pegawai, Jabatan, Departemen, Role) VALUES
('31833','password123','Ahmad Fauzi',      'Planner Senior',  'Maintenance','planner'),
('31539','password123','Siti Rahayu',      'Planner',         'Maintenance','planner'),
('32158','password123','Budi Hartono',     'Planner',         'Operasional','planner'),
('31877','password123','Dewi Kusuma',      'Planner Senior',  'Maintenance','planner'),
('31835','password123','Reza Firmansyah',  'Planner',         'Maintenance','planner'),
('31733','password123','Linda Wijaya',     'Planner',         'Operasional','planner'),
('31385','password123','Ir. Hendra Saputra','Supervisor',     'Maintenance','approver'),
('30662','password123','Drs. Agung Wibowo','Manager',         'Maintenance','approver'),
('30909','password123','Ir. Sri Mulyani',  'Manager',         'Operasional','approver'),
('31196','password123','Dr. Bambang Susilo','General Manager','Perawatan',  'approver'),
('1003', 'admin123',   'Admin Sistem',     'Administrator',   'IT',         'admin');

-- ─────────────────────────────────────────────
-- 2. data_cc
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS data_cc (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  Cost_Center VARCHAR(100) NOT NULL UNIQUE,
  Keterangan  VARCHAR(255),
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT IGNORE INTO data_cc (Cost_Center, Keterangan) VALUES
('CC-01','Maintenance Unit 1'),
('CC-02','Maintenance Unit 2'),
('CC-03','Operasional Tambang'),
('CC-04','Workshop'),
('CC-05','Infrastruktur');

-- ─────────────────────────────────────────────
-- 3. data_pr
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS data_pr (
  id                  INT AUTO_INCREMENT PRIMARY KEY,
  Nomor_PR            VARCHAR(60)   NOT NULL DEFAULT '',
  WO                  VARCHAR(100),
  Judul_PR            VARCHAR(255)  NOT NULL DEFAULT '',
  Tanggal             DATE,
  Planner             VARCHAR(100),
  NIK                 VARCHAR(30),
  Cost_Center         VARCHAR(100),
  Elemen              VARCHAR(100),
  Kode_Item           VARCHAR(100),
  Nama_Item           VARCHAR(255),
  Deskripsi           TEXT,
  Merek               VARCHAR(100),
  Part_Number         VARCHAR(100),
  Jumlah              DECIMAL(14,2) DEFAULT 1,
  Satuan              VARCHAR(30)   DEFAULT 'Pcs',
  History_PO          DECIMAL(20,2) DEFAULT 0,
  Harga_Marketplace   DECIMAL(20,2) DEFAULT 0,
  Harga_Rekanan       DECIMAL(20,2) DEFAULT 0,
  Harga_Last_Tender   DECIMAL(20,2) DEFAULT 0,
  Penyesuaian_Harga   DECIMAL(20,2) DEFAULT 0,
  Harga_Satuan        DECIMAL(20,2) DEFAULT 0,
  Total               DECIMAL(20,2) DEFAULT 0,
  TKDN                DECIMAL(8,2)  DEFAULT 0,
  Sumber              VARCHAR(100),
  Term_Of_Delivery    INT DEFAULT 30,
  Approval_1          VARCHAR(100),
  Tanggal_Approval_1  DATETIME,
  Durasi_Approval_1   INT DEFAULT 0,
  Status_Approval_1   VARCHAR(20)   DEFAULT 'pending',
  Approval_2          VARCHAR(100),
  Tanggal_Approval_2  DATETIME,
  Durasi_Approval_2   INT DEFAULT 0,
  Status_Approval_2   VARCHAR(20)   DEFAULT 'pending',
  Approval_3          VARCHAR(100),
  Tanggal_Approval_3  DATETIME,
  Durasi_Approval_3   INT DEFAULT 0,
  Status_Approval_3   VARCHAR(20)   DEFAULT 'pending',
  NIK_Approval_1      VARCHAR(30),
  NIK_Approval_2      VARCHAR(30),
  NIK_Approval_3      VARCHAR(30),
  status              VARCHAR(20)   DEFAULT 'submitted',
  created_by          VARCHAR(100)  DEFAULT 'SISTEM',
  NIK_created_by      VARCHAR(30),
  created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Migrasi kolom baru jika tabel lama sudah ada
ALTER TABLE data_pr ADD COLUMN IF NOT EXISTS Status_Approval_1  VARCHAR(20) DEFAULT 'pending' AFTER Durasi_Approval_1;
ALTER TABLE data_pr ADD COLUMN IF NOT EXISTS Status_Approval_2  VARCHAR(20) DEFAULT 'pending' AFTER Durasi_Approval_2;
ALTER TABLE data_pr ADD COLUMN IF NOT EXISTS Status_Approval_3  VARCHAR(20) DEFAULT 'pending' AFTER Durasi_Approval_3;
ALTER TABLE data_pr ADD COLUMN IF NOT EXISTS NIK_Approval_1     VARCHAR(30) AFTER Status_Approval_3;
ALTER TABLE data_pr ADD COLUMN IF NOT EXISTS NIK_Approval_2     VARCHAR(30) AFTER NIK_Approval_1;
ALTER TABLE data_pr ADD COLUMN IF NOT EXISTS NIK_Approval_3     VARCHAR(30) AFTER NIK_Approval_2;
ALTER TABLE data_pr ADD COLUMN IF NOT EXISTS NIK_created_by     VARCHAR(30) AFTER created_by;
ALTER TABLE data_pr MODIFY COLUMN IF EXISTS Tanggal_Approval_1  DATETIME;
ALTER TABLE data_pr MODIFY COLUMN IF EXISTS Tanggal_Approval_2  DATETIME;
ALTER TABLE data_pr MODIFY COLUMN IF EXISTS Tanggal_Approval_3  DATETIME;

UPDATE data_pr SET status='submitted' WHERE status IS NULL OR status='';
UPDATE data_pr SET Status_Approval_1='pending' WHERE Status_Approval_1 IS NULL;
UPDATE data_pr SET Status_Approval_2='pending' WHERE Status_Approval_2 IS NULL;
UPDATE data_pr SET Status_Approval_3='pending' WHERE Status_Approval_3 IS NULL;

CREATE INDEX IF NOT EXISTS idx_nomor_pr ON data_pr (Nomor_PR);
CREATE INDEX IF NOT EXISTS idx_nik      ON data_pr (NIK);
CREATE INDEX IF NOT EXISTS idx_status   ON data_pr (status);

-- ─────────────────────────────────────────────
-- Migrasi: Kolom realisasi pekerjaan perawatan
-- ─────────────────────────────────────────────
ALTER TABLE rekap_jadwal_service ADD COLUMN IF NOT EXISTS Status_Pekerjaan       VARCHAR(30)   DEFAULT 'belum_mulai' AFTER Status;
ALTER TABLE rekap_jadwal_service ADD COLUMN IF NOT EXISTS Tanggal_Mulai_Realisasi DATE          AFTER Tanggal_Selesai_Service;
ALTER TABLE rekap_jadwal_service ADD COLUMN IF NOT EXISTS HM_Saat_Service        INT           AFTER Tanggal_Mulai_Realisasi;
ALTER TABLE rekap_jadwal_service ADD COLUMN IF NOT EXISTS Teknisi                VARCHAR(150)  AFTER HM_Saat_Service;
ALTER TABLE rekap_jadwal_service ADD COLUMN IF NOT EXISTS Kendala                TEXT          AFTER Teknisi;
ALTER TABLE rekap_jadwal_service ADD COLUMN IF NOT EXISTS Catatan_Realisasi      TEXT          AFTER Kendala;
-- Status_Pekerjaan values: belum_mulai | sedang_dikerjakan | selesai | tertunda
UPDATE rekap_jadwal_service SET Status_Pekerjaan='belum_mulai' WHERE Status_Pekerjaan IS NULL;

-- ─────────────────────────────────────────────
-- SOH Gudang MSF 1 (tabel terpisah dari MSF 2)
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS soh_gudang_msf_1 (
  id                  INT AUTO_INCREMENT PRIMARY KEY,
  Part_Number         VARCHAR(100),
  Part_Name           VARCHAR(255),
  Brand               VARCHAR(100),
  Stock               DECIMAL(14,2) DEFAULT 0,
  Satuan              VARCHAR(30),
  Location            VARCHAR(100),
  Box                 VARCHAR(100),
  Equipment_Register  VARCHAR(150),
  Model               VARCHAR(150),
  Part_Number_Ex1     VARCHAR(100),
  Part_Number_Ex2     VARCHAR(100),
  Part_Pengganti_Dari VARCHAR(100),
  Nama_Gudang         VARCHAR(100) DEFAULT 'MSF 1',
  created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_msf1_brand  ON soh_gudang_msf_1 (Brand);
CREATE INDEX IF NOT EXISTS idx_msf1_partno ON soh_gudang_msf_1 (Part_Number);

-- ─────────────────────────────────────────────
-- Migrasi: Tambah kolom Satker_Perawatan ke data_unit
-- ─────────────────────────────────────────────
ALTER TABLE data_unit ADD COLUMN IF NOT EXISTS Satker_Perawatan VARCHAR(30) DEFAULT NULL AFTER Equipment_Register;
CREATE INDEX IF NOT EXISTS idx_unit_satker ON data_unit (Satker_Perawatan);

-- ─────────────────────────────────────────────
-- Migrasi: Kolom Status_Pembatalan di data_barang_keluar
-- ─────────────────────────────────────────────
ALTER TABLE data_barang_keluar ADD COLUMN IF NOT EXISTS Status_Pembatalan VARCHAR(20) DEFAULT NULL AFTER Items;
ALTER TABLE data_barang_keluar ADD COLUMN IF NOT EXISTS Tgl_Pembatalan DATETIME DEFAULT NULL AFTER Status_Pembatalan;

-- ─────────────────────────────────────────────
-- Migrasi: Kolom Foto_Barang di data_barang_keluar
-- ─────────────────────────────────────────────
ALTER TABLE data_barang_keluar ADD COLUMN IF NOT EXISTS Foto_Barang VARCHAR(255) DEFAULT NULL AFTER Tgl_Pembatalan;

ALTER TABLE data_barang_keluar ADD COLUMN IF NOT EXISTS Alasan_Pembatalan TEXT DEFAULT NULL AFTER Tgl_Pembatalan;