use crowdvest;

CREATE TABLE `activities` (
  `id` int NOT NULL,
  `umkm_id` int NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `type` enum('investment','document','withdrawal') NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `activities`
--

INSERT INTO `activities` (`id`, `umkm_id`, `title`, `description`, `type`, `created_at`) VALUES
(4, 2, 'Kampanye Baru Diluncurkan!', 'Kampanye pendanaan untuk proyek \"Pembuatan Teknologi Baru\" telah dimulai dengan target 100.000.000', 'document', '2026-07-27 06:00:38'),
(5, 2, 'Kampanye Baru Diluncurkan!', 'Kampanye pendanaan untuk proyek \"Pembuatan Teknologi AI terbaru\" telah dimulai dengan target 1.000.000.000', 'document', '2026-07-28 11:25:29'),
(6, 2, 'Pembaruan Proyek Baru', 'Memublikasikan update: sudah memabngun algoritma learning', 'document', '2026-07-28 11:40:19'),
(7, 2, 'Pencatatan Keuangan', 'Mencatat Pemasukan sebesar Rp1.000.000 untuk beli', 'withdrawal', '2026-07-28 12:22:53'),
(8, 2, 'Kampanye Baru Diluncurkan!', 'Kampanye pendanaan untuk proyek \"Kedai Warkop TJ karang\" telah dimulai dengan target 100.000.000', 'document', '2026-07-28 13:30:40'),
(9, 2, 'Kampanye Baru Diluncurkan!', 'Kampanye pendanaan untuk proyek \"butik om tejo\" telah dimulai dengan target 150.000.000', 'document', '2026-07-28 14:33:11');

-- --------------------------------------------------------

--
-- Table structure for table `financial_reports`
--

CREATE TABLE `financial_reports` (
  `id` int NOT NULL,
  `project_id` int NOT NULL,
  `type` enum('income','expense') NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `description` varchar(255) NOT NULL,
  `report_date` date NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `financial_reports`
--

INSERT INTO `financial_reports` (`id`, `project_id`, `type`, `amount`, `description`, `report_date`, `created_at`) VALUES
(1, 8, 'income', '1000000.00', 'beli', '2026-07-28', '2026-07-28 12:22:53');

-- --------------------------------------------------------

--
-- Table structure for table `investments`
--

CREATE TABLE `investments` (
  `id` int NOT NULL,
  `trx_id` varchar(50) DEFAULT NULL,
  `project_id` int NOT NULL,
  `investor_id` int NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `payment_method` varchar(100) DEFAULT 'Bank Transfer',
  `status` enum('pending','success','failed') DEFAULT 'success',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `investments`
--

INSERT INTO `investments` (`id`, `trx_id`, `project_id`, `investor_id`, `amount`, `payment_method`, `status`, `created_at`) VALUES
(125, 'TRX-6A68B24A12412', 8, 1, '500000.00', 'transfer_bank', 'success', '2026-07-28 13:44:42'),
(126, 'TRX-6A68B26944BC6', 8, 1, '100000000.00', 'transfer_bank', 'success', '2026-07-28 13:45:13'),
(127, 'TRX-6A68C58E68F22', 9, 1, '100000000.00', 'ewallet', 'success', '2026-07-28 15:06:54');

-- --------------------------------------------------------

--
-- Table structure for table `notifications`
--

CREATE TABLE `notifications` (
  `id` int NOT NULL,
  `user_id` int NOT NULL,
  `type` enum('success','warning','info','error') DEFAULT 'info',
  `title` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `is_read` tinyint(1) DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `notifications`
--

INSERT INTO `notifications` (`id`, `user_id`, `type`, `title`, `message`, `is_read`, `created_at`) VALUES
(1, 1, 'success', 'Investasi Diterima!', 'Investasi Anda telah diverifikasi oleh sistem.', 1, '2026-07-28 04:34:52'),
(2, 1, 'info', 'Laporan Proyek Rilis', 'Laporan keuangan proyek yang Anda danai telah dirilis.', 1, '2026-07-27 06:34:52'),
(3, 1, 'warning', 'Peringatan Keamanan', 'Terdeteksi login baru dari IP berbeda.', 1, '2026-07-28 01:34:52'),
(4, 1, 'success', 'Investasi Berhasil!', 'Investasi Anda sebesar Rp500.000 dengan No Ref TRX-6A68B24A12412 telah kami terima.', 1, '2026-07-28 13:44:42'),
(5, 1, 'success', 'Investasi Berhasil!', 'Investasi Anda sebesar Rp100.000.000 dengan No Ref TRX-6A68B26944BC6 telah kami terima.', 1, '2026-07-28 13:45:13'),
(6, 1, 'success', 'Investasi Berhasil!', 'Investasi Anda sebesar Rp100.000.000 dengan No Ref TRX-6A68C58E68F22 telah kami terima.', 1, '2026-07-28 15:06:54');

-- --------------------------------------------------------

--
-- Table structure for table `projects`
--

CREATE TABLE `projects` (
  `id` int NOT NULL,
  `umkm_id` int NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text,
  `category` varchar(50) NOT NULL,
  `target_amount` decimal(15,2) NOT NULL,
  `collected_amount` decimal(15,2) DEFAULT '0.00',
  `image` varchar(255) DEFAULT NULL,
  `end_date` date NOT NULL,
  `status` enum('Sedang Berjalan','Selesai') DEFAULT 'Sedang Berjalan',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `roi_percent` decimal(5,2) DEFAULT '12.00',
  `period_months` int DEFAULT '12',
  `min_investment` decimal(15,2) DEFAULT '100000.00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `projects`
--

INSERT INTO `projects` (`id`, `umkm_id`, `title`, `description`, `category`, `target_amount`, `collected_amount`, `image`, `end_date`, `status`, `created_at`, `updated_at`, `roi_percent`, `period_months`, `min_investment`) VALUES
(8, 2, 'Pembuatan Teknologi AI terbaru', 'buat AI terkeren dan akan sukses', 'Teknologi', '1000000000.00', '100500000.00', 'http://localhost:8000/uploads/campaigns/campaign_1785237929_93920dd8.webp', '2026-08-25', 'Sedang Berjalan', '2026-07-28 11:25:29', '2026-07-28 13:45:13', '12.00', 12, '100000.00'),
(9, 2, 'Kedai Warkop TJ karang', 'ini warkop sanngat amntab dan bagus banget', 'Kuliner', '100000000.00', '100000000.00', 'http://localhost:8000/uploads/campaigns/campaign_1785245440_cdd2e943.jpg', '2026-10-14', 'Sedang Berjalan', '2026-07-28 13:30:40', '2026-07-28 15:06:54', '12.00', 12, '100000.00'),
(10, 2, 'butik om tejo', 'butik yang menjual berbagai pakaian', 'Fashion', '150000000.00', '0.00', 'http://localhost:8000/uploads/campaigns/campaign_1785249191_1729d7d7.jpg', '2026-09-10', 'Sedang Berjalan', '2026-07-28 14:33:11', '2026-07-28 14:33:11', '24.00', 12, '200000.00');

-- --------------------------------------------------------

--
-- Table structure for table `project_documents`
--

CREATE TABLE `project_documents` (
  `id` int NOT NULL,
  `umkm_id` int NOT NULL,
  `project_id` int DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `document_url` varchar(512) NOT NULL,
  `document_type` enum('legal','financial','prospectus','other') DEFAULT 'other',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `project_updates`
--

CREATE TABLE `project_updates` (
  `id` int NOT NULL,
  `project_id` int NOT NULL,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `attachment_url` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `project_updates`
--

INSERT INTO `project_updates` (`id`, `project_id`, `title`, `content`, `attachment_url`, `created_at`) VALUES
(1, 8, 'sudah memabngun algoritma learning', 'sudah belajar belajar tipis', 'http://localhost:8000/uploads/updates/update_1785238819_99781aa4.png', '2026-07-28 11:40:19');

-- --------------------------------------------------------

--
-- Table structure for table `wallet_transactions`
--

CREATE TABLE `wallet_transactions` (
  `id` int NOT NULL,
  `user_id` int NOT NULL,
  `type` enum('deposit','withdraw','invest','return','project_funding') NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `status` enum('pending','success','failed') DEFAULT 'pending',
  `description` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `wallet_transactions`
--

INSERT INTO `wallet_transactions` (`id`, `user_id`, `type`, `amount`, `status`, `description`, `created_at`, `updated_at`) VALUES
(1, 1, 'deposit', '1000000.00', 'success', 'Deposit via Transfer Bank BCA', '2026-07-28 15:20:27', '2026-07-28 15:20:27'),
(2, 1, 'withdraw', '1000000.00', 'success', 'Penarikan ke BCA (12121212121)', '2026-07-28 15:40:54', '2026-07-28 15:40:54');

-- --------------------------------------------------------

--
-- Table structure for table `watchlists`
--

CREATE TABLE `watchlists` (
  `id` int NOT NULL,
  `investor_id` int NOT NULL,
  `project_id` int NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `watchlists`
--

INSERT INTO `watchlists` (`id`, `investor_id`, `project_id`, `created_at`) VALUES
(1, 1, 8, '2026-07-28 13:34:52'),
(2, 1, 9, '2026-07-28 13:34:52');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `activities`
--
ALTER TABLE `activities`
  ADD PRIMARY KEY (`id`),
  ADD KEY `umkm_id` (`umkm_id`);

--
-- Indexes for table `financial_reports`
--
ALTER TABLE `financial_reports`
  ADD PRIMARY KEY (`id`),
  ADD KEY `project_id` (`project_id`);

--
-- Indexes for table `investments`
--
ALTER TABLE `investments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `project_id` (`project_id`),
  ADD KEY `investor_id` (`investor_id`);

--
-- Indexes for table `notifications`
--
ALTER TABLE `notifications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `projects`
--
ALTER TABLE `projects`
  ADD PRIMARY KEY (`id`),
  ADD KEY `umkm_id` (`umkm_id`);

--
-- Indexes for table `project_documents`
--
ALTER TABLE `project_documents`
  ADD PRIMARY KEY (`id`),
  ADD KEY `umkm_id` (`umkm_id`),
  ADD KEY `project_id` (`project_id`);

--
-- Indexes for table `project_updates`
--
ALTER TABLE `project_updates`
  ADD PRIMARY KEY (`id`),
  ADD KEY `project_id` (`project_id`);

--
-- Indexes for table `wallet_transactions`
--
ALTER TABLE `wallet_transactions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `watchlists`
--
ALTER TABLE `watchlists`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_watchlist` (`investor_id`,`project_id`),
  ADD KEY `project_id` (`project_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `activities`
--
ALTER TABLE `activities`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

--
-- AUTO_INCREMENT for table `financial_reports`
--
ALTER TABLE `financial_reports`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `investments`
--
ALTER TABLE `investments`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=128;

--
-- AUTO_INCREMENT for table `notifications`
--
ALTER TABLE `notifications`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `projects`
--
ALTER TABLE `projects`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

--
-- AUTO_INCREMENT for table `project_documents`
--
ALTER TABLE `project_documents`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `project_updates`
--
ALTER TABLE `project_updates`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `wallet_transactions`
--
ALTER TABLE `wallet_transactions`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `watchlists`
--
ALTER TABLE `watchlists`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `activities`
--
ALTER TABLE `activities`
  ADD CONSTRAINT `activities_ibfk_1` FOREIGN KEY (`umkm_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `financial_reports`
--
ALTER TABLE `financial_reports`
  ADD CONSTRAINT `financial_reports_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `investments`
--
ALTER TABLE `investments`
  ADD CONSTRAINT `investments_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `investments_ibfk_2` FOREIGN KEY (`investor_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `notifications`
--
ALTER TABLE `notifications`
  ADD CONSTRAINT `notifications_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `projects`
--
ALTER TABLE `projects`
  ADD CONSTRAINT `projects_ibfk_1` FOREIGN KEY (`umkm_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `project_documents`
--
ALTER TABLE `project_documents`
  ADD CONSTRAINT `project_documents_ibfk_1` FOREIGN KEY (`umkm_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `project_documents_ibfk_2` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `project_updates`
--
ALTER TABLE `project_updates`
  ADD CONSTRAINT `project_updates_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `wallet_transactions`
--
ALTER TABLE `wallet_transactions`
  ADD CONSTRAINT `wallet_transactions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `watchlists`
--
ALTER TABLE `watchlists`
  ADD CONSTRAINT `watchlists_ibfk_1` FOREIGN KEY (`investor_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `watchlists_ibfk_2` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE;
COMMIT;

