-- ============================================
-- SQL Script untuk Setup Menu Air Minum PDAM & PAMSIMAS
-- Copy paste script ini di SQLyog untuk setup database
-- ============================================

-- 1. Buat Tabel Master Sumber Air PDAM
CREATE TABLE IF NOT EXISTS `sumber_air_pdam` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nama` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Buat Tabel Master Sumber Air PAMSIMAS
CREATE TABLE IF NOT EXISTS `sumber_air_pamsimas` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nama` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Buat Tabel INTAKE PDAM
CREATE TABLE IF NOT EXISTS `intake_pdam` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nama_spam` varchar(255) NOT NULL,
  `kecamatan_id` bigint(20) UNSIGNED NOT NULL,
  `desa_id` bigint(20) UNSIGNED NOT NULL,
  `tahun_p` int(11) NOT NULL,
  `latitude` varchar(255) DEFAULT NULL,
  `longitude` varchar(255) DEFAULT NULL,
  `file_gambar` varchar(255) DEFAULT NULL,
  `sumber_air_pdam_id` bigint(20) UNSIGNED NOT NULL,
  `kapasitas` decimal(10,2) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `intake_pdam_kecamatan_id_foreign` (`kecamatan_id`),
  KEY `intake_pdam_desa_id_foreign` (`desa_id`),
  KEY `intake_pdam_sumber_air_pdam_id_foreign` (`sumber_air_pdam_id`),
  CONSTRAINT `intake_pdam_kecamatan_id_foreign` FOREIGN KEY (`kecamatan_id`) REFERENCES `kecamatan` (`id`) ON DELETE CASCADE,
  CONSTRAINT `intake_pdam_desa_id_foreign` FOREIGN KEY (`desa_id`) REFERENCES `desa` (`id`) ON DELETE CASCADE,
  CONSTRAINT `intake_pdam_sumber_air_pdam_id_foreign` FOREIGN KEY (`sumber_air_pdam_id`) REFERENCES `sumber_air_pdam` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Tambah Kolom ke Tabel tbl_spam untuk INTAKE PAMSIMAS
-- Cek apakah kolom sudah ada sebelum menambah
SET @dbname = DATABASE();
SET @tablename = 'tbl_spam';
SET @columnname = 'sumber_air_pamsimas_id';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (table_name = @tablename)
      AND (table_schema = @dbname)
      AND (column_name = @columnname)
  ) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `', @columnname, '` bigint(20) UNSIGNED NULL AFTER `file_gambar`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

SET @columnname = 'kapasitas';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (table_name = @tablename)
      AND (table_schema = @dbname)
      AND (column_name = @columnname)
  ) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `', @columnname, '` decimal(10,2) NULL AFTER `sumber_air_pamsimas_id`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

SET @columnname = 'tipe';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (table_name = @tablename)
      AND (table_schema = @dbname)
      AND (column_name = @columnname)
  ) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `', @columnname, '` enum(\'spam\',\'intake_pamsimas\') NOT NULL DEFAULT \'spam\' AFTER `kapasitas`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- Tambah Foreign Key untuk sumber_air_pamsimas_id (jika belum ada)
SET @constraintname = 'tbl_spam_sumber_air_pamsimas_id_foreign';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE
      (table_name = @tablename)
      AND (table_schema = @dbname)
      AND (constraint_name = @constraintname)
  ) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE `', @tablename, '` ADD CONSTRAINT `', @constraintname, '` FOREIGN KEY (`sumber_air_pamsimas_id`) REFERENCES `sumber_air_pamsimas` (`id`) ON DELETE RESTRICT')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 5. Buat Tabel PIPA PAMSIMAS
CREATE TABLE IF NOT EXISTS `pipa_pamsimas` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `kecamatan_id` bigint(20) UNSIGNED NOT NULL,
  `jenis_pipa` varchar(255) DEFAULT NULL,
  `panjang_digit` decimal(10,2) NOT NULL,
  `panjang_survey` decimal(10,2) NOT NULL,
  `diameter` decimal(10,2) DEFAULT NULL,
  `kondisi_pipa` varchar(255) DEFAULT NULL,
  `kedalaman` decimal(10,2) DEFAULT NULL,
  `tahun_pasang` int(11) DEFAULT NULL,
  `pelaksana` varchar(255) DEFAULT NULL,
  `sumber_dana` varchar(255) DEFAULT NULL,
  `penggunaan` varchar(255) DEFAULT NULL,
  `jns_jaringan` varchar(255) DEFAULT NULL,
  `file_geojson` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pipa_pamsimas_kecamatan_id_foreign` (`kecamatan_id`),
  CONSTRAINT `pipa_pamsimas_kecamatan_id_foreign` FOREIGN KEY (`kecamatan_id`) REFERENCES `kecamatan` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Buat Tabel SR PAMSIMAS
CREATE TABLE IF NOT EXISTS `sr_pamsimas` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nama` varchar(255) NOT NULL,
  `nik` varchar(16) NOT NULL,
  `alamat` text NOT NULL,
  `rt` int(11) NOT NULL,
  `rw` int(11) NOT NULL,
  `desa_id` bigint(20) UNSIGNED NOT NULL,
  `kecamatan_id` bigint(20) UNSIGNED NOT NULL,
  `coverage` int(11) NOT NULL DEFAULT '0',
  `file_geojson` varchar(255) DEFAULT NULL,
  `intake_pamsimas_id` bigint(20) UNSIGNED NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `sr_pamsimas_desa_id_foreign` (`desa_id`),
  KEY `sr_pamsimas_kecamatan_id_foreign` (`kecamatan_id`),
  KEY `sr_pamsimas_intake_pamsimas_id_foreign` (`intake_pamsimas_id`),
  CONSTRAINT `sr_pamsimas_desa_id_foreign` FOREIGN KEY (`desa_id`) REFERENCES `desa` (`id`) ON DELETE CASCADE,
  CONSTRAINT `sr_pamsimas_kecamatan_id_foreign` FOREIGN KEY (`kecamatan_id`) REFERENCES `kecamatan` (`id`) ON DELETE CASCADE,
  CONSTRAINT `sr_pamsimas_intake_pamsimas_id_foreign` FOREIGN KEY (`intake_pamsimas_id`) REFERENCES `tbl_spam` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Tambah Kolom intake_pdam_id ke Tabel sambungan_rumah untuk SR PDAM
SET @tablename = 'sambungan_rumah';
SET @columnname = 'intake_pdam_id';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (table_name = @tablename)
      AND (table_schema = @dbname)
      AND (column_name = @columnname)
  ) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `', @columnname, '` bigint(20) UNSIGNED NULL AFTER `file_geojson`')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- Tambah Foreign Key untuk intake_pdam_id (jika belum ada)
SET @constraintname = 'sambungan_rumah_intake_pdam_id_foreign';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE
      (table_name = @tablename)
      AND (table_schema = @dbname)
      AND (constraint_name = @constraintname)
  ) > 0,
  'SELECT 1',
  CONCAT('ALTER TABLE `', @tablename, '` ADD CONSTRAINT `', @constraintname, '` FOREIGN KEY (`intake_pdam_id`) REFERENCES `intake_pdam` (`id`) ON DELETE RESTRICT')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- ============================================
-- INSERT DATA SEEDER
-- ============================================

-- Insert Data Master Sumber Air PDAM (jika belum ada)
INSERT IGNORE INTO `sumber_air_pdam` (`nama`, `created_at`, `updated_at`) VALUES
('Banjar Bakula', NOW(), NOW()),
('Sungai Tabuk', NOW(), NOW()),
('Syarkawi', NOW(), NOW()),
('Astanbul', NOW(), NOW()),
('Sungai Pinang', NOW(), NOW()),
('Sambung Makmur', NOW(), NOW());

-- Insert Data Master Sumber Air PAMSIMAS (jika belum ada)
INSERT IGNORE INTO `sumber_air_pamsimas` (`nama`, `created_at`, `updated_at`) VALUES
('Sungai', NOW(), NOW()),
('Sumur Bor', NOW(), NOW()),
('Penangkap Mata Air (PMA)', NOW(), NOW()),
('Embung', NOW(), NOW());

-- ============================================
-- SELESAI
-- ============================================

