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

START TRANSACTION;



--
-- Database: `buzzwarems`
--

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

--
-- Table structure for table `alternate_saturday_off`
--

CREATE TABLE `alternate_saturday_off` (
  `id` int UNSIGNED NOT NULL,
  `date` date NOT NULL,
  `name` varchar(255) DEFAULT 'Alternate Saturday Off',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `alternate_saturday_off`
--

INSERT INTO `alternate_saturday_off` (`id`, `date`, `name`, `created_at`, `updated_at`) VALUES
(1, '2026-04-18', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(2, '2026-05-02', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(3, '2026-05-16', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(4, '2026-05-30', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(5, '2026-06-13', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(6, '2026-06-27', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(7, '2026-07-11', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(8, '2026-07-25', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(9, '2026-08-08', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(10, '2026-08-22', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(11, '2026-09-05', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(12, '2026-09-19', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(13, '2026-10-03', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(14, '2026-10-17', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(15, '2026-10-31', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(16, '2026-11-14', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(17, '2026-11-28', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(18, '2026-12-12', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(19, '2026-12-26', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(20, '2027-01-09', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(21, '2027-01-23', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(22, '2027-02-06', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(23, '2027-02-20', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(24, '2027-03-06', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(25, '2027-03-20', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(26, '2027-04-03', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(27, '2027-04-17', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(28, '2027-05-01', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(29, '2027-05-15', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(30, '2027-05-29', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(31, '2027-06-12', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(32, '2027-06-26', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(33, '2027-07-10', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(34, '2027-07-24', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(35, '2027-08-07', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(36, '2027-08-21', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(37, '2027-09-04', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(38, '2027-09-18', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(39, '2027-10-02', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(40, '2027-10-16', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(41, '2027-10-30', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(42, '2027-11-13', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(43, '2027-11-27', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(44, '2027-12-11', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54'),
(45, '2027-12-25', 'Alternate Saturday Off', '2026-04-16 08:03:54', '2026-04-16 08:03:54');

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

--
-- Table structure for table `attendance_adjustments`
--

CREATE TABLE `attendance_adjustments` (
  `id` int UNSIGNED NOT NULL,
  `user_id` int UNSIGNED NOT NULL,
  `date` date NOT NULL,
  `corrected_check_in` timestamp NULL DEFAULT NULL,
  `corrected_check_out` timestamp NULL DEFAULT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `status` enum('pending','approved','rejected') DEFAULT 'pending',
  `approved_by` int UNSIGNED DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `attendance_adjustments`
--

INSERT INTO `attendance_adjustments` (`id`, `user_id`, `date`, `corrected_check_in`, `corrected_check_out`, `reason`, `status`, `approved_by`, `created_at`, `updated_at`) VALUES
(1, 5, '2026-04-01', '2026-04-01 04:00:00', '2026-04-01 12:05:00', 'Forgot to check out', 'approved', 1, '2026-04-02 06:48:19', '2026-04-02 06:48:19');

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

--
-- Table structure for table `attendance_daily`
--

CREATE TABLE `attendance_daily` (
  `id` int UNSIGNED NOT NULL,
  `user_id` int UNSIGNED NOT NULL,
  `date` date NOT NULL,
  `check_in` timestamp NULL DEFAULT NULL,
  `check_out` timestamp NULL DEFAULT NULL,
  `total_work_minutes` int DEFAULT '0',
  `overtime_minutes` int DEFAULT '0',
  `status` enum('present','absent','incomplete','on_leave','holiday') DEFAULT 'present',
  `is_manual` tinyint(1) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `attendance_daily`
--

INSERT INTO `attendance_daily` (`id`, `user_id`, `date`, `check_in`, `check_out`, `total_work_minutes`, `overtime_minutes`, `status`, `is_manual`, `created_at`, `updated_at`) VALUES
(1, 1, '2026-03-25', '2026-03-25 04:05:00', '2026-03-25 12:10:00', 485, 5, 'present', 0, '2026-04-02 06:47:42', '2026-04-02 06:47:42'),
(2, 2, '2026-03-25', '2026-03-25 04:15:00', '2026-03-25 12:15:00', 480, 0, 'present', 0, '2026-04-02 06:47:42', '2026-04-02 06:47:42'),
(3, 3, '2026-03-25', NULL, NULL, 0, 0, 'on_leave', 0, '2026-04-02 06:47:42', '2026-04-02 06:47:42'),
(4, 4, '2026-03-25', '2026-03-25 04:10:00', '2026-03-25 14:00:00', 590, 110, 'present', 1, '2026-04-02 06:47:42', '2026-04-02 06:47:42'),
(5, 5, '2026-03-25', NULL, NULL, 0, 0, 'absent', 0, '2026-04-02 06:47:42', '2026-04-02 06:47:42'),
(6, 1, '2026-04-01', '2026-04-01 04:05:00', '2026-04-01 12:10:00', 485, 5, 'present', 0, '2026-04-02 06:47:58', '2026-04-02 06:47:58'),
(7, 2, '2026-04-01', '2026-04-01 04:20:00', '2026-04-01 12:30:00', 490, 10, 'present', 0, '2026-04-02 06:47:58', '2026-04-02 06:47:58'),
(8, 3, '2026-04-01', '2026-04-01 04:00:00', '2026-04-01 12:00:00', 480, 0, 'incomplete', 0, '2026-04-02 06:47:58', '2026-04-02 06:47:58'),
(9, 4, '2026-04-01', '2026-04-01 04:15:00', '2026-04-01 12:10:00', 475, 0, 'present', 0, '2026-04-02 06:47:58', '2026-04-02 06:47:58'),
(10, 5, '2026-04-01', '2026-04-01 04:00:00', '2026-04-01 14:48:00', 648, 168, 'present', 1, '2026-04-02 06:47:58', '2026-04-02 06:47:58'),
(18, 2, '2026-04-02', '2026-04-02 05:45:48', '2026-04-02 07:49:55', 124, 0, 'present', 0, '2026-04-02 07:45:47', '2026-04-02 07:45:47'),
(20, 6, '2026-04-07', '2026-04-07 06:05:32', '2026-04-07 06:18:46', 13, 0, 'present', 0, '2026-04-07 06:05:32', '2026-04-07 06:05:32'),
(23, 6, '2026-04-15', '2026-04-14 19:41:42', '2026-04-14 20:01:18', 19, 0, 'present', 0, '2026-04-14 19:41:42', '2026-04-14 19:41:42'),
(26, 6, '2026-04-16', '2026-04-16 05:02:40', '2026-04-16 07:32:41', 150, 0, 'incomplete', 0, '2026-04-16 05:02:39', '2026-04-16 05:02:39'),
(28, 6, '2026-04-17', '2026-04-16 21:51:24', NULL, 0, 0, 'present', 0, '2026-04-16 21:51:23', '2026-04-16 21:51:23');

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

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

CREATE TABLE `departments` (
  `id` int UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `departments`
--

INSERT INTO `departments` (`id`, `name`, `created_at`, `updated_at`) VALUES
(1, 'Web Development', '2026-04-02 06:45:30', '2026-04-02 06:45:30'),
(2, 'Design', '2026-04-02 06:45:30', '2026-04-02 06:45:30'),
(3, 'App Development', '2026-04-02 06:45:30', '2026-04-02 06:45:30'),
(4, 'HR/Admin', '2026-04-02 06:45:30', '2026-04-02 06:45:30');

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

--
-- Table structure for table `holidays`
--

CREATE TABLE `holidays` (
  `id` int UNSIGNED NOT NULL,
  `holiday_date` date NOT NULL,
  `name` varchar(100) NOT NULL,
  `type` enum('public','optional') DEFAULT 'public',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `holidays`
--

INSERT INTO `holidays` (`id`, `holiday_date`, `name`, `type`, `created_at`, `updated_at`) VALUES
(3, '2026-04-24', 'ggg', 'public', '2026-04-16 20:54:43', '2026-04-16 20:54:43');

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

--
-- Table structure for table `knex_migrations`
--

CREATE TABLE `knex_migrations` (
  `id` int UNSIGNED NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `batch` int DEFAULT NULL,
  `migration_time` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `knex_migrations`
--

INSERT INTO `knex_migrations` (`id`, `name`, `batch`, `migration_time`) VALUES
(1, '20260118000001_create_roles_table.cjs', 1, '2026-04-02 06:31:04'),
(2, '20260118000002_create_departments_table.cjs', 1, '2026-04-02 06:31:04'),
(3, '20260118000003_create_users_table.cjs', 1, '2026-04-02 06:31:05'),
(4, '20260118000004_create_attendance_table.cjs', 1, '2026-04-02 06:31:06'),
(5, '20260118000005_create_leaves_table.cjs', 1, '2026-04-02 06:31:07'),
(6, '20260401071423_create_holidays_table.cjs', 1, '2026-04-02 06:31:07'),
(7, '20260402060519_create_attendance_adjustments.cjs', 1, '2026-04-02 06:31:08'),
(9, '20260416075621_create_alternate_saturday_off_table.cjs', 2, '2026-04-16 08:00:02');

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

--
-- Table structure for table `knex_migrations_lock`
--

CREATE TABLE `knex_migrations_lock` (
  `index` int UNSIGNED NOT NULL,
  `is_locked` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `knex_migrations_lock`
--

INSERT INTO `knex_migrations_lock` (`index`, `is_locked`) VALUES
(1, 0);

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

--
-- Table structure for table `leaves`
--

CREATE TABLE `leaves` (
  `id` int UNSIGNED NOT NULL,
  `user_id` int UNSIGNED NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `leave_type` varchar(50) NOT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `status` enum('pending','approved','rejected') DEFAULT 'pending',
  `is_paid` tinyint(1) DEFAULT '1',
  `approved_by` int UNSIGNED DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `leaves`
--

INSERT INTO `leaves` (`id`, `user_id`, `start_date`, `end_date`, `leave_type`, `reason`, `status`, `is_paid`, `approved_by`, `created_at`, `updated_at`) VALUES
(1, 3, '2026-03-15', '2026-03-15', 'Casual', 'Personal work', 'approved', 1, 2, '2026-04-02 06:47:21', '2026-04-02 06:47:21'),
(2, 2, '2026-03-20', '2026-03-20', 'Sick', 'Flu', 'approved', 1, 1, '2026-04-02 06:47:21', '2026-04-02 06:47:21');

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

--
-- Table structure for table `roles`
--

CREATE TABLE `roles` (
  `id` int UNSIGNED NOT NULL,
  `name` varchar(50) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `roles`
--

INSERT INTO `roles` (`id`, `name`, `created_at`, `updated_at`) VALUES
(1, 'employee', '2026-04-02 06:44:54', '2026-04-02 06:44:54'),
(2, 'team Lead', '2026-04-02 06:44:54', '2026-04-02 06:44:54'),
(3, 'admin', '2026-04-02 06:44:54', '2026-04-02 06:44:54');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `alternate_saturday_off`
--
ALTER TABLE `alternate_saturday_off`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `alternate_saturday_off_date_unique` (`date`);

--
-- Indexes for table `attendance_adjustments`
--
ALTER TABLE `attendance_adjustments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `attendance_adjustments_user_id_foreign` (`user_id`),
  ADD KEY `attendance_adjustments_approved_by_foreign` (`approved_by`);

--
-- Indexes for table `attendance_daily`
--
ALTER TABLE `attendance_daily`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `attendance_daily_user_id_date_unique` (`user_id`,`date`);

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

--
-- Indexes for table `holidays`
--
ALTER TABLE `holidays`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `holidays_holiday_date_unique` (`holiday_date`);

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

--
-- Indexes for table `knex_migrations_lock`
--
ALTER TABLE `knex_migrations_lock`
  ADD PRIMARY KEY (`index`);

--
-- Indexes for table `leaves`
--
ALTER TABLE `leaves`
  ADD PRIMARY KEY (`id`),
  ADD KEY `leaves_user_id_foreign` (`user_id`),
  ADD KEY `leaves_approved_by_foreign` (`approved_by`);

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

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `alternate_saturday_off`
--
ALTER TABLE `alternate_saturday_off`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=46;

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

--
-- AUTO_INCREMENT for table `attendance_daily`
--
ALTER TABLE `attendance_daily`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=29;

--
-- AUTO_INCREMENT for table `departments`
--
ALTER TABLE `departments`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `holidays`
--
ALTER TABLE `holidays`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

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

--
-- AUTO_INCREMENT for table `knex_migrations_lock`
--
ALTER TABLE `knex_migrations_lock`
  MODIFY `index` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

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

--
-- AUTO_INCREMENT for table `roles`
--
ALTER TABLE `roles`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `attendance_adjustments`
--
ALTER TABLE `attendance_adjustments`
  ADD CONSTRAINT `attendance_adjustments_approved_by_foreign` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `attendance_adjustments_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

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

--
-- Constraints for table `leaves`
--
ALTER TABLE `leaves`
  ADD CONSTRAINT `leaves_approved_by_foreign` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `leaves_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;
COMMIT;

