-- =============================================================================
-- Migration: Delivery Trips - Internal vs External (Pengiriman Internal/Eksternal)
-- =============================================================================
-- Jalankan script ini di phpMyAdmin (pilih database SAPPS).
-- AMAN dijalankan berulang: kolom/tabel yang sudah ada akan di-SKIP (no duplicate error).
-- Script ini TIDAK menghapus data dan TIDAK mengubah data existing.
-- =============================================================================

-- -----------------------------------------------------------------------------
-- Helper: prosedur untuk ADD COLUMN hanya jika kolom belum ada (skip jika sudah ada)
-- -----------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS sp_add_column_if_not_exists;

DELIMITER //
CREATE PROCEDURE sp_add_column_if_not_exists(
  IN p_table VARCHAR(64),
  IN p_column VARCHAR(64),
  IN p_definition TEXT
)
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = p_table
      AND COLUMN_NAME = p_column
  ) THEN
    SET @sql = CONCAT('ALTER TABLE `', p_table, '` ADD COLUMN `', p_column, '` ', p_definition);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END//
DELIMITER ;

-- -----------------------------------------------------------------------------
-- 1. Tabel master: Vendor Ekspedisi (deliveryexpeditionvendors)
--    Termasuk createdAt, updatedAt (penulisan camelCase seperti di UI)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `deliveryexpeditionvendors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `creation` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified_by` int(11) NOT NULL DEFAULT 0,
  `owner` int(11) NOT NULL DEFAULT 0,
  `enabled` tinyint(1) NOT NULL DEFAULT 1,
  `name` varchar(200) NOT NULL COMMENT 'Nama vendor ekspedisi',
  `contact` varchar(255) DEFAULT NULL,
  `notes` text,
  `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Master vendor ekspedisi eksternal';

-- Jika tabel sudah ada tanpa createdAt/updatedAt, tambahkan sekarang (skip jika sudah ada)
CALL sp_add_column_if_not_exists('deliveryexpeditionvendors', 'createdAt',
  'datetime NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `notes`');
CALL sp_add_column_if_not_exists('deliveryexpeditionvendors', 'updatedAt',
  'datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `createdAt`');

-- Jika tabel lama pakai nama delivery_expedition_vendors, rename (uncomment jika perlu):
-- RENAME TABLE `delivery_expedition_vendors` TO `deliveryexpeditionvendors`;

-- -----------------------------------------------------------------------------
-- 2. Tabel deliverytrips: tipe pengiriman + kolom khusus eksternal
--    Setiap kolom hanya ditambah jika belum ada (skip jika duplicate)
-- -----------------------------------------------------------------------------
CALL sp_add_column_if_not_exists('deliverytrips', 'delivery_type',
  "varchar(20) NOT NULL DEFAULT 'internal' COMMENT 'internal | external' AFTER `enabled`");

CALL sp_add_column_if_not_exists('deliverytrips', 'expedition_vendor_id',
  'int(11) DEFAULT NULL COMMENT "FK ke deliveryexpeditionvendors" AFTER `delivery_type`');

CALL sp_add_column_if_not_exists('deliverytrips', 'resi_number',
  'varchar(100) DEFAULT NULL COMMENT "Nomor resi (external)" AFTER `expedition_vendor_id`');

CALL sp_add_column_if_not_exists('deliverytrips', 'pick_up_time',
  'varchar(50) DEFAULT NULL COMMENT "Jam Pick-Up (external)" AFTER `resi_number`');

CALL sp_add_column_if_not_exists('deliverytrips', 'cartons_pcs',
  'int(11) DEFAULT NULL COMMENT "Jumlah karton pcs (external)" AFTER `pick_up_time`');

CALL sp_add_column_if_not_exists('deliverytrips', 'weight_kg',
  'decimal(10,2) DEFAULT NULL COMMENT "Berat kg (external)" AFTER `cartons_pcs`');

CALL sp_add_column_if_not_exists('deliverytrips', 'dimensions_cm',
  'varchar(100) DEFAULT NULL COMMENT "Dimensi p x l x t cm (external)" AFTER `weight_kg`');

CALL sp_add_column_if_not_exists('deliverytrips', 'volume_calculated',
  'decimal(12,4) DEFAULT NULL COMMENT "Volume = dimensi/5000 (external)" AFTER `dimensions_cm`');

-- 2c. MODIFY driver dan vehicle_id agar NULL (hanya dijalankan jika kolom ada)
DROP PROCEDURE IF EXISTS sp_modify_column_if_exists;
DELIMITER //
CREATE PROCEDURE sp_modify_column_if_exists(
  IN p_table VARCHAR(64),
  IN p_column VARCHAR(64),
  IN p_definition TEXT
)
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = p_table AND COLUMN_NAME = p_column
  ) THEN
    SET @sql = CONCAT('ALTER TABLE `', p_table, '` MODIFY COLUMN `', p_column, '` ', p_definition);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END//
DELIMITER ;

CALL sp_modify_column_if_exists('deliverytrips', 'driver', 'int(11) DEFAULT NULL COMMENT "Employee ID (internal)"');
CALL sp_modify_column_if_exists('deliverytrips', 'vehicle_id', 'int(11) DEFAULT NULL COMMENT "Vehicle ID (internal)"');

DROP PROCEDURE IF EXISTS sp_modify_column_if_exists;

-- -----------------------------------------------------------------------------
-- 3. Tabel deliverytripchilds: send_date, receive_date (hanya jika belum ada)
-- -----------------------------------------------------------------------------
CALL sp_add_column_if_not_exists('deliverytripchilds', 'send_date',
  'date DEFAULT NULL COMMENT "Tanggal kirim (external)" AFTER `total_time`');

CALL sp_add_column_if_not_exists('deliverytripchilds', 'receive_date',
  'date DEFAULT NULL COMMENT "Tanggal terima (external)" AFTER `send_date`');

-- -----------------------------------------------------------------------------
-- Bersihkan prosedur helper
-- -----------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS sp_add_column_if_not_exists;
DROP PROCEDURE IF EXISTS sp_modify_column_if_exists;

-- Selesai. Script aman dijalankan berulang; kolom yang sudah ada akan di-skip.