-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `lms_project`
--

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

--
-- Table structure for table `activity_logs`
--

CREATE TABLE `activity_logs` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `action` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `announcements`
--

CREATE TABLE `announcements` (
  `id` int(11) NOT NULL,
  `title` varchar(200) NOT NULL,
  `content` text NOT NULL,
  `target_role` varchar(50) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `assignments`
--

CREATE TABLE `assignments` (
  `id` int(11) NOT NULL,
  `training_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `business_profile`
--

CREATE TABLE `business_profile` (
  `id` int(11) NOT NULL,
  `nama_pemilik` varchar(255) DEFAULT NULL,
  `tanggal_berdiri` date DEFAULT NULL,
  `jenis_bisnis` varchar(100) DEFAULT NULL,
  `lokasi_operasional` varchar(255) DEFAULT NULL,
  `legalitas` enum('Lengkap','Tidak Lengkap') DEFAULT NULL,
  `produk_utama` text DEFAULT NULL,
  `sasaran_pasar` text DEFAULT NULL,
  `business_id` int(11) DEFAULT NULL,
  `nama_bisnis` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `business_profile`
--

INSERT INTO `business_profile` (`id`, `nama_pemilik`, `tanggal_berdiri`, `jenis_bisnis`, `lokasi_operasional`, `legalitas`, `produk_utama`, `sasaran_pasar`, `business_id`, `nama_bisnis`) VALUES
(8, 'Anindya Santoso', '2008-01-01', 'Fashion', 'Bogor', 'Tidak Lengkap', 'Konveksi/Garment Gamis & Hijab', 'Wanita Muslimah', NULL, 'Pelangi Hijab'),
(9, 'Ayodhia Fitriaji', '1995-01-01', 'Kontraktor Electrical', 'Jakarta', 'Lengkap', 'Kontraktor Elektrikal', 'BUMN & Korporasi', NULL, 'PT. Yuditha Nugraha Karya'),
(10, 'Fitri Widjayanti', '2010-01-01', 'Beauty & Care', 'Jabodetabek', 'Lengkap', 'Salon & Spa', 'Wanita Muslimah', NULL, 'Haura Salon & Spa Muslimah'),
(11, 'Nur Aini Madjid', '2008-01-01', 'Fashion', 'Surabaya', 'Lengkap', 'Penyewaan Baju Pengantin', 'Wanita Muslimah', NULL, 'Laksmi Muslimah'),
(12, 'Danal Firmansyah', '2022-01-01', 'Pendidikan & Pelatihan', 'Bekasi', 'Lengkap', 'Bimbingan Belajar Taruna, PTN, AKPOL, AKMIL, Kedinasan, dan CPNS', 'Pelajar', NULL, 'Akademi Prestasi'),
(13, 'Mansur Wima', '2019-01-01', 'Beauty & Care', 'Pemalang', 'Lengkap', 'Klinik Kecantikan', 'Wanita Muslimah', NULL, 'Wima Aesthetic Clinic'),
(14, 'Muhammad Fahrizal', '2016-01-01', 'Pendidikan & Pelatihan', 'Jakarta', 'Lengkap', 'Event Simulasi Sidang PBB', 'Pelajar & Mahasiswa', NULL, 'International Global Network'),
(15, 'Ustadz Oemar Mita', '2018-01-01', 'Jual-Beli Emas', 'Jakarta', 'Lengkap', 'Jual-Beli Emas Dinar', 'Masyarakat Muslim', NULL, 'Navees Gold'),
(16, 'Syaifudin Fatta', '2024-01-01', 'Kontraktor Mekanikal & Elektrikal', 'Jakarta', 'Lengkap', 'Kontraktor Mekanikal & Elektrikal', 'UKM & Korporasi', NULL, 'Bintang Mekanikal Indonesia'),
(17, 'Catur Gunandi', '2015-01-01', 'Holding Companies', 'Jakarta', 'Lengkap', 'Investasi Bisnis', 'UKM & Korporasi', NULL, 'CGLink'),
(18, 'Solehudin Azhar', '2016-01-01', 'Perdagangan', 'Tangerang', 'Lengkap', 'Jual-Beli Laptop', 'Umum', NULL, 'Adrian Laptop'),
(19, 'Coach Tun kelana Jaya', '2000-01-01', 'Konsultan', 'Jakarta', 'Lengkap', 'Konsultan Bisnis Syariah', 'Pengusaha & Pebisnis', NULL, 'Mafahim'),
(20, 'Pak Harris', '2024-08-01', 'Fashion', 'Tangerang', 'Lengkap', 'Kain Ihram Halal', 'Muslimin Muslimah', NULL, 'Bintang Mode Indonesia'),
(22, 'Pak Uki', '2024-01-01', 'Kontraktor', 'Bogor', 'Lengkap', 'Kontraktor Interior', 'UKM & Korporasi', NULL, 'Bintang Mas Interior');

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

--
-- Table structure for table `business_units`
--

CREATE TABLE `business_units` (
  `id` int(11) NOT NULL,
  `name` varchar(150) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `business_units`
--

INSERT INTO `business_units` (`id`, `name`, `address`, `description`) VALUES
(1, 'PT. Bintang Mono Internasional', NULL, NULL),
(2, 'PT. Bintang Mekanikal Indonesia', NULL, NULL),
(3, 'PT. Bintang Mas Interior', NULL, NULL),
(4, 'PT. Bintang Mode Indonesia', NULL, NULL),
(5, 'PT. Bintang Anugerah Sukses', NULL, NULL),
(6, 'PT. Bintang Mafahim Indonesia', NULL, NULL),
(8, 'PT. Bintang Muda Indonesia', NULL, NULL);

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

--
-- Table structure for table `cash_ledger`
--

CREATE TABLE `cash_ledger` (
  `id` int(11) NOT NULL,
  `tanggal` date NOT NULL,
  `keterangan` varchar(255) DEFAULT NULL,
  `jenis_transaksi` enum('masuk','keluar') NOT NULL,
  `jumlah` decimal(18,2) NOT NULL,
  `sumber` varchar(100) DEFAULT NULL,
  `project_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `categories`
--

CREATE TABLE `categories` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` (`id`, `name`, `description`, `created_at`, `updated_at`) VALUES
(1, 'Sales', '', '2025-05-29 13:39:33', '2025-05-29 13:39:33'),
(2, 'Marketing', '', '2025-05-29 13:41:10', '2025-05-29 13:41:10'),
(3, 'Operation', '', '2025-05-29 13:41:53', '2025-05-29 13:41:53'),
(4, 'Administration', '', '2025-05-29 13:42:09', '2025-05-29 13:42:09'),
(5, 'Aqidah Ahlaq', '', '2025-05-29 13:46:19', '2025-05-29 13:46:19'),
(6, 'Muamalah', '', '2025-05-29 16:49:27', '2025-05-29 16:49:27');

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

--
-- Table structure for table `departments`
--

CREATE TABLE `departments` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `business_unit_id` int(11) NOT NULL,
  `description` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `final_assessment`
--

CREATE TABLE `final_assessment` (
  `id` int(11) NOT NULL,
  `project_id` int(11) DEFAULT NULL,
  `total_skor` int(11) DEFAULT NULL,
  `kesimpulan` text DEFAULT NULL,
  `rekomendasi` text DEFAULT NULL,
  `status_approval` enum('pending','approved','rejected') DEFAULT 'pending',
  `approved_by` int(11) DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `approval_notes` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `financial_evaluation`
--

CREATE TABLE `financial_evaluation` (
  `id` int(11) NOT NULL,
  `project_id` int(11) DEFAULT NULL,
  `laporan_neraca` tinyint(1) DEFAULT NULL,
  `laporan_laba_rugi` tinyint(1) DEFAULT NULL,
  `laporan_arus_kas` tinyint(1) DEFAULT NULL,
  `tren_keuangan` enum('Meningkat','Stabil','Menurun') DEFAULT NULL,
  `konsistensi_laba` enum('Konsisten','Tidak Konsisten') DEFAULT NULL,
  `status_approval` enum('pending','approved','rejected') DEFAULT 'pending',
  `approved_by` int(11) DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `approval_notes` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `financial_review`
--

CREATE TABLE `financial_review` (
  `id` int(11) NOT NULL,
  `project_id` int(11) DEFAULT NULL,
  `aspek` varchar(255) DEFAULT NULL,
  `nilai` decimal(12,2) DEFAULT NULL,
  `catatan` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `financial_snapshot`
--

CREATE TABLE `financial_snapshot` (
  `id` int(11) NOT NULL,
  `periode` date NOT NULL,
  `total_aset` decimal(18,2) NOT NULL,
  `total_investasi` decimal(18,2) NOT NULL,
  `total_kas` decimal(18,2) NOT NULL,
  `total_kewajiban_jangka_pendek` decimal(18,2) DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `first_screening`
--

CREATE TABLE `first_screening` (
  `id` int(11) NOT NULL,
  `project_id` int(11) DEFAULT NULL,
  `indikator` varchar(255) DEFAULT NULL,
  `skor` int(11) DEFAULT NULL,
  `catatan` text DEFAULT NULL,
  `status_approval` enum('pending','approved','rejected') DEFAULT 'pending',
  `approved_by` int(11) DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `approval_notes` text DEFAULT NULL,
  `visi_misi` text DEFAULT NULL,
  `relevansi_portfolio` enum('Sesuai','Kurang Sesuai','Tidak Sesuai') DEFAULT NULL,
  `target_pasar` text DEFAULT NULL,
  `keunikan_produk` enum('Sangat Unik','Cukup Unik','Biasa Saja') DEFAULT NULL,
  `estimasi_ukuran_pasar` enum('Besar','Sedang','Kecil') DEFAULT NULL,
  `risiko_hukum` enum('Tinggi','Sedang','Rendah') DEFAULT NULL,
  `risiko_finansial` enum('Tinggi','Sedang','Rendah') DEFAULT NULL,
  `risiko_operasional` enum('Tinggi','Sedang','Rendah') DEFAULT NULL,
  `patuh_syariah` tinyint(1) DEFAULT NULL,
  `syariah_produksi` text DEFAULT NULL,
  `syariah_operasional` text DEFAULT NULL,
  `syariah_pemasaran` text DEFAULT NULL,
  `syariah_keuangan` text DEFAULT NULL,
  `legalitas_lengkap` tinyint(1) DEFAULT NULL,
  `pasar_dapat_dipenetrasi` tinyint(1) DEFAULT NULL,
  `pasar_luas` tinyint(1) DEFAULT NULL,
  `data_keuangan_potensial` tinyint(1) DEFAULT NULL,
  `risiko_dapat_dimitigasi` tinyint(1) DEFAULT NULL,
  `kepatuhan_syariah` tinyint(1) DEFAULT NULL,
  `hasil_screening` enum('Lolos','Tidak Lolos') DEFAULT NULL,
  `catatan_penilai` text DEFAULT NULL,
  `rekomendasi_penilai` text DEFAULT NULL,
  `dinilai_oleh` varchar(255) DEFAULT NULL,
  `disetujui_oleh` varchar(255) DEFAULT NULL,
  `diketahui_oleh` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `first_screening`
--

INSERT INTO `first_screening` (`id`, `project_id`, `indikator`, `skor`, `catatan`, `status_approval`, `approved_by`, `approved_at`, `approval_notes`, `visi_misi`, `relevansi_portfolio`, `target_pasar`, `keunikan_produk`, `estimasi_ukuran_pasar`, `risiko_hukum`, `risiko_finansial`, `risiko_operasional`, `patuh_syariah`, `syariah_produksi`, `syariah_operasional`, `syariah_pemasaran`, `syariah_keuangan`, `legalitas_lengkap`, `pasar_dapat_dipenetrasi`, `pasar_luas`, `data_keuangan_potensial`, `risiko_dapat_dimitigasi`, `kepatuhan_syariah`, `hasil_screening`, `catatan_penilai`, `rekomendasi_penilai`, `dinilai_oleh`, `disetujui_oleh`, `diketahui_oleh`) VALUES
(6, 9, NULL, NULL, NULL, 'pending', NULL, NULL, NULL, '', 'Sesuai', 'Wanita Muslimah Remaja hingga Dewasa', 'Biasa Saja', 'Besar', 'Tinggi', 'Tinggi', 'Sedang', 1, '0', 'Memisahkan pekerja wanita dan pria agar tidak terjadi ikhtilat.\r\nOperasional menggunakan metode yang tidak menyalahi syariat islam', 'Metode pemasaran konvensional dari mulut ke mulut dan juga sosial media berupa Instagram.\r\n\r\nMetode yang digunakan tidak menyalahi syariat islam.', '0', 0, 1, 0, 0, 0, 1, 'Lolos', '', '', 'Adi Mulya Setiadi', '', '');

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

--
-- Table structure for table `instructors`
--

CREATE TABLE `instructors` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `bio` text DEFAULT NULL,
  `photo_url` varchar(255) DEFAULT NULL,
  `rating` decimal(3,2) DEFAULT 0.00,
  `total_reviews` int(11) DEFAULT 0,
  `expertise` varchar(255) DEFAULT NULL,
  `linkedin_url` varchar(255) DEFAULT NULL,
  `website_url` varchar(255) DEFAULT NULL,
  `experience_years` int(11) DEFAULT 0,
  `location` varchar(100) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `instructors`
--

INSERT INTO `instructors` (`id`, `user_id`, `bio`, `photo_url`, `rating`, `total_reviews`, `expertise`, `linkedin_url`, `website_url`, `experience_years`, `location`, `created_at`, `updated_at`) VALUES
(4, 4, '', 'uploads/instructors/6838813516dd7_Saya Pintar atau Bodoh.png', 0.00, 0, 'marketing, sales, artificialintelligent', '', '', 1, 'Tangerang Selatan', '2025-05-29 15:45:57', '2025-05-29 15:45:57'),
(5, 6, '', '', 0.00, 0, 'gaming', '', '', 0, 'bogor', '2025-05-30 20:39:09', '2025-05-30 20:39:09'),
(6, 5, '', '', 0.00, 0, 'cooking', '', '', 0, '', '2025-05-30 20:39:22', '2025-05-30 20:39:22'),
(7, 3, '', '', 0.00, 0, 'coding,problem solving', '', '', 0, '', '2025-05-30 20:39:48', '2025-05-30 20:39:48');

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

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

CREATE TABLE `notifications` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `message` text NOT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `created_at` datetime DEFAULT current_timestamp(),
  `training_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `notifications` (`id`, `user_id`, `message`, `is_read`, `created_at`, `training_id`) VALUES
(1, 4, 'You have been assigned as instructor for training: Notification Test for The User', 1, '2025-05-30 09:45:47', 18),
(2, 4, 'You have been assigned as instructor for training: Notification Test Number 2', 1, '2025-05-30 09:50:13', 19),
(3, 4, 'You have been assigned as instructor for training: Notification Test number 3', 1, '2025-05-30 09:54:06', 20),
(4, 4, 'You have been assigned as instructor for training: Notification Test No.5', 1, '2025-05-30 10:00:26', 22),
(5, 4, 'You have been assigned as instructor for training: Notif Test 5', 1, '2025-05-30 10:03:52', 23),
(6, 4, 'You have been assigned as instructor for training: nitif test 7', 1, '2025-05-30 10:08:19', 24),
(7, 4, 'You have been assigned as instructor for training: notif 8', 1, '2025-05-30 10:23:43', 25),
(8, 4, 'You have been assigned as instructor for training: Notif 9', 1, '2025-05-30 10:29:28', 26),
(9, 4, 'You have been assigned as instructor for training: test 10', 1, '2025-05-30 10:36:26', 27),
(10, 4, 'You have been assigned as instructor for training: test 11', 1, '2025-05-30 10:38:17', 28),
(11, 4, 'You have been assigned as instructor for training: test 11', 1, '2025-05-30 10:40:29', 29),
(12, 4, 'You have been assigned as instructor for training: test 12', 1, '2025-05-30 10:46:17', 30),
(13, 4, 'You have been assigned as instructor for training: test 13', 1, '2025-05-30 10:48:40', 31),
(14, 4, 'You have been assigned as instructor for training: test 13', 1, '2025-05-30 10:53:12', 32),
(15, 4, 'You have been assigned as instructor for training: test 15', 1, '2025-05-30 10:56:59', 33),
(16, 4, 'You have been assigned as instructor for training: 16', 1, '2025-05-30 11:13:03', 34),
(17, 3, 'You have been assigned to attend training: 16 on 2025-05-03', 1, '2025-05-30 11:13:03', 34),
(18, 2, 'You have been assigned to attend training: 16 on 2025-05-03', 1, '2025-05-30 11:13:03', 34),
(19, 4, 'You have been assigned as instructor for training: 17', 1, '2025-05-30 11:15:19', 35),
(20, 3, 'You have been assigned to attend training: 17 on 2025-05-31', 1, '2025-05-30 11:15:20', 35),
(21, 7, 'You have been assigned to attend training: 17 on 2025-05-31', 1, '2025-05-30 11:15:20', 35),
(22, 2, 'You have been assigned to attend training: 17 on 2025-05-31', 1, '2025-05-30 11:15:20', 35),
(23, 2, 'Reminder: You have an upcoming training \'image link\' starting on 2025-05-31.', 1, '2025-05-30 21:36:19', 11),
(24, 2, 'Reminder: You have an upcoming training \'Training Number 12\' starting on 2025-05-31.', 1, '2025-05-30 21:36:19', 12),
(25, 7, 'Reminder: You have an upcoming training \'Training Number 12\' starting on 2025-05-31.', 1, '2025-05-30 21:36:19', 12),
(26, 2, 'Reminder: You have an upcoming training \'Notification Test Number 2\' starting on 2025-05-31.', 1, '2025-05-30 21:36:19', 19),
(27, 3, 'Reminder: You have an upcoming training \'Notification Test Number 2\' starting on 2025-05-31.', 0, '2025-05-30 21:36:19', 19),
(28, 4, 'Reminder: You have an upcoming training \'Notification Test Number 2\' starting on 2025-05-31.', 1, '2025-05-30 21:36:19', 19),
(29, 5, 'Reminder: You have an upcoming training \'Notification Test Number 2\' starting on 2025-05-31.', 0, '2025-05-30 21:36:19', 19),
(30, 6, 'Reminder: You have an upcoming training \'Notification Test Number 2\' starting on 2025-05-31.', 1, '2025-05-30 21:36:19', 19),
(31, 7, 'Reminder: You have an upcoming training \'Notification Test Number 2\' starting on 2025-05-31.', 1, '2025-05-30 21:36:19', 19),
(32, 3, 'Reminder: You have an upcoming training \'test 10\' starting on 2025-05-31.', 0, '2025-05-30 21:36:19', 27),
(33, 7, 'Reminder: You have an upcoming training \'test 10\' starting on 2025-05-31.', 1, '2025-05-30 21:36:19', 27),
(34, 2, 'Reminder: You have an upcoming training \'17\' starting on 2025-05-31.', 1, '2025-05-30 21:36:19', 35),
(35, 3, 'Reminder: You have an upcoming training \'17\' starting on 2025-05-31.', 0, '2025-05-30 21:36:19', 35),
(36, 7, 'Reminder: You have an upcoming training \'17\' starting on 2025-05-31.', 1, '2025-05-30 21:36:19', 35),
(37, 7, 'Assalamualaikum Wr. Wb, anda telah ditugaskan sebagai instruktur pelatihan panjangin breeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee', 1, '2025-05-31 03:50:56', 35),
(38, 3, 'You have been assigned to attend training: panjangin breeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee on 2025-05-31', 0, '2025-05-31 03:50:56', 35),
(39, 2, 'You have been assigned to attend training: panjangin breeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee on 2025-05-31', 0, '2025-05-31 03:50:56', 35),
(40, 7, 'You have been assigned to attend training: panjangin breeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee on 2025-05-31', 1, '2025-05-31 03:50:56', 35),
(41, 6, 'You have been assigned to attend training: panjangin breeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee on 2025-05-31', 0, '2025-05-31 03:50:56', 35),
(42, 5, 'You have been assigned to attend training: panjangin breeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee on 2025-05-31', 0, '2025-05-31 03:50:56', 35),
(43, 4, 'You have been assigned to attend training: panjangin breeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee on 2025-05-31', 1, '2025-05-31 03:50:56', 35),
(44, 7, 'Assalamualaikum Wr. Wb, anda telah ditugaskan sebagai instruktur pelatihan panjangin breeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee', 0, '2025-05-31 17:20:16', 35),
(45, 3, 'You have been assigned to attend training: panjangin breeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee on 2025-05-31', 0, '2025-05-31 17:20:16', 35),
(46, 2, 'You have been assigned to attend training: panjangin breeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee on 2025-05-31', 0, '2025-05-31 17:20:16', 35),
(47, 7, 'You have been assigned to attend training: panjangin breeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee on 2025-05-31', 0, '2025-05-31 17:20:16', 35),
(48, 6, 'You have been assigned to attend training: panjangin breeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee on 2025-05-31', 0, '2025-05-31 17:20:16', 35),
(49, 5, 'You have been assigned to attend training: panjangin breeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee on 2025-05-31', 0, '2025-05-31 17:20:16', 35),
(50, 4, 'You have been assigned to attend training: panjangin breeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee on 2025-05-31', 1, '2025-05-31 17:20:16', 35),
(51, 4, 'Assalamualaikum Wr. Wb, anda telah ditugaskan sebagai instruktur pelatihan Notification Test No.5', 1, '2025-05-31 17:35:00', 22),
(52, 6, 'Assalamualaikum Wr. Wb, anda telah ditugaskan sebagai instruktur pelatihan Erika ngajar ngaji', 0, '2025-05-31 17:39:01', 36),
(53, 3, 'You have been assigned to attend training: Erika ngajar ngaji on 2025-06-02', 0, '2025-05-31 17:39:01', 36),
(54, 2, 'You have been assigned to attend training: Erika ngajar ngaji on 2025-06-02', 0, '2025-05-31 17:39:01', 36),
(55, 7, 'You have been assigned to attend training: Erika ngajar ngaji on 2025-06-02', 0, '2025-05-31 17:39:01', 36),
(56, 6, 'You have been assigned to attend training: Erika ngajar ngaji on 2025-06-02', 0, '2025-05-31 17:39:01', 36),
(57, 5, 'You have been assigned to attend training: Erika ngajar ngaji on 2025-06-02', 0, '2025-05-31 17:39:01', 36),
(58, 4, 'You have been assigned to attend training: Erika ngajar ngaji on 2025-06-02', 1, '2025-05-31 17:39:01', 36),
(59, 6, 'Assalamualaikum Wr. Wb, anda telah ditugaskan sebagai instruktur pelatihan Erika ngajar ngaji', 0, '2025-06-02 10:19:06', 36),
(60, 8, 'You have been assigned to attend training: Erika ngajar ngaji on 2025-06-02', 1, '2025-06-02 10:19:06', 36),
(61, 7, 'Assalamualaikum Wr. Wb, anda telah ditugaskan sebagai instruktur pelatihan Title dengan judul cukup panjang.', 0, '2025-06-03 09:43:43', 35);

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

--
-- Table structure for table `operational_market_analysis`
--

CREATE TABLE `operational_market_analysis` (
  `id` int(11) NOT NULL,
  `project_id` int(11) DEFAULT NULL,
  `kapasitas_produksi` text DEFAULT NULL,
  `efisiensi_operasional` enum('Baik','Sedang','Buruk') DEFAULT NULL,
  `status_approval` enum('pending','approved','rejected') DEFAULT 'pending',
  `approved_by` int(11) DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `approval_notes` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `participants`
--

CREATE TABLE `participants` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `training_id` int(11) NOT NULL,
  `status` enum('enrolled','completed') DEFAULT 'enrolled',
  `score` decimal(5,2) DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `participants`
--

INSERT INTO `participants` (`id`, `user_id`, `training_id`, `status`, `score`, `completed_at`, `created_at`, `updated_at`) VALUES
(1, 3, 1, '', NULL, NULL, '2025-05-29 18:33:44', '2025-05-29 18:33:44'),
(2, 2, 1, '', NULL, NULL, '2025-05-29 18:33:46', '2025-05-29 18:33:46'),
(3, 6, 1, '', NULL, NULL, '2025-05-29 18:33:48', '2025-05-29 18:33:48'),
(4, 5, 1, '', NULL, NULL, '2025-05-29 18:33:50', '2025-05-29 18:33:50'),
(5, 4, 1, '', NULL, NULL, '2025-05-29 18:33:51', '2025-05-29 18:33:51'),
(6, 2, 2, '', NULL, NULL, '2025-05-29 18:38:00', '2025-05-29 18:38:00'),
(7, 3, 3, '', NULL, NULL, '2025-05-29 18:38:09', '2025-05-29 18:38:09'),
(8, 6, 3, '', NULL, NULL, '2025-05-29 18:38:11', '2025-05-29 18:38:11'),
(9, 6, 4, '', NULL, NULL, '2025-05-29 18:38:17', '2025-05-29 18:38:17'),
(10, 5, 4, '', NULL, NULL, '2025-05-29 18:38:18', '2025-05-29 18:38:18'),
(11, 3, 5, '', NULL, NULL, '2025-05-29 18:38:23', '2025-05-29 18:38:23'),
(12, 3, 6, '', NULL, NULL, '2025-05-29 18:38:29', '2025-05-29 18:38:29'),
(13, 2, 6, '', NULL, NULL, '2025-05-29 18:38:30', '2025-05-29 18:38:30'),
(14, 6, 6, '', NULL, NULL, '2025-05-29 18:38:31', '2025-05-29 18:38:31'),
(15, 2, 7, '', NULL, NULL, '2025-05-29 18:38:37', '2025-05-29 18:38:37'),
(16, 3, 8, '', NULL, NULL, '2025-05-29 18:38:43', '2025-05-29 18:38:43'),
(17, 3, 35, '', NULL, NULL, '2025-05-30 13:56:07', '2025-05-30 13:56:07'),
(18, 3, 18, '', NULL, NULL, '2025-05-30 20:54:42', '2025-05-30 20:54:42'),
(19, 2, 18, '', NULL, NULL, '2025-05-30 20:54:44', '2025-05-30 20:54:44'),
(20, 7, 18, '', NULL, NULL, '2025-05-30 20:54:45', '2025-05-30 20:54:45'),
(21, 4, 18, '', NULL, NULL, '2025-05-30 20:55:23', '2025-05-30 20:55:23'),
(22, 8, 36, '', NULL, NULL, '2025-06-02 03:19:37', '2025-06-02 03:19:37');

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

--
-- Table structure for table `password_resets`
--

CREATE TABLE `password_resets` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `token` varchar(255) NOT NULL,
  `expires_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `pretest_posttest`
--

CREATE TABLE `pretest_posttest` (
  `id` int(11) NOT NULL,
  `training_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `pre_test_result` tinyint(3) UNSIGNED DEFAULT NULL CHECK (`pre_test_result` between 0 and 100),
  `post_test_result` tinyint(3) UNSIGNED DEFAULT NULL CHECK (`post_test_result` between 0 and 100),
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `pre_test_taken_at` datetime DEFAULT NULL,
  `post_test_taken_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

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

CREATE TABLE `projects` (
  `id` int(11) NOT NULL,
  `nama_proyek` varchar(255) DEFAULT NULL,
  `project_id_number` varchar(50) DEFAULT NULL,
  `business_id` int(11) DEFAULT NULL,
  `nama_bisnis` varchar(255) DEFAULT NULL,
  `tanggal_penilaian` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `nama_penilai` varchar(255) DEFAULT NULL,
  `project_description` text DEFAULT NULL,
  `project_value` decimal(18,2) DEFAULT NULL,
  `estimated_profit` decimal(18,2) DEFAULT NULL,
  `net_profit_margin` decimal(5,2) DEFAULT NULL,
  `monthly_net_profit` decimal(18,2) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `duration` int(11) DEFAULT NULL,
  `project_category` varchar(100) DEFAULT NULL,
  `project_location` varchar(255) DEFAULT NULL,
  `project_category_id` int(11) DEFAULT NULL,
  `project_assessment_progress` enum('L1. Pre-Assesment','L2. Deep-Assesment','L3. Decision-Stage','L4. Post Investation') DEFAULT 'L1. Pre-Assesment',
  `project_status` enum('Not-Started','In-Progress','Accepted','Declined') DEFAULT 'Not-Started'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `projects` (`id`, `nama_proyek`, `project_id_number`, `business_id`, `nama_bisnis`, `tanggal_penilaian`, `created_at`, `nama_penilai`, `project_description`, `project_value`, `estimated_profit`, `net_profit_margin`, `monthly_net_profit`, `start_date`, `end_date`, `duration`, `project_category`, `project_location`, `project_category_id`, `project_assessment_progress`, `project_status`) VALUES
(9, 'Hijab Pelangi Gamis 001', '001/Aqad/III/2024', NULL, 'Pelangi Hijab', NULL, '2025-07-02 03:47:52', NULL, 'Permodalan proyek pengadaan bahan baku & biaya kerja untuk PO Gamis 001', 1744200000.00, 337320000.00, 19.33, 2.42, '2024-03-28', '2024-08-15', 0, 'Project', 'Bogor', NULL, 'L1. Pre-Assesment', 'Not-Started'),
(10, 'Hijab Pelangi Gamis 002', '002/Aqad/IV/2024', NULL, 'Pelangi Hijab', NULL, '2025-07-02 03:53:12', NULL, '', 1926500000.00, 379400000.00, 19.69, 2.81, '2024-04-23', '2024-11-12', 203, 'Project', 'Bogor', NULL, 'L1. Pre-Assesment', 'Not-Started'),
(11, 'Hijab Pelangi Gamis 003', '003/Aqad/V/2024', NULL, 'Pelangi Hijab', NULL, '2025-07-02 03:54:44', NULL, '', 2199600000.00, 404160000.00, 18.37, 3.67, '2024-05-28', '2024-10-31', 156, 'Project', 'Bogor', NULL, 'L1. Pre-Assesment', 'Not-Started'),
(12, 'Hijab Pelangi Gamis 004', '004/Aqad/V/2024', NULL, 'Pelangi Hijab', NULL, '2025-07-02 03:56:17', NULL, '', 1879500000.00, 373200000.00, 19.85, 3.31, '2024-05-28', '2024-09-04', 99, 'Project', 'Bogor', NULL, 'L1. Pre-Assesment', 'Not-Started'),
(13, 'Hijab Pelangi Gamis 005', '005/Aqad/VII/2024', NULL, 'Pelangi Hijab', NULL, '2025-07-02 03:57:38', NULL, '', 2072500000.00, 426000000.00, 20.55, 3.43, '2024-07-03', '2024-12-30', 180, 'Project', 'Bogor', NULL, 'L1. Pre-Assesment', 'Not-Started'),
(14, 'Hijab Pelangi Gamis 006', '006/Aqad/VIII/2024', NULL, 'Pelangi Hijab', NULL, '2025-07-02 03:58:59', NULL, '', 4127500000.00, 873750000.00, 21.17, 3.02, '2024-08-26', '2025-03-10', 196, 'Project', 'Bogor', NULL, 'L1. Pre-Assesment', 'Not-Started'),
(15, 'Hijab Pelangi Gamis 007', '007/Aqad/VIII/2024', NULL, 'Pelangi Hijab', NULL, '2025-07-02 04:00:14', NULL, '', 1664000000.00, 334400000.00, 20.09, 3.35, '2024-08-26', '2025-02-25', 183, 'Project', 'Bogor', NULL, 'L1. Pre-Assesment', 'Not-Started'),
(16, 'Hijab Pelangi Gamis 008', '008/Aqad/VIII/2024', NULL, 'Pelangi Hijab', NULL, '2025-07-02 04:01:50', NULL, '', 1921000000.00, 371600000.00, 19.34, 4.84, '2024-11-04', '2025-02-27', 115, 'Project', 'Bogor', NULL, 'L1. Pre-Assesment', 'Not-Started'),
(17, 'Pak Ayud - 0047', 'Pak Ayud - 0047', NULL, 'PT. Yuditha Nugraha Karya', NULL, '2025-07-02 04:06:43', NULL, '', 683328876.00, 118724124.00, 17.37, 2.17, '2024-07-01', '2024-10-10', 101, 'Project', 'Jakarta', NULL, 'L1. Pre-Assesment', 'Not-Started'),
(18, 'Pak Ayud - 0052', 'Pak Ayud - 0052', NULL, 'PT. Yuditha Nugraha Karya', NULL, '2025-07-02 04:08:10', NULL, '', 1080000000.00, 126750500.00, 11.73, 3.91, '2024-05-31', '2024-07-25', 55, 'Project', 'Jakarta', NULL, 'L1. Pre-Assesment', 'Not-Started'),
(19, 'Pak Ayud - 337', 'Pak Ayud - 337', NULL, 'PT. Yuditha Nugraha Karya', NULL, '2025-07-02 04:09:27', NULL, '', 280000000.00, 56417030.00, 20.15, 3.36, '2024-08-15', '2025-01-06', 144, 'Project', 'Jakarta', NULL, 'L1. Pre-Assesment', 'Not-Started'),
(20, 'Pak Ayud - 039', 'Pak Ayud - 039', NULL, 'PT. Yuditha Nugraha Karya', NULL, '2025-07-02 04:10:34', NULL, '', 220000000.00, 38926480.00, 17.70, 2.53, '2024-07-15', '2025-01-12', 181, 'Project', 'Jakarta', NULL, 'L1. Pre-Assesment', 'Not-Started'),
(21, 'Pak Ayud - 019', 'Pak Ayud - 019', NULL, 'PT. Yuditha Nugraha Karya', NULL, '2025-07-02 04:12:51', NULL, '', 400000000.00, 71665234.00, 17.91, 4.48, '2024-10-11', '2024-02-05', 106, 'Project', 'Jakarta', NULL, 'L1. Pre-Assesment', 'Not-Started'),
(22, 'BMI-Mekanikal WHP', '001/Aqad/VII/2024', NULL, 'BMI Mekanikal', NULL, '2025-07-02 04:16:37', NULL, '', 354029375.00, 53935398.00, 15.23, 3.05, '2024-07-29', '2025-01-06', 161, 'Project', 'Palembang', NULL, 'L1. Pre-Assesment', 'Not-Started'),
(23, 'BMI-Mekanikal WB-TUK', '002/Aqad/VII/2024', NULL, 'BMI Mekanikal', NULL, '2025-07-02 04:17:50', NULL, '', 198357518.00, 44027988.00, 12.87, 1.84, '2024-07-29', '2025-03-13', 227, 'Project', 'Palembang', NULL, 'L1. Pre-Assesment', 'Not-Started'),
(24, 'Hari Ini Event Organizer', '0242/SPK/BMI/X/2024', NULL, 'CGLink', NULL, '2025-07-02 04:25:55', NULL, 'Permodalan Event Pertamina di Surabaya', 925000000.00, 142527747.00, 15.40, 3.08, '2024-10-05', '2024-12-30', 86, 'Project', 'Surabaya', NULL, 'L1. Pre-Assesment', 'Not-Started'),
(25, 'Laksmi Muslimah NikahFest 2024 Surabaya', 'No. 010/Aqad Syirkah/XII/2024', NULL, 'Laksmi Muslimah', NULL, '2025-07-02 04:30:25', NULL, '', 665300000.00, 109025000.00, 16.38, 2.70, '2024-12-06', '2025-06-15', 191, 'Project', 'Surabaya', NULL, 'L1. Pre-Assesment', 'Not-Started'),
(26, 'PENARIKAN SKTM 20 KV, PEMBANGUNAN SIPIL & INSTALASI GARDU UNTUK PB/PD TM LOT 2 – PLN UP 3 PONDOK KOPI', 'No. 005/Aqad Syirkah/III/2025', NULL, 'PT. Yuditha Nugraha Karya', NULL, '2025-07-02 04:34:06', NULL, '', 262773000.00, 87590929.00, 33.33, 6.67, '2025-03-26', '2025-08-26', 150, 'Project', 'Jakarta', NULL, 'L1. Pre-Assesment', 'Not-Started'),
(28, 'Haura Utan Kayu New Concept', 'No. 007/Aqad Syirkah/VIII/2024', NULL, 'Haura Salon & Spa Muslimah', NULL, '2025-07-02 06:52:41', NULL, 'Syirkah Mudharabah Pengembangan Haura Utan Kayu dengan konsep baru dengan nisbah 40% untuk pemodal', 511184000.00, 200968992.00, 39.31, 3.27, '2024-10-01', '2029-10-01', 1825, 'Investment', 'Jakarta', NULL, 'L1. Pre-Assesment', 'Not-Started'),
(31, 'Wima Aesthetic Clinic cabang Sukoharjo', 'No. 011/Aqad Syirkah/XII/2024', NULL, 'Wima Aesthetic Clinic', NULL, '2025-07-02 07:03:37', NULL, 'Investasi Pembukaan Wima Aesthetic Clinic cabang Sukoharjo dengan nisbah 50%', 1472000000.00, 292128000.00, 19.84, 1.65, '2025-02-12', '2030-02-12', 1825, 'Investment', 'Surakarta', NULL, 'L1. Pre-Assesment', 'Not-Started'),
(32, 'KERJASAMA SYIRKAH MUDHARABAH PENGEMBANGAN LEMBAGA BIMBINGAN BELAJAR AKADEMI PRESTASI CABANG PALEMBANG', 'No. 001/Aqad Syirkah/II/2025', NULL, 'Akademi Prestasi', NULL, '2025-07-02 07:11:01', NULL, 'Kerjasama Syirkah Mudharabah pengembangan lembaga bimbingan belajar Akademi Prestasi cabang Palembang dengan nisbah 50%', 556308600.00, 0.00, 0.00, 0.00, '2025-02-05', '2030-02-05', 1825, 'Investment', 'Palembang', NULL, 'L1. Pre-Assesment', 'Not-Started'),
(33, 'Syirkah Bintang Mafahim Inspiratif', '001/Mafahim/VII/2025', NULL, 'Mafahim', NULL, '2025-07-02 07:14:53', NULL, 'Syirkah Mudharabah pembentukan konsultan bisnis syariah bernama Bintang Mafahim Inspiratif dengan Nisbah kepada pemodal sebesar 30%', 200000000.00, 195000000.00, 97.50, 8.12, '2025-07-01', '2028-07-01', 1095, 'Investment', 'Tangerang', NULL, 'L1. Pre-Assesment', 'Not-Started'),
(34, 'PENARIKAN KABEL SKTM – SPJ 0033 PLN MARUNDA', 'No. 006/Aqad Syirkah/V/2025', NULL, 'PT. Yuditha Nugraha Karya', NULL, '2025-07-02 07:18:44', NULL, '', 365810000.00, 56278498.00, 15.38, 0.00, '2025-03-26', '2025-08-26', 150, 'Project', 'Jakarta', NULL, 'L1. Pre-Assesment', 'Not-Started');

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

--
-- Table structure for table `project_category`
--

CREATE TABLE `project_category` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `project_returns`
--

CREATE TABLE `project_returns` (
  `id` int(11) NOT NULL,
  `project_id` int(11) NOT NULL,
  `return_date` date NOT NULL,
  `return_type` enum('modal','keuntungan') NOT NULL,
  `amount` decimal(18,2) NOT NULL,
  `description` text DEFAULT NULL,
  `recorded_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `project_returns`
--

INSERT INTO `project_returns` (`id`, `project_id`, `return_date`, `return_type`, `amount`, `description`, `recorded_by`, `created_at`, `updated_at`) VALUES
(4, 9, '2024-03-28', 'modal', 1744200000.00, '', 4, '2025-07-02 06:04:30', '2025-07-02 06:04:30'),
(5, 9, '2024-08-27', 'keuntungan', 337320000.00, '', 4, '2025-07-02 06:04:52', '2025-07-02 06:04:52'),
(6, 10, '2024-04-23', 'modal', 1926500000.00, '', 4, '2025-07-02 06:05:50', '2025-07-02 06:05:50'),
(7, 10, '2024-11-12', 'keuntungan', 379400000.00, '', 4, '2025-07-02 06:06:06', '2025-07-02 06:06:06'),
(8, 11, '2024-10-31', 'modal', 2199600000.00, '', 4, '2025-07-02 06:06:44', '2025-07-02 06:06:44'),
(9, 11, '2024-10-31', 'keuntungan', 404160000.00, '', 4, '2025-07-02 06:07:01', '2025-07-02 06:07:01'),
(10, 12, '2024-11-25', 'modal', 1879500000.00, '', 4, '2025-07-02 06:07:20', '2025-07-02 06:07:20'),
(11, 12, '2024-11-25', 'keuntungan', 373200000.00, '', 4, '2025-07-02 06:07:36', '2025-07-02 06:07:36'),
(12, 13, '2025-01-03', 'modal', 2072500000.00, '', 4, '2025-07-02 07:22:12', '2025-07-02 07:22:12'),
(13, 13, '2024-01-07', 'keuntungan', 426000000.00, '', 4, '2025-07-02 07:23:25', '2025-07-02 07:23:25'),
(14, 14, '2025-06-20', 'modal', 3500000000.00, '', 4, '2025-07-02 07:24:39', '2025-07-02 07:24:39'),
(15, 14, '2024-03-11', 'keuntungan', 291250000.00, '', 4, '2025-07-02 07:26:28', '2025-07-02 07:26:28'),
(16, 14, '2024-12-23', 'keuntungan', 291250000.00, '', 4, '2025-07-02 07:26:47', '2025-07-02 07:26:47'),
(17, 15, '2025-03-21', 'modal', 1664000000.00, '', 4, '2025-07-02 07:27:40', '2025-07-02 07:27:40'),
(18, 15, '2025-03-21', 'keuntungan', 334400000.00, '', 4, '2025-07-02 07:28:06', '2025-07-02 07:28:06'),
(19, 16, '2025-04-25', 'modal', 1921000000.00, '', 4, '2025-07-02 07:28:46', '2025-07-02 07:28:46'),
(20, 16, '2024-05-02', 'keuntungan', 371600000.00, '', 4, '2025-07-02 07:29:09', '2025-07-02 07:29:09'),
(22, 24, '2025-05-05', 'modal', 899957497.00, '', 4, '2025-07-02 07:38:08', '2025-07-02 07:38:08'),
(23, 24, '2025-04-15', 'keuntungan', 149957497.00, '', 4, '2025-07-02 07:39:13', '2025-07-02 07:39:13'),
(24, 28, '2025-06-11', 'keuntungan', 30804515.00, 'Bagi hasil bulan maret-mei', 4, '2025-07-02 07:43:05', '2025-07-02 07:43:05'),
(25, 28, '2025-03-27', 'keuntungan', 11698872.00, 'Periode Jan-Mar 2025', 4, '2025-07-02 07:44:14', '2025-07-02 07:44:14'),
(26, 17, '2024-10-10', 'modal', 683328876.00, '', 4, '2025-07-02 07:46:09', '2025-07-02 07:46:09'),
(27, 17, '2024-07-04', 'keuntungan', 118724124.00, '', 4, '2025-07-02 07:46:28', '2025-07-02 07:46:28'),
(28, 18, '2024-05-31', 'modal', 1080000000.00, '', 4, '2025-07-02 07:46:48', '2025-07-02 07:46:48'),
(29, 18, '2024-07-25', 'keuntungan', 126750500.00, '', 4, '2025-07-02 07:47:03', '2025-07-02 07:47:03'),
(30, 19, '2025-01-06', 'modal', 280000000.00, '', 4, '2025-07-02 07:47:26', '2025-07-02 07:47:26'),
(31, 19, '2025-01-06', 'keuntungan', 56417030.00, '', 4, '2025-07-02 07:47:41', '2025-07-02 07:47:41'),
(32, 20, '2025-01-12', 'modal', 220000000.00, '', 4, '2025-07-02 07:47:57', '2025-07-02 07:47:57'),
(33, 20, '2025-01-12', 'keuntungan', 38926480.00, '', 4, '2025-07-02 07:48:13', '2025-07-02 07:48:13'),
(34, 21, '0000-00-00', 'modal', 400000000.00, '', 4, '2025-07-02 07:49:19', '2025-07-02 07:49:19'),
(35, 21, '2024-12-20', 'keuntungan', 71665234.00, '', 4, '2025-07-02 07:49:35', '2025-07-02 07:49:35'),
(36, 22, '2024-11-26', 'modal', 174020780.00, '', 4, '2025-07-02 07:58:43', '2025-07-02 07:58:43'),
(37, 22, '2025-04-10', 'modal', 169816244.00, '', 4, '2025-07-02 07:59:12', '2025-07-02 07:59:12'),
(38, 22, '2025-05-09', 'modal', 10192351.00, '', 4, '2025-07-02 07:59:35', '2025-07-02 07:59:35');

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

--
-- Table structure for table `resources`
--

CREATE TABLE `resources` (
  `id` int(11) NOT NULL,
  `title` varchar(200) NOT NULL,
  `description` text DEFAULT NULL,
  `file_url` varchar(255) DEFAULT NULL,
  `uploaded_by` int(11) NOT NULL,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `test_answers`
--

CREATE TABLE `test_answers` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `training_id` int(11) NOT NULL,
  `question_id` int(11) NOT NULL,
  `selected_option_id` int(11) NOT NULL,
  `test_type` enum('pre','post') NOT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `is_correct` tinyint(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `test_answers`
--

INSERT INTO `test_answers` (`id`, `user_id`, `training_id`, `question_id`, `selected_option_id`, `test_type`, `created_at`, `is_correct`) VALUES
(1, 4, 7, 2, 30, 'pre', '2025-05-31 14:45:34', 1),
(2, 4, 7, 3, 10, 'pre', '2025-05-31 14:45:34', 1),
(3, 4, 7, 4, 16, 'pre', '2025-05-31 14:45:34', 1),
(4, 4, 7, 5, 19, 'pre', '2025-05-31 14:45:34', 1),
(5, 4, 7, 6, 22, 'pre', '2025-05-31 14:45:34', 1),
(6, 4, 7, 10, 72, 'pre', '2025-05-31 14:45:34', 1),
(7, 4, 35, 11, 76, 'pre', '2025-05-31 17:26:07', 0),
(8, 4, 35, 12, 79, 'post', '2025-05-31 17:29:27', 0);

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

--
-- Table structure for table `test_options`
--

CREATE TABLE `test_options` (
  `id` int(11) NOT NULL,
  `question_id` int(11) NOT NULL,
  `option_text` varchar(255) NOT NULL,
  `is_correct` tinyint(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `test_options`
--

INSERT INTO `test_options` (`id`, `question_id`, `option_text`, `is_correct`) VALUES
(9, 3, '2 (incorrect)', 0),
(10, 3, '4 (incorrect)', 0),
(11, 3, '10 (correct)', 1),
(12, 3, '1 (incorrect)', 0),
(13, 4, '1 (incorrect)', 0),
(14, 4, '2 (incorrect)', 0),
(15, 4, '4 (incorrect)', 0),
(16, 4, '6 (correct)', 1),
(17, 5, '2 (incorrect)', 0),
(18, 5, '4 (incorrect)', 0),
(19, 5, '20 (correct)', 1),
(20, 5, '1 (incorrect)', 0),
(21, 6, '8 (correct)', 1),
(22, 6, '4 (incorrect)', 0),
(23, 6, '5 (incorrect)', 0),
(24, 6, '6 (incorrect)', 0),
(29, 2, '1 (incorrect)', 0),
(30, 2, '3 (incorrect)', 0),
(31, 2, '2 (correct)', 1),
(32, 2, '4 (incorrect)', 0),
(37, 7, 'Joko (incorrect)', 0),
(38, 7, 'Anwar (incorrect)', 0),
(39, 7, 'Budi (correct)', 1),
(40, 7, 'Barkah (incorrect)', 0),
(69, 10, '6 (incorrect)', 0),
(70, 10, '5 (incorrect)', 0),
(71, 10, '3 (incorrect)', 0),
(72, 10, '4 (correct)', 1),
(73, 11, 'Budi (incorrect)', 0),
(74, 11, 'Ahmad (incorrect)', 0),
(75, 11, 'Sapi\'i (incorrect)', 0),
(76, 11, 'Cantika (correct)', 1),
(77, 12, 'Ya (correct)', 1),
(78, 12, 'Ya (correct)', 1),
(79, 12, 'Ya (correct)', 1),
(80, 12, 'Ya (correct)', 1),
(81, 13, '2 (correct)', 1),
(82, 13, '1 (incorrect)', 0),
(83, 13, '3 (incorrect)', 0),
(84, 13, '4 (incorrect)', 0),
(85, 14, '3 (incorrect)', 0),
(86, 14, '3 (incorrect)', 0),
(87, 14, '3 (incorrect)', 0),
(88, 14, '2 (correct)', 1),
(89, 15, '3 (incorrect)', 0),
(90, 15, '4 (incorrect)', 0),
(91, 15, '5 (incorrect)', 0),
(92, 15, '2 (correct)', 1);

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

--
-- Table structure for table `test_questions`
--

CREATE TABLE `test_questions` (
  `id` int(11) NOT NULL,
  `training_id` int(11) NOT NULL,
  `question_text` text NOT NULL,
  `question_type` enum('pre','post') NOT NULL,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `test_questions`
--

INSERT INTO `test_questions` (`id`, `training_id`, `question_text`, `question_type`, `created_at`) VALUES
(2, 7, '1x2', 'pre', '2025-05-31 02:44:34'),
(3, 7, '5+5', 'pre', '2025-05-31 02:45:47'),
(4, 7, '3+3', 'pre', '2025-05-31 02:46:48'),
(5, 7, '10+10', 'pre', '2025-05-31 02:47:36'),
(6, 7, '4+4', 'pre', '2025-05-31 02:50:02'),
(7, 7, 'Siapa anak dari ibu budi?', 'post', '2025-05-31 02:57:49'),
(10, 7, '1+3', 'pre', '2025-05-31 12:19:46'),
(11, 35, 'Siapa Nama Kamu?', 'pre', '2025-05-31 17:22:36'),
(12, 35, 'Apakah kamu mau masuk barak militer?', 'post', '2025-05-31 17:22:59'),
(13, 22, '1+1', 'pre', '2025-05-31 17:32:02'),
(14, 22, '1+1', 'post', '2025-05-31 17:32:15'),
(15, 34, '1+1', 'pre', '2025-06-02 10:12:56');

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

--
-- Table structure for table `trainer_performance`
--

CREATE TABLE `trainer_performance` (
  `id` int(11) NOT NULL,
  `instructor_id` int(11) NOT NULL,
  `training_id` int(11) NOT NULL,
  `avg_score` decimal(5,2) DEFAULT NULL,
  `feedback` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `trainings`
--

CREATE TABLE `trainings` (
  `id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `image_url` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `event_place` enum('Online','Offline','Hybrid') DEFAULT NULL,
  `platform_link` varchar(255) DEFAULT NULL,
  `duration` varchar(50) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  `instructor_id` int(11) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `status` enum('planned','ongoing','completed') DEFAULT 'planned',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `trainings`
--

INSERT INTO `trainings` (`id`, `title`, `image_url`, `description`, `event_place`, `platform_link`, `duration`, `category_id`, `instructor_id`, `start_date`, `end_date`, `status`, `created_at`, `updated_at`) VALUES
(1, 'Mafahim Week 1', NULL, '', NULL, NULL, NULL, 5, 4, '2025-06-02', '2025-06-02', 'planned', '2025-05-29 15:46:46', '2025-05-29 15:46:46'),
(2, 'Mafahim Week 2', NULL, '', NULL, NULL, NULL, 4, 4, '2025-05-28', '2025-05-29', 'completed', '2025-05-29 15:52:22', '2025-05-29 15:52:22'),
(3, '123', NULL, '', NULL, NULL, NULL, 5, 4, '2025-05-22', '2025-05-22', 'ongoing', '2025-05-29 15:56:40', '2025-05-29 16:11:33'),
(4, '123', NULL, '', NULL, NULL, NULL, 5, 4, '2025-05-22', '2025-05-22', 'ongoing', '2025-05-29 15:59:01', '2025-05-29 16:11:40'),
(5, '123', NULL, '', 'Online', '', '', 5, 4, '2025-05-22', '2025-05-22', 'ongoing', '2025-05-29 16:01:08', '2025-05-29 18:01:08'),
(6, '98', NULL, '', NULL, NULL, NULL, 1, 4, '2025-05-21', '2025-05-21', 'completed', '2025-05-29 16:12:25', '2025-05-29 16:12:25'),
(7, '09', NULL, '', 'Hybrid', '', '', 4, 4, '2025-05-16', '2025-05-16', 'completed', '2025-05-29 17:54:42', '2025-05-29 17:54:42'),
(8, '90', NULL, '', 'Offline', '', '', 3, 4, '2025-05-02', '2025-05-02', 'completed', '2025-05-29 17:58:14', '2025-05-29 17:58:14'),
(9, 'Image Training', NULL, 'Image Training with Upload', 'Online', '', '', 2, 4, '2025-05-03', '2025-05-03', 'completed', '2025-05-29 19:08:47', '2025-05-29 19:08:47'),
(10, 'Image Upload', NULL, 'asdasd', 'Online', '', '', 4, 4, '2025-05-10', '2025-05-17', 'completed', '2025-05-29 19:11:07', '2025-05-29 19:11:07'),
(11, 'image link', 'uploads/trainings/1748546009_Alea Konveksi.png', 'image link', 'Online', '', '', 4, 4, '2025-05-31', '2025-05-31', 'planned', '2025-05-29 19:13:29', '2025-05-29 19:13:29'),
(12, 'Training Number 12', 'uploads/trainings/1748569830_WhatsApp Image 2025-04-16 at 10.15.37.jpeg', '\"Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat', 'Online', '', '', 3, 4, '2025-05-31', '2025-05-31', 'planned', '2025-05-30 01:50:30', '2025-05-30 01:50:30'),
(13, 'Training Number 13 with Long Title that Fill the Card', 'uploads/trainings/1748570233_WhatsApp Image 2025-04-16 at 10.15.53.jpeg', 'lor', 'Online', '', '', 4, 4, '2025-05-03', '2025-05-03', 'ongoing', '2025-05-30 01:57:13', '2025-05-30 01:57:13'),
(15, 'Training Number 14 with Long Title and Description that will Fill Whole Card', 'uploads/trainings/1748570559_pexels-edmond-dantes-8068220.jpg', '\"Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat', 'Online', '', '', 6, 4, '2025-05-02', '2025-05-09', 'planned', '2025-05-30 02:02:39', '2025-05-30 02:02:39'),
(16, 'The Title Are Not Long Enough so I Create This Training Number 15 to Test the Title of The Training', 'uploads/trainings/1748570720_pexels-edmond-dantes-8068254.jpg', '\"Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum', 'Online', '', '', 5, 4, '2025-05-03', '2025-05-31', 'completed', '2025-05-30 02:05:20', '2025-05-30 02:05:20'),
(17, 'Training with Portrait Image', 'uploads/trainings/1748571895_Saya Pintar atau Bodoh.png', 'a', 'Online', '', '', 6, 4, '2025-05-03', '2025-05-10', 'planned', '2025-05-30 02:24:55', '2025-05-30 02:24:55'),
(18, 'Notification Test for The User', 'uploads/trainings/1748573147_WhatsApp Image 2025-04-16 at 10.17.40 (1).jpeg', 'Notification Training', 'Offline', '', '', 5, 4, '2025-05-03', '2025-05-31', 'planned', '2025-05-30 02:45:47', '2025-05-30 02:45:47'),
(19, 'Notification Test Number 2', 'uploads/trainings/1748573413_pexels-edmond-dantes-8070693.jpg', '', 'Online', '', '', 4, 4, '2025-05-31', '2025-05-31', 'planned', '2025-05-30 02:50:13', '2025-05-30 02:50:13'),
(20, 'Notification Test number 3', NULL, 'Send Assign to 3', 'Online', '', '', 4, 4, '2025-05-03', '2025-05-03', 'planned', '2025-05-30 02:54:06', '2025-05-30 02:54:06'),
(21, 'Training Notification No.4', NULL, 'Notification for Instructor & User', 'Online', '', '', 5, 4, '2025-05-03', '2025-05-03', 'planned', '2025-05-30 02:58:33', '2025-05-30 02:58:33'),
(22, 'Notification Test No.5', NULL, 'User & Instructor', 'Online', 'http://www.google.com/', '', 5, 4, '2025-06-01', '2025-06-01', 'planned', '2025-05-30 03:00:26', '2025-05-31 10:35:00'),
(23, 'Notif Test 5', NULL, '', 'Online', '', '', 4, 4, '2025-05-03', '2025-05-03', 'planned', '2025-05-30 03:03:52', '2025-05-30 03:03:52'),
(24, 'nitif test 7', NULL, '', 'Online', '', '', 4, 4, '2025-05-03', '2025-05-03', 'planned', '2025-05-30 03:08:19', '2025-05-30 03:08:19'),
(25, 'notif 8', NULL, '', 'Online', '', '', 6, 4, '2025-05-03', '2025-05-31', 'planned', '2025-05-30 03:23:43', '2025-05-30 03:23:43'),
(26, 'Notif 9', 'uploads/trainings/1748575768_pexels-edmond-dantes-8068878.jpg', 'test 2', 'Online', '', '', 4, 4, '2025-05-03', '2025-05-03', 'planned', '2025-05-30 03:29:28', '2025-05-30 03:29:28'),
(27, 'test 10', NULL, '', 'Online', '', '', 6, 4, '2025-05-31', '2025-05-31', 'planned', '2025-05-30 03:36:26', '2025-05-30 03:36:26'),
(28, 'test 11', NULL, '', 'Online', '', '', 2, 4, '2025-05-03', '2025-05-03', 'planned', '2025-05-30 03:38:17', '2025-05-30 03:38:17'),
(29, 'test 11', NULL, '', 'Online', '', '', 4, 4, '2025-05-03', '2025-05-03', 'planned', '2025-05-30 03:40:29', '2025-05-30 03:40:29'),
(30, 'test 12', NULL, '', 'Online', '', '', 4, 4, '2025-05-01', '2025-05-01', 'planned', '2025-05-30 03:46:17', '2025-05-30 03:46:17'),
(31, 'test 13', NULL, '', 'Online', '', '', 4, 4, '2025-05-03', '2025-05-31', 'completed', '2025-05-30 03:48:40', '2025-05-30 03:48:40'),
(32, 'test 13', NULL, '', 'Online', '', '', 4, 4, '2025-05-03', '2025-05-31', 'planned', '2025-05-30 03:53:12', '2025-05-30 03:53:12'),
(33, 'test 15', NULL, '', 'Online', '', '', 4, 4, '2025-05-01', '2025-05-31', 'planned', '2025-05-30 03:56:59', '2025-05-30 03:56:59'),
(34, '16', NULL, '', 'Online', '', '', 4, 4, '2025-05-03', '2025-05-03', 'planned', '2025-05-30 04:13:03', '2025-05-30 04:13:03'),
(35, 'Title dengan judul cukup panjang.', NULL, '', 'Online', '', '', 4, 7, '2025-05-31', '2025-05-31', 'completed', '2025-05-30 04:15:19', '2025-06-03 02:43:43'),
(36, 'Erika ngajar ngaji', NULL, '', 'Online', 'http://www.google.com/', '', 5, 6, '2025-06-02', '2025-06-02', 'planned', '2025-05-31 10:39:01', '2025-06-02 03:19:06');

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

--
-- Table structure for table `training_assignment`
--

CREATE TABLE `training_assignment` (
  `id` int(11) NOT NULL,
  `training_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `instructor_id` int(11) DEFAULT NULL,
  `assigned_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `training_assignment`
--

INSERT INTO `training_assignment` (`id`, `training_id`, `user_id`, `instructor_id`, `assigned_at`) VALUES
(1, 1, 6, NULL, '2025-05-29 17:51:11'),
(2, 1, 3, NULL, '2025-05-29 17:51:11'),
(3, 1, 2, NULL, '2025-05-29 17:51:11'),
(4, 1, 5, NULL, '2025-05-29 17:51:11'),
(5, 1, 4, NULL, '2025-05-29 17:51:11'),
(6, 2, 2, NULL, '2025-05-29 17:51:23'),
(7, 2, 6, NULL, '2025-05-29 17:51:23'),
(8, 3, 3, NULL, '2025-05-29 17:51:32'),
(9, 3, 6, NULL, '2025-05-29 17:51:32'),
(10, 4, 6, NULL, '2025-05-29 17:51:43'),
(11, 4, 5, NULL, '2025-05-29 17:51:43'),
(12, 4, 4, NULL, '2025-05-29 17:51:43'),
(17, 6, 3, NULL, '2025-05-29 17:52:00'),
(18, 6, 2, NULL, '2025-05-29 17:52:00'),
(19, 6, 6, NULL, '2025-05-29 17:52:00'),
(20, 6, 5, NULL, '2025-05-29 17:52:00'),
(21, 6, 4, NULL, '2025-05-29 17:52:00'),
(22, 7, 2, NULL, '2025-05-29 17:57:02'),
(23, 7, 6, NULL, '2025-05-29 17:57:02'),
(24, 8, 3, NULL, '2025-05-29 17:58:14'),
(25, 8, 6, NULL, '2025-05-29 17:58:14'),
(26, 5, 3, NULL, '2025-05-29 18:01:21'),
(27, 5, 6, NULL, '2025-05-29 18:01:21'),
(28, 9, 3, NULL, '2025-05-29 19:08:47'),
(29, 9, 7, NULL, '2025-05-29 19:08:47'),
(30, 10, 2, NULL, '2025-05-29 19:11:07'),
(31, 11, 2, NULL, '2025-05-29 19:13:29'),
(32, 12, 7, NULL, '2025-05-30 01:50:30'),
(33, 12, 2, NULL, '2025-05-30 01:50:30'),
(34, 13, 3, NULL, '2025-05-30 01:57:13'),
(36, 15, 2, NULL, '2025-05-30 02:02:39'),
(37, 16, 3, NULL, '2025-05-30 02:05:20'),
(38, 17, 3, NULL, '2025-05-30 02:24:55'),
(39, 18, 3, NULL, '2025-05-30 02:45:47'),
(40, 18, 2, NULL, '2025-05-30 02:45:47'),
(41, 18, 7, NULL, '2025-05-30 02:45:47'),
(42, 18, 6, NULL, '2025-05-30 02:45:47'),
(43, 18, 5, NULL, '2025-05-30 02:45:47'),
(44, 18, 4, NULL, '2025-05-30 02:45:47'),
(45, 19, 3, NULL, '2025-05-30 02:50:13'),
(46, 19, 2, NULL, '2025-05-30 02:50:13'),
(47, 19, 7, NULL, '2025-05-30 02:50:13'),
(48, 19, 6, NULL, '2025-05-30 02:50:13'),
(49, 19, 5, NULL, '2025-05-30 02:50:13'),
(50, 19, 4, NULL, '2025-05-30 02:50:13'),
(51, 20, 3, NULL, '2025-05-30 02:54:06'),
(52, 20, 5, NULL, '2025-05-30 02:54:06'),
(53, 20, 2, NULL, '2025-05-30 02:54:06'),
(60, 23, 3, NULL, '2025-05-30 03:03:52'),
(61, 23, 2, NULL, '2025-05-30 03:03:52'),
(62, 23, 7, NULL, '2025-05-30 03:03:52'),
(63, 24, 3, NULL, '2025-05-30 03:08:19'),
(64, 24, 2, NULL, '2025-05-30 03:08:19'),
(65, 24, 7, NULL, '2025-05-30 03:08:19'),
(66, 25, 3, NULL, '2025-05-30 03:23:43'),
(67, 25, 7, NULL, '2025-05-30 03:23:43'),
(68, 25, 2, NULL, '2025-05-30 03:23:43'),
(69, 26, 3, NULL, '2025-05-30 03:29:28'),
(70, 26, 2, NULL, '2025-05-30 03:29:28'),
(71, 27, 3, NULL, '2025-05-30 03:36:26'),
(72, 27, 7, NULL, '2025-05-30 03:36:26'),
(73, 28, 3, NULL, '2025-05-30 03:38:17'),
(74, 28, 7, NULL, '2025-05-30 03:38:17'),
(75, 28, 6, NULL, '2025-05-30 03:38:17'),
(76, 29, 3, NULL, '2025-05-30 03:40:29'),
(77, 29, 2, NULL, '2025-05-30 03:40:29'),
(78, 29, 6, NULL, '2025-05-30 03:40:29'),
(79, 30, 3, NULL, '2025-05-30 03:46:17'),
(80, 30, 2, NULL, '2025-05-30 03:46:17'),
(81, 30, 7, NULL, '2025-05-30 03:46:17'),
(82, 31, 3, NULL, '2025-05-30 03:48:40'),
(83, 31, 2, NULL, '2025-05-30 03:48:40'),
(84, 31, 7, NULL, '2025-05-30 03:48:40'),
(85, 32, 3, NULL, '2025-05-30 03:53:12'),
(86, 32, 7, NULL, '2025-05-30 03:53:12'),
(87, 32, 6, NULL, '2025-05-30 03:53:12'),
(88, 32, 5, NULL, '2025-05-30 03:53:12'),
(89, 33, 3, NULL, '2025-05-30 03:56:59'),
(90, 33, 2, NULL, '2025-05-30 03:56:59'),
(91, 33, 7, NULL, '2025-05-30 03:56:59'),
(92, 34, 3, NULL, '2025-05-30 04:13:03'),
(93, 34, 2, NULL, '2025-05-30 04:13:03'),
(115, 36, 8, NULL, '2025-06-02 03:19:06');

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

--
-- Table structure for table `training_category_effectiveness`
--

CREATE TABLE `training_category_effectiveness` (
  `id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `year` int(11) NOT NULL,
  `avg_score` decimal(5,2) DEFAULT NULL,
  `completion_rate` decimal(5,2) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `training_feedback`
--

CREATE TABLE `training_feedback` (
  `id` int(11) NOT NULL,
  `participant_id` int(11) NOT NULL,
  `training_id` int(11) NOT NULL,
  `instructor_id` int(11) NOT NULL,
  `rating` int(11) DEFAULT NULL CHECK (`rating` >= 1 and `rating` <= 5),
  `comment` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL,
  `employee_id` varchar(50) NOT NULL,
  `username` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `google_login` varchar(255) DEFAULT NULL,
  `hashed_password` varchar(255) NOT NULL,
  `role` enum('user','admin','superadmin') NOT NULL DEFAULT 'user',
  `is_active` tinyint(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`user_id`, `employee_id`, `username`, `email`, `google_login`, `hashed_password`, `role`, `is_active`) VALUES
(2, '4', 'admin', 'admin@example.com', NULL, '$2y$10$15ofDxjng2o0R5.av2CxpeJjFNhWOGPxKApji7zsLKh40ruZL0rTO', 'superadmin', 1),
(3, '5', 'adi', 'iamsetiadi88@gmail.com', NULL, '$2y$10$hdUuUq8iKx7WG/rnReCHf.xTfN5SCZEOXocs05J/5I5eMaktJoQ4.', 'superadmin', 1),
(4, '6', 'riki', 'rikisuper@gmail.com', NULL, '$2y$10$fARHGF.QkeJ6RcBEasPZt.kfmDzgR5k5VDXdpqQR.cmLn8vEoR8DC', 'superadmin', 1),
(5, '7', 'erika', 'erika@gmail.com', NULL, '$2y$10$RgC1sMsbs.LrMUjgtlUd5.ivp/SMlSqji4FJB1rUIapPKsxrLJHH2', 'superadmin', 1),
(6, '8', 'cantika', 'cantika@gmail.com', NULL, '$2y$10$vMOcDehqKN6LP2CX/C3ZXOMv4ct0pZYVVxzemE7jbj0l0IoH1sSeS', 'admin', 1),
(7, '9', 'blackbox', 'blackbox@blackbox.com', NULL, '$2y$10$BtkffrBWpxIsuYFoYCs2JO2GcnO7qsxFfyiW9cpcPuWJfH6bq.ioy', 'user', 1),
(8, '10293091', 'ratna', 'ratna@gmail.com', NULL, '$2y$10$vM6FrzD5KcDN7cbqzeF40uygU.oNzwphqmre5sYE1j5v.aIdUV3w.', 'user', 1),
(9, '684b9f638605d', '123', '123@gmail.com', NULL, '$2y$10$8HFJYmDCUbxC5q1yuWK2QO.OJmb5L/ggqH3Vuvk5FIt4Y0EFBQ9W2', 'user', 1);

--
-- Triggers `users`
--
DELIMITER $$
CREATE TRIGGER `after_user_insert` AFTER INSERT ON `users` FOR EACH ROW BEGIN
  INSERT INTO user_profiles (user_id, first_name, last_name)
  VALUES (NEW.user_id, '', '');
END
$$
DELIMITER ;

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

--
-- Table structure for table `user_profiles`
--

CREATE TABLE `user_profiles` (
  `user_id` int(11) NOT NULL,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `bio` varchar(255) DEFAULT NULL,
  `country` varchar(50) DEFAULT NULL,
  `city_state` varchar(100) DEFAULT NULL,
  `postal_code` varchar(20) DEFAULT NULL,
  `tax_id` varchar(50) DEFAULT NULL,
  `profile_image` varchar(255) DEFAULT NULL,
  `facebook` varchar(255) DEFAULT NULL,
  `twitter` varchar(255) DEFAULT NULL,
  `linkedin` varchar(255) DEFAULT NULL,
  `instagram` varchar(255) DEFAULT NULL,
  `business_unit_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `user_profiles`
--

INSERT INTO `user_profiles` (`user_id`, `first_name`, `last_name`, `phone`, `address`, `bio`, `country`, `city_state`, `postal_code`, `tax_id`, `profile_image`, `facebook`, `twitter`, `linkedin`, `instagram`, `business_unit_id`) VALUES
(4, 'Riki', 'Ardiansyah', '08123456789', 'Tangerang Selatan', 'Best Digital Marketer', 'Indonesia', 'Bogor', '16920', '', 'uploads/profile_images/profile_68399126f1392.png', '', '', '', '', 1),
(5, 'Erika', 'Pratiwi', '08123456789', 'Grande Village Residence 1 Blok I No.17', 'Be The Best Mother in The World', 'Indonesia', 'Bogor', '16920', '', 'uploads/profile_images/profile_6838d1140d9c1.jpg', '', '', '', '', 1),
(6, 'Cantika', 'Nur hafidzah', '082282408825', 'Grande Village Residence 1 Blok I No.17', 'Best Daughter in The World', 'Indonesia', 'Bogor', '16920', '', 'uploads/profile_images/profile_6839c9965dea1.png', '', '', '', '', 6),
(7, '', '', '', '', '', '', '', '', '', 'uploads/profile_images/profile_683aa10671feb.jpg', '', '', '', '', 1),
(8, '', '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
(9, '', '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

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

--
-- Table structure for table `user_training_performance`
--

CREATE TABLE `user_training_performance` (
  `id` int(11) NOT NULL,
  `participant_id` int(11) NOT NULL,
  `assignment_id` int(11) DEFAULT NULL,
  `attendance` tinyint(1) DEFAULT 0,
  `score` decimal(5,2) DEFAULT NULL,
  `completion` tinyint(1) DEFAULT 0,
  `feedback` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Stand-in structure for view `view_financial_ratios`
-- (See below for the actual view)
--
CREATE TABLE `view_financial_ratios` (
`id` int(11)
,`periode` date
,`total_aset` decimal(18,2)
,`total_investasi` decimal(18,2)
,`total_kas` decimal(18,2)
,`total_kewajiban_jangka_pendek` decimal(18,2)
,`rasio_investasi_aset` decimal(24,2)
,`rasio_kas_aset` decimal(24,2)
,`rasio_likuiditas` decimal(21,2)
);

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

--
-- Stand-in structure for view `v_dashboard_business_unit_participation`
-- (See below for the actual view)
--
CREATE TABLE `v_dashboard_business_unit_participation` (
`business_unit` varchar(150)
,`participant_count` bigint(21)
);

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

--
-- Stand-in structure for view `v_dashboard_completion_rate`
-- (See below for the actual view)
--
CREATE TABLE `v_dashboard_completion_rate` (
`title` varchar(255)
,`completion_rate` decimal(28,5)
);

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

--
-- Stand-in structure for view `v_dashboard_instructors`
-- (See below for the actual view)
--
CREATE TABLE `v_dashboard_instructors` (
`id` int(11)
,`username` varchar(50)
,`total_trainings` bigint(21)
);

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

--
-- Stand-in structure for view `v_dashboard_main`
-- (See below for the actual view)
--
CREATE TABLE `v_dashboard_main` (
`total_trainings` bigint(21)
,`ongoing_trainings` bigint(21)
,`completed_trainings` bigint(21)
,`planned_trainings` bigint(21)
,`total_participants` bigint(21)
,`total_active_users` bigint(21)
,`total_instructors` bigint(21)
);

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

--
-- Stand-in structure for view `v_dashboard_notifications`
-- (See below for the actual view)
--
CREATE TABLE `v_dashboard_notifications` (
`id` int(11)
,`user_id` int(11)
,`message` text
,`is_read` tinyint(1)
,`created_at` datetime
,`training_id` int(11)
);

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

--
-- Stand-in structure for view `v_dashboard_notification_stats`
-- (See below for the actual view)
--
CREATE TABLE `v_dashboard_notification_stats` (
`unread_count` bigint(21)
,`read_count` bigint(21)
);

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

--
-- Stand-in structure for view `v_dashboard_test_scores`
-- (See below for the actual view)
--
CREATE TABLE `v_dashboard_test_scores` (
`title` varchar(255)
,`avg_pre_test` decimal(7,4)
,`avg_post_test` decimal(7,4)
);

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

--
-- Stand-in structure for view `v_dashboard_top_users`
-- (See below for the actual view)
--
CREATE TABLE `v_dashboard_top_users` (
`username` varchar(50)
,`completed_trainings` bigint(21)
,`avg_pre_test` decimal(7,4)
,`avg_post_test` decimal(7,4)
);

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

--
-- Stand-in structure for view `v_dashboard_training_categories`
-- (See below for the actual view)
--
CREATE TABLE `v_dashboard_training_categories` (
`name` varchar(100)
,`count` bigint(21)
);

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

--
-- Stand-in structure for view `v_dashboard_training_status`
-- (See below for the actual view)
--
CREATE TABLE `v_dashboard_training_status` (
`status` enum('planned','ongoing','completed')
,`count` bigint(21)
);

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

--
-- Stand-in structure for view `v_dashboard_upcoming_trainings`
-- (See below for the actual view)
--
CREATE TABLE `v_dashboard_upcoming_trainings` (
`id` int(11)
,`title` varchar(255)
,`image_url` varchar(255)
,`description` text
,`event_place` enum('Online','Offline','Hybrid')
,`platform_link` varchar(255)
,`duration` varchar(50)
,`category_id` int(11)
,`instructor_id` int(11)
,`start_date` date
,`end_date` date
,`status` enum('planned','ongoing','completed')
,`created_at` timestamp
,`updated_at` timestamp
);

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

--
-- Stand-in structure for view `v_dashboard_user_status`
-- (See below for the actual view)
--
CREATE TABLE `v_dashboard_user_status` (
`active_users` decimal(22,0)
,`inactive_users` decimal(22,0)
);

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

--
-- Structure for view `view_financial_ratios`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_financial_ratios`  AS SELECT `financial_snapshot`.`id` AS `id`, `financial_snapshot`.`periode` AS `periode`, `financial_snapshot`.`total_aset` AS `total_aset`, `financial_snapshot`.`total_investasi` AS `total_investasi`, `financial_snapshot`.`total_kas` AS `total_kas`, `financial_snapshot`.`total_kewajiban_jangka_pendek` AS `total_kewajiban_jangka_pendek`, round(`financial_snapshot`.`total_investasi` / `financial_snapshot`.`total_aset` * 100,2) AS `rasio_investasi_aset`, round(`financial_snapshot`.`total_kas` / `financial_snapshot`.`total_aset` * 100,2) AS `rasio_kas_aset`, round(`financial_snapshot`.`total_kas` / nullif(`financial_snapshot`.`total_kewajiban_jangka_pendek`,0),2) AS `rasio_likuiditas` FROM `financial_snapshot` ;

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

--
-- Structure for view `v_dashboard_business_unit_participation`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_dashboard_business_unit_participation`  AS SELECT `bu`.`name` AS `business_unit`, count(distinct `p`.`user_id`) AS `participant_count` FROM ((`business_units` `bu` left join `user_profiles` `up` on(`bu`.`id` = `up`.`business_unit_id`)) left join `participants` `p` on(`up`.`user_id` = `p`.`user_id`)) GROUP BY `bu`.`id`, `bu`.`name` ;

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

--
-- Structure for view `v_dashboard_completion_rate`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_dashboard_completion_rate`  AS SELECT `t`.`title` AS `title`, count(case when `p`.`status` = 'completed' then 1 end) * 100.0 / count(0) AS `completion_rate` FROM (`trainings` `t` left join `participants` `p` on(`t`.`id` = `p`.`training_id`)) GROUP BY `t`.`id`, `t`.`title` ;

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

--
-- Structure for view `v_dashboard_instructors`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_dashboard_instructors`  AS SELECT `i`.`id` AS `id`, `u`.`username` AS `username`, count(`t`.`id`) AS `total_trainings` FROM ((`instructors` `i` join `users` `u` on(`i`.`user_id` = `u`.`user_id`)) left join `trainings` `t` on(`i`.`id` = `t`.`instructor_id`)) GROUP BY `i`.`id`, `u`.`username` ;

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

--
-- Structure for view `v_dashboard_main`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_dashboard_main`  AS SELECT (select count(0) from `trainings`) AS `total_trainings`, (select count(0) from `trainings` where `trainings`.`status` = 'ongoing') AS `ongoing_trainings`, (select count(0) from `trainings` where `trainings`.`status` = 'completed') AS `completed_trainings`, (select count(0) from `trainings` where `trainings`.`status` = 'planned') AS `planned_trainings`, (select count(distinct `participants`.`user_id`) from `participants`) AS `total_participants`, (select count(0) from `users` where `users`.`is_active` = 1) AS `total_active_users`, (select count(0) from `instructors`) AS `total_instructors` ;

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

--
-- Structure for view `v_dashboard_notifications`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_dashboard_notifications`  AS SELECT `notifications`.`id` AS `id`, `notifications`.`user_id` AS `user_id`, `notifications`.`message` AS `message`, `notifications`.`is_read` AS `is_read`, `notifications`.`created_at` AS `created_at`, `notifications`.`training_id` AS `training_id` FROM `notifications` ORDER BY `notifications`.`created_at` DESC LIMIT 0, 10 ;

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

--
-- Structure for view `v_dashboard_notification_stats`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_dashboard_notification_stats`  AS SELECT count(case when `notifications`.`is_read` = 0 then 1 end) AS `unread_count`, count(case when `notifications`.`is_read` = 1 then 1 end) AS `read_count` FROM `notifications` ;

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

--
-- Structure for view `v_dashboard_test_scores`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_dashboard_test_scores`  AS SELECT `t`.`title` AS `title`, avg(`pp`.`pre_test_result`) AS `avg_pre_test`, avg(`pp`.`post_test_result`) AS `avg_post_test` FROM (`trainings` `t` left join `pretest_posttest` `pp` on(`t`.`id` = `pp`.`training_id`)) GROUP BY `t`.`id`, `t`.`title` ;

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

--
-- Structure for view `v_dashboard_top_users`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_dashboard_top_users`  AS SELECT `u`.`username` AS `username`, count(case when `p`.`status` = 'completed' then 1 end) AS `completed_trainings`, avg(`pp`.`pre_test_result`) AS `avg_pre_test`, avg(`pp`.`post_test_result`) AS `avg_post_test` FROM ((`users` `u` left join `participants` `p` on(`u`.`user_id` = `p`.`user_id`)) left join `pretest_posttest` `pp` on(`u`.`user_id` = `pp`.`user_id`)) GROUP BY `u`.`user_id`, `u`.`username` ORDER BY count(case when `p`.`status` = 'completed' then 1 end) DESC, avg(`pp`.`post_test_result`) DESC LIMIT 0, 5 ;

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

--
-- Structure for view `v_dashboard_training_categories`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_dashboard_training_categories`  AS SELECT `c`.`name` AS `name`, count(`t`.`id`) AS `count` FROM (`categories` `c` left join `trainings` `t` on(`c`.`id` = `t`.`category_id`)) GROUP BY `c`.`id`, `c`.`name` ;

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

--
-- Structure for view `v_dashboard_training_status`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_dashboard_training_status`  AS SELECT `trainings`.`status` AS `status`, count(0) AS `count` FROM `trainings` GROUP BY `trainings`.`status` ;

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

--
-- Structure for view `v_dashboard_upcoming_trainings`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_dashboard_upcoming_trainings`  AS SELECT `trainings`.`id` AS `id`, `trainings`.`title` AS `title`, `trainings`.`image_url` AS `image_url`, `trainings`.`description` AS `description`, `trainings`.`event_place` AS `event_place`, `trainings`.`platform_link` AS `platform_link`, `trainings`.`duration` AS `duration`, `trainings`.`category_id` AS `category_id`, `trainings`.`instructor_id` AS `instructor_id`, `trainings`.`start_date` AS `start_date`, `trainings`.`end_date` AS `end_date`, `trainings`.`status` AS `status`, `trainings`.`created_at` AS `created_at`, `trainings`.`updated_at` AS `updated_at` FROM `trainings` WHERE `trainings`.`start_date` between curdate() and curdate() + interval 7 day ;

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

--
-- Structure for view `v_dashboard_user_status`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_dashboard_user_status`  AS SELECT sum(case when `users`.`is_active` = 1 then 1 else 0 end) AS `active_users`, sum(case when `users`.`is_active` = 0 then 1 else 0 end) AS `inactive_users` FROM `users` ;

--
-- Indexes for dumped tables
--

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

--
-- Indexes for table `announcements`
--
ALTER TABLE `announcements`
  ADD PRIMARY KEY (`id`),
  ADD KEY `created_by` (`created_by`);

--
-- Indexes for table `assignments`
--
ALTER TABLE `assignments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `training_id` (`training_id`);

--
-- Indexes for table `business_profile`
--
ALTER TABLE `business_profile`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_bp_business_id` (`business_id`);

--
-- Indexes for table `business_units`
--
ALTER TABLE `business_units`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `cash_ledger`
--
ALTER TABLE `cash_ledger`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `categories`
--
ALTER TABLE `categories`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `departments`
--
ALTER TABLE `departments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `business_unit_id` (`business_unit_id`);

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

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

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

--
-- Indexes for table `financial_snapshot`
--
ALTER TABLE `financial_snapshot`
  ADD PRIMARY KEY (`id`);

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

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

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

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

--
-- Indexes for table `participants`
--
ALTER TABLE `participants`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_participant` (`user_id`,`training_id`),
  ADD KEY `training_id` (`training_id`);

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

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

--
-- Indexes for table `projects`
--
ALTER TABLE `projects`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `project_id_number` (`project_id_number`),
  ADD KEY `fk_projects_business_id` (`business_id`),
  ADD KEY `fk_project_category` (`project_category_id`);

--
-- Indexes for table `project_category`
--
ALTER TABLE `project_category`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `name` (`name`);

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

--
-- Indexes for table `resources`
--
ALTER TABLE `resources`
  ADD PRIMARY KEY (`id`),
  ADD KEY `uploaded_by` (`uploaded_by`);

--
-- Indexes for table `test_answers`
--
ALTER TABLE `test_answers`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `training_id` (`training_id`),
  ADD KEY `question_id` (`question_id`),
  ADD KEY `selected_option_id` (`selected_option_id`);

--
-- Indexes for table `test_options`
--
ALTER TABLE `test_options`
  ADD PRIMARY KEY (`id`),
  ADD KEY `question_id` (`question_id`);

--
-- Indexes for table `test_questions`
--
ALTER TABLE `test_questions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `training_id` (`training_id`);

--
-- Indexes for table `trainer_performance`
--
ALTER TABLE `trainer_performance`
  ADD PRIMARY KEY (`id`),
  ADD KEY `instructor_id` (`instructor_id`),
  ADD KEY `training_id` (`training_id`);

--
-- Indexes for table `trainings`
--
ALTER TABLE `trainings`
  ADD PRIMARY KEY (`id`),
  ADD KEY `category_id` (`category_id`),
  ADD KEY `instructor_id` (`instructor_id`);

--
-- Indexes for table `training_assignment`
--
ALTER TABLE `training_assignment`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_training_user` (`training_id`,`user_id`),
  ADD KEY `idx_training_id` (`training_id`),
  ADD KEY `idx_user_id` (`user_id`),
  ADD KEY `idx_instructor_id` (`instructor_id`);

--
-- Indexes for table `training_category_effectiveness`
--
ALTER TABLE `training_category_effectiveness`
  ADD PRIMARY KEY (`id`),
  ADD KEY `category_id` (`category_id`);

--
-- Indexes for table `training_feedback`
--
ALTER TABLE `training_feedback`
  ADD PRIMARY KEY (`id`),
  ADD KEY `participant_id` (`participant_id`),
  ADD KEY `training_id` (`training_id`),
  ADD KEY `instructor_id` (`instructor_id`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`),
  ADD UNIQUE KEY `username` (`username`),
  ADD UNIQUE KEY `email` (`email`),
  ADD UNIQUE KEY `employee_id` (`employee_id`);

--
-- Indexes for table `user_profiles`
--
ALTER TABLE `user_profiles`
  ADD PRIMARY KEY (`user_id`),
  ADD KEY `idx_user_profiles_business_unit_id` (`business_unit_id`);

--
-- Indexes for table `user_training_performance`
--
ALTER TABLE `user_training_performance`
  ADD PRIMARY KEY (`id`),
  ADD KEY `participant_id` (`participant_id`),
  ADD KEY `assignment_id` (`assignment_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `activity_logs`
--
ALTER TABLE `activity_logs`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `announcements`
--
ALTER TABLE `announcements`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `assignments`
--
ALTER TABLE `assignments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `business_profile`
--
ALTER TABLE `business_profile`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=23;

--
-- AUTO_INCREMENT for table `business_units`
--
ALTER TABLE `business_units`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT for table `cash_ledger`
--
ALTER TABLE `cash_ledger`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `categories`
--
ALTER TABLE `categories`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `departments`
--
ALTER TABLE `departments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT for table `final_assessment`
--
ALTER TABLE `final_assessment`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `financial_evaluation`
--
ALTER TABLE `financial_evaluation`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `financial_review`
--
ALTER TABLE `financial_review`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `financial_snapshot`
--
ALTER TABLE `financial_snapshot`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `first_screening`
--
ALTER TABLE `first_screening`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

--
-- AUTO_INCREMENT for table `instructors`
--
ALTER TABLE `instructors`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

--
-- AUTO_INCREMENT for table `notifications`
--
ALTER TABLE `notifications`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=62;

--
-- AUTO_INCREMENT for table `operational_market_analysis`
--
ALTER TABLE `operational_market_analysis`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `participants`
--
ALTER TABLE `participants`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=23;

--
-- AUTO_INCREMENT for table `password_resets`
--
ALTER TABLE `password_resets`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `pretest_posttest`
--
ALTER TABLE `pretest_posttest`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

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

--
-- AUTO_INCREMENT for table `project_category`
--
ALTER TABLE `project_category`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `project_returns`
--
ALTER TABLE `project_returns`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=39;

--
-- AUTO_INCREMENT for table `resources`
--
ALTER TABLE `resources`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `test_answers`
--
ALTER TABLE `test_answers`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT for table `test_options`
--
ALTER TABLE `test_options`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=93;

--
-- AUTO_INCREMENT for table `test_questions`
--
ALTER TABLE `test_questions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;

--
-- AUTO_INCREMENT for table `trainer_performance`
--
ALTER TABLE `trainer_performance`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `trainings`
--
ALTER TABLE `trainings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=37;

--
-- AUTO_INCREMENT for table `training_assignment`
--
ALTER TABLE `training_assignment`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=116;

--
-- AUTO_INCREMENT for table `training_category_effectiveness`
--
ALTER TABLE `training_category_effectiveness`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `training_feedback`
--
ALTER TABLE `training_feedback`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

--
-- AUTO_INCREMENT for table `user_training_performance`
--
ALTER TABLE `user_training_performance`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `activity_logs`
--
ALTER TABLE `activity_logs`
  ADD CONSTRAINT `activity_logs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`);

--
-- Constraints for table `announcements`
--
ALTER TABLE `announcements`
  ADD CONSTRAINT `announcements_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`);

--
-- Constraints for table `assignments`
--
ALTER TABLE `assignments`
  ADD CONSTRAINT `assignments_ibfk_1` FOREIGN KEY (`training_id`) REFERENCES `trainings` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `departments`
--
ALTER TABLE `departments`
  ADD CONSTRAINT `departments_ibfk_1` FOREIGN KEY (`business_unit_id`) REFERENCES `business_units` (`id`);

--
-- Constraints for table `final_assessment`
--
ALTER TABLE `final_assessment`
  ADD CONSTRAINT `final_assessment_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`),
  ADD CONSTRAINT `fk_fa_approver` FOREIGN KEY (`approved_by`) REFERENCES `users` (`user_id`);

--
-- Constraints for table `financial_evaluation`
--
ALTER TABLE `financial_evaluation`
  ADD CONSTRAINT `financial_evaluation_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`),
  ADD CONSTRAINT `fk_fe_approver` FOREIGN KEY (`approved_by`) REFERENCES `users` (`user_id`);

--
-- Constraints for table `financial_review`
--
ALTER TABLE `financial_review`
  ADD CONSTRAINT `financial_review_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`);

--
-- Constraints for table `first_screening`
--
ALTER TABLE `first_screening`
  ADD CONSTRAINT `first_screening_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`),
  ADD CONSTRAINT `fk_fs_approver` FOREIGN KEY (`approved_by`) REFERENCES `users` (`user_id`);

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

--
-- Constraints for table `notifications`
--
ALTER TABLE `notifications`
  ADD CONSTRAINT `fk_notifications_training` FOREIGN KEY (`training_id`) REFERENCES `trainings` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `notifications_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`);

--
-- Constraints for table `operational_market_analysis`
--
ALTER TABLE `operational_market_analysis`
  ADD CONSTRAINT `fk_oma_approver` FOREIGN KEY (`approved_by`) REFERENCES `users` (`user_id`),
  ADD CONSTRAINT `operational_market_analysis_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`);

--
-- Constraints for table `participants`
--
ALTER TABLE `participants`
  ADD CONSTRAINT `participants_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `participants_ibfk_2` FOREIGN KEY (`training_id`) REFERENCES `trainings` (`id`) ON DELETE CASCADE;

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

--
-- Constraints for table `pretest_posttest`
--
ALTER TABLE `pretest_posttest`
  ADD CONSTRAINT `pretest_posttest_ibfk_1` FOREIGN KEY (`training_id`) REFERENCES `trainings` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `pretest_posttest_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE;

--
-- Constraints for table `projects`
--
ALTER TABLE `projects`
  ADD CONSTRAINT `fk_project_category` FOREIGN KEY (`project_category_id`) REFERENCES `project_category` (`id`);

--
-- Constraints for table `project_returns`
--
ALTER TABLE `project_returns`
  ADD CONSTRAINT `project_returns_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`);

--
-- Constraints for table `resources`
--
ALTER TABLE `resources`
  ADD CONSTRAINT `resources_ibfk_1` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`user_id`);

--
-- Constraints for table `test_answers`
--
ALTER TABLE `test_answers`
  ADD CONSTRAINT `test_answers_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `test_answers_ibfk_2` FOREIGN KEY (`training_id`) REFERENCES `trainings` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `test_answers_ibfk_3` FOREIGN KEY (`question_id`) REFERENCES `test_questions` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `test_answers_ibfk_4` FOREIGN KEY (`selected_option_id`) REFERENCES `test_options` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `test_options`
--
ALTER TABLE `test_options`
  ADD CONSTRAINT `test_options_ibfk_1` FOREIGN KEY (`question_id`) REFERENCES `test_questions` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `test_questions`
--
ALTER TABLE `test_questions`
  ADD CONSTRAINT `test_questions_ibfk_1` FOREIGN KEY (`training_id`) REFERENCES `trainings` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `trainer_performance`
--
ALTER TABLE `trainer_performance`
  ADD CONSTRAINT `trainer_performance_ibfk_1` FOREIGN KEY (`instructor_id`) REFERENCES `instructors` (`id`),
  ADD CONSTRAINT `trainer_performance_ibfk_2` FOREIGN KEY (`training_id`) REFERENCES `trainings` (`id`);

--
-- Constraints for table `trainings`
--
ALTER TABLE `trainings`
  ADD CONSTRAINT `trainings_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `trainings_ibfk_2` FOREIGN KEY (`instructor_id`) REFERENCES `instructors` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `training_assignment`
--
ALTER TABLE `training_assignment`
  ADD CONSTRAINT `fk_training_assignment_instructor` FOREIGN KEY (`instructor_id`) REFERENCES `instructors` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_training_assignment_training` FOREIGN KEY (`training_id`) REFERENCES `trainings` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_training_assignment_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE;

--
-- Constraints for table `training_category_effectiveness`
--
ALTER TABLE `training_category_effectiveness`
  ADD CONSTRAINT `training_category_effectiveness_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`);

--
-- Constraints for table `training_feedback`
--
ALTER TABLE `training_feedback`
  ADD CONSTRAINT `training_feedback_ibfk_1` FOREIGN KEY (`participant_id`) REFERENCES `participants` (`id`),
  ADD CONSTRAINT `training_feedback_ibfk_2` FOREIGN KEY (`training_id`) REFERENCES `trainings` (`id`),
  ADD CONSTRAINT `training_feedback_ibfk_3` FOREIGN KEY (`instructor_id`) REFERENCES `instructors` (`id`);

--
-- Constraints for table `user_profiles`
--
ALTER TABLE `user_profiles`
  ADD CONSTRAINT `fk_user_profiles_business_unit` FOREIGN KEY (`business_unit_id`) REFERENCES `business_units` (`id`),
  ADD CONSTRAINT `fk_user_profiles_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE;

--
-- Constraints for table `user_training_performance`
--
ALTER TABLE `user_training_performance`
  ADD CONSTRAINT `user_training_performance_ibfk_1` FOREIGN KEY (`participant_id`) REFERENCES `participants` (`id`),
  ADD CONSTRAINT `user_training_performance_ibfk_2` FOREIGN KEY (`assignment_id`) REFERENCES `assignments` (`id`);
COMMIT;

