-- =====================================================
-- UPGRADE SQL — CMS Sekolahku v3.1.0 → v3.1.1
-- Bisa dijalankan berulang kali via phpMyAdmin atau CLI
-- =====================================================

-- ============================================================
-- 1. Tambah kolom updated_at (jika belum ada)
-- ============================================================

SET @db = 'sekolahku_db';

SET @c = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'contact_messages' AND COLUMN_NAME = 'updated_at');
SET @sql = IF(@c = 0,
  'ALTER TABLE contact_messages ADD COLUMN updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at',
  'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @c = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'news_comments' AND COLUMN_NAME = 'updated_at');
SET @sql = IF(@c = 0,
  'ALTER TABLE news_comments ADD COLUMN updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at',
  'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @c = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'testimonials' AND COLUMN_NAME = 'updated_at');
SET @sql = IF(@c = 0,
  'ALTER TABLE testimonials ADD COLUMN updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at',
  'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ============================================================
-- 2. Fix NULL defaults → CURRENT_TIMESTAMP (aman diulang)
-- ============================================================

ALTER TABLE downloads
  MODIFY created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  MODIFY updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

ALTER TABLE menu_items
  MODIFY created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  MODIFY updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

ALTER TABLE pages
  MODIFY created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  MODIFY updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

ALTER TABLE galleries
  MODIFY updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- ============================================================
-- 3. Buat tabel downloads (jika belum ada, aman diulang)
-- ============================================================

CREATE TABLE IF NOT EXISTS `downloads` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `category` VARCHAR(100) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT NULL,
  `url` VARCHAR(500) NOT NULL,
  `file_size` VARCHAR(50) NULL,
  `sort_order` INT UNSIGNED NOT NULL DEFAULT 0,
  `show` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_category` (`category`),
  KEY `idx_sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- 4. Hapus kolom image dari events (jika masih ada)
-- ============================================================

SET @c = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'events' AND COLUMN_NAME = 'image');
SET @sql = IF(@c > 0,
  'ALTER TABLE `events` DROP COLUMN `image`',
  'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ============================================================
-- 5. Tambah kolom type & page_id ke menu_items (jika belum ada)
-- ============================================================

SET @c = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'menu_items' AND COLUMN_NAME = 'type');
SET @sql = IF(@c = 0,
  'ALTER TABLE `menu_items` ADD COLUMN `type` VARCHAR(20) DEFAULT ''custom'' AFTER `status`, ADD COLUMN `page_id` INT UNSIGNED NULL AFTER `type`',
  'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ============================================================
-- 6. Tambah foreign key page_id (jika belum ada)
-- ============================================================

SET @c = (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = @db AND TABLE_NAME = 'menu_items' AND CONSTRAINT_NAME = 'menu_items_page_id_foreign');
SET @sql = IF(@c = 0,
  'ALTER TABLE `menu_items` ADD CONSTRAINT `menu_items_page_id_foreign` FOREIGN KEY (`page_id`) REFERENCES `pages` (`id`) ON DELETE SET NULL ON UPDATE CASCADE',
  'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ============================================================
-- 7. Tambah kolom updated_at ke galleries (jika belum ada)
-- ============================================================

SET @c = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'galleries' AND COLUMN_NAME = 'updated_at');
SET @sql = IF(@c = 0,
  'ALTER TABLE `galleries` ADD COLUMN `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
  'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ============================================================
-- 8. Reset password admin (opsional, jalankan jika lupa)
-- ============================================================

-- UPDATE `users` SET `password` = '$2y$12$4i2j.cHw2GQd6mQocMnSDuGlOY/oQx4SBPd2zlGZo2ohbGNQw4aJG' WHERE `username` = 'administrator';