-- ------------------------------------------------------------
-- MDS HR Management System – database
-- Generated 2026-10-29 13:47 from the Laravel migrations in database/migrations
-- Carried over from mds_crm_live: users, roles, departments, designations, employees, payroll, shortcuts
-- MySQL 5.7+ / MariaDB 10.3+
-- ------------------------------------------------------------

SET NAMES utf8mb4;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET FOREIGN_KEY_CHECKS = 0;
SET time_zone = "+00:00";

DROP TABLE IF EXISTS `announcements`;
DROP TABLE IF EXISTS `employee_documents`;
DROP TABLE IF EXISTS `salary_revisions`;
DROP TABLE IF EXISTS `payroll_recoveries`;
DROP TABLE IF EXISTS `payroll_adjustments`;
DROP TABLE IF EXISTS `employee_payrolls`;
DROP TABLE IF EXISTS `employee_advances`;
DROP TABLE IF EXISTS `employee_loans`;
DROP TABLE IF EXISTS `employee_overtimes`;
DROP TABLE IF EXISTS `employee_salary_components`;
DROP TABLE IF EXISTS `salary_components`;
DROP TABLE IF EXISTS `employee_leaves`;
DROP TABLE IF EXISTS `leave_types`;
DROP TABLE IF EXISTS `holidays`;
DROP TABLE IF EXISTS `employee_attendances`;
DROP TABLE IF EXISTS `employees`;
DROP TABLE IF EXISTS `shifts`;
DROP TABLE IF EXISTS `designations`;
DROP TABLE IF EXISTS `departments`;
DROP TABLE IF EXISTS `user_notifications`;
DROP TABLE IF EXISTS `shortcuts`;
DROP TABLE IF EXISTS `settings`;
DROP TABLE IF EXISTS `activity_log`;
DROP TABLE IF EXISTS `role_has_permissions`;
DROP TABLE IF EXISTS `model_has_roles`;
DROP TABLE IF EXISTS `model_has_permissions`;
DROP TABLE IF EXISTS `roles`;
DROP TABLE IF EXISTS `permissions`;
DROP TABLE IF EXISTS `password_resets`;
DROP TABLE IF EXISTS `users`;
DROP TABLE IF EXISTS `migrations`;

-- ---------------- Schema ----------------
create table `migrations` (`id` int unsigned not null auto_increment primary key, `migration` varchar(191) not null, `batch` int not null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
create table `users` (`id` int unsigned not null auto_increment primary key, `name` varchar(191) not null, `short_name` varchar(50) null, `email` varchar(191) not null, `phone` varchar(30) null, `password` varchar(191) null, `avatar` varchar(191) null, `status` tinyint not null default '1' comment '1=active, 2=inactive', `last_login_at` timestamp null, `remember_token` varchar(100) null, `created_at` timestamp null, `updated_at` timestamp null, `deleted_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
alter table `users` add unique `users_email_unique`(`email`);
create table `password_resets` (`email` varchar(191) not null, `token` varchar(191) not null, `created_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
alter table `password_resets` add index `password_resets_email_index`(`email`);
create table `permissions` (`id` int unsigned not null auto_increment primary key, `name` varchar(191) not null, `guard_name` varchar(191) not null, `parent_id` int unsigned not null default '0', `sort_order` int unsigned not null default '0', `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
alter table `permissions` add unique `permissions_name_guard_name_unique`(`name`, `guard_name`);
create table `roles` (`id` int unsigned not null auto_increment primary key, `name` varchar(191) not null, `guard_name` varchar(191) not null, `description` varchar(191) null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
alter table `roles` add unique `roles_name_guard_name_unique`(`name`, `guard_name`);
create table `model_has_permissions` (`permission_id` int unsigned not null, `model_type` varchar(191) not null, `model_id` bigint unsigned not null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
alter table `model_has_permissions` add index `model_has_permissions_model_id_model_type_index`(`model_id`, `model_type`);
alter table `model_has_permissions` add constraint `model_has_permissions_permission_id_foreign` foreign key (`permission_id`) references `permissions` (`id`) on delete cascade;
alter table `model_has_permissions` add primary key `model_has_permissions_permission_id_model_id_model_type_primary`(`permission_id`, `model_id`, `model_type`);
create table `model_has_roles` (`role_id` int unsigned not null, `model_type` varchar(191) not null, `model_id` bigint unsigned not null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
alter table `model_has_roles` add index `model_has_roles_model_id_model_type_index`(`model_id`, `model_type`);
alter table `model_has_roles` add constraint `model_has_roles_role_id_foreign` foreign key (`role_id`) references `roles` (`id`) on delete cascade;
alter table `model_has_roles` add primary key `model_has_roles_role_id_model_id_model_type_primary`(`role_id`, `model_id`, `model_type`);
create table `role_has_permissions` (`permission_id` int unsigned not null, `role_id` int unsigned not null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
alter table `role_has_permissions` add constraint `role_has_permissions_permission_id_foreign` foreign key (`permission_id`) references `permissions` (`id`) on delete cascade;
alter table `role_has_permissions` add constraint `role_has_permissions_role_id_foreign` foreign key (`role_id`) references `roles` (`id`) on delete cascade;
alter table `role_has_permissions` add primary key `role_has_permissions_permission_id_role_id_primary`(`permission_id`, `role_id`);
create table `activity_log` (`id` bigint unsigned not null auto_increment primary key, `log_name` varchar(191) null, `description` text not null, `subject_id` bigint unsigned null, `subject_type` varchar(191) null, `causer_id` bigint unsigned null, `causer_type` varchar(191) null, `properties` text null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
alter table `activity_log` add index `subject`(`subject_id`, `subject_type`);
alter table `activity_log` add index `causer`(`causer_id`, `causer_type`);
alter table `activity_log` add index `activity_log_log_name_index`(`log_name`);
create table `settings` (`id` int unsigned not null auto_increment primary key, `group` varchar(50) not null default 'general', `key` varchar(100) not null, `value` text null, `label` varchar(191) not null, `type` varchar(20) not null default 'text' comment 'text, number, time, select, boolean, textarea', `options` text null comment 'JSON list for select inputs', `help` varchar(191) null, `sort_order` int unsigned not null default '0', `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
alter table `settings` add unique `settings_key_unique`(`key`);
create table `shortcuts` (`id` int unsigned not null auto_increment primary key, `name` varchar(191) not null, `action` varchar(50) not null comment 'open_create, save_form, go_dashboard, go_employees, go_attendance', `key_1` varchar(20) not null default 'Ctrl', `key_2` varchar(20) not null, `status` tinyint(1) not null default '1', `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
create table `user_notifications` (`id` bigint unsigned not null auto_increment primary key, `user_id` int unsigned not null, `title` varchar(191) not null, `message` varchar(500) null, `url` varchar(191) null, `type` varchar(30) not null default 'info', `read_at` timestamp null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
alter table `user_notifications` add constraint `user_notifications_user_id_foreign` foreign key (`user_id`) references `users` (`id`) on delete cascade;
alter table `user_notifications` add index `user_notifications_user_id_index`(`user_id`);
create table `departments` (`id` bigint unsigned not null auto_increment primary key, `name` varchar(191) not null, `code` varchar(50) null, `head_employee_id` bigint unsigned null, `description` text null, `status` varchar(20) not null default 'active', `created_by` int unsigned null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
alter table `departments` add unique `departments_code_unique`(`code`);
create table `designations` (`id` bigint unsigned not null auto_increment primary key, `title` varchar(191) not null, `department_id` bigint unsigned null, `status` varchar(20) not null default 'active', `created_by` int unsigned null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
alter table `designations` add constraint `designations_department_id_foreign` foreign key (`department_id`) references `departments` (`id`) on delete set null;
alter table `designations` add index `designations_department_id_index`(`department_id`);
create table `shifts` (`id` bigint unsigned not null auto_increment primary key, `name` varchar(191) not null, `start_time` time not null, `end_time` time not null, `grace_minutes` smallint unsigned not null default '15' comment 'Check-in after start + grace = late', `half_day_after` time null comment 'Check-in after this time = half day', `working_days` varchar(191) not null default 'Mon,Tue,Wed,Thu,Fri,Sat', `is_default` tinyint(1) not null default '0', `status` varchar(20) not null default 'active', `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
create table `employees` (`id` bigint unsigned not null auto_increment primary key, `user_id` int unsigned null, `employee_no` varchar(30) not null, `first_name` varchar(191) not null, `last_name` varchar(191) null, `father_name` varchar(191) null, `cnic` varchar(30) null, `dob` date null, `gender` varchar(10) null, `marital_status` varchar(20) null, `blood_group` varchar(5) null, `phone` varchar(30) null, `alt_phone` varchar(30) null, `email` varchar(191) null, `address` varchar(500) null, `city` varchar(100) null, `emergency_contact_name` varchar(191) null, `emergency_contact_phone` varchar(30) null, `emergency_contact_relation` varchar(50) null, `photo` varchar(191) null, `department_id` bigint unsigned null, `designation_id` bigint unsigned null, `shift_id` bigint unsigned null, `reporting_to` bigint unsigned null comment 'Manager employee id', `employment_type` varchar(20) not null default 'permanent' comment 'permanent, contract, probation, intern, part_time', `joining_date` date null, `confirmation_date` date null, `exit_date` date null, `exit_reason` varchar(191) null, `basic_salary` decimal(12, 2) not null default '0', `payment_method` varchar(20) not null default 'bank' comment 'bank, cash, cheque', `bank_name` varchar(191) null, `bank_account_title` varchar(191) null, `bank_account_no` varchar(50) null, `status` varchar(20) not null default 'active' comment 'active, inactive, resigned, terminated', `created_by` int unsigned null, `created_at` timestamp null, `updated_at` timestamp null, `deleted_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
alter table `employees` add constraint `employees_user_id_foreign` foreign key (`user_id`) references `users` (`id`) on delete set null;
alter table `employees` add constraint `employees_department_id_foreign` foreign key (`department_id`) references `departments` (`id`) on delete set null;
alter table `employees` add constraint `employees_designation_id_foreign` foreign key (`designation_id`) references `designations` (`id`) on delete set null;
alter table `employees` add constraint `employees_shift_id_foreign` foreign key (`shift_id`) references `shifts` (`id`) on delete set null;
alter table `employees` add unique `employees_user_id_unique`(`user_id`);
alter table `employees` add unique `employees_employee_no_unique`(`employee_no`);
alter table `employees` add index `employees_department_id_index`(`department_id`);
alter table `employees` add index `employees_designation_id_index`(`designation_id`);
create table `employee_attendances` (`id` bigint unsigned not null auto_increment primary key, `employee_id` bigint unsigned not null, `date` date not null, `check_in` datetime null, `check_out` datetime null, `worked_minutes` int unsigned not null default '0', `status` varchar(20) not null default 'present' comment 'present, late, half-day, absent, on_leave, holiday', `source` varchar(20) not null default 'self' comment 'self, admin, system, leave', `notes` text null, `marked_by` int unsigned null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
alter table `employee_attendances` add unique `employee_attendances_employee_id_date_unique`(`employee_id`, `date`);
alter table `employee_attendances` add index `employee_attendances_date_index`(`date`);
alter table `employee_attendances` add constraint `employee_attendances_employee_id_foreign` foreign key (`employee_id`) references `employees` (`id`) on delete cascade;
create table `holidays` (`id` bigint unsigned not null auto_increment primary key, `title` varchar(191) not null, `from_date` date not null, `to_date` date not null, `type` varchar(20) not null default 'public' comment 'public, company, optional', `description` varchar(191) null, `created_by` int unsigned null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
alter table `holidays` add index `holidays_from_date_to_date_index`(`from_date`, `to_date`);
create table `leave_types` (`id` bigint unsigned not null auto_increment primary key, `name` varchar(191) not null, `code` varchar(20) null, `days_per_year` decimal(5, 1) not null default '0', `is_paid` tinyint(1) not null default '1', `carry_forward` tinyint(1) not null default '0', `max_carry_forward` decimal(5, 1) not null default '0', `color` varchar(10) not null default '#3366ff', `status` varchar(20) not null default 'active', `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
create table `employee_leaves` (`id` bigint unsigned not null auto_increment primary key, `employee_id` bigint unsigned not null, `leave_type_id` bigint unsigned not null, `from_date` date not null, `to_date` date not null, `half_day` tinyint(1) not null default '0', `days` decimal(5, 1) not null, `reason` text null, `attachment` varchar(191) null, `status` varchar(20) not null default 'pending' comment 'pending, approved, rejected, cancelled', `applied_by` int unsigned null, `approved_by` int unsigned null, `approved_at` timestamp null, `approver_remarks` varchar(191) null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
alter table `employee_leaves` add index `employee_leaves_employee_id_status_index`(`employee_id`, `status`);
alter table `employee_leaves` add constraint `employee_leaves_employee_id_foreign` foreign key (`employee_id`) references `employees` (`id`) on delete cascade;
alter table `employee_leaves` add constraint `employee_leaves_leave_type_id_foreign` foreign key (`leave_type_id`) references `leave_types` (`id`);
create table `salary_components` (`id` bigint unsigned not null auto_increment primary key, `name` varchar(191) not null, `type` varchar(20) not null comment 'allowance, deduction', `calculation` varchar(20) not null default 'fixed' comment 'fixed amount or percentage of basic', `default_value` decimal(12, 2) not null default '0', `status` varchar(20) not null default 'active', `sort_order` int unsigned not null default '0', `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
create table `employee_salary_components` (`id` bigint unsigned not null auto_increment primary key, `employee_id` bigint unsigned not null, `salary_component_id` bigint unsigned not null, `calculation` varchar(20) not null default 'fixed', `value` decimal(12, 2) not null default '0', `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
alter table `employee_salary_components` add unique `emp_component_unique`(`employee_id`, `salary_component_id`);
alter table `employee_salary_components` add constraint `employee_salary_components_employee_id_foreign` foreign key (`employee_id`) references `employees` (`id`) on delete cascade;
alter table `employee_salary_components` add constraint `employee_salary_components_salary_component_id_foreign` foreign key (`salary_component_id`) references `salary_components` (`id`) on delete cascade;
create table `employee_overtimes` (`id` bigint unsigned not null auto_increment primary key, `employee_id` bigint unsigned not null, `year` smallint unsigned not null, `month` tinyint unsigned not null, `ot_hours` decimal(8, 2) not null default '0', `ot_multiplier` decimal(4, 2) not null default '1.5', `ot_amount` decimal(12, 2) not null default '0', `notes` text null, `created_by` int unsigned null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
alter table `employee_overtimes` add unique `employee_overtimes_employee_id_year_month_unique`(`employee_id`, `year`, `month`);
alter table `employee_overtimes` add constraint `employee_overtimes_employee_id_foreign` foreign key (`employee_id`) references `employees` (`id`) on delete cascade;
create table `employee_loans` (`id` bigint unsigned not null auto_increment primary key, `employee_id` bigint unsigned not null, `total_amount` decimal(12, 2) not null, `paid_amount` decimal(12, 2) not null default '0', `monthly_deduction` decimal(12, 2) not null default '0', `start_date` date not null, `end_date` date null, `status` varchar(20) not null default 'active' comment 'active, completed, cancelled', `notes` text null, `created_by` int unsigned null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
alter table `employee_loans` add constraint `employee_loans_employee_id_foreign` foreign key (`employee_id`) references `employees` (`id`) on delete cascade;
create table `employee_advances` (`id` bigint unsigned not null auto_increment primary key, `employee_id` bigint unsigned not null, `amount` decimal(12, 2) not null, `recovered` decimal(12, 2) not null default '0', `monthly_deduction` decimal(12, 2) not null default '0', `given_date` date not null, `status` varchar(20) not null default 'active' comment 'active, recovered, cancelled', `notes` text null, `created_by` int unsigned null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
alter table `employee_advances` add constraint `employee_advances_employee_id_foreign` foreign key (`employee_id`) references `employees` (`id`) on delete cascade;
create table `employee_payrolls` (`id` bigint unsigned not null auto_increment primary key, `employee_id` bigint unsigned not null, `year` smallint unsigned not null, `month` tinyint unsigned not null, `working_days` decimal(5, 1) not null default '0', `present_days` decimal(5, 1) not null default '0', `absent_days` decimal(5, 1) not null default '0', `late_days` smallint unsigned not null default '0', `half_days` smallint unsigned not null default '0', `paid_leave_days` decimal(5, 1) not null default '0', `unpaid_leave_days` decimal(5, 1) not null default '0', `basic_salary` decimal(12, 2) not null default '0', `allowances` decimal(12, 2) not null default '0', `ot_hours` decimal(8, 2) not null default '0', `ot_amount` decimal(12, 2) not null default '0', `bonus_amount` decimal(12, 2) not null default '0', `gross_salary` decimal(12, 2) not null default '0', `attendance_deduction` decimal(12, 2) not null default '0', `component_deductions` decimal(12, 2) not null default '0', `loan_deduction` decimal(12, 2) not null default '0', `advance_deduction` decimal(12, 2) not null default '0', `other_deduction` decimal(12, 2) not null default '0', `deductions` decimal(12, 2) not null default '0', `net_salary` decimal(12, 2) not null default '0', `breakdown` text null comment 'JSON line items for the payslip', `status` varchar(20) not null default 'unpaid' comment 'unpaid, paid', `paid_at` datetime null, `payment_method` varchar(20) null, `payment_reference` varchar(191) null, `generated_by` int unsigned null, `paid_by` int unsigned null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
alter table `employee_payrolls` add unique `employee_payrolls_employee_id_year_month_unique`(`employee_id`, `year`, `month`);
alter table `employee_payrolls` add index `employee_payrolls_year_month_index`(`year`, `month`);
alter table `employee_payrolls` add constraint `employee_payrolls_employee_id_foreign` foreign key (`employee_id`) references `employees` (`id`) on delete cascade;
create table `payroll_adjustments` (`id` bigint unsigned not null auto_increment primary key, `employee_id` bigint unsigned not null, `year` smallint unsigned not null, `month` tinyint unsigned not null, `type` varchar(20) not null comment 'bonus, commission, arrears, fine, other_deduction', `title` varchar(191) not null, `amount` decimal(12, 2) not null, `notes` text null, `payroll_id` bigint unsigned null, `created_by` int unsigned null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
alter table `payroll_adjustments` add index `payroll_adjustments_employee_id_year_month_index`(`employee_id`, `year`, `month`);
alter table `payroll_adjustments` add constraint `payroll_adjustments_employee_id_foreign` foreign key (`employee_id`) references `employees` (`id`) on delete cascade;
create table `payroll_recoveries` (`id` bigint unsigned not null auto_increment primary key, `payroll_id` bigint unsigned not null, `source_type` varchar(20) not null comment 'loan, advance', `source_id` bigint unsigned not null, `amount` decimal(12, 2) not null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
alter table `payroll_recoveries` add index `payroll_recoveries_source_type_source_id_index`(`source_type`, `source_id`);
alter table `payroll_recoveries` add constraint `payroll_recoveries_payroll_id_foreign` foreign key (`payroll_id`) references `employee_payrolls` (`id`) on delete cascade;
create table `salary_revisions` (`id` bigint unsigned not null auto_increment primary key, `employee_id` bigint unsigned not null, `type` varchar(20) not null default 'increment' comment 'increment, promotion, decrement, correction', `effective_date` date not null, `previous_salary` decimal(12, 2) not null default '0', `new_salary` decimal(12, 2) not null default '0', `previous_designation_id` bigint unsigned null, `new_designation_id` bigint unsigned null, `reason` varchar(191) null, `created_by` int unsigned null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
alter table `salary_revisions` add constraint `salary_revisions_employee_id_foreign` foreign key (`employee_id`) references `employees` (`id`) on delete cascade;
create table `employee_documents` (`id` bigint unsigned not null auto_increment primary key, `employee_id` bigint unsigned not null, `title` varchar(191) not null, `document_type` varchar(30) not null default 'other' comment 'cnic, contract, cv, certificate, offer_letter, other', `file_path` varchar(191) not null, `original_name` varchar(191) null, `expiry_date` date null, `notes` varchar(191) null, `uploaded_by` int unsigned null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
alter table `employee_documents` add constraint `employee_documents_employee_id_foreign` foreign key (`employee_id`) references `employees` (`id`) on delete cascade;
create table `announcements` (`id` bigint unsigned not null auto_increment primary key, `title` varchar(191) not null, `body` text not null, `department_id` bigint unsigned null comment 'NULL = everyone', `publish_date` date not null, `expires_at` date null, `is_pinned` tinyint(1) not null default '0', `created_by` int unsigned null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;

-- ---------------- Data ----------------
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES
('1', '2024_01_01_000001_create_users_table', '1'),
('2', '2024_01_01_000002_create_permission_tables', '1'),
('3', '2024_01_01_000003_create_system_tables', '1'),
('4', '2024_01_01_000010_create_organization_tables', '1'),
('5', '2024_01_01_000020_create_time_tables', '1'),
('6', '2024_01_01_000030_create_payroll_tables', '1'),
('7', '2024_01_01_000040_create_engagement_tables', '1');
INSERT INTO `users` (`id`, `name`, `short_name`, `email`, `phone`, `password`, `avatar`, `status`, `last_login_at`, `remember_token`, `created_at`, `updated_at`, `deleted_at`) VALUES
('45', 'Super Admin', 'Admin', 'admin@admin.com', NULL, '$2a$12$sHcUxVPRq3NtOIJKlIu/AOA9eOptVegEWOOEtTyX/92iylvNJdr0O', NULL, '1', NULL, NULL, '2024-07-16 07:15:16', '2024-07-16 07:15:16', NULL),
('80', 'FAHAD', NULL, 'fahad@mdskarachi.com', NULL, '$2y$10$9ru6QQb692zhOpc6/gTzsebiLRnZ5w9j4ujvlGUZXbo4VHdQZ1Xvm', NULL, '1', NULL, NULL, '2025-12-06 18:05:13', '2025-12-06 18:05:13', NULL),
('81', 'UMER ABDUL REHMAN', NULL, 'umer@mdskarachi.com', NULL, '$2y$10$nsTusfD8WGGUi0Vj2UtKtO82j6K0biW6MwE4gusuGfRhz.FW0ILBO', NULL, '1', NULL, NULL, '2025-12-07 15:00:50', '2025-12-07 15:03:35', NULL),
('82', 'SHUKAT', NULL, 'shukat@mdskarachi.com', NULL, '$2y$10$dTXvFsCJS4JNhKTqu1J5ROmHHcy3BfIykS4upW/aQwf65uMXppsE.', NULL, '1', NULL, NULL, '2025-12-07 15:02:52', '2025-12-07 15:02:52', NULL);
INSERT INTO `permissions` (`id`, `name`, `guard_name`, `parent_id`, `sort_order`, `created_at`, `updated_at`) VALUES
('1', 'Dashboard', 'web', '0', '1', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('2', 'HR Dashboard', 'web', '1', '2', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('3', 'HR Dashboard view', 'web', '2', '3', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('4', 'Employee Management', 'web', '0', '4', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('5', 'Employees', 'web', '4', '5', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('6', 'Employees list', 'web', '5', '6', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('7', 'Employees view', 'web', '5', '7', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('8', 'Employees add', 'web', '5', '8', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('9', 'Employees edit', 'web', '5', '9', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('10', 'Employees delete', 'web', '5', '10', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('11', 'Departments', 'web', '4', '11', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('12', 'Departments list', 'web', '11', '12', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('13', 'Departments add', 'web', '11', '13', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('14', 'Departments edit', 'web', '11', '14', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('15', 'Departments delete', 'web', '11', '15', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('16', 'Designations', 'web', '4', '16', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('17', 'Designations list', 'web', '16', '17', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('18', 'Designations add', 'web', '16', '18', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('19', 'Designations edit', 'web', '16', '19', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('20', 'Designations delete', 'web', '16', '20', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('21', 'Employee Documents', 'web', '4', '21', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('22', 'Employee Documents list', 'web', '21', '22', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('23', 'Employee Documents add', 'web', '21', '23', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('24', 'Employee Documents delete', 'web', '21', '24', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('25', 'Salary Revisions', 'web', '4', '25', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('26', 'Salary Revisions list', 'web', '25', '26', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('27', 'Salary Revisions add', 'web', '25', '27', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('28', 'Salary Revisions delete', 'web', '25', '28', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('29', 'Announcements', 'web', '4', '29', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('30', 'Announcements list', 'web', '29', '30', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('31', 'Announcements add', 'web', '29', '31', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('32', 'Announcements edit', 'web', '29', '32', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('33', 'Announcements delete', 'web', '29', '33', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('34', 'Time & Attendance', 'web', '0', '34', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('35', 'Attendance', 'web', '34', '35', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('36', 'Attendance list', 'web', '35', '36', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('37', 'Attendance mark', 'web', '35', '37', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('38', 'Leaves', 'web', '34', '38', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('39', 'Leaves list', 'web', '38', '39', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('40', 'Leaves add', 'web', '38', '40', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('41', 'Leaves approve', 'web', '38', '41', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('42', 'Leaves delete', 'web', '38', '42', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('43', 'Holidays', 'web', '34', '43', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('44', 'Holidays list', 'web', '43', '44', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('45', 'Holidays add', 'web', '43', '45', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('46', 'Holidays edit', 'web', '43', '46', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('47', 'Holidays delete', 'web', '43', '47', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('48', 'Shifts', 'web', '34', '48', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('49', 'Shifts list', 'web', '48', '49', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('50', 'Shifts add', 'web', '48', '50', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('51', 'Shifts edit', 'web', '48', '51', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('52', 'Shifts delete', 'web', '48', '52', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('53', 'Leave Types', 'web', '34', '53', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('54', 'Leave Types list', 'web', '53', '54', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('55', 'Leave Types add', 'web', '53', '55', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('56', 'Leave Types edit', 'web', '53', '56', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('57', 'Leave Types delete', 'web', '53', '57', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('58', 'Payroll', 'web', '0', '58', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('59', 'Employee Payrolls', 'web', '58', '59', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('60', 'Employee Payrolls list', 'web', '59', '60', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('61', 'Employee Payrolls generate', 'web', '59', '61', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('62', 'Employee Payrolls pay', 'web', '59', '62', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('63', 'Employee Payrolls delete', 'web', '59', '63', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('64', 'Employee Payrolls payslip', 'web', '59', '64', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('65', 'Salary Components', 'web', '58', '65', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('66', 'Salary Components list', 'web', '65', '66', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('67', 'Salary Components add', 'web', '65', '67', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('68', 'Salary Components edit', 'web', '65', '68', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('69', 'Salary Components delete', 'web', '65', '69', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('70', 'Payroll Adjustments', 'web', '58', '70', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('71', 'Payroll Adjustments list', 'web', '70', '71', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('72', 'Payroll Adjustments add', 'web', '70', '72', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('73', 'Payroll Adjustments edit', 'web', '70', '73', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('74', 'Payroll Adjustments delete', 'web', '70', '74', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('75', 'Employee Overtimes', 'web', '58', '75', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('76', 'Employee Overtimes list', 'web', '75', '76', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('77', 'Employee Overtimes add', 'web', '75', '77', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('78', 'Employee Overtimes edit', 'web', '75', '78', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('79', 'Employee Overtimes delete', 'web', '75', '79', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('80', 'Employee Advances', 'web', '58', '80', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('81', 'Employee Advances list', 'web', '80', '81', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('82', 'Employee Advances add', 'web', '80', '82', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('83', 'Employee Advances edit', 'web', '80', '83', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('84', 'Employee Advances delete', 'web', '80', '84', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('85', 'Employee Loans', 'web', '58', '85', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('86', 'Employee Loans list', 'web', '85', '86', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('87', 'Employee Loans add', 'web', '85', '87', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('88', 'Employee Loans edit', 'web', '85', '88', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('89', 'Employee Loans delete', 'web', '85', '89', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('90', 'Reports', 'web', '0', '90', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('91', 'Reports Management', 'web', '90', '91', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('92', 'Reports Management Attendance Summary', 'web', '91', '92', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('93', 'Reports Management Leave Report', 'web', '91', '93', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('94', 'Reports Management Payroll History', 'web', '91', '94', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('95', 'Reports Management Payroll Register', 'web', '91', '95', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('96', 'Reports Management Loan Advance', 'web', '91', '96', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('97', 'Reports Management Headcount', 'web', '91', '97', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('98', 'Administration', 'web', '0', '98', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('99', 'Users Management', 'web', '98', '99', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('100', 'Users Management list', 'web', '99', '100', '2026-10-29 13:47:54', '2026-10-29 13:47:54');
INSERT INTO `permissions` (`id`, `name`, `guard_name`, `parent_id`, `sort_order`, `created_at`, `updated_at`) VALUES
('101', 'Users Management add', 'web', '99', '101', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('102', 'Users Management edit', 'web', '99', '102', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('103', 'Users Management delete', 'web', '99', '103', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('104', 'Roles Management', 'web', '98', '104', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('105', 'Roles Management list', 'web', '104', '105', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('106', 'Roles Management add', 'web', '104', '106', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('107', 'Roles Management edit', 'web', '104', '107', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('108', 'Roles Management delete', 'web', '104', '108', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('109', 'Roles Permissions', 'web', '98', '109', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('110', 'Roles Permissions list', 'web', '109', '110', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('111', 'Roles Permissions edit', 'web', '109', '111', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('112', 'Audit Log', 'web', '98', '112', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('113', 'Audit Log list', 'web', '112', '113', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('114', 'Preferences', 'web', '0', '114', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('115', 'General Preferences', 'web', '114', '115', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('116', 'General Preferences list', 'web', '115', '116', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('117', 'General Preferences edit', 'web', '115', '117', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('118', 'Shortcut Preferences', 'web', '114', '118', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('119', 'Shortcut Preferences list', 'web', '118', '119', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('120', 'Shortcut Preferences add', 'web', '118', '120', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('121', 'Shortcut Preferences edit', 'web', '118', '121', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('122', 'Shortcut Preferences delete', 'web', '118', '122', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('123', 'Self Service', 'web', '0', '123', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('124', 'My HR', 'web', '123', '124', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('125', 'My HR view', 'web', '124', '125', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('126', 'My HR check in', 'web', '124', '126', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('127', 'My HR apply leave', 'web', '124', '127', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('128', 'My HR payslips', 'web', '124', '128', '2026-10-29 13:47:54', '2026-10-29 13:47:54');
INSERT INTO `roles` (`id`, `name`, `guard_name`, `description`, `created_at`, `updated_at`) VALUES
('1', 'Super-Admin', 'web', 'Full access to everything', '2024-07-18 14:24:28', '2026-10-29 13:47:55'),
('5', 'Accountant', 'web', 'Payroll, loans, advances and payroll reports', '2025-12-03 12:05:24', '2026-10-29 13:47:55'),
('6', 'USER', 'web', 'Legacy role from the old CRM – self service only', '2025-12-07 15:06:43', '2026-10-29 13:47:55'),
('7', 'HR Manager', 'web', 'Runs HR, attendance, leave and payroll (no user / role admin)', '2026-10-29 13:47:55', '2026-10-29 13:47:55'),
('8', 'Employee', 'web', 'Self service: check-in, leave requests, own payslips', '2026-10-29 13:47:55', '2026-10-29 13:47:55');
INSERT INTO `model_has_roles` (`role_id`, `model_type`, `model_id`) VALUES
('1', 'App\\User', '45'),
('5', 'App\\User', '80'),
('5', 'App\\User', '81'),
('5', 'App\\User', '82');
INSERT INTO `role_has_permissions` (`permission_id`, `role_id`) VALUES
('1', '1'),
('2', '1'),
('3', '1'),
('4', '1'),
('5', '1'),
('6', '1'),
('7', '1'),
('8', '1'),
('9', '1'),
('10', '1'),
('11', '1'),
('12', '1'),
('13', '1'),
('14', '1'),
('15', '1'),
('16', '1'),
('17', '1'),
('18', '1'),
('19', '1'),
('20', '1'),
('21', '1'),
('22', '1'),
('23', '1'),
('24', '1'),
('25', '1'),
('26', '1'),
('27', '1'),
('28', '1'),
('29', '1'),
('30', '1'),
('31', '1'),
('32', '1'),
('33', '1'),
('34', '1'),
('35', '1'),
('36', '1'),
('37', '1'),
('38', '1'),
('39', '1'),
('40', '1'),
('41', '1'),
('42', '1'),
('43', '1'),
('44', '1'),
('45', '1'),
('46', '1'),
('47', '1'),
('48', '1'),
('49', '1'),
('50', '1'),
('51', '1'),
('52', '1'),
('53', '1'),
('54', '1'),
('55', '1'),
('56', '1'),
('57', '1'),
('58', '1'),
('59', '1'),
('60', '1'),
('61', '1'),
('62', '1'),
('63', '1'),
('64', '1'),
('65', '1'),
('66', '1'),
('67', '1'),
('68', '1'),
('69', '1'),
('70', '1'),
('71', '1'),
('72', '1'),
('73', '1'),
('74', '1'),
('75', '1'),
('76', '1'),
('77', '1'),
('78', '1'),
('79', '1'),
('80', '1'),
('81', '1'),
('82', '1'),
('83', '1'),
('84', '1'),
('85', '1'),
('86', '1'),
('87', '1'),
('88', '1'),
('89', '1'),
('90', '1'),
('91', '1'),
('92', '1'),
('93', '1'),
('94', '1'),
('95', '1'),
('96', '1'),
('97', '1'),
('98', '1'),
('99', '1'),
('100', '1');
INSERT INTO `role_has_permissions` (`permission_id`, `role_id`) VALUES
('101', '1'),
('102', '1'),
('103', '1'),
('104', '1'),
('105', '1'),
('106', '1'),
('107', '1'),
('108', '1'),
('109', '1'),
('110', '1'),
('111', '1'),
('112', '1'),
('113', '1'),
('114', '1'),
('115', '1'),
('116', '1'),
('117', '1'),
('118', '1'),
('119', '1'),
('120', '1'),
('121', '1'),
('122', '1'),
('123', '1'),
('124', '1'),
('125', '1'),
('126', '1'),
('127', '1'),
('128', '1'),
('1', '7'),
('2', '7'),
('3', '7'),
('4', '7'),
('5', '7'),
('6', '7'),
('7', '7'),
('8', '7'),
('9', '7'),
('10', '7'),
('11', '7'),
('12', '7'),
('13', '7'),
('14', '7'),
('15', '7'),
('16', '7'),
('17', '7'),
('18', '7'),
('19', '7'),
('20', '7'),
('21', '7'),
('22', '7'),
('23', '7'),
('24', '7'),
('25', '7'),
('26', '7'),
('27', '7'),
('28', '7'),
('29', '7'),
('30', '7'),
('31', '7'),
('32', '7'),
('33', '7'),
('34', '7'),
('35', '7'),
('36', '7'),
('37', '7'),
('38', '7'),
('39', '7'),
('40', '7'),
('41', '7'),
('42', '7'),
('43', '7'),
('44', '7'),
('45', '7'),
('46', '7'),
('47', '7'),
('48', '7'),
('49', '7'),
('50', '7'),
('51', '7'),
('52', '7'),
('53', '7'),
('54', '7'),
('55', '7'),
('56', '7'),
('57', '7'),
('58', '7'),
('59', '7'),
('60', '7'),
('61', '7'),
('62', '7'),
('63', '7'),
('64', '7'),
('65', '7'),
('66', '7'),
('67', '7'),
('68', '7'),
('69', '7'),
('70', '7'),
('71', '7'),
('72', '7');
INSERT INTO `role_has_permissions` (`permission_id`, `role_id`) VALUES
('73', '7'),
('74', '7'),
('75', '7'),
('76', '7'),
('77', '7'),
('78', '7'),
('79', '7'),
('80', '7'),
('81', '7'),
('82', '7'),
('83', '7'),
('84', '7'),
('85', '7'),
('86', '7'),
('87', '7'),
('88', '7'),
('89', '7'),
('90', '7'),
('91', '7'),
('92', '7'),
('93', '7'),
('94', '7'),
('95', '7'),
('96', '7'),
('97', '7'),
('114', '7'),
('115', '7'),
('116', '7'),
('117', '7'),
('118', '7'),
('119', '7'),
('120', '7'),
('121', '7'),
('122', '7'),
('123', '7'),
('124', '7'),
('125', '7'),
('126', '7'),
('127', '7'),
('128', '7'),
('1', '5'),
('2', '5'),
('3', '5'),
('58', '5'),
('59', '5'),
('60', '5'),
('61', '5'),
('62', '5'),
('63', '5'),
('64', '5'),
('65', '5'),
('66', '5'),
('67', '5'),
('68', '5'),
('69', '5'),
('70', '5'),
('71', '5'),
('72', '5'),
('73', '5'),
('74', '5'),
('75', '5'),
('76', '5'),
('77', '5'),
('78', '5'),
('79', '5'),
('80', '5'),
('81', '5'),
('82', '5'),
('83', '5'),
('84', '5'),
('85', '5'),
('86', '5'),
('87', '5'),
('88', '5'),
('89', '5'),
('123', '5'),
('124', '5'),
('125', '5'),
('126', '5'),
('127', '5'),
('128', '5'),
('5', '5'),
('6', '5'),
('7', '5'),
('90', '5'),
('91', '5'),
('94', '5'),
('95', '5'),
('96', '5'),
('123', '8'),
('124', '8'),
('125', '8'),
('126', '8'),
('127', '8'),
('128', '8'),
('123', '6'),
('124', '6'),
('125', '6'),
('126', '6'),
('127', '6');
INSERT INTO `role_has_permissions` (`permission_id`, `role_id`) VALUES
('128', '6');
INSERT INTO `settings` (`id`, `group`, `key`, `value`, `label`, `type`, `options`, `help`, `sort_order`, `created_at`, `updated_at`) VALUES
('1', 'company', 'company_name', 'M.D.S Group of Companies', 'Company name', 'text', NULL, 'Shown on payslips, reports and the login page', '1', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('2', 'company', 'company_address', '', 'Company address', 'textarea', NULL, NULL, '2', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('3', 'company', 'company_phone', '', 'Company phone', 'text', NULL, NULL, '3', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('4', 'company', 'company_email', '', 'Company email', 'text', NULL, NULL, '4', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('5', 'company', 'currency_symbol', 'Rs.', 'Currency symbol', 'text', NULL, NULL, '5', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('6', 'company', 'date_format', 'd-m-Y', 'Date format', 'select', '{"d-m-Y":"31-12-2026","d\\/m\\/Y":"31\\/12\\/2026","Y-m-d":"2026-12-31","d M Y":"31 Dec 2026"}', NULL, '6', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('7', 'company', 'employee_no_prefix', 'EMP', 'Employee number prefix', 'text', NULL, 'New employees get PREFIX00001, PREFIX00002 …', '7', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('8', 'attendance', 'allow_self_checkin', '1', 'Allow employees to check in / out themselves', 'boolean', NULL, 'From the dashboard or My HR', '8', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('9', 'attendance', 'auto_absent_unmarked', '0', 'Treat unmarked working days as absent', 'boolean', NULL, 'When on, payroll deducts working days with no check-in, leave or holiday, and the nightly job marks them absent', '9', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('10', 'payroll', 'salary_day_basis', 'calendar', 'Per-day salary basis', 'select', '{"calendar":"Basic \\u00f7 days in month","working":"Basic \\u00f7 working days per month"}', 'Used for absent / late / unpaid-leave deductions', '10', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('11', 'payroll', 'working_days_per_month', '26', 'Working days per month', 'number', NULL, 'Used for the overtime hourly rate', '11', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('12', 'payroll', 'daily_hours', '8', 'Working hours per day', 'number', NULL, 'Hourly rate = basic ÷ (working days × hours)', '12', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('13', 'payroll', 'absent_deduction_fraction', '1', 'Deduction per absent day (days)', 'number', NULL, '1 = one full day of pay', '13', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('14', 'payroll', 'late_deduction_fraction', '0.5', 'Deduction per late arrival (days)', 'number', NULL, '0 = no deduction for lates', '14', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('15', 'payroll', 'half_day_deduction_fraction', '0.5', 'Deduction per half day (days)', 'number', NULL, NULL, '15', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('16', 'payroll', 'default_ot_multiplier', '1.5', 'Default overtime multiplier', 'number', NULL, NULL, '16', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('17', 'payroll', 'prorate_new_joiners', '1', 'Pro-rate salary for mid-month joiners / leavers', 'boolean', NULL, NULL, '17', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('18', 'leave', 'leave_year_start_month', '1', 'Leave year starts in', 'select', '{"1":"January","2":"February","3":"March","4":"April","5":"May","6":"June","7":"July","8":"August","9":"September","10":"October","11":"November","12":"December"}', 'Leave balances reset at the start of this month', '18', '2026-10-29 13:47:54', '2026-10-29 13:47:54'),
('19', 'leave', 'allow_negative_leave_balance', '0', 'Allow applying beyond leave balance', 'boolean', NULL, NULL, '19', '2026-10-29 13:47:54', '2026-10-29 13:47:54');
INSERT INTO `shortcuts` (`id`, `name`, `action`, `key_1`, `key_2`, `status`, `created_at`, `updated_at`) VALUES
('1', 'Open Insert Screen', 'open_create', 'Ctrl', 'B', '1', '2024-11-22 18:30:49', '2024-12-26 07:18:14'),
('2', 'Save Form', 'save_form', 'Ctrl', 'S', '1', '2024-11-22 18:42:58', '2024-11-22 18:42:58'),
('3', 'My HR', 'go_my_hr', 'Alt', 'M', '1', '2026-10-29 13:47:54', '2026-10-29 13:47:54');
INSERT INTO `departments` (`id`, `name`, `code`, `head_employee_id`, `description`, `status`, `created_by`, `created_at`, `updated_at`) VALUES
('4', 'Admin', 'Admin', NULL, NULL, 'active', NULL, '2025-11-15 22:46:56', '2025-12-04 06:43:09');
INSERT INTO `designations` (`id`, `title`, `department_id`, `status`, `created_by`, `created_at`, `updated_at`) VALUES
('5', 'Accountant', '4', 'active', '45', '2025-11-15 22:47:18', '2025-11-15 22:47:18'),
('7', 'Senior Accountant', NULL, 'active', '45', '2025-12-03 00:46:51', '2025-12-03 00:46:51');
INSERT INTO `shifts` (`id`, `name`, `start_time`, `end_time`, `grace_minutes`, `half_day_after`, `working_days`, `is_default`, `status`, `created_at`, `updated_at`) VALUES
('1', 'General Shift', '11:00:00', '19:00:00', '30', '12:00:00', 'Mon,Tue,Wed,Thu,Fri,Sat', '1', 'active', '2026-10-29 13:47:55', '2026-10-29 13:47:55');
INSERT INTO `employees` (`id`, `user_id`, `employee_no`, `first_name`, `last_name`, `father_name`, `cnic`, `dob`, `gender`, `marital_status`, `blood_group`, `phone`, `alt_phone`, `email`, `address`, `city`, `emergency_contact_name`, `emergency_contact_phone`, `emergency_contact_relation`, `photo`, `department_id`, `designation_id`, `shift_id`, `reporting_to`, `employment_type`, `joining_date`, `confirmation_date`, `exit_date`, `exit_reason`, `basic_salary`, `payment_method`, `bank_name`, `bank_account_title`, `bank_account_no`, `status`, `created_by`, `created_at`, `updated_at`, `deleted_at`) VALUES
('7', NULL, 'EMP00001', 'Fahad', 'Iqbal', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '4', '5', NULL, NULL, 'permanent', NULL, NULL, NULL, NULL, '50000', 'bank', NULL, NULL, NULL, 'active', '45', '2025-11-15 23:01:07', '2025-12-03 12:06:17', NULL);
INSERT INTO `leave_types` (`id`, `name`, `code`, `days_per_year`, `is_paid`, `carry_forward`, `max_carry_forward`, `color`, `status`, `created_at`, `updated_at`) VALUES
('1', 'Annual Leave', 'AL', '14', '1', '1', '7', '#3366ff', 'active', '2026-10-29 13:47:55', '2026-10-29 13:47:55'),
('2', 'Casual Leave', 'CL', '10', '1', '0', '0', '#00b8d4', 'active', '2026-10-29 13:47:55', '2026-10-29 13:47:55'),
('3', 'Sick Leave', 'SL', '8', '1', '0', '0', '#f59e0b', 'active', '2026-10-29 13:47:55', '2026-10-29 13:47:55'),
('4', 'Unpaid Leave', 'UL', '0', '0', '0', '0', '#94a3b8', 'active', '2026-10-29 13:47:55', '2026-10-29 13:47:55');
INSERT INTO `salary_components` (`id`, `name`, `type`, `calculation`, `default_value`, `status`, `sort_order`, `created_at`, `updated_at`) VALUES
('1', 'House Rent Allowance', 'allowance', 'percentage', '45', 'active', '1', '2026-10-29 13:47:55', '2026-10-29 13:47:55'),
('2', 'Medical Allowance', 'allowance', 'percentage', '10', 'active', '2', '2026-10-29 13:47:55', '2026-10-29 13:47:55'),
('3', 'Conveyance Allowance', 'allowance', 'fixed', '0', 'active', '3', '2026-10-29 13:47:55', '2026-10-29 13:47:55'),
('4', 'Mobile Allowance', 'allowance', 'fixed', '0', 'active', '4', '2026-10-29 13:47:55', '2026-10-29 13:47:55'),
('5', 'Income Tax', 'deduction', 'fixed', '0', 'active', '5', '2026-10-29 13:47:55', '2026-10-29 13:47:55'),
('6', 'EOBI Contribution', 'deduction', 'fixed', '0', 'active', '6', '2026-10-29 13:47:55', '2026-10-29 13:47:55'),
('7', 'Provident Fund', 'deduction', 'percentage', '0', 'active', '7', '2026-10-29 13:47:55', '2026-10-29 13:47:55');
INSERT INTO `employee_payrolls` (`id`, `employee_id`, `year`, `month`, `working_days`, `present_days`, `absent_days`, `late_days`, `half_days`, `paid_leave_days`, `unpaid_leave_days`, `basic_salary`, `allowances`, `ot_hours`, `ot_amount`, `bonus_amount`, `gross_salary`, `attendance_deduction`, `component_deductions`, `loan_deduction`, `advance_deduction`, `other_deduction`, `deductions`, `net_salary`, `breakdown`, `status`, `paid_at`, `payment_method`, `payment_reference`, `generated_by`, `paid_by`, `created_at`, `updated_at`) VALUES
('9', '7', '2025', '11', '0', '0', '0', '0', '0', '0', '0', '50000', '0', '0', '0', '0', '50000', '0', '0', '0', '0', '0', '0', '50000', NULL, 'unpaid', NULL, NULL, NULL, NULL, NULL, '2025-11-15 23:02:14', '2025-11-15 23:02:14');

SET FOREIGN_KEY_CHECKS = 1;