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 `users` -- CREATE TABLE `users` ( `id` int NOT NULL, `name` varchar(100) NOT NULL, `email` varchar(100) NOT NULL, `password` varchar(255) NOT NULL, `role` enum('investor','umkm','admin') NOT NULL DEFAULT 'investor', `status` enum('active','inactive','banned') NOT NULL DEFAULT 'active', `balance` decimal(15,2) DEFAULT '0.00', `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `phone` varchar(20) DEFAULT NULL, `address` text, `bio` text, `photo_url` varchar(255) DEFAULT NULL, `photo_cropped_url` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `users` -- INSERT INTO `users` (`id`, `name`, `email`, `password`, `role`, `status`, `balance`, `created_at`, `updated_at`, `phone`, `address`, `bio`, `photo_url`, `photo_cropped_url`) VALUES (1, 'Riski Jaya Putra', 'rizkijayaputra2005@gmail.com', '$2y$12$5u48rHu/XB7KXll75VhYuupOi0lUTBuNGaOT/LIt946hc1izArF8u', 'investor', 'active', '0.00', '2026-07-26 10:25:51', '2026-07-28 15:40:54', NULL, NULL, NULL, NULL, NULL), (2, 'Riski Jaya Putra', 'pengusaha@gmail.com', '$2y$12$GI9u.wddwkO7ecAWFXniKuGtwBLg80ufkT50FSDFyK4E6HH5djW9u', 'umkm', 'active', '0.00', '2026-07-26 11:51:44', '2026-07-28 14:50:30', '089653880020', 'perumahan nunyai permai jln.haji komarudin kec.rajabasa raya\r\nhouse with a white colot at the front', '', 'avatar_2_1785250230_full.jpg', 'avatar_2_1785250230_cropped.png'); -- -------------------------------------------------------- -- -- 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 `users` -- ALTER TABLE `users` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `email` (`email`); -- -- 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 `users` -- ALTER TABLE `users` MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3; -- -- 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; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;