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

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

-- =============================================
-- Tabel: e_proposals
-- =============================================
CREATE TABLE IF NOT EXISTS `e_proposals` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `nama` VARCHAR(255) NOT NULL,
  `alamat` TEXT NOT NULL,
  `instansi` VARCHAR(255) NOT NULL,
  `nomor_telepon` VARCHAR(20) NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `e_proposals_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Tabel: e_proposal_files
-- =============================================
CREATE TABLE IF NOT EXISTS `e_proposal_files` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `e_proposal_id` BIGINT UNSIGNED NOT NULL,
  `file_path` VARCHAR(500) NOT NULL,
  `original_name` VARCHAR(255) NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_e_proposal_id` (`e_proposal_id`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `e_proposal_files_e_proposal_id_foreign` FOREIGN KEY (`e_proposal_id`) REFERENCES `e_proposals` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- VERIFIKASI DATA (Optional - untuk cek hasil)
-- =============================================
-- SELECT * FROM e_proposals ORDER BY id;
-- SELECT * FROM e_proposal_files ORDER BY id;

