-- https://www.phpmyadmin.net/
--
-- 主機： localhost
-- 產生時間： 2025 年 03 月 31 日 16:19
-- 伺服器版本： 10.1.43-MariaDB-0ubuntu0.18.04.1
-- PHP 版本： 7.4.28

START TRANSACTION;

--
-- 資料庫: `ct-shuifung`
--

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

--
-- 資料表結構 `activity_log`
--

CREATE TABLE `activity_log` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `activity_type` varchar(50) NOT NULL,
  `details` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- 傾印資料表的資料 `activity_log`
--

INSERT INTO `activity_log` (`id`, `user_id`, `activity_type`, `details`, `created_at`) VALUES
(1, 3, 'user_login', '{\"username\":\"erictam\"}', '2025-01-10 02:31:30'),
(2, 4, 'user_login', '{\"username\":\"anguslai\"}', '2025-01-10 02:40:59'),
(3, 5, 'user_login', '{\"username\":\"jefferylam\"}', '2025-01-10 06:09:59'),
(4, 5, 'user_login', '{\"username\":\"jefferylam\"}', '2025-01-10 06:15:19'),
(5, 5, 'job_created', '{\"job_id\":\"TA-JOB-003\",\"project_id\":1}', '2025-01-10 06:15:20'),
(6, 5, 'job_updated', '{\"job_id\":\"TA-JOB-003\",\"updated_by\":5}', '2025-01-10 06:15:20'),
(7, 3, 'user_login', '{\"username\":\"erictam\"}', '2025-01-10 06:15:21'),
(8, 3, 'job_status_updated', '{\"job_id\":\"TA-JOB-003\",\"action\":\"approve\"}', '2025-01-10 06:15:21'),
(9, 5, 'user_login', '{\"username\":\"jefferylam\"}', '2025-01-10 06:15:41'),
(10, 5, 'job_created', '{\"job_id\":\"TA-JOB-004\",\"project_id\":1}', '2025-01-10 06:15:41'),
(11, 5, 'job_updated', '{\"job_id\":\"TA-JOB-004\",\"updated_by\":5}', '2025-01-10 06:15:41'),
(12, 3, 'user_login', '{\"username\":\"erictam\"}', '2025-01-10 06:15:41'),
(13, 3, 'job_status_updated', '{\"job_id\":\"TA-JOB-004\",\"action\":\"approve\"}', '2025-01-10 06:15:41'),
(14, 5, 'user_login', '{\"username\":\"jefferylam\"}', '2025-01-10 06:29:04'),
(15, 5, 'job_created', '{\"job_id\":\"TA-JOB-005\",\"project_id\":1,\"created_by\":5}', '2025-01-10 06:29:05'),
(16, 5, 'job_updated', '{\"job_id\":\"TA-JOB-005\",\"updated_by\":5}', '2025-01-10 06:29:06'),
(17, 3, 'user_login', '{\"username\":\"erictam\"}', '2025-01-10 06:29:06'),
(18, 3, 'job_status_updated', '{\"job_id\":\"TA-JOB-005\",\"action\":\"approve\"}', '2025-01-10 06:29:07'),
(19, 5, 'user_login', '{\"username\":\"jefferylam\"}', '2025-01-10 06:29:23'),
(20, 5, 'job_created', '{\"job_id\":\"TA-JOB-006\",\"project_id\":1,\"created_by\":5}', '2025-01-10 06:29:23'),
(21, 5, 'job_updated', '{\"job_id\":\"TA-JOB-006\",\"updated_by\":5}', '2025-01-10 06:29:23'),
(22, 3, 'user_login', '{\"username\":\"erictam\"}', '2025-01-10 06:29:23'),
(23, 3, 'job_status_updated', '{\"job_id\":\"TA-JOB-006\",\"action\":\"approve\"}', '2025-01-10 06:29:23'),
(24, 5, 'user_login', '{\"username\":\"jefferylam\"}', '2025-01-10 06:38:14'),
(25, 5, 'job_created', '{\"job_id\":\"TA-JOB-007\",\"project_id\":1,\"created_by\":5}', '2025-01-10 06:38:14'),
(26, 5, 'job_updated', '{\"job_id\":\"TA-JOB-007\",\"updated_by\":5}', '2025-01-10 06:38:14'),
(27, 3, 'user_login', '{\"username\":\"erictam\"}', '2025-01-10 06:38:15'),
(28, 3, 'job_status_updated', '{\"job_id\":\"TA-JOB-007\",\"action\":\"approve\"}', '2025-01-10 06:38:15'),
(29, 3, 'job_created', '{\"job_id\":\"TA-JOB-008\",\"project_id\":1,\"created_by\":3}', '2025-01-10 06:38:15'),
(30, 3, 'job_status_updated', '{\"job_id\":\"TA-JOB-008\",\"action\":\"reject\"}', '2025-01-10 06:38:15'),
(31, 3, 'job_updated', '{\"job_id\":\"TA-JOB-008\",\"updated_by\":3}', '2025-01-10 06:38:15'),
(32, 5, 'user_login', '{\"username\":\"jefferylam\"}', '2025-01-10 06:39:05'),
(33, 5, 'job_created', '{\"job_id\":\"TA-JOB-009\",\"project_id\":1,\"created_by\":5}', '2025-01-10 06:39:06'),
(34, 5, 'job_updated', '{\"job_id\":\"TA-JOB-009\",\"updated_by\":5}', '2025-01-10 06:39:06'),
(35, 3, 'user_login', '{\"username\":\"erictam\"}', '2025-01-10 06:39:06'),
(36, 3, 'job_status_updated', '{\"job_id\":\"TA-JOB-009\",\"action\":\"approve\"}', '2025-01-10 06:39:07'),
(37, 3, 'job_created', '{\"job_id\":\"TA-JOB-010\",\"project_id\":1,\"created_by\":3}', '2025-01-10 06:39:07'),
(38, 3, 'job_status_updated', '{\"job_id\":\"TA-JOB-010\",\"action\":\"reject\"}', '2025-01-10 06:39:08'),
(39, 3, 'job_updated', '{\"job_id\":\"TA-JOB-010\",\"updated_by\":3}', '2025-01-10 06:39:08'),
(40, 5, 'user_login', '{\"username\":\"jefferylam\"}', '2025-01-10 06:48:06'),
(41, 5, 'job_created', '{\"job_id\":\"TA-JOB-011\",\"project_id\":1,\"created_by\":5}', '2025-01-10 06:48:07'),
(42, 5, 'job_updated', '{\"job_id\":\"TA-JOB-011\",\"updated_by\":5}', '2025-01-10 06:48:07'),
(43, 3, 'user_login', '{\"username\":\"erictam\"}', '2025-01-10 06:48:07'),
(44, 3, 'job_status_updated', '{\"job_id\":\"TA-JOB-011\",\"action\":\"approve\"}', '2025-01-10 06:48:08'),
(45, 3, 'job_created', '{\"job_id\":\"TA-JOB-012\",\"project_id\":1,\"created_by\":3}', '2025-01-10 06:48:08'),
(46, 3, 'job_status_updated', '{\"job_id\":\"TA-JOB-012\",\"action\":\"reject\"}', '2025-01-10 06:48:09'),
(47, 3, 'job_updated', '{\"job_id\":\"TA-JOB-012\",\"updated_by\":3}', '2025-01-10 06:48:09'),
(48, 3, 'user_login', '{\"username\":\"erictam\"}', '2025-01-10 06:51:42'),
(49, 5, 'user_login', '{\"username\":\"jefferylam\"}', '2025-01-10 07:28:33'),
(50, 5, 'job_created', '{\"job_id\":\"20\",\"project_id\":1,\"created_by\":5}', '2025-01-10 07:28:33'),
(51, 5, 'job_updated', '{\"job_id\":20,\"updated_by\":5}', '2025-01-10 07:28:33'),
(52, 3, 'user_login', '{\"username\":\"erictam\"}', '2025-01-10 07:28:33'),
(53, 3, 'job_status_updated', '{\"job_id\":20,\"action\":\"approve\"}', '2025-01-10 07:28:33'),
(54, 3, 'job_created', '{\"job_id\":\"21\",\"project_id\":1,\"created_by\":3}', '2025-01-10 07:28:33'),
(55, 3, 'job_status_updated', '{\"job_id\":21,\"action\":\"reject\"}', '2025-01-10 07:28:33'),
(56, 3, 'job_updated', '{\"job_id\":21,\"updated_by\":3}', '2025-01-10 07:28:33'),
(57, 5, 'user_login', '{\"username\":\"jefferylam\"}', '2025-01-10 07:28:39'),
(58, 5, 'job_created', '{\"job_id\":\"22\",\"project_id\":1,\"created_by\":5}', '2025-01-10 07:28:39'),
(59, 5, 'job_updated', '{\"job_id\":22,\"updated_by\":5}', '2025-01-10 07:28:39'),
(60, 3, 'user_login', '{\"username\":\"erictam\"}', '2025-01-10 07:28:39'),
(61, 3, 'job_status_updated', '{\"job_id\":22,\"action\":\"approve\"}', '2025-01-10 07:28:39'),
(62, 3, 'job_created', '{\"job_id\":\"23\",\"project_id\":1,\"created_by\":3}', '2025-01-10 07:28:39'),
(63, 3, 'job_status_updated', '{\"job_id\":23,\"action\":\"reject\"}', '2025-01-10 07:28:39'),
(64, 3, 'job_updated', '{\"job_id\":23,\"updated_by\":3}', '2025-01-10 07:28:39'),
(65, 3, 'job_status_updated', '{\"job_id\":1,\"action\":\"approve\"}', '2025-01-10 07:29:06'),
(66, 3, 'job_status_updated', '{\"job_id\":22,\"action\":\"cancel\"}', '2025-01-10 07:31:03'),
(67, 3, 'job_status_updated', '{\"job_id\":23,\"action\":\"cancel\"}', '2025-01-10 07:31:10'),
(68, 5, 'user_login', '{\"username\":\"jefferylam\"}', '2025-01-10 08:34:28'),
(69, 1, 'user_login', '{\"username\":\"philiptsoi\"}', '2025-01-10 08:56:39'),
(70, 3, 'job_status_updated', '{\"job_id\":1,\"action\":\"assign\"}', '2025-01-10 09:43:21'),
(71, 8, 'user_login', '{\"username\":\"michaelfung\"}', '2025-01-10 09:45:24'),
(72, 8, 'user_login', '{\"username\":\"michaelfung\"}', '2025-01-10 14:11:48'),
(73, 3, 'user_login', '{\"username\":\"erictam\"}', '2025-01-10 16:24:15'),
(74, 1, 'user_login', '{\"username\":\"philiptsoi\"}', '2025-01-11 04:05:18'),
(75, 8, 'job_status_updated', '{\"job_id\":1,\"action\":\"accept\"}', '2025-01-11 04:13:10'),
(76, 8, 'job_status_updated', '{\"job_id\":1,\"action\":\"accept\"}', '2025-01-11 04:18:54'),
(77, 8, 'job_status_updated', '{\"job_id\":1,\"action\":\"accept\"}', '2025-01-11 04:18:55'),
(78, 8, 'job_status_updated', '{\"job_id\":1,\"action\":\"accept\"}', '2025-01-11 04:18:56'),
(79, 8, 'job_status_updated', '{\"job_id\":1,\"action\":\"accept\"}', '2025-01-11 04:19:14'),
(80, 8, 'job_status_updated', '{\"job_id\":1,\"action\":\"accept\"}', '2025-01-11 04:21:27'),
(81, 8, 'job_status_updated', '{\"job_id\":1,\"action\":\"accept\"}', '2025-01-11 04:21:43'),
(82, 8, 'job_status_updated', '{\"job_id\":1,\"action\":\"accept\"}', '2025-01-11 04:22:16'),
(83, 8, 'job_status_updated', '{\"job_id\":1,\"action\":\"accept\"}', '2025-01-11 04:22:17'),
(84, 8, 'job_status_updated', '{\"job_id\":1,\"action\":\"accept\"}', '2025-01-11 04:22:18'),
(85, 8, 'job_status_updated', '{\"job_id\":1,\"action\":\"accept\"}', '2025-01-11 04:22:19'),
(86, 8, 'job_status_updated', '{\"job_id\":1,\"action\":\"accept\"}', '2025-01-11 04:29:12'),
(87, 8, 'job_status_updated', '{\"job_id\":1,\"action\":\"accept\"}', '2025-01-11 04:29:13'),
(88, 8, 'job_status_updated', '{\"job_id\":1,\"action\":\"accept\"}', '2025-01-11 04:29:14'),
(89, 8, 'job_status_updated', '{\"job_id\":1,\"action\":\"accept\"}', '2025-01-11 04:29:14'),
(90, 8, 'job_status_updated', '{\"job_id\":1,\"action\":\"accept\"}', '2025-01-11 15:02:35'),
(91, 8, 'job_status_updated', '{\"job_id\":1,\"action\":\"accept\"}', '2025-01-11 15:02:39'),
(92, 8, 'job_status_updated', '{\"job_id\":1,\"action\":\"accept\"}', '2025-01-11 15:02:39'),
(93, 8, 'job_status_updated', '{\"job_id\":1,\"action\":\"request_completion\"}', '2025-01-11 15:11:16'),
(94, 8, 'job_status_updated', '{\"job_id\":1,\"action\":\"request_completion\"}', '2025-01-11 15:13:49'),
(95, 8, 'job_status_updated', '{\"job_id\":1,\"action\":\"request_completion\"}', '2025-01-11 15:16:26'),
(96, 8, 'job_status_updated', '{\"job_id\":1,\"action\":\"request_completion\"}', '2025-01-11 15:17:56'),
(97, 8, 'job_status_updated', '{\"job_id\":1,\"action\":\"request_completion\"}', '2025-01-11 15:21:36'),
(98, 8, 'job_status_updated', '{\"job_id\":1,\"action\":\"request_completion\"}', '2025-01-11 15:23:50'),
(99, 3, 'user_login', '{\"username\":\"erictam\"}', '2025-01-11 15:25:02'),
(100, 3, 'job_status_updated', '{\"job_id\":2,\"action\":\"approve\"}', '2025-01-12 05:44:34'),
(101, 3, 'job_status_updated', '{\"job_id\":2,\"action\":\"approve\"}', '2025-01-12 05:54:19'),
(102, 3, 'job_status_updated', '{\"job_id\":2,\"action\":\"assign\"}', '2025-01-12 08:05:39'),
(103, 3, 'job_status_updated', '{\"job_id\":3,\"action\":\"approve\"}', '2025-01-12 08:17:39'),
(104, 3, 'user_login', '{\"username\":\"erictam\"}', '2025-01-12 08:58:37'),
(105, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-13 05:40:21'),
(106, 6, 'job_created', '{\"job_id\":\"24\",\"project_id\":2,\"created_by\":6}', '2025-01-13 06:05:44'),
(107, 3, 'user_login', '{\"username\":\"erictam\"}', '2025-01-13 06:06:32'),
(108, 3, 'job_status_updated', '{\"job_id\":24,\"action\":\"reject\"}', '2025-01-13 06:12:26'),
(109, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-13 06:19:56'),
(110, 6, 'job_created', '{\"job_id\":\"25\",\"project_id\":2,\"created_by\":6}', '2025-01-13 06:28:40'),
(111, 3, 'user_login', '{\"username\":\"erictam\"}', '2025-01-13 06:32:23'),
(112, 3, 'job_status_updated', '{\"job_id\":25,\"action\":\"approve\"}', '2025-01-13 06:32:33'),
(113, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-13 06:35:27'),
(114, 6, 'job_created', '{\"job_id\":\"26\",\"project_id\":2,\"created_by\":6}', '2025-01-13 06:36:35'),
(115, 3, 'user_login', '{\"username\":\"erictam\"}', '2025-01-13 06:39:00'),
(116, 3, 'job_status_updated', '{\"job_id\":26,\"action\":\"reject\"}', '2025-01-13 06:41:50'),
(117, 3, 'user_login', '{\"username\":\"erictam\"}', '2025-01-13 06:42:13'),
(118, 6, 'job_created', '{\"job_id\":\"27\",\"project_id\":2,\"created_by\":6}', '2025-01-13 06:43:15'),
(119, 6, 'job_created', '{\"job_id\":\"28\",\"project_id\":3,\"created_by\":6}', '2025-01-13 06:46:33'),
(120, 4, 'user_login', '{\"username\":\"anguslai\"}', '2025-01-13 06:47:50'),
(121, 4, 'job_status_updated', '{\"job_id\":28,\"action\":\"approve\"}', '2025-01-13 06:47:58'),
(122, 4, 'job_status_updated', '{\"job_id\":28,\"action\":\"assign\"}', '2025-01-13 06:54:13'),
(123, 9, 'user_login', '{\"username\":\"marksmanyuen\"}', '2025-01-13 06:56:18'),
(124, 9, 'job_status_updated', '{\"job_id\":28,\"action\":\"request_completion\"}', '2025-01-13 06:56:22'),
(125, 4, 'job_status_updated', '{\"job_id\":28,\"action\":\"reprocess\"}', '2025-01-13 07:07:32'),
(126, 9, 'job_status_updated', '{\"job_id\":28,\"action\":\"request_completion\"}', '2025-01-13 07:08:34'),
(127, 4, 'job_status_updated', '{\"job_id\":28,\"action\":\"reprocess\"}', '2025-01-13 07:09:14'),
(128, 9, 'job_status_updated', '{\"job_id\":28,\"action\":\"request_completion\"}', '2025-01-13 07:10:04'),
(129, 4, 'job_status_updated', '{\"job_id\":28,\"action\":\"verify\"}', '2025-01-13 07:10:11'),
(130, 4, 'job_status_updated', '{\"job_id\":4,\"action\":\"assign\"}', '2025-01-13 07:11:07'),
(131, 9, 'job_status_updated', '{\"job_id\":4,\"action\":\"request_completion\"}', '2025-01-13 07:11:24'),
(132, 4, 'job_status_updated', '{\"job_id\":4,\"action\":\"reprocess\"}', '2025-01-13 07:12:16'),
(133, 9, 'job_status_updated', '{\"job_id\":4,\"action\":\"decline\"}', '2025-01-13 07:12:24'),
(134, 4, 'job_status_updated', '{\"job_id\":4,\"action\":\"assign\"}', '2025-01-13 07:12:41'),
(135, 9, 'job_status_updated', '{\"job_id\":4,\"action\":\"decline\"}', '2025-01-13 07:19:24'),
(136, 4, 'job_status_updated', '{\"job_id\":4,\"action\":\"assign\"}', '2025-01-13 07:21:04'),
(137, 9, 'job_status_updated', '{\"job_id\":4,\"action\":\"accept\"}', '2025-01-13 07:23:28'),
(138, 9, 'job_status_updated', '{\"job_id\":4,\"action\":\"request_completion\"}', '2025-01-13 07:24:17'),
(139, 4, 'job_status_updated', '{\"job_id\":4,\"action\":\"verify\"}', '2025-01-13 07:24:43'),
(140, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-13 08:09:09'),
(141, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-13 08:11:09'),
(142, 9, 'user_login', '{\"username\":\"marksmanyuen\"}', '2025-01-13 08:11:09'),
(143, 3, 'user_login', '{\"username\":\"erictam\"}', '2025-01-13 08:11:09'),
(144, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-13 08:15:43'),
(145, 9, 'user_login', '{\"username\":\"marksmanyuen\"}', '2025-01-13 08:15:43'),
(146, 3, 'user_login', '{\"username\":\"erictam\"}', '2025-01-13 08:15:43'),
(147, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-13 08:16:10'),
(148, 9, 'user_login', '{\"username\":\"marksmanyuen\"}', '2025-01-13 08:16:10'),
(149, 4, 'user_login', '{\"username\":\"anguslai\"}', '2025-01-28 08:13:46'),
(150, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-28 08:16:38'),
(151, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-28 08:36:43'),
(152, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-28 08:38:53'),
(153, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-28 08:39:42'),
(154, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-28 08:40:11'),
(155, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-28 08:40:44'),
(156, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-28 08:40:45'),
(157, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-28 08:40:54'),
(158, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-28 08:41:14'),
(159, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-28 08:41:21'),
(160, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-28 08:41:47'),
(161, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-28 08:43:31'),
(162, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-28 08:44:37'),
(163, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-28 08:44:46'),
(164, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-28 08:45:08'),
(165, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-28 08:45:16'),
(166, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-28 08:45:37'),
(167, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-28 08:45:50'),
(168, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-28 08:45:51'),
(169, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-28 08:45:52'),
(170, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-28 08:45:54'),
(171, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-28 08:46:20'),
(172, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-28 08:46:22'),
(173, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-28 08:46:24'),
(174, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-28 08:46:25'),
(175, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-28 08:46:30'),
(176, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-28 08:46:38'),
(177, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-28 08:46:42'),
(178, 1, 'user_login', '{\"username\":\"philiptsoi\"}', '2025-01-29 05:30:59'),
(179, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-01-31 01:08:33'),
(180, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-02-04 05:00:49'),
(181, 4, 'user_login', '{\"username\":\"anguslai\"}', '2025-02-04 08:05:28'),
(182, 4, 'user_login', '{\"username\":\"anguslai\"}', '2025-02-05 00:15:44'),
(183, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-02-05 00:31:05'),
(184, 3, 'user_login', '{\"username\":\"erictam\"}', '2025-02-05 00:38:30'),
(185, 8, 'user_login', '{\"username\":\"michaelfung\"}', '2025-02-05 00:45:28'),
(186, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-02-05 00:47:39'),
(187, 3, 'user_login', '{\"username\":\"erictam\"}', '2025-02-05 00:47:54'),
(188, 3, 'user_login', '{\"username\":\"erictam\"}', '2025-02-05 00:49:43'),
(189, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-02-05 00:49:53'),
(190, 6, 'user_login', '{\"username\":\"andytang\"}', '2025-03-20 07:58:23'),
(191, 3, 'user_login', '{\"username\":\"erictam\"}', '2025-03-24 09:49:02'),
(192, 3, 'user_login', '{\"username\":\"erictam\"}', '2025-03-24 10:20:16'),
(193, 3, 'user_login', '{\"username\":\"erictam\"}', '2025-03-24 10:24:35'),
(194, 3, 'user_login', '{\"username\":\"erictam\"}', '2025-03-24 10:49:28'),
(195, 3, 'user_login', '{\"username\":\"erictam\"}', '2025-03-31 07:36:23'),
(196, 6, 'user_login', '{\"username\":\"timothyng\"}', '2025-03-31 07:43:50'),
(197, 6, 'job_created', '{\"job_id\":\"29\",\"project_id\":2,\"created_by\":6}', '2025-03-31 07:45:45'),
(198, 3, 'user_login', '{\"username\":\"erictam\"}', '2025-03-31 07:46:03'),
(199, 6, 'user_login', '{\"username\":\"timothyng\"}', '2025-03-31 07:49:40'),
(200, 1, 'user_login', '{\"username\":\"philiptsoi\"}', '2025-03-31 08:00:03'),
(201, 1, 'user_login', '{\"username\":\"hauchunyin\"}', '2025-03-31 08:01:46'),
(202, 4, 'user_login', '{\"username\":\"amberlo\"}', '2025-03-31 08:06:20'),
(203, 4, 'user_login', '{\"username\":\"amberlo\"}', '2025-03-31 08:07:20'),
(204, 1, 'user_login', '{\"username\":\"hauchunyin\"}', '2025-03-31 08:08:15');

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

--
-- 資料表結構 `company_settings`
--

CREATE TABLE `company_settings` (
  `id` int(11) NOT NULL,
  `setting_key` varchar(50) NOT NULL,
  `setting_value` text NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- 資料表結構 `documents`
--

CREATE TABLE `documents` (
  `id` int(11) NOT NULL,
  `job_id` int(11) NOT NULL,
  `type` enum('quotation','invoice','payment_proof') NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `file_type` varchar(50) NOT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- 資料表結構 `events`
--

CREATE TABLE `events` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `title` varchar(100) NOT NULL,
  `description` text,
  `event_date` datetime NOT NULL,
  `notification_sent` tinyint(1) DEFAULT '0',
  `recurring` tinyint(1) DEFAULT '0',
  `recurrence_pattern` enum('daily','weekly','monthly','yearly') DEFAULT NULL,
  `last_notification_date` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- 資料表結構 `jobs`
--

CREATE TABLE `jobs` (
  `id` int(11) NOT NULL,
  `job_unique_id` varchar(20) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `project_id` int(11) DEFAULT NULL,
  `client_company_name` varchar(100) NOT NULL,
  `price` decimal(15,2) NOT NULL,
  `job_cost` decimal(15,2) DEFAULT NULL,
  `commission_amount` decimal(15,2) DEFAULT NULL,
  `service_charge` decimal(15,2) DEFAULT NULL,
  `deposit_amount` decimal(15,2) DEFAULT NULL,
  `finance_amount` decimal(15,2) DEFAULT NULL,
  `due_date` date NOT NULL,
  `end_date` date DEFAULT NULL,
  `status` enum('draft','approved','rejected','in_progress','assigned','sent','completed','cancelled') DEFAULT 'draft',
  `remark` text,
  `additional_fields` text,
  `agent_id` int(11) DEFAULT NULL,
  `account_manager_id` int(11) DEFAULT NULL,
  `vendor_id` 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;

--
-- 傾印資料表的資料 `jobs`
--

INSERT INTO `jobs` (`id`, `job_unique_id`, `title`, `project_id`, `client_company_name`, `price`, `job_cost`, `commission_amount`, `service_charge`, `deposit_amount`, `finance_amount`, `due_date`, `end_date`, `status`, `remark`, `additional_fields`, `agent_id`, `account_manager_id`, `vendor_id`, `created_at`, `updated_at`) VALUES
(1, 'TP24-JOB-001', 'Individual Tax Return', 1, 'Smith Corp', '500.00', '10.00', '40.00', NULL, '30.00', '40.00', '2025-01-30', NULL, 'in_progress', NULL, NULL, 5, 3, 8, '2025-01-10 02:31:23', '2025-01-12 08:52:18'),
(2, 'TP24-JOB-002', 'Corporate Tax Return', 1, 'Smith Corp', '1500.00', '30.00', '33.00', NULL, '10.00', '15.00', '2025-03-15', NULL, 'sent', NULL, NULL, 6, 3, 10, '2025-01-10 02:31:23', '2025-01-12 08:16:52'),
(3, 'ASJC-JOB-001', 'Financial Audit', 2, 'Jones Co.', '10000.00', NULL, '20.00', NULL, NULL, NULL, '2025-06-30', NULL, 'approved', NULL, NULL, 5, 3, NULL, '2025-01-10 02:31:23', '2025-01-12 08:17:39'),
(4, 'MB-JOB-001', 'Monthly Reconciliation', 3, 'ABC Corp', '1200.00', '11.00', '30.00', NULL, '11.00', '11.00', '2025-12-31', NULL, 'completed', NULL, NULL, 6, 4, 9, '2025-01-10 02:31:23', '2025-01-13 07:24:43'),
(5, 'MB-JOB-002', 'Accounts Payable', 3, 'ABC Corp', '800.00', NULL, NULL, NULL, NULL, NULL, '2025-01-15', NULL, 'assigned', NULL, NULL, 6, 4, NULL, '2025-01-10 02:31:23', '2025-01-10 02:31:23'),
(6, 'MB-JOB-003', 'Accounts Receivable', 3, 'ABC Corp', '900.00', NULL, NULL, NULL, NULL, NULL, '2025-01-31', NULL, 'draft', NULL, NULL, 7, 4, NULL, '2025-01-10 02:31:23', '2025-01-10 02:31:23'),
(7, 'YE-JOB-001', 'Balance Sheet Prep', 4, 'XYZ Ltd', '3000.00', NULL, NULL, NULL, NULL, NULL, '2025-02-28', NULL, 'draft', NULL, NULL, 6, 4, NULL, '2025-01-10 02:31:23', '2025-01-10 02:31:23'),
(8, 'YE-JOB-002', 'Income Statement Prep', 4, 'XYZ Ltd', '2500.00', NULL, NULL, NULL, NULL, NULL, '2025-03-15', NULL, 'draft', NULL, NULL, 7, 4, NULL, '2025-01-10 02:31:23', '2025-01-10 02:31:23'),
(9, 'YE-JOB-003', 'Cash Flow Statement Prep', 4, 'XYZ Ltd', '2000.00', NULL, NULL, NULL, NULL, NULL, '2025-03-31', NULL, 'draft', NULL, NULL, 7, 4, NULL, '2025-01-10 02:31:23', '2025-01-10 02:31:23'),
(29, 'AU-JOB-006', 'test', 2, 'AAA', '11000.00', NULL, NULL, NULL, NULL, NULL, '2025-04-01', NULL, 'draft', '', '{\"job_category\":\"audit\"}', 6, 3, NULL, '2025-03-31 07:45:45', '2025-03-31 07:45:45');

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

--
-- 資料表結構 `payments`
--

CREATE TABLE `payments` (
  `id` int(11) NOT NULL,
  `job_id` int(11) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `payment_type` enum('full','deposit') NOT NULL,
  `payment_date` date NOT NULL,
  `document_id` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- 資料表結構 `payslips`
--

CREATE TABLE `payslips` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `timefrom` datetime NOT NULL,
  `timeto` datetime NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `status` enum('draft','released') DEFAULT 'draft',
  `file_path` varchar(255) DEFAULT NULL,
  `file_type` varchar(50) DEFAULT NULL COMMENT 'MIME type of uploaded file',
  `created_by` int(11) NOT NULL,
  `updated_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;

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

--
-- 資料表結構 `projects`
--

CREATE TABLE `projects` (
  `id` int(11) NOT NULL,
  `project_name` varchar(255) NOT NULL,
  `description` text,
  `status` enum('enquiry','quotation','planning','in_progress','on_hold','completed','cancelled') DEFAULT 'enquiry',
  `client_company_name` varchar(255) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `estimated_end_date` date DEFAULT NULL,
  `actual_end_date` date DEFAULT NULL,
  `total_job_value` decimal(15,2) DEFAULT '0.00',
  `total_commission` decimal(15,2) DEFAULT '0.00',
  `total_service_charge` decimal(15,2) DEFAULT '0.00',
  `google_drive_folder_link` varchar(500) DEFAULT NULL,
  `created_by` int(11) NOT 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;

--
-- 傾印資料表的資料 `projects`
--

INSERT INTO `projects` (`id`, `project_name`, `description`, `status`, `client_company_name`, `start_date`, `estimated_end_date`, `actual_end_date`, `total_job_value`, `total_commission`, `total_service_charge`, `google_drive_folder_link`, `created_by`, `created_at`, `updated_at`) VALUES
(1, 'Tax Preparation 2024', 'Prepare and file 2024 taxes for Smith Corp.', 'in_progress', NULL, NULL, NULL, NULL, '0.00', '0.00', '0.00', NULL, 1, '2025-01-10 02:31:22', '2025-01-10 02:31:22'),
(2, 'Audit Service - Jones Co.', 'Conduct annual audit for Jones Company.', 'planning', NULL, NULL, NULL, NULL, '0.00', '0.00', '0.00', NULL, 1, '2025-01-10 02:31:22', '2025-01-10 02:31:22'),
(3, 'Monthly Bookkeeping - ABC Corp', 'Provide monthly bookkeeping services for ABC Corp.', 'in_progress', NULL, NULL, NULL, NULL, '0.00', '0.00', '0.00', NULL, 2, '2025-01-10 02:31:22', '2025-01-10 02:31:22'),
(4, 'Year-End Financials - XYZ Ltd', 'Prepare year-end financial statements for XYZ Ltd.', 'planning', NULL, NULL, NULL, NULL, '0.00', '0.00', '0.00', NULL, 2, '2025-01-10 02:31:22', '2025-01-10 02:31:22');

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

--
-- 資料表結構 `project_assignments`
--

CREATE TABLE `project_assignments` (
  `id` int(11) NOT NULL,
  `project_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `role` enum('manager','agent','vendor') NOT NULL,
  `assigned_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- 傾印資料表的資料 `project_assignments`
--

INSERT INTO `project_assignments` (`id`, `project_id`, `user_id`, `role`, `assigned_at`) VALUES
(1, 1, 3, 'manager', '2025-01-10 02:31:23'),
(2, 2, 3, 'manager', '2025-01-10 02:31:23'),
(3, 3, 4, 'manager', '2025-01-10 02:31:23'),
(4, 4, 4, 'manager', '2025-01-10 02:31:23'),
(5, 1, 5, 'agent', '2025-01-10 02:31:23'),
(6, 1, 6, 'agent', '2025-01-10 02:31:23'),
(7, 2, 5, 'agent', '2025-01-10 02:31:23'),
(8, 2, 6, 'agent', '2025-01-10 02:31:23'),
(9, 3, 6, 'agent', '2025-01-10 02:31:23'),
(10, 3, 7, 'agent', '2025-01-10 02:31:23'),
(11, 4, 6, 'agent', '2025-01-10 02:31:23'),
(12, 4, 7, 'agent', '2025-01-10 02:31:23'),
(13, 1, 8, 'vendor', '2025-01-10 02:31:23'),
(14, 1, 9, 'vendor', '2025-01-10 02:31:23'),
(15, 1, 10, 'vendor', '2025-01-10 02:31:23'),
(16, 2, 8, 'vendor', '2025-01-10 02:31:23'),
(17, 2, 9, 'vendor', '2025-01-10 02:31:23'),
(18, 2, 10, 'vendor', '2025-01-10 02:31:23'),
(19, 3, 8, 'vendor', '2025-01-10 02:31:23'),
(20, 3, 9, 'vendor', '2025-01-10 02:31:23'),
(21, 3, 10, 'vendor', '2025-01-10 02:31:23'),
(22, 4, 8, 'vendor', '2025-01-10 02:31:23'),
(23, 4, 9, 'vendor', '2025-01-10 02:31:23'),
(24, 4, 10, 'vendor', '2025-01-10 02:31:23');

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

--
-- 資料表結構 `project_documents`
--

CREATE TABLE `project_documents` (
  `id` int(11) NOT NULL,
  `project_id` int(11) NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `upload_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `uploaded_by` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- 資料表結構 `project_status_updates`
--

CREATE TABLE `project_status_updates` (
  `id` int(11) NOT NULL,
  `project_id` int(11) NOT NULL,
  `status` enum('planning','in_progress','on_hold','completed','cancelled') NOT NULL,
  `updated_by` int(11) NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `comment` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- 資料表結構 `service_configs`
--

CREATE TABLE `service_configs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `service_name` varchar(100) NOT NULL,
  `config_data` text NOT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_service_name` (`service_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Default service configurations
--
INSERT INTO `service_configs` (`service_name`, `config_data`, `status`, `created_at`, `updated_at`) VALUES
('audit_service', '{"default_rate": 150.00, "minimum_hours": 8, "allowed_file_types": ["pdf","doc","docx","xls","xlsx"]}', 'active', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
('tax_service', '{"default_rate": 100.00, "minimum_hours": 4, "allowed_file_types": ["pdf","doc","docx","xls","xlsx"]}', 'active', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
('bookkeeping_service', '{"default_rate": 80.00, "minimum_hours": 2, "allowed_file_types": ["pdf","doc","docx","xls","xlsx","csv"]}', 'active', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

--
-- 已傾印資料表的索引
--

--
-- 資料表索引 `activity_log`
--
ALTER TABLE `activity_log`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`);

--
-- 資料表索引 `company_settings`
--
ALTER TABLE `company_settings`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `setting_key` (`setting_key`);

--
-- 資料表索引 `documents`
--
ALTER TABLE `documents`
  ADD PRIMARY KEY (`id`),
  ADD KEY `job_id` (`job_id`),
  ADD KEY `created_by` (`created_by`),
  ADD KEY `idx_documents_type` (`type`);

--
-- 資料表索引 `events`
--
ALTER TABLE `events`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `idx_events_event_date` (`event_date`),
  ADD KEY `idx_events_notification` (`notification_sent`, `event_date`),
  ADD KEY `idx_events_user_date` (`user_id`, `event_date`);

--
-- 資料表索引 `jobs`
--
ALTER TABLE `jobs`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `job_unique_id` (`job_unique_id`),
  ADD KEY `agent_id` (`agent_id`),
  ADD KEY `account_manager_id` (`account_manager_id`),
  ADD KEY `vendor_id` (`vendor_id`),
  ADD KEY `project_id` (`project_id`),
  ADD KEY `idx_jobs_status` (`status`),
  ADD KEY `idx_jobs_due_date` (`due_date`),
  ADD KEY `idx_jobs_date_status` (`created_at`, `status`),
  ADD KEY `idx_jobs_agent_commission` (`agent_id`, `commission_amount`),
  ADD KEY `idx_jobs_vendor_cost` (`vendor_id`, `job_cost`);

--
-- 資料表索引 `payments`
--
ALTER TABLE `payments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `job_id` (`job_id`),
  ADD KEY `document_id` (`document_id`),
  ADD KEY `idx_payments_payment_date` (`payment_date`);

--
-- 資料表索引 `payslips`
--
ALTER TABLE `payslips`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `created_by` (`created_by`),
  ADD KEY `updated_by` (`updated_by`),
  ADD KEY `idx_payslips_status` (`status`),
  ADD KEY `idx_payslips_user_period` (`user_id`, `timefrom`, `timeto`);

--
-- 資料表索引 `projects`
--
ALTER TABLE `projects`
  ADD PRIMARY KEY (`id`),
  ADD KEY `created_by` (`created_by`);

--
-- 資料表索引 `project_assignments`
--
ALTER TABLE `project_assignments`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_project_user_role` (`project_id`,`user_id`,`role`),
  ADD KEY `user_id` (`user_id`);

--
-- 資料表索引 `project_documents`
--
ALTER TABLE `project_documents`
  ADD PRIMARY KEY (`id`),
  ADD KEY `project_id` (`project_id`),
  ADD KEY `uploaded_by` (`uploaded_by`);

--
-- 資料表索引 `project_status_updates`
--
ALTER TABLE `project_status_updates`
  ADD PRIMARY KEY (`id`),
  ADD KEY `project_id` (`project_id`),
  ADD KEY `updated_by` (`updated_by`);

--
-- 在傾印的資料表使用自動遞增(AUTO_INCREMENT)
--

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `activity_log`
--
ALTER TABLE `activity_log`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=205;

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `company_settings`
--
ALTER TABLE `company_settings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `documents`
--
ALTER TABLE `documents`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `events`
--
ALTER TABLE `events`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `jobs`
--
ALTER TABLE `jobs`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=30;

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `payments`
--
ALTER TABLE `payments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `payslips`
--
ALTER TABLE `payslips`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `projects`
--
ALTER TABLE `projects`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `project_assignments`
--
ALTER TABLE `project_assignments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=25;

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `project_documents`
--
ALTER TABLE `project_documents`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `project_status_updates`
--
ALTER TABLE `project_status_updates`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- 使用資料表自動遞增(AUTO_INCREMENT) `service_configs`
--
ALTER TABLE `service_configs`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- 已傾印資料表的限制式
--

--
-- 資料表的限制式 `activity_log`
--
ALTER TABLE `activity_log`
  ADD CONSTRAINT `activity_log_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

--
-- 資料表的限制式 `documents`
--
ALTER TABLE `documents`
  ADD CONSTRAINT `documents_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`id`),
  ADD CONSTRAINT `documents_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);

--
-- 資料表的限制式 `events`
--
ALTER TABLE `events`
  ADD CONSTRAINT `events_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

--
-- 資料表的限制式 `jobs`
--
ALTER TABLE `jobs`
  ADD CONSTRAINT `jobs_ibfk_1` FOREIGN KEY (`agent_id`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `jobs_ibfk_2` FOREIGN KEY (`account_manager_id`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `jobs_ibfk_3` FOREIGN KEY (`vendor_id`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `jobs_ibfk_4` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`);

--
-- 資料表的限制式 `payments`
--
ALTER TABLE `payments`
  ADD CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`id`),
  ADD CONSTRAINT `payments_ibfk_2` FOREIGN KEY (`document_id`) REFERENCES `documents` (`id`);

--
-- 資料表的限制式 `payslips`
--
ALTER TABLE `payslips`
  ADD CONSTRAINT `payslips_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `payslips_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `payslips_ibfk_3` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`);

--
-- 資料表的限制式 `projects`
--
ALTER TABLE `projects`
  ADD CONSTRAINT `projects_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);

--
-- 資料表的限制式 `project_assignments`
--
ALTER TABLE `project_assignments`
  ADD CONSTRAINT `project_assignments_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`),
  ADD CONSTRAINT `project_assignments_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

--
-- 資料表的限制式 `project_documents`
--
ALTER TABLE `project_documents`
  ADD CONSTRAINT `project_documents_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`),
  ADD CONSTRAINT `project_documents_ibfk_2` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`);

--
-- 資料表的限制式 `project_status_updates`
--
ALTER TABLE `project_status_updates`
  ADD CONSTRAINT `project_status_updates_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`),
  ADD CONSTRAINT `project_status_updates_ibfk_2` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`);
COMMIT;

-- Events table
CREATE TABLE IF NOT EXISTS events (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    time DATETIME NOT NULL,
    is_recurring BOOLEAN DEFAULT FALSE,
    recurrence_type ENUM('daily', 'weekly', 'monthly') NULL,
    notify_before INT DEFAULT 15, -- minutes before event to notify
    created_by INT NOT NULL,
    user_id INT NOT NULL, -- The user this event is for
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Event participants table for shared events
CREATE TABLE IF NOT EXISTS event_participants (
    id INT PRIMARY KEY AUTO_INCREMENT,
    event_id INT NOT NULL,
    user_id INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id),
    UNIQUE KEY unique_participant (event_id, user_id)
);

-- Event notifications table to track sent notifications
CREATE TABLE IF NOT EXISTS event_notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    event_id INT NOT NULL,
    user_id INT NOT NULL,
    notification_time DATETIME NOT NULL,
    is_sent BOOLEAN DEFAULT FALSE,
    sent_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Indexes for better query performance
CREATE INDEX idx_events_time ON events(time);
CREATE INDEX idx_events_user ON events(user_id);
CREATE INDEX idx_notification_time ON event_notifications(notification_time);
CREATE INDEX idx_notification_status ON event_notifications(is_sent);