-- =============================================
-- SQL Script untuk menambahkan kolom pengelola_sts_id ke tabel spalds
-- Database: sipracika
-- =============================================

-- Cek apakah kolom sudah ada, jika belum maka tambahkan
SET @dbname = DATABASE();
SET @tablename = 'spalds';
SET @columnname = 'pengelola_sts_id';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = @columnname)
  ) > 0,
  'SELECT 1', -- Kolom sudah ada, tidak perlu ditambahkan
  CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `', @columnname, '` BIGINT UNSIGNED NULL DEFAULT NULL AFTER `desa_id`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- Hapus foreign key constraint jika sudah ada (untuk update)
SET @constraint_name = (SELECT CONSTRAINT_NAME 
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
  WHERE TABLE_SCHEMA = @dbname 
    AND TABLE_NAME = @tablename 
    AND COLUMN_NAME = @columnname 
    AND REFERENCED_TABLE_NAME IS NOT NULL
  LIMIT 1);

SET @drop_fk = IF(@constraint_name IS NOT NULL, 
  CONCAT('ALTER TABLE `', @tablename, '` DROP FOREIGN KEY `', @constraint_name, '`'),
  'SELECT 1');

PREPARE stmt FROM @drop_fk;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Tambahkan foreign key constraint
ALTER TABLE `spalds`
  ADD CONSTRAINT `spalds_pengelola_sts_id_foreign`
  FOREIGN KEY (`pengelola_sts_id`) 
  REFERENCES `pengelola_sts` (`id`) 
  ON DELETE SET NULL 
  ON UPDATE CASCADE;

-- Tambahkan index untuk performa
CREATE INDEX IF NOT EXISTS `idx_spalds_pengelola_sts_id` ON `spalds` (`pengelola_sts_id`);

-- =============================================
-- Selesai
-- =============================================

