-- =============================================
-- SQL Script untuk membuat tabel E-Saran
-- Database: sipracika
-- =============================================

-- Hapus tabel jika sudah ada (untuk testing, hati-hati di production)
-- DROP TABLE IF EXISTS `e_sarans`;

-- =============================================
-- Tabel: e_sarans
-- =============================================
CREATE TABLE IF NOT EXISTS `e_sarans` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `nama` VARCHAR(255) NOT NULL,
  `saran` TEXT NOT NULL,
  `penilaian` INT NOT NULL DEFAULT 1 COMMENT 'Penilaian 1-10',
  `is_visible` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1 = Visible, 0 = Hidden',
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_is_visible` (`is_visible`),
  KEY `idx_penilaian` (`penilaian`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `e_sarans_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `chk_penilaian_range` CHECK (`penilaian` >= 1 AND `penilaian` <= 10)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- VERIFIKASI DATA (Optional - untuk cek hasil)
-- =============================================
-- SELECT * FROM e_sarans ORDER BY created_at DESC;
-- SELECT COUNT(*) as total_saran FROM e_sarans;
-- SELECT COUNT(*) as visible_saran FROM e_sarans WHERE is_visible = 1;
-- SELECT COUNT(*) as hidden_saran FROM e_sarans WHERE is_visible = 0;

