-- Workbee — Complete database schema + demo data
-- Generated fresh for distribution. Imported automatically by /install step 3.
--
-- Demo accounts (change passwords after install):
--   Admin:    anita@brightline.studio / Workbee@2026
--   Employee: priya@brightline.studio / Priya@2026


--
-- Host: localhost    Database: workbee
-- ------------------------------------------------------


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

CREATE TABLE `activity_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `action` varchar(60) NOT NULL COMMENT 'create·update·delete·login·run',
  `target_type` varchar(60) DEFAULT NULL,
  `target_id` bigint(20) unsigned DEFAULT NULL,
  `summary` varchar(500) NOT NULL,
  `before_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`before_json`)),
  `after_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`after_json`)),
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_log_company_date` (`company_id`,`created_at`),
  KEY `idx_log_user` (`user_id`,`created_at`),
  KEY `idx_log_target` (`target_type`,`target_id`),
  CONSTRAINT `fk_log_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_log_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `activity_logs`
--


--
-- Table structure for table `ai_policy_docs`
--

CREATE TABLE `ai_policy_docs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `title` varchar(200) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `mime_type` varchar(120) DEFAULT NULL,
  `size_bytes` int(10) unsigned DEFAULT NULL,
  `indexed_at` timestamp NULL DEFAULT NULL,
  `embedding_chunks` int(10) unsigned DEFAULT NULL,
  `status` enum('pending','indexing','indexed','failed') NOT NULL DEFAULT 'pending',
  `uploaded_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_apd_company_status` (`company_id`,`status`,`deleted_at`),
  CONSTRAINT `fk_apd_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `ai_policy_docs`
--


--
-- Table structure for table `ai_prompts`
--

CREATE TABLE `ai_prompts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `feature` varchar(60) NOT NULL COMMENT 'policy_assistant·anomaly·doc_gen·resume·narration',
  `prompt_template` mediumtext NOT NULL,
  `temperature` decimal(3,2) NOT NULL DEFAULT 0.70,
  `max_tokens` smallint(5) unsigned DEFAULT 2000,
  `is_default` tinyint(1) NOT NULL DEFAULT 1,
  `updated_by` bigint(20) unsigned DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ap_company_feature` (`company_id`,`feature`),
  CONSTRAINT `fk_ap_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `ai_prompts`
--

INSERT INTO `ai_prompts` (`id`, `company_id`, `feature`, `prompt_template`, `temperature`, `max_tokens`, `is_default`, `updated_by`, `updated_at`) VALUES (1,1,'policy_assistant','You are Workbee, a warm HR assistant for {{company.name}}. Answer using only verified policy docs and the user\'s visible data. Always cite sources.',0.30,2000,1,NULL,'2026-04-22 05:27:06'),(2,1,'anomaly','Scan this payroll grid against the previous 3 months. Flag employees with net pay delta > 25%, missing bank accounts, or unusual LOP. Output JSON.',0.10,2000,1,NULL,'2026-04-22 05:27:06'),(3,1,'doc_gen','Draft a formal {{template}} for {{employee.name}}. Warm professional tone. Populate merge fields from context.',0.60,2000,1,NULL,'2026-04-22 05:27:06'),(4,1,'resume','Extract structured fields from this resume: name, email, phone, experience[], education[], skills[]. Output strict JSON matching the schema.',0.20,2000,1,NULL,'2026-04-22 05:27:06'),(5,1,'narration','Summarise this report in 3 paragraphs: headline number, one anomaly, one recommended action.',0.50,2000,1,NULL,'2026-04-22 05:27:06');

--
-- Table structure for table `ai_settings`
--

CREATE TABLE `ai_settings` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `provider` enum('openai','anthropic','google','azure','ollama','disabled') NOT NULL DEFAULT 'disabled',
  `model` varchar(80) DEFAULT NULL,
  `api_key_enc` varbinary(1024) DEFAULT NULL COMMENT 'AES-256 encrypted',
  `endpoint_url` varchar(255) DEFAULT NULL,
  `monthly_budget_usd` decimal(10,2) DEFAULT NULL,
  `pii_redaction` tinyint(1) NOT NULL DEFAULT 0,
  `features_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'per-feature on/off' CHECK (json_valid(`features_json`)),
  `paused_until` timestamp NULL DEFAULT NULL COMMENT 'auto-pause on budget exceeded',
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ai_company` (`company_id`),
  CONSTRAINT `fk_ai_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `ai_settings`
--


--
-- Table structure for table `ai_usage_logs`
--

CREATE TABLE `ai_usage_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `feature` varchar(60) NOT NULL,
  `model` varchar(80) NOT NULL,
  `input_tokens` int(10) unsigned NOT NULL DEFAULT 0,
  `output_tokens` int(10) unsigned NOT NULL DEFAULT 0,
  `cost_usd` decimal(10,6) NOT NULL DEFAULT 0.000000,
  `latency_ms` smallint(5) unsigned DEFAULT NULL,
  `prompt_preview` varchar(500) DEFAULT NULL,
  `response_preview` varchar(500) DEFAULT NULL,
  `function_calls_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`function_calls_json`)),
  `success` tinyint(1) NOT NULL DEFAULT 1,
  `error_message` varchar(500) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_ai_log_company_date` (`company_id`,`created_at`),
  KEY `idx_ai_log_feature` (`feature`,`created_at`),
  KEY `fk_ai_log_user` (`user_id`),
  CONSTRAINT `fk_ai_log_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ai_log_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `ai_usage_logs`
--


--
-- Table structure for table `announcement_reads`
--

CREATE TABLE `announcement_reads` (
  `announcement_id` bigint(20) unsigned NOT NULL,
  `employee_id` bigint(20) unsigned NOT NULL,
  `read_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `acknowledged_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`announcement_id`,`employee_id`),
  KEY `idx_ar_employee` (`employee_id`),
  CONSTRAINT `fk_ar_announcement` FOREIGN KEY (`announcement_id`) REFERENCES `announcements` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ar_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `announcement_reads`
--

INSERT INTO `announcement_reads` (`announcement_id`, `employee_id`, `read_at`, `acknowledged_at`) VALUES (1,1,'2026-04-21 14:22:32',NULL),(1,2,'2026-04-21 19:22:32',NULL),(2,1,'2026-04-20 10:22:32','2026-04-20 10:22:32'),(2,2,'2026-04-21 10:22:32','2026-04-22 09:24:42'),(3,2,'2026-04-22 09:24:42',NULL);

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

CREATE TABLE `announcements` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `author_id` bigint(20) unsigned DEFAULT NULL,
  `title` varchar(200) NOT NULL,
  `body_html` mediumtext NOT NULL,
  `audience_type` enum('all','department','branch','grade','employees') NOT NULL DEFAULT 'all',
  `audience_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`audience_json`)),
  `pinned` tinyint(1) NOT NULL DEFAULT 0,
  `require_acknowledgement` tinyint(1) NOT NULL DEFAULT 0,
  `email_sent` tinyint(1) NOT NULL DEFAULT 0,
  `push_sent` tinyint(1) NOT NULL DEFAULT 0,
  `published_at` timestamp NULL DEFAULT NULL,
  `status` enum('draft','scheduled','published','archived') NOT NULL DEFAULT 'draft',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_ann_company_published` (`company_id`,`published_at`,`status`),
  KEY `fk_ann_author` (`author_id`),
  CONSTRAINT `fk_ann_author` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_ann_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `announcements`
--

INSERT INTO `announcements` (`id`, `company_id`, `author_id`, `title`, `body_html`, `audience_type`, `audience_json`, `pinned`, `require_acknowledgement`, `email_sent`, `push_sent`, `published_at`, `status`, `created_at`, `updated_at`) VALUES (1,1,1,'Welcome to Q2 2026','<p>Team — Q1 wrap-up calls this Friday at 4pm GST. Strong revenue quarter, thanks to everyone!</p>','all',NULL,1,0,0,0,'2026-04-21 10:22:32','published','2026-04-21 10:22:32','2026-04-22 10:22:32'),(2,1,1,'Updated leave policy — please read','<p>Carry-forward cap is now 10 days/year (was 15). Please <b>acknowledge</b> by end of month so we have a record.</p>','all',NULL,0,1,0,0,'2026-04-19 10:22:32','published','2026-04-19 10:22:32','2026-04-22 10:22:32'),(3,1,1,'Office closed Monday (Eid holiday)','<p>Dubai office will be closed Monday for Eid. WFH if urgent.</p>','branch',NULL,0,0,0,0,'2026-04-17 10:22:32','published','2026-04-17 10:22:32','2026-04-22 10:22:32'),(4,1,1,'Draft · New health insurance','<p>Draft copy — finalize with Aetna contact details before publishing.</p>','all',NULL,0,0,0,0,'2026-04-22 09:24:41','published','2026-04-22 08:22:32','2026-04-22 09:24:41'),(5,1,1,'Test ann (renamed)','<p>Hello</p>','all',NULL,1,1,0,0,'2026-04-22 09:24:41','archived','2026-04-22 09:24:41','2026-04-22 09:24:41');

--
-- Table structure for table `assets`
--

CREATE TABLE `assets` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `asset_type` varchar(40) NOT NULL,
  `name` varchar(200) NOT NULL,
  `serial_number` varchar(120) DEFAULT NULL,
  `value` decimal(14,2) DEFAULT NULL,
  `currency` char(3) DEFAULT NULL,
  `purchased_at` date DEFAULT NULL,
  `status` enum('in_stock','assigned','in_repair','retired','lost') NOT NULL DEFAULT 'in_stock',
  `notes` text DEFAULT NULL,
  `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 `uq_assets_company_serial` (`company_id`,`serial_number`),
  KEY `idx_assets_status` (`company_id`,`status`),
  CONSTRAINT `fk_assets_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `assets`
--


--
-- Table structure for table `attendance`
--

CREATE TABLE `attendance` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) unsigned NOT NULL,
  `company_id` bigint(20) unsigned NOT NULL,
  `work_date` date NOT NULL,
  `shift_id` bigint(20) unsigned DEFAULT NULL,
  `check_in_at` timestamp NULL DEFAULT NULL,
  `check_out_at` timestamp NULL DEFAULT NULL,
  `check_in_tz` varchar(64) DEFAULT NULL,
  `work_mode` enum('office','wfh','client_site','travel') NOT NULL DEFAULT 'office',
  `status` enum('present','absent','half_day','on_leave','holiday','weekly_off','regularised') NOT NULL DEFAULT 'present',
  `hours_worked` decimal(5,2) DEFAULT NULL,
  `overtime_hours` decimal(5,2) DEFAULT 0.00,
  `late_minutes` smallint(5) unsigned DEFAULT 0,
  `early_out_minutes` smallint(5) unsigned DEFAULT 0,
  `check_in_lat` decimal(10,7) DEFAULT NULL,
  `check_in_lng` decimal(10,7) DEFAULT NULL,
  `check_out_lat` decimal(10,7) DEFAULT NULL,
  `check_out_lng` decimal(10,7) DEFAULT NULL,
  `selfie_path` varchar(255) DEFAULT NULL,
  `geofence_passed` tinyint(1) DEFAULT NULL,
  `source` enum('web','pwa','mobile','kiosk','biometric','manual','import') NOT NULL DEFAULT 'web',
  `notes` varchar(255) DEFAULT NULL,
  `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 `uq_attendance_emp_date` (`employee_id`,`work_date`),
  KEY `idx_att_company_date` (`company_id`,`work_date`),
  KEY `idx_att_status` (`status`,`work_date`),
  KEY `fk_att_shift` (`shift_id`),
  CONSTRAINT `fk_att_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_att_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_att_shift` FOREIGN KEY (`shift_id`) REFERENCES `shifts` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `attendance`
--

INSERT INTO `attendance` (`id`, `employee_id`, `company_id`, `work_date`, `shift_id`, `check_in_at`, `check_out_at`, `check_in_tz`, `work_mode`, `status`, `hours_worked`, `overtime_hours`, `late_minutes`, `early_out_minutes`, `check_in_lat`, `check_in_lng`, `check_out_lat`, `check_out_lng`, `selfie_path`, `geofence_passed`, `source`, `notes`, `created_at`, `updated_at`) VALUES (1,1,1,'2026-04-22',NULL,'2026-04-22 08:02:11','2026-04-22 17:04:22',NULL,'office','present',9.00,0.00,2,0,NULL,NULL,NULL,NULL,NULL,NULL,'web',NULL,'2026-04-22 08:50:38','2026-04-22 08:50:38'),(2,2,1,'2026-04-22',NULL,'2026-04-22 07:55:42',NULL,NULL,'wfh','present',5.50,0.00,0,0,NULL,NULL,NULL,NULL,NULL,NULL,'web',NULL,'2026-04-22 08:50:38','2026-04-22 08:50:38'),(3,3,1,'2026-04-22',NULL,'2026-04-22 09:18:05','2026-04-22 18:10:11',NULL,'office','present',8.87,0.00,18,0,NULL,NULL,NULL,NULL,NULL,NULL,'web',NULL,'2026-04-22 08:50:38','2026-04-22 08:50:38'),(4,4,1,'2026-04-22',NULL,NULL,NULL,NULL,'office','on_leave',0.00,0.00,0,0,NULL,NULL,NULL,NULL,NULL,NULL,'web',NULL,'2026-04-22 08:50:38','2026-04-22 08:50:38'),(5,1,1,'2026-04-21',NULL,'2026-04-21 08:00:00','2026-04-21 17:00:00',NULL,'office','regularised',9.00,0.00,0,0,NULL,NULL,NULL,NULL,NULL,NULL,'web',NULL,'2026-04-22 08:50:38','2026-04-22 07:53:41'),(6,2,1,'2026-04-21',NULL,'2026-04-21 08:00:00','2026-04-21 17:02:45',NULL,'wfh','present',9.04,0.00,0,0,NULL,NULL,NULL,NULL,NULL,NULL,'web',NULL,'2026-04-22 08:50:38','2026-04-22 08:50:38'),(7,3,1,'2026-04-21',NULL,'2026-04-21 08:12:33','2026-04-21 17:30:05',NULL,'office','present',9.29,0.00,12,0,NULL,NULL,NULL,NULL,NULL,NULL,'web',NULL,'2026-04-22 08:50:38','2026-04-22 08:50:38'),(8,4,1,'2026-04-21',NULL,'2026-04-21 08:05:00','2026-04-21 16:58:00',NULL,'office','present',8.88,0.00,5,0,NULL,NULL,NULL,NULL,NULL,NULL,'web',NULL,'2026-04-22 08:50:38','2026-04-22 08:50:38'),(9,1,1,'2026-04-20',NULL,'2026-04-20 07:59:00','2026-04-20 16:55:00',NULL,'office','present',8.93,0.00,0,0,NULL,NULL,NULL,NULL,NULL,NULL,'web',NULL,'2026-04-22 08:50:38','2026-04-22 08:50:38'),(10,2,1,'2026-04-20',NULL,NULL,NULL,NULL,'office','absent',0.00,0.00,0,0,NULL,NULL,NULL,NULL,NULL,NULL,'web',NULL,'2026-04-22 08:50:38','2026-04-22 08:50:38'),(11,3,1,'2026-04-20',NULL,'2026-04-20 08:30:00','2026-04-20 17:15:00',NULL,'wfh','present',8.75,0.00,30,0,NULL,NULL,NULL,NULL,NULL,NULL,'web',NULL,'2026-04-22 08:50:38','2026-04-22 08:50:38'),(12,4,1,'2026-04-20',NULL,'2026-04-20 07:45:00','2026-04-20 16:50:00',NULL,'office','present',9.08,0.00,0,0,NULL,NULL,NULL,NULL,NULL,NULL,'web',NULL,'2026-04-22 08:50:38','2026-04-22 08:50:38'),(13,2,1,'2026-04-23',NULL,'2026-04-23 08:15:00',NULL,NULL,'wfh','present',NULL,0.00,0,0,NULL,NULL,NULL,NULL,NULL,NULL,'web',NULL,'2026-04-22 07:53:41','2026-04-22 07:53:41');

--
-- Table structure for table `attendance_regularisation`
--

CREATE TABLE `attendance_regularisation` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) unsigned NOT NULL,
  `work_date` date NOT NULL,
  `issue_type` enum('missed_checkin','missed_checkout','wrong_time','wrong_mode') NOT NULL,
  `requested_check_in` time DEFAULT NULL,
  `requested_check_out` time DEFAULT NULL,
  `requested_mode` enum('office','wfh','client_site','travel') DEFAULT NULL,
  `reason` text NOT NULL,
  `status` enum('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
  `approver_id` bigint(20) unsigned DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `rejection_reason` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_reg_employee` (`employee_id`,`status`),
  KEY `idx_reg_approver` (`approver_id`,`status`),
  CONSTRAINT `fk_reg_approver` FOREIGN KEY (`approver_id`) REFERENCES `employees` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_reg_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `attendance_regularisation`
--


--
-- Table structure for table `backups`
--

CREATE TABLE `backups` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `trigger` enum('manual','cron','pre_restore') NOT NULL DEFAULT 'manual',
  `size_bytes` bigint(20) unsigned DEFAULT NULL,
  `file_path` varchar(255) DEFAULT NULL,
  `checksum` char(64) DEFAULT NULL,
  `taken_by` bigint(20) unsigned DEFAULT NULL,
  `status` enum('queued','running','complete','failed') NOT NULL DEFAULT 'queued',
  `started_at` timestamp NULL DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_backup_company_date` (`company_id`,`created_at`),
  KEY `fk_backup_user` (`taken_by`),
  CONSTRAINT `fk_backup_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_backup_user` FOREIGN KEY (`taken_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `backups`
--


--
-- Table structure for table `bank_file_exports`
--

CREATE TABLE `bank_file_exports` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `run_id` bigint(20) unsigned NOT NULL,
  `company_id` bigint(20) unsigned NOT NULL,
  `format_id` bigint(20) unsigned NOT NULL,
  `bank_account_id` bigint(20) unsigned NOT NULL,
  `reference` varchar(60) NOT NULL,
  `value_date` date NOT NULL,
  `currency` char(3) NOT NULL,
  `employee_count` int(10) unsigned NOT NULL,
  `total_amount` decimal(18,2) NOT NULL,
  `file_path` varchar(255) DEFAULT NULL,
  `file_size_bytes` int(10) unsigned DEFAULT NULL,
  `checksum` char(64) DEFAULT NULL COMMENT 'sha256 of file bytes',
  `status` enum('draft','generated','downloaded','submitted','acknowledged','failed') NOT NULL DEFAULT 'draft',
  `generated_by` bigint(20) unsigned DEFAULT NULL,
  `generated_at` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL COMMENT '90-day retention',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_export_ref` (`reference`),
  KEY `idx_export_run` (`run_id`),
  KEY `idx_export_company_date` (`company_id`,`value_date`),
  KEY `idx_export_expiry` (`expires_at`),
  KEY `fk_export_format` (`format_id`),
  KEY `fk_export_bank` (`bank_account_id`),
  KEY `fk_export_user` (`generated_by`),
  CONSTRAINT `fk_export_bank` FOREIGN KEY (`bank_account_id`) REFERENCES `company_bank_accounts` (`id`),
  CONSTRAINT `fk_export_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_export_format` FOREIGN KEY (`format_id`) REFERENCES `bank_formats` (`id`),
  CONSTRAINT `fk_export_run` FOREIGN KEY (`run_id`) REFERENCES `payroll_runs` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_export_user` FOREIGN KEY (`generated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `bank_file_exports`
--

INSERT INTO `bank_file_exports` (`id`, `run_id`, `company_id`, `format_id`, `bank_account_id`, `reference`, `value_date`, `currency`, `employee_count`, `total_amount`, `file_path`, `file_size_bytes`, `checksum`, `status`, `generated_by`, `generated_at`, `expires_at`, `created_at`) VALUES (1,2,1,1,1,'WPS-MAR-2026','2026-04-01','AED',3,39000.00,'writable/bank-files/WPS-MAR-2026.csv',254,'828759155346499e8d14e966c9cf6331568e1862f5d18da8fb194f8c8db4de8d','acknowledged',1,'2026-04-22 08:46:57','2026-07-21 08:46:57','2026-04-22 08:46:57');

--
-- Table structure for table `bank_formats`
--

CREATE TABLE `bank_formats` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(40) NOT NULL,
  `country_code` char(2) NOT NULL,
  `bank_name` varchar(80) NOT NULL,
  `format_label` varchar(80) NOT NULL,
  `version` varchar(20) NOT NULL DEFAULT '1.0',
  `delimiter` varchar(4) NOT NULL DEFAULT '|',
  `encoding` varchar(20) NOT NULL DEFAULT 'UTF-8',
  `line_ending` enum('CRLF','LF') NOT NULL DEFAULT 'CRLF',
  `header_template` varchar(500) DEFAULT NULL,
  `trailer_template` varchar(500) DEFAULT NULL,
  `columns_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`columns_json`)),
  `php_class` varchar(120) DEFAULT NULL COMMENT 'optional custom exporter',
  `is_system` tinyint(1) NOT NULL DEFAULT 1,
  `status` enum('active','archived') NOT NULL 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 `uq_bank_code` (`code`),
  KEY `idx_bank_country` (`country_code`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `bank_formats`
--

INSERT INTO `bank_formats` (`id`, `code`, `country_code`, `bank_name`, `format_label`, `version`, `delimiter`, `encoding`, `line_ending`, `header_template`, `trailer_template`, `columns_json`, `php_class`, `is_system`, `status`, `created_at`, `updated_at`) VALUES (1,'AE_WPS_SIF','AE','UAE WPS','SIF v3.0','1.0','|','UTF-8','CRLF',NULL,NULL,'[{\"n\":\"record_type\"},{\"n\":\"employee_id\"},{\"n\":\"employee_name\"},{\"n\":\"iban\"},{\"n\":\"bank_name\"},{\"n\":\"amount\"},{\"n\":\"currency\"}]',NULL,1,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(2,'SA_MUDAD','SA','Saudi Mudad','WPS','1.0','|','UTF-8','CRLF',NULL,NULL,'[]',NULL,1,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(3,'IN_SBI','IN','State Bank of India','NEFT CSV','1.0',',','UTF-8','CRLF',NULL,NULL,'[]',NULL,1,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(4,'IN_HDFC','IN','HDFC Bank','NEFT TXT','1.0',',','UTF-8','CRLF',NULL,NULL,'[]',NULL,1,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(5,'IN_ICICI','IN','ICICI Bank','NEFT CSV','1.0',',','UTF-8','CRLF',NULL,NULL,'[]',NULL,1,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(6,'IN_AXIS','IN','Axis Bank','NEFT CSV','1.0',',','UTF-8','CRLF',NULL,NULL,'[]',NULL,1,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(7,'US_ACH','US','US ACH','NACHA','1.0','','UTF-8','CRLF',NULL,NULL,'[]',NULL,1,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(8,'EU_SEPA','EU','SEPA','XML pain.001.001.03','1.0','','UTF-8','CRLF',NULL,NULL,'[]',NULL,1,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(9,'GB_BACS','GB','UK BACS','Standard 18','1.0','','UTF-8','CRLF',NULL,NULL,'[]',NULL,1,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(10,'PH_BPI','PH','BPI','CSV','1.0',',','UTF-8','CRLF',NULL,NULL,'[]',NULL,1,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(11,'PH_BDO','PH','BDO','CSV','1.0',',','UTF-8','CRLF',NULL,NULL,'[]',NULL,1,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(12,'NG_GTB','NG','GT Bank','XLS','1.0','','UTF-8','CRLF',NULL,NULL,'[]',NULL,1,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(13,'KE_KCB','KE','Kenya Commercial Bank','CSV','1.0',',','UTF-8','CRLF',NULL,NULL,'[]',NULL,1,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(14,'SG_GIRO','SG','Singapore GIRO','Standard','1.0','','UTF-8','CRLF',NULL,NULL,'[]',NULL,1,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(15,'BD_BB','BD','Bangladesh Bank','CSV','1.0',',','UTF-8','CRLF',NULL,NULL,'[]',NULL,1,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(16,'GENERIC_CSV','XX','Generic','CSV','1.0',',','UTF-8','CRLF',NULL,NULL,'[]',NULL,1,'active','2026-04-22 05:27:06','2026-04-22 05:27:06');

--
-- Table structure for table `branches`
--

CREATE TABLE `branches` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `name` varchar(120) NOT NULL,
  `code` varchar(20) DEFAULT NULL,
  `country_code` char(2) NOT NULL,
  `timezone` varchar(64) NOT NULL,
  `currency` char(3) NOT NULL,
  `weekly_off_days` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'array 0-6 · 0=Sun' CHECK (json_valid(`weekly_off_days`)),
  `address` text DEFAULT NULL,
  `phone` varchar(32) DEFAULT NULL,
  `geofence_lat` decimal(10,7) DEFAULT NULL,
  `geofence_lng` decimal(10,7) DEFAULT NULL,
  `geofence_radius_m` smallint(5) unsigned DEFAULT 100,
  `is_hq` tinyint(1) NOT NULL DEFAULT 0,
  `status` enum('active','closed') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_branches_company` (`company_id`,`status`),
  CONSTRAINT `fk_branches_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `branches`
--

INSERT INTO `branches` (`id`, `company_id`, `name`, `code`, `country_code`, `timezone`, `currency`, `weekly_off_days`, `address`, `phone`, `geofence_lat`, `geofence_lng`, `geofence_radius_m`, `is_hq`, `status`, `created_at`, `updated_at`, `deleted_at`) VALUES (1,1,'Dubai (HQ)','DXB','AE','Asia/Dubai','AED','[6,0]',NULL,NULL,NULL,NULL,100,1,'active','2026-04-22 05:27:06','2026-04-22 10:13:08',NULL),(2,1,'Bengaluru','BLR','IN','Asia/Kolkata','INR','[6,0]',NULL,NULL,NULL,NULL,100,0,'active','2026-04-22 05:27:06','2026-04-22 05:27:06',NULL),(3,1,'Manila','MNL','PH','Asia/Manila','PHP','[6,0]',NULL,NULL,NULL,NULL,100,0,'active','2026-04-22 05:27:06','2026-04-22 05:27:06',NULL),(4,1,'Lagos','LOS','NG','Africa/Lagos','NGN','[6,0]',NULL,NULL,NULL,NULL,100,0,'active','2026-04-22 05:27:06','2026-04-22 05:27:06',NULL),(5,1,'Riyadh','RUH','SA','Asia/Riyadh','SAR','[5,6]',NULL,NULL,NULL,NULL,100,0,'active','2026-04-22 10:13:07','2026-04-22 11:13:08','2026-04-22 10:13:07');

--
-- Table structure for table `companies`
--

CREATE TABLE `companies` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(160) NOT NULL,
  `legal_name` varchar(200) DEFAULT NULL,
  `slug` varchar(80) NOT NULL,
  `country_code` char(2) NOT NULL,
  `base_currency` char(3) NOT NULL DEFAULT 'USD',
  `timezone` varchar(64) NOT NULL DEFAULT 'UTC',
  `fiscal_year_start_month` tinyint(3) unsigned NOT NULL DEFAULT 1,
  `date_format` varchar(20) NOT NULL DEFAULT 'DD MMM YYYY',
  `tax_id` varchar(80) DEFAULT NULL,
  `logo_path` varchar(255) DEFAULT NULL,
  `logo_icon_path` varchar(255) DEFAULT NULL,
  `accent_color` varchar(7) DEFAULT '#D97706',
  `address` text DEFAULT NULL,
  `website` varchar(255) DEFAULT NULL,
  `phone` varchar(32) DEFAULT NULL,
  `email` varchar(160) DEFAULT NULL,
  `status` enum('active','suspended','archived') NOT NULL DEFAULT 'active',
  `settings_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`settings_json`)),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_companies_slug` (`slug`),
  KEY `idx_companies_status` (`status`,`deleted_at`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `companies`
--

INSERT INTO `companies` (`id`, `name`, `legal_name`, `slug`, `country_code`, `base_currency`, `timezone`, `fiscal_year_start_month`, `date_format`, `tax_id`, `logo_path`, `logo_icon_path`, `accent_color`, `address`, `website`, `phone`, `email`, `status`, `settings_json`, `created_at`, `updated_at`, `deleted_at`) VALUES (1,'Brightline Studios','Brightline Studios LLC','brightline','AE','AED','Asia/Dubai',1,'DD MMM YYYY',NULL,NULL,NULL,'#D97706',NULL,'https://brightline.studio',NULL,'hello@brightline.studio','active',NULL,'2026-04-22 05:27:06','2026-04-22 09:45:17',NULL);

--
-- Table structure for table `compliance_completions`
--

CREATE TABLE `compliance_completions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `deadline_id` bigint(20) unsigned NOT NULL,
  `period` varchar(10) NOT NULL COMMENT 'YYYY-MM or YYYY',
  `completed_by` bigint(20) unsigned DEFAULT NULL,
  `completed_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `on_time` tinyint(1) NOT NULL DEFAULT 1,
  `proof_path` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_cc_deadline_period` (`deadline_id`,`period`),
  KEY `idx_cc_user` (`completed_by`),
  CONSTRAINT `fk_cc_deadline` FOREIGN KEY (`deadline_id`) REFERENCES `compliance_deadlines` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cc_user` FOREIGN KEY (`completed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `compliance_completions`
--

INSERT INTO `compliance_completions` (`id`, `deadline_id`, `period`, `completed_by`, `completed_at`, `on_time`, `proof_path`, `notes`) VALUES (1,1,'2026-03',1,'2026-03-23 11:04:38',1,NULL,'Filed on time'),(2,4,'2026-03',1,'2026-03-25 11:04:38',1,NULL,'All employees included'),(3,2,'2026-04',1,'2026-04-22 09:08:59',0,NULL,'Filed on SBI portal');

--
-- Table structure for table `compliance_deadlines`
--

CREATE TABLE `compliance_deadlines` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `country_code` char(2) NOT NULL,
  `name` varchar(200) NOT NULL,
  `description` text DEFAULT NULL,
  `deadline_type` enum('tax','social_security','filing','reporting','renewal') NOT NULL,
  `severity` enum('critical','routine') NOT NULL DEFAULT 'routine',
  `recurrence` enum('monthly','quarterly','annually','one_off') NOT NULL DEFAULT 'monthly',
  `due_day` tinyint(3) unsigned DEFAULT NULL COMMENT 'day of month/year',
  `due_month` tinyint(3) unsigned DEFAULT NULL,
  `next_due_date` date DEFAULT NULL,
  `lead_time_days` tinyint(3) unsigned NOT NULL DEFAULT 7,
  `owner_employee_id` bigint(20) unsigned DEFAULT NULL,
  `filing_url` varchar(500) DEFAULT NULL,
  `checklist_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`checklist_json`)),
  `status` enum('active','paused','archived') NOT NULL 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`),
  KEY `idx_cd_company_due` (`company_id`,`next_due_date`,`status`),
  KEY `idx_cd_owner` (`owner_employee_id`),
  CONSTRAINT `fk_cd_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cd_owner` FOREIGN KEY (`owner_employee_id`) REFERENCES `employees` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `compliance_deadlines`
--

INSERT INTO `compliance_deadlines` (`id`, `company_id`, `country_code`, `name`, `description`, `deadline_type`, `severity`, `recurrence`, `due_day`, `due_month`, `next_due_date`, `lead_time_days`, `owner_employee_id`, `filing_url`, `checklist_json`, `status`, `created_at`, `updated_at`) VALUES (1,1,'IN','India PF Return',NULL,'tax','critical','monthly',15,NULL,'2026-05-15',7,1,NULL,NULL,'active','2026-04-22 05:27:06','2026-04-22 10:04:38'),(2,1,'IN','India TDS Challan',NULL,'tax','critical','monthly',7,NULL,'2026-05-19',5,1,NULL,NULL,'active','2026-04-22 05:27:06','2026-04-22 09:08:59'),(3,1,'IN','Form 16 distribution',NULL,'filing','routine','annually',31,NULL,'2026-12-31',30,NULL,NULL,NULL,'active','2026-04-22 05:27:06','2026-04-22 10:04:38'),(4,1,'AE','UAE WPS Submission',NULL,'filing','critical','monthly',18,NULL,'2026-05-18',7,2,NULL,NULL,'active','2026-04-22 05:27:06','2026-04-22 10:04:38'),(5,1,'PH','PH SSS Contribution',NULL,'social_security','routine','monthly',22,NULL,'2026-04-22',10,3,NULL,NULL,'active','2026-04-22 05:27:06','2026-04-22 09:08:59'),(6,1,'NG','NG PAYE Remittance',NULL,'tax','critical','monthly',10,NULL,'2026-05-10',5,3,NULL,NULL,'active','2026-04-22 05:27:06','2026-04-22 10:04:38'),(7,1,'GB','UK RTI Submission',NULL,'reporting','critical','monthly',22,NULL,'2026-04-22',5,NULL,NULL,NULL,'active','2026-04-22 05:27:06','2026-04-22 10:04:38');

--
-- Table structure for table `country_rule_packs`
--

CREATE TABLE `country_rule_packs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned DEFAULT NULL COMMENT 'NULL = system default',
  `country_code` char(2) NOT NULL,
  `name` varchar(120) NOT NULL,
  `currency` char(3) NOT NULL,
  `timezone` varchar(64) DEFAULT NULL,
  `fiscal_year_start_month` tinyint(3) unsigned NOT NULL DEFAULT 1,
  `weekly_off_days` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`weekly_off_days`)),
  `tax_slabs_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'progressive brackets' CHECK (json_valid(`tax_slabs_json`)),
  `statutory_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'PF, ESI, GOSI, SSS etc' CHECK (json_valid(`statutory_json`)),
  `leave_types_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`leave_types_json`)),
  `components_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`components_json`)),
  `id_fields_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`id_fields_json`)),
  `bank_format_code` varchar(40) DEFAULT NULL,
  `version` varchar(20) NOT NULL DEFAULT '1.0.0',
  `is_system` tinyint(1) NOT NULL DEFAULT 0,
  `status` enum('active','archived','draft') NOT NULL 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`),
  KEY `idx_rp_country` (`country_code`,`status`),
  KEY `idx_rp_company` (`company_id`),
  CONSTRAINT `fk_rp_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `country_rule_packs`
--

INSERT INTO `country_rule_packs` (`id`, `company_id`, `country_code`, `name`, `currency`, `timezone`, `fiscal_year_start_month`, `weekly_off_days`, `tax_slabs_json`, `statutory_json`, `leave_types_json`, `components_json`, `id_fields_json`, `bank_format_code`, `version`, `is_system`, `status`, `created_at`, `updated_at`) VALUES (1,NULL,'AE','UAE','AED','Asia/Dubai',1,'[6,0]',NULL,NULL,NULL,NULL,NULL,NULL,'1.0.0',1,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(2,NULL,'IN','India','INR','Asia/Kolkata',4,'[6,0]',NULL,NULL,NULL,NULL,NULL,NULL,'1.0.0',1,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(3,NULL,'PH','Philippines','PHP','Asia/Manila',1,'[6,0]',NULL,NULL,NULL,NULL,NULL,NULL,'1.0.0',1,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(4,NULL,'SA','Saudi Arabia','SAR','Asia/Riyadh',1,'[5,6]',NULL,NULL,NULL,NULL,NULL,NULL,'1.0.0',1,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(5,NULL,'NG','Nigeria','NGN','Africa/Lagos',1,'[6,0]',NULL,NULL,NULL,NULL,NULL,NULL,'1.0.0',1,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(6,NULL,'KE','Kenya','KES','Africa/Nairobi',1,'[6,0]',NULL,NULL,NULL,NULL,NULL,NULL,'1.0.0',1,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(7,NULL,'US','United States','USD','America/New_York',1,'[6,0]',NULL,NULL,NULL,NULL,NULL,NULL,'1.0.0',1,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(8,NULL,'GB','United Kingdom','GBP','Europe/London',4,'[6,0]',NULL,NULL,NULL,NULL,NULL,NULL,'1.0.0',1,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(9,NULL,'BD','Bangladesh','BDT','Asia/Dhaka',7,'[5,6]',NULL,NULL,NULL,NULL,NULL,NULL,'1.0.0',1,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(10,NULL,'PK','Pakistan','PKR','Asia/Karachi',7,'[6,0]',NULL,NULL,NULL,NULL,NULL,NULL,'1.0.0',1,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(11,NULL,'ID','Indonesia','IDR','Asia/Jakarta',1,'[6,0]',NULL,NULL,NULL,NULL,NULL,NULL,'1.0.0',1,'active','2026-04-22 05:27:06','2026-04-22 05:27:06');

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

CREATE TABLE `departments` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `parent_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(120) NOT NULL,
  `code` varchar(20) DEFAULT NULL,
  `head_employee_id` bigint(20) unsigned DEFAULT NULL,
  `status` enum('active','archived') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_departments_company` (`company_id`,`status`),
  KEY `idx_departments_parent` (`parent_id`),
  KEY `fk_departments_head` (`head_employee_id`),
  CONSTRAINT `fk_departments_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_departments_head` FOREIGN KEY (`head_employee_id`) REFERENCES `employees` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_departments_parent` FOREIGN KEY (`parent_id`) REFERENCES `departments` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `departments` (`id`, `company_id`, `parent_id`, `name`, `code`, `head_employee_id`, `status`, `created_at`, `updated_at`, `deleted_at`) VALUES (1,1,NULL,'Design','DES',NULL,'active','2026-04-22 05:27:06','2026-04-22 05:27:06',NULL),(2,1,NULL,'Engineering','ENG',NULL,'active','2026-04-22 05:27:06','2026-04-22 05:27:06',NULL),(3,1,NULL,'Product','PROD',NULL,'active','2026-04-22 05:27:06','2026-04-22 05:27:06',NULL),(4,1,NULL,'Revenue','REV',NULL,'active','2026-04-22 05:27:06','2026-04-22 05:27:06',NULL),(5,1,NULL,'Marketing','MKT',NULL,'active','2026-04-22 05:27:06','2026-04-22 05:27:06',NULL),(6,1,NULL,'Finance','FIN',NULL,'active','2026-04-22 05:27:06','2026-04-22 05:27:06',NULL),(7,1,NULL,'People','PPL',NULL,'active','2026-04-22 05:27:06','2026-04-22 05:27:06',NULL),(8,1,2,'Platform Eng','PLAT',1,'active','2026-04-22 10:13:08','2026-04-22 11:13:08','2026-04-22 10:13:08');

--
-- Table structure for table `designations`
--

CREATE TABLE `designations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `department_id` bigint(20) unsigned DEFAULT NULL,
  `title` varchar(120) NOT NULL,
  `status` enum('active','archived') NOT NULL 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`),
  KEY `idx_designations_company` (`company_id`),
  KEY `idx_designations_department` (`department_id`),
  CONSTRAINT `fk_designations_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_designations_department` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `designations`
--

INSERT INTO `designations` (`id`, `company_id`, `department_id`, `title`, `status`, `created_at`, `updated_at`) VALUES (1,1,1,'Principal Designer','archived','2026-04-22 10:13:08','2026-04-22 10:13:08');

--
-- Table structure for table `document_requests`
--

CREATE TABLE `document_requests` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) unsigned NOT NULL,
  `document_type` varchar(60) NOT NULL,
  `purpose` varchar(255) DEFAULT NULL,
  `address_to` varchar(200) DEFAULT NULL,
  `urgency` enum('standard','urgent') NOT NULL DEFAULT 'standard',
  `status` enum('pending','in_progress','ready','sent','rejected','cancelled') NOT NULL DEFAULT 'pending',
  `fulfilled_letter_id` bigint(20) unsigned DEFAULT NULL,
  `fulfilled_by` bigint(20) unsigned DEFAULT NULL,
  `fulfilled_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_dr_employee_status` (`employee_id`,`status`),
  KEY `fk_dr_letter` (`fulfilled_letter_id`),
  CONSTRAINT `fk_dr_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_dr_letter` FOREIGN KEY (`fulfilled_letter_id`) REFERENCES `generated_letters` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `document_requests`
--

INSERT INTO `document_requests` (`id`, `employee_id`, `document_type`, `purpose`, `address_to`, `urgency`, `status`, `fulfilled_letter_id`, `fulfilled_by`, `fulfilled_at`, `created_at`) VALUES (1,2,'salary_certificate','Bank loan application','Emirates NBD','urgent','ready',3,1,'2026-04-22 09:18:13','2026-04-21 10:14:00'),(2,3,'employment_cert','Apartment rental','Al Futtaim Real Estate','standard','rejected',NULL,NULL,NULL,'2026-04-20 10:14:00'),(3,1,'noc','Dubai driving license','RTA','standard','cancelled',NULL,NULL,NULL,'2026-04-19 10:14:00'),(4,3,'experience','Portfolio update',NULL,'standard','ready',NULL,NULL,NULL,'2026-04-17 10:14:00');

--
-- Table structure for table `email_templates`
--

CREATE TABLE `email_templates` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `code` varchar(60) NOT NULL COMMENT 'welcome·leave_approved·payslip_ready',
  `subject` varchar(200) NOT NULL,
  `body_html` mediumtext NOT NULL,
  `body_text` mediumtext DEFAULT NULL,
  `merge_fields_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`merge_fields_json`)),
  `is_system` tinyint(1) NOT NULL DEFAULT 1,
  `status` enum('active','draft','archived') NOT NULL DEFAULT 'active',
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_et_company_code` (`company_id`,`code`),
  CONSTRAINT `fk_et_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `email_templates`
--



--
-- Table structure for table `employee_assets`
--

CREATE TABLE `employee_assets` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `asset_id` bigint(20) unsigned NOT NULL,
  `employee_id` bigint(20) unsigned NOT NULL,
  `assigned_at` date NOT NULL,
  `returned_at` date DEFAULT NULL,
  `condition_on_return` varchar(60) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_ea_asset` (`asset_id`),
  KEY `idx_ea_employee` (`employee_id`),
  CONSTRAINT `fk_ea_asset` FOREIGN KEY (`asset_id`) REFERENCES `assets` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ea_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `employee_assets`
--


--
-- Table structure for table `employee_bank`
--

CREATE TABLE `employee_bank` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) unsigned NOT NULL,
  `bank_name` varchar(120) NOT NULL,
  `branch_name` varchar(120) DEFAULT NULL,
  `account_number_enc` varbinary(512) DEFAULT NULL COMMENT 'AES-256 encrypted',
  `account_last4` char(4) DEFAULT NULL COMMENT 'for display',
  `iban_enc` varbinary(512) DEFAULT NULL,
  `swift_code` varchar(16) DEFAULT NULL,
  `routing_number` varchar(20) DEFAULT NULL,
  `account_holder` varchar(120) DEFAULT NULL,
  `currency` char(3) NOT NULL,
  `is_primary` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_emp_bank_employee` (`employee_id`,`is_primary`),
  CONSTRAINT `fk_emp_bank` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `employee_bank`
--


--
-- Table structure for table `employee_documents`
--

CREATE TABLE `employee_documents` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) unsigned NOT NULL,
  `category` varchar(40) NOT NULL COMMENT 'offer·contract·id·tax·policy',
  `title` varchar(200) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `mime_type` varchar(120) DEFAULT NULL,
  `size_bytes` int(10) unsigned DEFAULT NULL,
  `uploaded_by` bigint(20) unsigned DEFAULT NULL,
  `visibility` enum('private','employee_hr','company') NOT NULL DEFAULT 'employee_hr',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_doc_employee` (`employee_id`,`category`,`deleted_at`),
  KEY `fk_doc_uploader` (`uploaded_by`),
  CONSTRAINT `fk_doc_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_doc_uploader` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `employee_documents`
--

INSERT INTO `employee_documents` (`id`, `employee_id`, `category`, `title`, `file_path`, `mime_type`, `size_bytes`, `uploaded_by`, `visibility`, `created_at`, `deleted_at`) VALUES (1,1,'contract','Employment contract — Marcus','uploads/emp/1/contract.pdf','application/pdf',145320,1,'employee_hr','2026-02-21 11:14:00',NULL),(2,1,'id_proof','Emirates ID — Marcus','uploads/emp/1/eid.pdf','application/pdf',76800,1,'private','2026-02-21 11:14:00',NULL),(3,2,'contract','Employment contract — Layla','uploads/emp/2/contract.pdf','application/pdf',148220,1,'employee_hr','2026-03-13 11:14:00',NULL),(4,3,'visa','Work visa — Wei','uploads/emp/3/visa.pdf','application/pdf',92100,1,'private','2026-04-02 10:14:00',NULL),(5,1,'certificate','Fire safety certificate','uploads/emp/1/fire.pdf','application/pdf',48120,1,'employee_hr','2026-04-22 09:18:13',NULL);

--
-- Table structure for table `employee_education`
--

CREATE TABLE `employee_education` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) unsigned NOT NULL,
  `degree` varchar(120) NOT NULL,
  `institution` varchar(200) DEFAULT NULL,
  `field_of_study` varchar(120) DEFAULT NULL,
  `start_year` smallint(5) unsigned DEFAULT NULL,
  `end_year` smallint(5) unsigned DEFAULT NULL,
  `grade` varchar(40) DEFAULT NULL,
  `file_path` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_edu_employee` (`employee_id`),
  CONSTRAINT `fk_edu_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `employee_education`
--


--
-- Table structure for table `employee_experience`
--

CREATE TABLE `employee_experience` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) unsigned NOT NULL,
  `employer` varchar(200) NOT NULL,
  `position` varchar(120) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `summary` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_exp_employee` (`employee_id`),
  CONSTRAINT `fk_exp_hist_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `employee_experience`
--


--
-- Table structure for table `employee_family`
--

CREATE TABLE `employee_family` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) unsigned NOT NULL,
  `name` varchar(120) NOT NULL,
  `relation` varchar(40) NOT NULL,
  `date_of_birth` date DEFAULT NULL,
  `is_dependent` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_family_employee` (`employee_id`),
  CONSTRAINT `fk_family_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `employee_family`
--


--
-- Table structure for table `employee_identity`
--

CREATE TABLE `employee_identity` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) unsigned NOT NULL,
  `type` varchar(40) NOT NULL COMMENT 'pan·aadhaar·passport·nid·emirates_id·ssn etc',
  `number_enc` varbinary(512) DEFAULT NULL,
  `issue_date` date DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `issuing_country` char(2) DEFAULT NULL,
  `file_path` varchar(255) DEFAULT NULL,
  `verified_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_emp_id_employee` (`employee_id`),
  KEY `idx_emp_id_expiry` (`expiry_date`),
  CONSTRAINT `fk_emp_id` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `employee_identity`
--


--
-- Table structure for table `employee_personal`
--

CREATE TABLE `employee_personal` (
  `employee_id` bigint(20) unsigned NOT NULL,
  `address_line1` varchar(255) DEFAULT NULL,
  `address_line2` varchar(255) DEFAULT NULL,
  `city` varchar(80) DEFAULT NULL,
  `state` varchar(80) DEFAULT NULL,
  `postal_code` varchar(20) DEFAULT NULL,
  `country_code` char(2) DEFAULT NULL,
  `emergency_name` varchar(120) DEFAULT NULL,
  `emergency_relation` varchar(40) DEFAULT NULL,
  `emergency_phone` varchar(32) DEFAULT NULL,
  `blood_group` varchar(5) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`employee_id`),
  CONSTRAINT `fk_emp_personal` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `employee_personal`
--


--
-- Table structure for table `employee_salaries`
--

CREATE TABLE `employee_salaries` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) unsigned NOT NULL,
  `template_id` bigint(20) unsigned DEFAULT NULL,
  `ctc_monthly` decimal(14,2) NOT NULL,
  `currency` char(3) NOT NULL,
  `effective_from` date NOT NULL,
  `effective_to` date DEFAULT NULL,
  `revision_reason` varchar(255) DEFAULT NULL,
  `previous_ctc` decimal(14,2) DEFAULT NULL,
  `approved_by` bigint(20) unsigned DEFAULT NULL,
  `components_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'snapshot of resolved amounts' CHECK (json_valid(`components_json`)),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_es_employee_effective` (`employee_id`,`effective_from`),
  KEY `fk_esl_template` (`template_id`),
  KEY `fk_esl_approver` (`approved_by`),
  CONSTRAINT `fk_esl_approver` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_esl_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_esl_template` FOREIGN KEY (`template_id`) REFERENCES `salary_templates` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `employee_salaries`
--

INSERT INTO `employee_salaries` (`id`, `employee_id`, `template_id`, `ctc_monthly`, `currency`, `effective_from`, `effective_to`, `revision_reason`, `previous_ctc`, `approved_by`, `components_json`, `created_at`) VALUES (1,1,NULL,15000.00,'AED','2024-01-15','2026-04-30',NULL,NULL,NULL,NULL,'2026-04-22 09:18:23'),(2,2,NULL,15000.00,'AED','2024-03-22',NULL,NULL,NULL,NULL,NULL,'2026-04-22 09:18:23'),(3,3,NULL,15000.00,'AED','2024-06-10',NULL,NULL,NULL,NULL,NULL,'2026-04-22 09:18:23'),(4,1,NULL,18000.00,'AED','2026-05-01',NULL,'Annual review',15000.00,NULL,NULL,'2026-04-22 08:21:36');

--
-- Table structure for table `employee_shifts`
--

CREATE TABLE `employee_shifts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) unsigned NOT NULL,
  `shift_id` bigint(20) unsigned NOT NULL,
  `effective_from` date NOT NULL,
  `effective_to` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_es_employee_from` (`employee_id`,`effective_from`),
  KEY `idx_es_shift` (`shift_id`),
  CONSTRAINT `fk_es_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_es_shift` FOREIGN KEY (`shift_id`) REFERENCES `shifts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `employee_shifts`
--

INSERT INTO `employee_shifts` (`id`, `employee_id`, `shift_id`, `effective_from`, `effective_to`, `created_at`) VALUES (1,1,1,'2026-04-22','2026-04-21','2026-04-22 08:09:59'),(2,2,1,'2026-04-22','2026-04-21','2026-04-22 08:09:59'),(3,1,2,'2026-04-23','2026-04-21','2026-04-22 08:09:59'),(4,2,5,'2026-04-22',NULL,'2026-04-22 08:10:56'),(5,1,5,'2026-04-22',NULL,'2026-04-22 08:10:56');

--
-- Table structure for table `employee_timeline`
--

CREATE TABLE `employee_timeline` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) unsigned NOT NULL,
  `event_type` varchar(40) NOT NULL COMMENT 'join·confirm·revise·promote·award·exit',
  `event_date` date NOT NULL,
  `title` varchar(200) NOT NULL,
  `body` text DEFAULT NULL,
  `metadata_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`metadata_json`)),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_timeline_employee_date` (`employee_id`,`event_date`),
  CONSTRAINT `fk_timeline_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `employee_timeline`
--

INSERT INTO `employee_timeline` (`id`, `employee_id`, `event_type`, `event_date`, `title`, `body`, `metadata_json`, `created_at`) VALUES (2,1,'revise','2026-05-01','Salary revision','From 15,000 to 18,000 AED',NULL,'2026-04-22 08:21:36');

--
-- Table structure for table `employees`
--

CREATE TABLE `employees` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL COMMENT 'NULL if not yet invited',
  `code` varchar(40) NOT NULL COMMENT 'employee ID eg BLS-IN-0042',
  `first_name` varchar(80) NOT NULL,
  `last_name` varchar(80) DEFAULT NULL,
  `email` varchar(191) DEFAULT NULL,
  `phone` varchar(32) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `gender` enum('female','male','other','prefer_not_say') DEFAULT 'prefer_not_say',
  `marital_status` enum('single','married','divorced','widowed','other') DEFAULT NULL,
  `nationality` varchar(80) DEFAULT NULL,
  `branch_id` bigint(20) unsigned NOT NULL,
  `department_id` bigint(20) unsigned DEFAULT NULL,
  `designation_id` bigint(20) unsigned DEFAULT NULL,
  `grade_id` bigint(20) unsigned DEFAULT NULL,
  `manager_id` bigint(20) unsigned DEFAULT NULL,
  `join_date` date NOT NULL,
  `probation_end_date` date DEFAULT NULL,
  `confirmation_date` date DEFAULT NULL,
  `contract_end_date` date DEFAULT NULL,
  `exit_date` date DEFAULT NULL,
  `exit_reason` varchar(255) DEFAULT NULL,
  `employment_type` enum('full_time','part_time','contractor','intern','consultant') NOT NULL DEFAULT 'full_time',
  `status` enum('active','probation','on_leave','suspended','exited') NOT NULL DEFAULT 'active',
  `work_mode_default` enum('office','wfh','hybrid','client_site') NOT NULL DEFAULT 'office',
  `timezone` varchar(64) DEFAULT NULL,
  `avatar_path` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_employees_company_code` (`company_id`,`code`),
  UNIQUE KEY `uq_employees_user` (`user_id`),
  KEY `idx_employees_company_status` (`company_id`,`status`,`deleted_at`),
  KEY `idx_employees_branch` (`branch_id`),
  KEY `idx_employees_department` (`department_id`),
  KEY `idx_employees_manager` (`manager_id`),
  KEY `idx_employees_email` (`email`),
  KEY `fk_employees_designation` (`designation_id`),
  KEY `fk_employees_grade` (`grade_id`),
  CONSTRAINT `fk_employees_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`),
  CONSTRAINT `fk_employees_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_employees_department` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_employees_designation` FOREIGN KEY (`designation_id`) REFERENCES `designations` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_employees_grade` FOREIGN KEY (`grade_id`) REFERENCES `grades` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_employees_manager` FOREIGN KEY (`manager_id`) REFERENCES `employees` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_employees_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `employees`
--

INSERT INTO `employees` (`id`, `company_id`, `user_id`, `code`, `first_name`, `last_name`, `email`, `phone`, `date_of_birth`, `gender`, `marital_status`, `nationality`, `branch_id`, `department_id`, `designation_id`, `grade_id`, `manager_id`, `join_date`, `probation_end_date`, `confirmation_date`, `contract_end_date`, `exit_date`, `exit_reason`, `employment_type`, `status`, `work_mode_default`, `timezone`, `avatar_path`, `created_at`, `updated_at`, `deleted_at`) VALUES (1,1,NULL,'BLS-DXB-0001','Marcus','Chen','marcus@brightline.studio',NULL,'1990-05-12','prefer_not_say',NULL,NULL,1,1,NULL,NULL,NULL,'2024-01-15',NULL,NULL,NULL,NULL,NULL,'full_time','active','office',NULL,NULL,'2026-04-22 05:28:45','2026-04-22 09:12:58',NULL),(2,1,2,'BLS-DXB-0002','Layla','Mansour','layla@brightline.studio','+971500000999','1988-04-25','prefer_not_say',NULL,'Filipino',1,2,NULL,NULL,NULL,'2024-03-22',NULL,NULL,NULL,NULL,NULL,'full_time','active','office',NULL,NULL,'2026-04-22 05:28:45','2026-04-22 10:24:41',NULL),(3,1,NULL,'BLS-BLR-0003','Wei','Zhang','wei@brightline.studio',NULL,'1993-06-03','prefer_not_say',NULL,NULL,2,2,NULL,NULL,NULL,'2024-06-10',NULL,NULL,NULL,NULL,NULL,'full_time','active','office',NULL,NULL,'2026-04-22 05:28:45','2026-04-22 09:12:58',NULL),(4,1,NULL,'BLS-PH-0004','Felipe','Cruz','felipe@brightline.studio',NULL,NULL,'prefer_not_say',NULL,NULL,3,3,NULL,NULL,NULL,'2025-02-18',NULL,NULL,NULL,'2026-04-22',NULL,'full_time','active','office',NULL,NULL,'2026-04-22 05:28:45','2026-04-22 12:26:32',NULL);

--
-- Table structure for table `expense_categories`
--

CREATE TABLE `expense_categories` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `name` varchar(80) NOT NULL,
  `gl_account` varchar(20) DEFAULT NULL,
  `max_per_claim` decimal(14,2) DEFAULT NULL,
  `currency` char(3) DEFAULT NULL,
  `requires_receipt` tinyint(1) NOT NULL DEFAULT 1,
  `approver_rule` enum('manager','finance','hr','custom') NOT NULL DEFAULT 'manager',
  `status` enum('active','archived') NOT NULL DEFAULT 'active',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ec_company_name` (`company_id`,`name`),
  CONSTRAINT `fk_ec_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `expense_categories`
--

INSERT INTO `expense_categories` (`id`, `company_id`, `name`, `gl_account`, `max_per_claim`, `currency`, `requires_receipt`, `approver_rule`, `status`) VALUES (1,1,'Meals & entertainment','6201',1000.00,'AED',1,'manager','active'),(2,1,'Travel','6301',10000.00,'AED',1,'finance','active'),(3,1,'Office supplies','6101',500.00,'AED',1,'manager','active'),(4,1,'Software / SaaS','6401',5000.00,'AED',1,'finance','active'),(5,1,'Training','6501',3000.00,'AED',1,'hr','active'),(6,1,'Utilities (WFH)','6102',300.00,'AED',0,'manager','active'),(7,1,'Client gift','6202',500.00,'AED',1,'finance','active');

--
-- Table structure for table `expense_claims`
--

CREATE TABLE `expense_claims` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) unsigned NOT NULL,
  `category_id` bigint(20) unsigned NOT NULL,
  `reference` varchar(30) NOT NULL,
  `vendor` varchar(200) DEFAULT NULL,
  `expense_date` date NOT NULL,
  `amount` decimal(14,2) NOT NULL,
  `currency` char(3) NOT NULL,
  `description` text DEFAULT NULL,
  `project_code` varchar(60) DEFAULT NULL,
  `is_reimbursable` tinyint(1) NOT NULL DEFAULT 1,
  `receipt_path` varchar(255) DEFAULT NULL,
  `ocr_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'AI-extracted fields' CHECK (json_valid(`ocr_json`)),
  `status` enum('draft','submitted','pending','approved','rejected','reimbursed','cancelled') NOT NULL DEFAULT 'submitted',
  `approver_id` bigint(20) unsigned DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `approver_note` varchar(500) DEFAULT NULL,
  `reimbursed_via_payslip_id` bigint(20) unsigned DEFAULT NULL,
  `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 `uq_exp_ref` (`reference`),
  KEY `idx_exp_employee_status` (`employee_id`,`status`),
  KEY `idx_exp_approver_status` (`approver_id`,`status`),
  KEY `fk_exp_category` (`category_id`),
  KEY `fk_exp_payslip` (`reimbursed_via_payslip_id`),
  CONSTRAINT `fk_exp_approver` FOREIGN KEY (`approver_id`) REFERENCES `employees` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_exp_category` FOREIGN KEY (`category_id`) REFERENCES `expense_categories` (`id`),
  CONSTRAINT `fk_exp_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_exp_payslip` FOREIGN KEY (`reimbursed_via_payslip_id`) REFERENCES `payslips` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `expense_claims`
--

INSERT INTO `expense_claims` (`id`, `employee_id`, `category_id`, `reference`, `vendor`, `expense_date`, `amount`, `currency`, `description`, `project_code`, `is_reimbursable`, `receipt_path`, `ocr_json`, `status`, `approver_id`, `approved_at`, `approver_note`, `reimbursed_via_payslip_id`, `created_at`, `updated_at`) VALUES (1,1,1,'EXP-2026-0001','Jumeirah Restaurant','2026-04-19',487.00,'AED','Team lunch with client',NULL,1,NULL,NULL,'approved',NULL,'2026-04-22 09:01:41','OK',NULL,'2026-04-19 09:59:17','2026-04-22 09:01:41'),(2,2,2,'EXP-2026-0002','Emirates DXB→SIN','2026-04-17',2840.00,'AED','Client visit Singapore',NULL,1,NULL,NULL,'rejected',NULL,'2026-04-22 09:01:41','Travel not pre-approved',NULL,'2026-04-17 09:59:17','2026-04-22 09:01:41'),(3,3,3,'EXP-2026-0003','Amazon Business','2026-04-14',320.00,'AED','Ergonomic keyboard',NULL,1,NULL,NULL,'approved',1,'2026-04-15 09:59:17','OK',NULL,'2026-04-14 09:59:17','2026-04-15 09:59:17'),(4,1,4,'EXP-2026-0004','Figma (annual)','2026-04-10',1800.00,'AED','Design team license',NULL,1,NULL,NULL,'reimbursed',1,'2026-04-12 09:59:17',NULL,NULL,'2026-04-10 09:59:17','2026-04-22 09:01:41'),(5,2,5,'EXP-2026-0005','Udemy','2026-04-02',199.00,'AED','TypeScript course',NULL,1,NULL,NULL,'reimbursed',1,'2026-04-04 09:59:17','Included in March payslip',NULL,'2026-04-02 09:59:17','2026-04-07 09:59:17'),(7,2,2,'EXP-2026-0007','Etihad Airways','2026-04-20',1500.00,'AED','Dubai to London business trip',NULL,1,NULL,NULL,'submitted',NULL,NULL,NULL,NULL,'2026-04-22 09:01:40','2026-04-22 09:01:40'),(8,1,1,'EXP-2026-0008','Nobu','2026-04-20',800.00,'AED',NULL,NULL,1,NULL,NULL,'submitted',NULL,NULL,NULL,NULL,'2026-04-22 09:01:41','2026-04-22 09:01:41');

--
-- Table structure for table `fx_rates`
--

CREATE TABLE `fx_rates` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `base_currency` char(3) NOT NULL,
  `target_currency` char(3) NOT NULL,
  `rate` decimal(14,8) NOT NULL,
  `source` varchar(40) NOT NULL DEFAULT 'openexchangerates',
  `is_manual_override` tinyint(1) NOT NULL DEFAULT 0,
  `fetched_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `effective_date` date NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_fx_pair_date` (`base_currency`,`target_currency`,`effective_date`),
  KEY `idx_fx_date` (`effective_date`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `fx_rates`
--

INSERT INTO `fx_rates` (`id`, `base_currency`, `target_currency`, `rate`, `source`, `is_manual_override`, `fetched_at`, `effective_date`) VALUES (1,'AED','USD',0.27229000,'manual',1,'2026-04-22 09:45:19','2026-04-22'),(2,'INR','USD',0.01200000,'manual',1,'2026-04-22 09:45:19','2026-04-22');

--
-- Table structure for table `generated_letters`
--

CREATE TABLE `generated_letters` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `template_id` bigint(20) unsigned DEFAULT NULL,
  `employee_id` bigint(20) unsigned NOT NULL,
  `title` varchar(200) NOT NULL,
  `body_html` mediumtext NOT NULL,
  `merge_values_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`merge_values_json`)),
  `pdf_path` varchar(255) DEFAULT NULL,
  `sent_at` timestamp NULL DEFAULT NULL,
  `sent_to_email` varchar(191) DEFAULT NULL,
  `generated_by` bigint(20) unsigned DEFAULT NULL,
  `ai_prompt` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_gl_employee` (`employee_id`),
  KEY `fk_gl_template` (`template_id`),
  KEY `fk_gl_user` (`generated_by`),
  CONSTRAINT `fk_gl_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_gl_template` FOREIGN KEY (`template_id`) REFERENCES `letter_templates` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_gl_user` FOREIGN KEY (`generated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `generated_letters`
--

INSERT INTO `generated_letters` (`id`, `template_id`, `employee_id`, `title`, `body_html`, `merge_values_json`, `pdf_path`, `sent_at`, `sent_to_email`, `generated_by`, `ai_prompt`, `created_at`) VALUES (1,2,1,'Salary certificate — Marcus Chen','<p>This is to certify that Marcus Chen is employed with us and earns a gross monthly salary of 15,000 AED.</p>','{\"salary\":15000,\"currency\":\"AED\"}',NULL,'2026-04-17 10:14:00','marcus@brightline.studio',1,NULL,'2026-04-17 10:14:00'),(2,1,2,'Employment certificate — Layla Mansour','<p>This is to certify that Layla Mansour (BLS-DXB-0002) has been employed with us since 2024-03-22 as Senior Engineer.</p>',NULL,NULL,'2026-04-22 09:18:13','layla@brightline.studio',1,NULL,'2026-04-10 10:14:00'),(3,2,2,'Salary certificate — Layla Mansour','<p>Layla Mansour earns AED 15000/month.</p>',NULL,NULL,NULL,NULL,1,NULL,'2026-04-22 09:18:13');

--
-- Table structure for table `grades`
--

CREATE TABLE `grades` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `code` varchar(10) NOT NULL,
  `name` varchar(80) NOT NULL,
  `rank` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `min_salary` decimal(14,2) DEFAULT NULL,
  `max_salary` decimal(14,2) DEFAULT NULL,
  `currency` char(3) DEFAULT NULL,
  `leave_policy_id` bigint(20) unsigned DEFAULT NULL,
  `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 `uq_grades_company_code` (`company_id`,`code`),
  KEY `fk_grades_policy` (`leave_policy_id`),
  CONSTRAINT `fk_grades_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_grades_policy` FOREIGN KEY (`leave_policy_id`) REFERENCES `leave_policies` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `grades`
--

INSERT INTO `grades` (`id`, `company_id`, `code`, `name`, `rank`, `min_salary`, `max_salary`, `currency`, `leave_policy_id`, `created_at`, `updated_at`) VALUES (1,1,'L1','Associate',1,NULL,NULL,NULL,NULL,'2026-04-22 05:27:06','2026-04-22 05:27:06'),(2,1,'L2','Senior',2,NULL,NULL,NULL,NULL,'2026-04-22 05:27:06','2026-04-22 05:27:06'),(3,1,'L3','Lead',3,NULL,NULL,NULL,NULL,'2026-04-22 05:27:06','2026-04-22 05:27:06'),(4,1,'L4','Manager / Director',4,NULL,NULL,NULL,NULL,'2026-04-22 05:27:06','2026-04-22 05:27:06');

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

CREATE TABLE `holidays` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `name` varchar(120) NOT NULL,
  `holiday_date` date NOT NULL,
  `type` enum('public','optional','company') NOT NULL DEFAULT 'public',
  `applicable_countries` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'array of ISO country codes' CHECK (json_valid(`applicable_countries`)),
  `applicable_branches` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`applicable_branches`)),
  `description` varchar(255) DEFAULT NULL,
  `notify_employees` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_holidays_company_date` (`company_id`,`holiday_date`),
  CONSTRAINT `fk_holidays_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `holidays` (`id`, `company_id`, `name`, `holiday_date`, `type`, `applicable_countries`, `applicable_branches`, `description`, `notify_employees`, `created_at`, `updated_at`) VALUES (1,1,'Eid al-Fitr','2026-04-10','public','[\"AE\",\"IN\"]',NULL,'End of Ramadan — 1 day',1,'2026-04-22 09:12:58','2026-04-22 09:12:58'),(2,1,'Labour Day','2026-05-01','public','[\"AE\",\"IN\",\"PH\",\"NG\"]',NULL,'International Workers Day',1,'2026-04-22 09:12:58','2026-04-22 09:12:58'),(3,1,'Founder\'s Day','2026-06-15','company','[\"AE\",\"IN\",\"PH\",\"NG\"]',NULL,'Brightline founding anniversary',1,'2026-04-22 09:12:58','2026-04-22 09:12:58'),(4,1,'UAE National Day','2026-12-02','public','[\"AE\"]',NULL,'UAE National Day',1,'2026-04-22 09:12:58','2026-04-22 09:12:58'),(5,1,'Christmas Day','2026-12-25','public','[\"PH\",\"NG\",\"AE\",\"IN\"]',NULL,'Christmas',1,'2026-04-22 09:12:58','2026-04-22 09:12:58'),(6,1,'New Year\'s Day','2027-01-01','public','[\"AE\",\"IN\",\"PH\",\"NG\"]',NULL,'New Year',1,'2026-04-22 09:12:58','2026-04-22 09:12:58'),(8,1,'dfsdafadsf','2026-04-22','public','[\"AE\"]',NULL,'',1,'2026-04-22 08:16:47','2026-04-22 08:16:47');

--
-- Table structure for table `hr_chat_messages`
--

CREATE TABLE `hr_chat_messages` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `chat_id` bigint(20) unsigned NOT NULL,
  `sender_type` enum('employee','hr','system','ai') NOT NULL,
  `sender_user_id` bigint(20) unsigned DEFAULT NULL,
  `body` text NOT NULL,
  `attachment_path` varchar(255) DEFAULT NULL,
  `is_ai_draft` tinyint(1) NOT NULL DEFAULT 0,
  `read_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_msg_chat_created` (`chat_id`,`created_at`),
  KEY `fk_msg_sender` (`sender_user_id`),
  CONSTRAINT `fk_msg_chat` FOREIGN KEY (`chat_id`) REFERENCES `hr_chats` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_msg_sender` FOREIGN KEY (`sender_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `hr_chat_messages`
--

INSERT INTO `hr_chat_messages` (`id`, `chat_id`, `sender_type`, `sender_user_id`, `body`, `attachment_path`, `is_ai_draft`, `read_at`, `created_at`) VALUES (1,1,'employee',2,'Hi Anita 👋 I have a quick question about maternity leave eligibility.',NULL,0,'2026-04-21 15:24:18','2026-04-21 13:24:18'),(2,1,'hr',1,'Hi Layla! Happy to help. You joined in March 2024, so you are fully eligible. We offer 180 days paid maternity leave.',NULL,0,'2026-04-21 15:24:18','2026-04-21 15:24:18'),(3,1,'employee',2,'Great, thanks! Can I apply through the portal?',NULL,0,'2026-04-22 03:24:18','2026-04-22 03:24:18'),(4,1,'hr',1,'Yes — go to Leaves → Apply, pick \"Maternity\".',NULL,0,'2026-04-22 06:24:18','2026-04-22 04:24:18'),(5,1,'employee',2,'Thanks, got it!',NULL,0,'2026-04-22 10:29:59','2026-04-22 11:19:18'),(6,2,'employee',1,'Hi Anita, could I get a salary certificate for my bank?',NULL,0,'2026-04-21 11:24:18','2026-04-20 11:24:18'),(7,2,'hr',1,'Of course — what amount do you need listed?',NULL,0,'2026-04-21 11:24:18','2026-04-21 09:24:18'),(8,2,'employee',1,'Uploaded the doctor\'s note.',NULL,0,'2026-04-22 10:41:51','2026-04-22 09:24:18'),(9,3,'employee',1,'Question about WFH allowance',NULL,0,'2026-04-22 08:24:18','2026-04-22 08:24:18'),(10,3,'hr',1,'Yes — up to AED 200/mo for internet + utilities.',NULL,0,'2026-04-22 08:24:18','2026-04-22 08:09:18'),(11,1,'hr',1,'You are welcome! Let me know if you need anything else.',NULL,0,NULL,'2026-04-22 10:29:59'),(12,1,'hr',1,'Live SSE test message',NULL,0,NULL,'2026-04-22 10:30:02'),(13,1,'hr',1,'SSE live push test',NULL,0,NULL,'2026-04-22 10:30:48'),(14,2,'hr',1,'hello',NULL,0,NULL,'2026-04-22 10:42:36'),(15,2,'hr',1,'how to do',NULL,0,NULL,'2026-04-22 10:43:11'),(16,1,'hr',1,'debug send',NULL,0,NULL,'2026-04-22 11:00:19'),(17,2,'hr',1,'wait',NULL,0,NULL,'2026-04-22 11:01:31');

--
-- Table structure for table `hr_chats`
--

CREATE TABLE `hr_chats` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `employee_id` bigint(20) unsigned NOT NULL,
  `assigned_hr_id` bigint(20) unsigned DEFAULT NULL,
  `subject` varchar(200) DEFAULT NULL,
  `last_message_at` timestamp NULL DEFAULT NULL,
  `last_message_preview` varchar(255) DEFAULT NULL,
  `status` enum('open','resolved','archived') NOT NULL DEFAULT 'open',
  `employee_unread` smallint(5) unsigned NOT NULL DEFAULT 0,
  `hr_unread` smallint(5) unsigned NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_chat_company_status` (`company_id`,`status`,`last_message_at`),
  KEY `idx_chat_employee` (`employee_id`),
  KEY `idx_chat_hr` (`assigned_hr_id`),
  CONSTRAINT `fk_chat_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_chat_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_chat_hr` FOREIGN KEY (`assigned_hr_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `hr_chats`
--

INSERT INTO `hr_chats` (`id`, `company_id`, `employee_id`, `assigned_hr_id`, `subject`, `last_message_at`, `last_message_preview`, `status`, `employee_unread`, `hr_unread`, `created_at`, `updated_at`) VALUES (1,1,2,1,'Maternity leave question','2026-04-22 11:00:19','debug send','open',4,0,'2026-04-21 11:24:18','2026-04-22 11:05:07'),(2,1,1,1,'Salary certificate needed','2026-04-22 11:01:31','wait','open',3,0,'2026-04-20 11:24:18','2026-04-22 11:04:54'),(3,1,3,1,'WFH allowance clarification','2026-04-22 08:24:18','Question about WFH allowance','resolved',0,0,'2026-04-17 11:24:18','2026-04-22 08:24:18'),(4,1,4,1,'Onboarding check-in',NULL,NULL,'open',0,0,'2026-04-22 10:29:59','2026-04-22 11:05:08');

--
-- Table structure for table `leave_balance_adjustments`
--

CREATE TABLE `leave_balance_adjustments` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `balance_id` bigint(20) unsigned NOT NULL,
  `action` enum('credit','debit','set') NOT NULL,
  `days` decimal(5,2) NOT NULL,
  `reason` text NOT NULL,
  `adjusted_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_adj_balance` (`balance_id`),
  KEY `fk_adj_user` (`adjusted_by`),
  CONSTRAINT `fk_adj_balance` FOREIGN KEY (`balance_id`) REFERENCES `leave_balances` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_adj_user` FOREIGN KEY (`adjusted_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `leave_balance_adjustments`
--


--
-- Table structure for table `leave_balances`
--

CREATE TABLE `leave_balances` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) unsigned NOT NULL,
  `leave_type_id` bigint(20) unsigned NOT NULL,
  `year` smallint(5) unsigned NOT NULL,
  `entitled` decimal(5,2) NOT NULL DEFAULT 0.00,
  `carried_forward` decimal(5,2) NOT NULL DEFAULT 0.00,
  `credited` decimal(5,2) NOT NULL DEFAULT 0.00,
  `used` decimal(5,2) NOT NULL DEFAULT 0.00,
  `encashed` decimal(5,2) NOT NULL DEFAULT 0.00,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_lb` (`employee_id`,`leave_type_id`,`year`),
  KEY `fk_lb_type` (`leave_type_id`),
  CONSTRAINT `fk_lb_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_lb_type` FOREIGN KEY (`leave_type_id`) REFERENCES `leave_types` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `leave_balances`
--

INSERT INTO `leave_balances` (`id`, `employee_id`, `leave_type_id`, `year`, `entitled`, `carried_forward`, `credited`, `used`, `encashed`, `updated_at`) VALUES (1,1,1,2026,12.00,0.00,0.00,2.00,0.00,'2026-04-22 08:59:11'),(2,2,1,2026,12.00,0.00,0.00,0.00,0.00,'2026-04-22 08:59:11'),(3,3,1,2026,12.00,0.00,0.00,0.00,0.00,'2026-04-22 08:59:11'),(4,1,2,2026,12.00,0.00,0.00,0.00,0.00,'2026-04-22 08:59:11'),(5,2,2,2026,12.00,0.00,0.00,0.00,0.00,'2026-04-22 08:59:11'),(6,3,2,2026,12.00,0.00,0.00,0.00,0.00,'2026-04-22 08:59:11'),(7,1,3,2026,21.00,0.00,0.00,5.00,0.00,'2026-04-22 09:00:58'),(8,2,3,2026,21.00,0.00,0.00,5.00,0.00,'2026-04-22 08:59:11'),(9,3,3,2026,21.00,0.00,0.00,0.00,0.00,'2026-04-22 08:59:11'),(10,1,4,2026,180.00,0.00,0.00,0.00,0.00,'2026-04-22 08:59:11'),(11,2,4,2026,180.00,0.00,0.00,0.00,0.00,'2026-04-22 08:59:11'),(12,3,4,2026,180.00,0.00,0.00,0.00,0.00,'2026-04-22 08:59:11'),(13,1,5,2026,15.00,0.00,0.00,0.00,0.00,'2026-04-22 08:59:11'),(14,2,5,2026,15.00,0.00,0.00,0.00,0.00,'2026-04-22 08:59:11'),(15,3,5,2026,15.00,0.00,0.00,0.00,0.00,'2026-04-22 08:59:11'),(16,1,6,2026,5.00,0.00,0.00,0.00,0.00,'2026-04-22 08:59:11'),(17,2,6,2026,5.00,0.00,0.00,0.00,0.00,'2026-04-22 08:59:11'),(18,3,6,2026,5.00,0.00,0.00,0.00,0.00,'2026-04-22 08:59:11'),(19,1,7,2026,0.00,0.00,0.00,0.00,0.00,'2026-04-22 08:59:11'),(20,2,7,2026,0.00,0.00,0.00,0.00,0.00,'2026-04-22 08:59:11'),(21,3,7,2026,0.00,0.00,0.00,0.00,0.00,'2026-04-22 08:59:11');

--
-- Table structure for table `leave_policies`
--

CREATE TABLE `leave_policies` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `name` varchar(120) NOT NULL,
  `scope_type` enum('all','grade','department','branch','employee') NOT NULL DEFAULT 'all',
  `scope_id` bigint(20) unsigned DEFAULT NULL,
  `entitlements_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '{leave_type_id: days}' CHECK (json_valid(`entitlements_json`)),
  `status` enum('active','archived') NOT NULL 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`),
  KEY `idx_lp_company_scope` (`company_id`,`scope_type`,`scope_id`),
  CONSTRAINT `fk_lp_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `leave_policies`
--


--
-- Table structure for table `leave_requests`
--

CREATE TABLE `leave_requests` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) unsigned NOT NULL,
  `leave_type_id` bigint(20) unsigned NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `days` decimal(5,2) NOT NULL,
  `is_half_day` tinyint(1) NOT NULL DEFAULT 0,
  `half_day_part` enum('first_half','second_half') DEFAULT NULL,
  `reason` text NOT NULL,
  `attachment_path` varchar(255) DEFAULT NULL,
  `status` enum('pending','approved','rejected','cancelled','withdrawn') NOT NULL DEFAULT 'pending',
  `approver_id` bigint(20) unsigned DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `approver_note` varchar(500) DEFAULT NULL,
  `applied_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_lr_employee_status` (`employee_id`,`status`),
  KEY `idx_lr_approver_status` (`approver_id`,`status`),
  KEY `idx_lr_dates` (`start_date`,`end_date`),
  KEY `fk_lr_type` (`leave_type_id`),
  CONSTRAINT `fk_lr_approver` FOREIGN KEY (`approver_id`) REFERENCES `employees` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_lr_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_lr_type` FOREIGN KEY (`leave_type_id`) REFERENCES `leave_types` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `leave_requests`
--

INSERT INTO `leave_requests` (`id`, `employee_id`, `leave_type_id`, `start_date`, `end_date`, `days`, `is_half_day`, `half_day_part`, `reason`, `attachment_path`, `status`, `approver_id`, `approved_at`, `approver_note`, `applied_at`, `created_at`, `updated_at`) VALUES (1,1,3,'2026-05-02','2026-05-06',5.00,0,NULL,'Family wedding in Manila',NULL,'approved',NULL,'2026-04-22 08:00:58','Approved — have fun at the wedding','2026-04-20 08:59:11','2026-04-20 08:59:11','2026-04-22 08:00:58'),(2,2,2,'2026-04-24','2026-04-24',1.00,0,NULL,'Flu — doctor certificate attached',NULL,'rejected',NULL,'2026-04-22 08:00:58','Not enough balance left','2026-04-22 05:59:11','2026-04-22 05:59:11','2026-04-22 08:00:58'),(3,3,3,'2026-05-15','2026-05-22',8.00,0,NULL,'Annual leave to visit family in Ghana',NULL,'pending',NULL,NULL,NULL,'2026-04-21 08:59:11','2026-04-21 08:59:11','2026-04-22 08:59:11'),(4,1,1,'2026-03-10','2026-03-11',2.00,0,NULL,'Personal errands',NULL,'cancelled',NULL,NULL,NULL,'2026-04-02 08:59:11','2026-04-02 08:59:11','2026-04-22 08:00:58'),(5,2,3,'2026-02-01','2026-02-05',5.00,0,NULL,'Vacation',NULL,'approved',NULL,NULL,NULL,'2026-02-21 09:59:11','2026-02-21 09:59:11','2026-02-22 09:59:11'),(6,3,7,'2026-03-20','2026-03-20',1.00,0,NULL,'Personal reasons',NULL,'rejected',NULL,NULL,NULL,'2026-03-23 09:59:11','2026-03-23 09:59:11','2026-03-24 09:59:11');

--
-- Table structure for table `leave_types`
--

CREATE TABLE `leave_types` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `code` varchar(10) NOT NULL,
  `name` varchar(80) NOT NULL,
  `is_paid` tinyint(1) NOT NULL DEFAULT 1,
  `annual_entitlement` decimal(5,2) NOT NULL DEFAULT 0.00,
  `carry_forward_max` decimal(5,2) NOT NULL DEFAULT 0.00,
  `encashment_allowed` tinyint(1) NOT NULL DEFAULT 0,
  `accrual` enum('annual','monthly','on_joining') NOT NULL DEFAULT 'annual',
  `requires_approval` tinyint(1) NOT NULL DEFAULT 1,
  `requires_attachment_after_days` tinyint(3) unsigned DEFAULT NULL,
  `gender_restriction` enum('none','female','male') NOT NULL DEFAULT 'none',
  `status` enum('active','archived') NOT NULL 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 `uq_lt_company_code` (`company_id`,`code`),
  CONSTRAINT `fk_lt_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `leave_types`
--

INSERT INTO `leave_types` (`id`, `company_id`, `code`, `name`, `is_paid`, `annual_entitlement`, `carry_forward_max`, `encashment_allowed`, `accrual`, `requires_approval`, `requires_attachment_after_days`, `gender_restriction`, `status`, `created_at`, `updated_at`) VALUES (1,1,'CL','Casual Leave',1,12.00,5.00,1,'annual',1,NULL,'none','active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(2,1,'SL','Sick Leave',1,12.00,0.00,0,'annual',1,NULL,'none','active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(3,1,'EL','Earned Leave',1,21.00,10.00,1,'monthly',1,NULL,'none','active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(4,1,'ML','Maternity Leave',1,180.00,0.00,0,'on_joining',1,NULL,'female','active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(5,1,'PL','Paternity Leave',1,15.00,0.00,0,'on_joining',1,NULL,'male','active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(6,1,'BL','Bereavement Leave',1,5.00,0.00,0,'on_joining',1,NULL,'none','active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(7,1,'LOP','Loss of Pay',0,0.00,0.00,0,'annual',1,NULL,'none','active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(8,1,'SDY','Study Leave',1,7.00,0.00,0,'annual',1,NULL,'none','archived','2026-04-22 09:54:34','2026-04-22 09:54:34');

--
-- Table structure for table `letter_templates`
--

CREATE TABLE `letter_templates` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `name` varchar(120) NOT NULL,
  `category` varchar(40) NOT NULL COMMENT 'offer·promotion·confirm·noc etc',
  `body_html` mediumtext NOT NULL,
  `merge_fields_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`merge_fields_json`)),
  `tone` enum('warm','formal','friendly') NOT NULL DEFAULT 'warm',
  `is_default_for_category` tinyint(1) NOT NULL DEFAULT 0,
  `is_system` tinyint(1) NOT NULL DEFAULT 0,
  `status` enum('active','draft','archived') NOT NULL DEFAULT 'active',
  `created_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_lt_company_cat` (`company_id`,`category`,`status`),
  CONSTRAINT `fk_lt_company_letter` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `letter_templates`
--

INSERT INTO `letter_templates` (`id`, `company_id`, `name`, `category`, `body_html`, `merge_fields_json`, `tone`, `is_default_for_category`, `is_system`, `status`, `created_by`, `created_at`, `updated_at`) VALUES (1,1,'Employment certificate','employment','<p>This is to certify that {{employee_name}} ({{employee_code}}) has been employed with us since {{join_date}} as {{designation}}.</p><p>Regards,<br>HR Team</p>','[\"employee_name\",\"employee_code\",\"join_date\",\"designation\"]','formal',1,1,'active',1,'2026-04-22 10:14:00','2026-04-22 10:14:00'),(2,1,'Salary certificate','salary','<p>This is to certify that {{employee_name}} is employed with us and earns a gross monthly salary of {{salary}} {{currency}}.</p>','[\"employee_name\",\"salary\",\"currency\"]','formal',1,1,'active',1,'2026-04-22 10:14:00','2026-04-22 10:14:00'),(3,1,'Experience letter','experience','<p>{{employee_name}} worked with us from {{join_date}} to {{exit_date}} as {{designation}}.</p><p>We wish them all the best.</p>','[\"employee_name\",\"join_date\",\"exit_date\",\"designation\"]','warm',1,1,'active',1,'2026-04-22 10:14:00','2026-04-22 10:14:00'),(4,1,'Offer letter','offer','<p>Dear {{employee_name}},</p><p>We are delighted to offer you the position of {{designation}} with a gross monthly salary of {{salary}} {{currency}}, effective {{join_date}}.</p>','[\"employee_name\",\"designation\",\"salary\",\"currency\",\"join_date\"]','warm',1,1,'active',1,'2026-04-22 10:14:00','2026-04-22 10:14:00'),(5,1,'No-objection certificate','noc','<p>We have no objection to {{employee_name}} applying for {{purpose}}.</p>','[\"employee_name\",\"purpose\"]','formal',1,1,'active',1,'2026-04-22 10:14:00','2026-04-22 10:14:00'),(6,1,'Bonus letter','bonus','<p>{{employee_name}}, bonus of {{amount}}.</p>','[\"employee_name\",\"amount\"]','warm',0,0,'archived',1,'2026-04-22 09:18:13','2026-04-22 09:18:13');

--
-- Table structure for table `loan_schedules`
--

CREATE TABLE `loan_schedules` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `loan_id` bigint(20) unsigned NOT NULL,
  `installment_no` tinyint(3) unsigned NOT NULL,
  `due_date` date NOT NULL,
  `period` varchar(7) NOT NULL,
  `principal_due` decimal(14,2) NOT NULL,
  `interest_due` decimal(14,2) NOT NULL DEFAULT 0.00,
  `total_due` decimal(14,2) NOT NULL,
  `balance_after` decimal(14,2) NOT NULL,
  `status` enum('upcoming','due','paid','skipped','waived') NOT NULL DEFAULT 'upcoming',
  `paid_via_payslip_id` bigint(20) unsigned DEFAULT NULL,
  `paid_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ls_loan_no` (`loan_id`,`installment_no`),
  KEY `idx_ls_due` (`due_date`,`status`),
  KEY `fk_ls_payslip` (`paid_via_payslip_id`),
  CONSTRAINT `fk_ls_loan` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ls_payslip` FOREIGN KEY (`paid_via_payslip_id`) REFERENCES `payslips` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `loan_schedules`
--

INSERT INTO `loan_schedules` (`id`, `loan_id`, `installment_no`, `due_date`, `period`, `principal_due`, `interest_due`, `total_due`, `balance_after`, `status`, `paid_via_payslip_id`, `paid_at`) VALUES (1,1,1,'2026-03-25','2026-03',2500.00,125.00,2625.00,27500.00,'paid',NULL,'2026-02-21 10:52:48'),(2,1,2,'2026-04-25','2026-04',2510.00,115.00,2625.00,24990.00,'paid',NULL,'2026-03-23 10:52:48'),(3,1,3,'2026-05-25','2026-05',2520.00,105.00,2625.00,22470.00,'paid',NULL,'2026-04-21 09:52:48'),(4,1,4,'2026-06-25','2026-06',2531.00,94.00,2625.00,19939.00,'upcoming',NULL,NULL),(5,1,5,'2026-07-25','2026-07',2541.00,84.00,2625.00,17398.00,'upcoming',NULL,NULL),(6,1,6,'2026-08-25','2026-08',2552.00,73.00,2625.00,14846.00,'upcoming',NULL,NULL),(7,1,7,'2026-09-25','2026-09',2563.00,62.00,2625.00,12283.00,'upcoming',NULL,NULL),(8,1,8,'2026-10-25','2026-10',2574.00,51.00,2625.00,9709.00,'upcoming',NULL,NULL),(9,1,9,'2026-11-25','2026-11',2585.00,40.00,2625.00,7124.00,'upcoming',NULL,NULL),(10,1,10,'2026-12-25','2026-12',2595.00,30.00,2625.00,4529.00,'upcoming',NULL,NULL),(11,1,11,'2027-01-25','2027-01',2606.00,19.00,2625.00,1923.00,'upcoming',NULL,NULL),(12,1,12,'2027-02-25','2027-02',1923.00,8.00,1931.00,0.00,'upcoming',NULL,NULL),(13,3,1,'2026-04-25','2026-04',2021.00,125.00,2146.00,47979.00,'paid',NULL,'2026-04-21 09:52:48'),(14,3,2,'2026-05-25','2026-05',2026.00,120.00,2146.00,45953.00,'paid',NULL,'2026-04-22 08:55:12'),(15,3,3,'2026-06-25','2026-06',2032.00,114.00,2146.00,43921.00,'upcoming',NULL,NULL),(16,5,1,'2026-05-25','2026-05',666.67,0.00,666.67,3333.33,'upcoming',NULL,NULL),(17,5,2,'2026-06-25','2026-06',666.67,0.00,666.67,2666.66,'upcoming',NULL,NULL),(18,5,3,'2026-07-25','2026-07',666.67,0.00,666.67,1999.99,'upcoming',NULL,NULL),(19,5,4,'2026-08-25','2026-08',666.67,0.00,666.67,1333.32,'upcoming',NULL,NULL),(20,5,5,'2026-09-25','2026-09',666.67,0.00,666.67,666.65,'upcoming',NULL,NULL),(21,5,6,'2026-10-25','2026-10',666.67,0.00,666.67,0.00,'upcoming',NULL,NULL);

--
-- Table structure for table `loan_types`
--

CREATE TABLE `loan_types` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `name` varchar(80) NOT NULL,
  `max_multiple_of_salary` decimal(6,2) DEFAULT NULL,
  `max_amount` decimal(14,2) DEFAULT NULL,
  `currency` char(3) DEFAULT NULL,
  `min_tenure_months` tinyint(3) unsigned NOT NULL DEFAULT 1,
  `max_tenure_months` tinyint(3) unsigned NOT NULL DEFAULT 12,
  `interest_pct` decimal(5,2) NOT NULL DEFAULT 0.00,
  `interest_type` enum('simple','reducing','zero') NOT NULL DEFAULT 'zero',
  `eligibility_tenure_months` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `status` enum('active','archived') NOT NULL DEFAULT 'active',
  PRIMARY KEY (`id`),
  KEY `idx_lt_company` (`company_id`,`status`),
  CONSTRAINT `fk_lntype_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `loan_types`
--

INSERT INTO `loan_types` (`id`, `company_id`, `name`, `max_multiple_of_salary`, `max_amount`, `currency`, `min_tenure_months`, `max_tenure_months`, `interest_pct`, `interest_type`, `eligibility_tenure_months`, `status`) VALUES (1,1,'Salary advance',1.00,NULL,NULL,1,1,0.00,'zero',0,'active'),(2,1,'Personal loan',6.00,NULL,NULL,1,24,5.00,'simple',12,'active'),(3,1,'Education loan',NULL,NULL,NULL,6,36,3.00,'simple',24,'active'),(4,1,'Home advance',2.00,NULL,NULL,3,12,0.00,'zero',6,'active'),(5,1,'Emergency loan',1.00,NULL,NULL,1,6,0.00,'zero',0,'active');

--
-- Table structure for table `loans`
--

CREATE TABLE `loans` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(20) unsigned NOT NULL,
  `type_id` bigint(20) unsigned NOT NULL,
  `principal` decimal(14,2) NOT NULL,
  `currency` char(3) NOT NULL,
  `tenure_months` tinyint(3) unsigned NOT NULL,
  `interest_pct` decimal(5,2) NOT NULL DEFAULT 0.00,
  `emi` decimal(14,2) NOT NULL,
  `purpose` text DEFAULT NULL,
  `status` enum('pending','approved','active','closed','rejected','cancelled','defaulted') NOT NULL DEFAULT 'pending',
  `applied_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `approved_by` bigint(20) unsigned DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `disbursed_at` date DEFAULT NULL,
  `first_emi_period` varchar(7) DEFAULT NULL COMMENT 'YYYY-MM',
  `total_paid` decimal(14,2) NOT NULL DEFAULT 0.00,
  `outstanding` decimal(14,2) NOT NULL,
  `closed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_loans_employee_status` (`employee_id`,`status`),
  KEY `fk_loans_type` (`type_id`),
  KEY `fk_loans_approver` (`approved_by`),
  CONSTRAINT `fk_loans_approver` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_loans_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_loans_type` FOREIGN KEY (`type_id`) REFERENCES `loan_types` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `loans`
--

INSERT INTO `loans` (`id`, `employee_id`, `type_id`, `principal`, `currency`, `tenure_months`, `interest_pct`, `emi`, `purpose`, `status`, `applied_at`, `approved_by`, `approved_at`, `disbursed_at`, `first_emi_period`, `total_paid`, `outstanding`, `closed_at`, `created_at`, `updated_at`) VALUES (1,1,2,30000.00,'AED',12,5.00,2625.00,'Home renovation','active','2026-01-22 10:52:16',1,'2026-01-24 10:52:16','2026-01-24','2026-03',7875.00,22125.00,NULL,'2026-01-22 10:52:16','2026-04-22 09:52:16'),(2,2,1,5000.00,'AED',1,0.00,5000.00,'Medical emergency','closed','2026-02-21 10:52:16',1,'2026-02-23 10:52:16','2026-02-23','2026-03',5000.00,0.00,NULL,'2026-02-21 10:52:16','2026-04-22 09:52:16'),(3,3,3,50000.00,'AED',24,3.00,2145.83,'MBA program','active','2026-03-08 10:52:16',1,'2026-03-11 10:52:16','2026-03-11','2026-04',4291.83,45708.17,NULL,'2026-03-08 10:52:16','2026-04-22 08:55:12'),(4,2,4,8000.00,'AED',6,0.00,1333.33,'Housing deposit','rejected','2026-04-20 09:52:16',1,'2026-04-22 08:55:12',NULL,NULL,0.00,8000.00,NULL,'2026-04-20 09:52:16','2026-04-22 08:55:12'),(5,2,5,4000.00,'AED',6,0.00,666.67,'Car repair','closed','2026-04-22 08:55:11',1,'2026-04-22 08:55:11','2026-04-22','2026-05',0.00,0.00,'2026-04-22 08:55:12','2026-04-22 08:55:11','2026-04-22 08:55:12');

--
-- Table structure for table `notification_preferences`
--

CREATE TABLE `notification_preferences` (
  `user_id` bigint(20) unsigned NOT NULL,
  `event_type` varchar(60) NOT NULL,
  `channel_in_app` tinyint(1) NOT NULL DEFAULT 1,
  `channel_email` tinyint(1) NOT NULL DEFAULT 1,
  `channel_push` tinyint(1) NOT NULL DEFAULT 1,
  `channel_whatsapp` tinyint(1) NOT NULL DEFAULT 0,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`user_id`,`event_type`),
  CONSTRAINT `fk_np_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `notification_preferences`
--


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

CREATE TABLE `notifications` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `company_id` bigint(20) unsigned NOT NULL,
  `type` varchar(60) NOT NULL,
  `icon` varchar(40) DEFAULT NULL,
  `severity` enum('info','success','warning','danger','ai') NOT NULL DEFAULT 'info',
  `title` varchar(200) NOT NULL,
  `body` varchar(500) DEFAULT NULL,
  `url` varchar(500) DEFAULT NULL,
  `metadata_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`metadata_json`)),
  `channel_in_app` tinyint(1) NOT NULL DEFAULT 1,
  `channel_email` tinyint(1) NOT NULL DEFAULT 0,
  `channel_push` tinyint(1) NOT NULL DEFAULT 0,
  `email_sent_at` timestamp NULL DEFAULT NULL,
  `push_sent_at` timestamp NULL DEFAULT NULL,
  `read_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_notif_user_read` (`user_id`,`read_at`,`created_at`),
  KEY `idx_notif_type` (`type`),
  KEY `fk_notif_company` (`company_id`),
  CONSTRAINT `fk_notif_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_notif_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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


--
-- Table structure for table `overtime_rules`
--

CREATE TABLE `overtime_rules` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `name` varchar(80) NOT NULL,
  `trigger_type` enum('after_hours_per_day','weekly_off','public_holiday','night_hours') NOT NULL,
  `threshold_hours` decimal(4,2) DEFAULT NULL,
  `multiplier` decimal(4,2) NOT NULL DEFAULT 1.50,
  `monthly_cap_hours` smallint(5) unsigned DEFAULT NULL,
  `status` enum('active','archived') NOT NULL DEFAULT 'active',
  PRIMARY KEY (`id`),
  KEY `idx_ot_company` (`company_id`),
  CONSTRAINT `fk_ot_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `overtime_rules`
--


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

CREATE TABLE `password_resets` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(191) NOT NULL,
  `token_hash` char(64) NOT NULL COMMENT 'sha256 of token — never store raw',
  `ip_address` varchar(45) DEFAULT NULL,
  `expires_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `used_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_pwr_token` (`token_hash`),
  KEY `idx_pwr_email` (`email`),
  KEY `idx_pwr_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `password_resets`
--


--
-- Table structure for table `payroll_anomalies`
--

CREATE TABLE `payroll_anomalies` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `run_id` bigint(20) unsigned NOT NULL,
  `employee_id` bigint(20) unsigned DEFAULT NULL,
  `severity` enum('info','warning','critical') NOT NULL DEFAULT 'warning',
  `code` varchar(60) NOT NULL COMMENT 'net_delta · missing_bank · lop_spike',
  `message` varchar(500) NOT NULL,
  `metadata_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`metadata_json`)),
  `status` enum('open','reviewed','ignored','resolved') NOT NULL DEFAULT 'open',
  `reviewed_by` bigint(20) unsigned DEFAULT NULL,
  `reviewed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_anomaly_run_status` (`run_id`,`status`),
  KEY `fk_anomaly_employee` (`employee_id`),
  CONSTRAINT `fk_anomaly_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_anomaly_run` FOREIGN KEY (`run_id`) REFERENCES `payroll_runs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `payroll_anomalies`
--


--
-- Table structure for table `payroll_periods`
--

CREATE TABLE `payroll_periods` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `year` smallint(5) unsigned NOT NULL,
  `month` tinyint(3) unsigned NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `cutoff_date` date NOT NULL,
  `pay_date` date NOT NULL,
  `status` enum('draft','open','locked','disbursed','closed') NOT NULL DEFAULT 'draft',
  `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 `uq_period_company_month` (`company_id`,`year`,`month`),
  CONSTRAINT `fk_pp_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `payroll_periods`
--

INSERT INTO `payroll_periods` (`id`, `company_id`, `year`, `month`, `start_date`, `end_date`, `cutoff_date`, `pay_date`, `status`, `created_at`, `updated_at`) VALUES (1,1,2026,2,'2026-02-01','2026-02-28','2026-02-25','2026-03-01','disbursed','2026-02-21 10:18:23','2026-02-26 10:18:23'),(2,1,2026,3,'2026-03-01','2026-03-31','2026-03-25','2026-04-01','disbursed','2026-03-23 10:18:23','2026-03-28 10:18:23'),(3,1,2026,4,'2026-04-01','2026-04-30','2026-04-25','2026-05-01','open','2026-04-17 09:18:23','2026-04-22 09:18:23');

--
-- Table structure for table `payroll_runs`
--

CREATE TABLE `payroll_runs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `period_id` bigint(20) unsigned NOT NULL,
  `company_id` bigint(20) unsigned NOT NULL,
  `run_type` enum('regular','correction','off_cycle','bonus') NOT NULL DEFAULT 'regular',
  `step` enum('period','inputs','compute','review','approve','disbursed','closed') NOT NULL DEFAULT 'period',
  `employee_count` int(10) unsigned NOT NULL DEFAULT 0,
  `total_gross_base` decimal(18,2) DEFAULT NULL,
  `total_net_base` decimal(18,2) DEFAULT NULL,
  `total_deductions_base` decimal(18,2) DEFAULT NULL,
  `base_currency` char(3) NOT NULL,
  `currencies_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'per-currency totals' CHECK (json_valid(`currencies_json`)),
  `anomaly_count` smallint(5) unsigned NOT NULL DEFAULT 0,
  `started_by` bigint(20) unsigned DEFAULT NULL,
  `approved_by` bigint(20) unsigned DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `disbursed_at` timestamp NULL DEFAULT NULL,
  `locked_at` timestamp NULL DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_runs_period` (`period_id`),
  KEY `idx_runs_step` (`company_id`,`step`),
  KEY `fk_runs_started_by` (`started_by`),
  KEY `fk_runs_approved_by` (`approved_by`),
  CONSTRAINT `fk_runs_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_runs_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_runs_period` FOREIGN KEY (`period_id`) REFERENCES `payroll_periods` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_runs_started_by` FOREIGN KEY (`started_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `payroll_runs`
--

INSERT INTO `payroll_runs` (`id`, `period_id`, `company_id`, `run_type`, `step`, `employee_count`, `total_gross_base`, `total_net_base`, `total_deductions_base`, `base_currency`, `currencies_json`, `anomaly_count`, `started_by`, `approved_by`, `approved_at`, `disbursed_at`, `locked_at`, `notes`, `created_at`, `updated_at`) VALUES (1,1,1,'regular','closed',3,45000.00,40500.00,4500.00,'AED',NULL,0,1,1,NULL,NULL,NULL,NULL,'2026-02-23 10:18:23','2026-02-26 10:18:23'),(2,2,1,'regular','closed',3,45000.00,40500.00,4500.00,'AED',NULL,1,1,1,NULL,NULL,NULL,NULL,'2026-03-25 10:18:23','2026-03-28 10:18:23'),(4,3,1,'regular','disbursed',3,NULL,NULL,NULL,'AED',NULL,0,NULL,1,'2026-04-22 08:21:35',NULL,NULL,NULL,'2026-04-22 08:21:35','2026-04-22 08:21:35');

--
-- Table structure for table `payslips`
--

CREATE TABLE `payslips` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `run_id` bigint(20) unsigned NOT NULL,
  `employee_id` bigint(20) unsigned NOT NULL,
  `code` varchar(60) NOT NULL COMMENT 'human-readable eg BLS-IN-0042-NOV2026',
  `period_year` smallint(5) unsigned NOT NULL,
  `period_month` tinyint(3) unsigned NOT NULL,
  `paid_days` decimal(5,2) NOT NULL,
  `total_days` decimal(5,2) NOT NULL,
  `lop_days` decimal(5,2) NOT NULL DEFAULT 0.00,
  `gross` decimal(14,2) NOT NULL,
  `total_earnings` decimal(14,2) NOT NULL,
  `total_deductions` decimal(14,2) NOT NULL,
  `net_pay` decimal(14,2) NOT NULL,
  `currency` char(3) NOT NULL,
  `fx_rate_snapshot` decimal(14,8) DEFAULT NULL,
  `components_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'frozen payslip content' CHECK (json_valid(`components_json`)),
  `ytd_gross` decimal(14,2) DEFAULT NULL,
  `ytd_tax` decimal(14,2) DEFAULT NULL,
  `bank_id` bigint(20) unsigned DEFAULT NULL,
  `pdf_path` varchar(255) DEFAULT NULL,
  `status` enum('draft','issued','emailed','viewed','revoked') NOT NULL DEFAULT 'draft',
  `issued_at` timestamp NULL DEFAULT NULL,
  `viewed_at` timestamp NULL DEFAULT NULL,
  `is_flagged` tinyint(1) NOT NULL DEFAULT 0,
  `flag_reason` varchar(500) DEFAULT NULL,
  `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 `uq_payslip_run_employee` (`run_id`,`employee_id`),
  UNIQUE KEY `uq_payslip_code` (`code`),
  KEY `idx_payslip_employee_period` (`employee_id`,`period_year`,`period_month`),
  KEY `idx_payslip_flagged` (`run_id`,`is_flagged`),
  KEY `fk_payslip_bank` (`bank_id`),
  CONSTRAINT `fk_payslip_bank` FOREIGN KEY (`bank_id`) REFERENCES `employee_bank` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_payslip_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_payslip_run` FOREIGN KEY (`run_id`) REFERENCES `payroll_runs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `payslips`
--

INSERT INTO `payslips` (`id`, `run_id`, `employee_id`, `code`, `period_year`, `period_month`, `paid_days`, `total_days`, `lop_days`, `gross`, `total_earnings`, `total_deductions`, `net_pay`, `currency`, `fx_rate_snapshot`, `components_json`, `ytd_gross`, `ytd_tax`, `bank_id`, `pdf_path`, `status`, `issued_at`, `viewed_at`, `is_flagged`, `flag_reason`, `created_at`, `updated_at`) VALUES (1,1,1,'PS-2026-02-001',2026,2,28.00,28.00,0.00,15000.00,15000.00,1500.00,13500.00,'AED',NULL,'[{\"name\":\"Basic\",\"amount\":9000},{\"name\":\"HRA\",\"amount\":4500},{\"name\":\"Transport\",\"amount\":1500},{\"name\":\"Tax\",\"amount\":-1500}]',NULL,NULL,NULL,NULL,'emailed','2026-04-22 08:37:11',NULL,0,NULL,'2026-02-23 10:18:23','2026-04-22 08:37:11'),(2,1,2,'PS-2026-02-002',2026,2,28.00,28.00,0.00,15000.00,15000.00,1500.00,13500.00,'AED',NULL,'[{\"name\":\"Basic\",\"amount\":9000},{\"name\":\"HRA\",\"amount\":4500},{\"name\":\"Transport\",\"amount\":1500},{\"name\":\"Tax\",\"amount\":-1500}]',NULL,NULL,NULL,NULL,'issued','2026-02-26 10:18:23',NULL,0,NULL,'2026-02-23 10:18:23','2026-04-22 08:37:11'),(3,1,3,'PS-2026-02-003',2026,2,28.00,28.00,0.00,15000.00,15000.00,1500.00,13500.00,'AED',NULL,'[{\"name\":\"Basic\",\"amount\":9000},{\"name\":\"HRA\",\"amount\":4500},{\"name\":\"Transport\",\"amount\":1500},{\"name\":\"Tax\",\"amount\":-1500}]',NULL,NULL,NULL,NULL,'issued','2026-02-26 10:18:23',NULL,0,NULL,'2026-02-23 10:18:23','2026-04-22 09:18:23'),(4,2,1,'PS-2026-03-001',2026,3,31.00,31.00,0.00,15000.00,15000.00,1500.00,13500.00,'AED',NULL,'[{\"name\":\"Basic\",\"amount\":9000},{\"name\":\"HRA\",\"amount\":4500},{\"name\":\"Transport\",\"amount\":1500},{\"name\":\"Tax\",\"amount\":-1500}]',NULL,NULL,NULL,NULL,'issued','2026-04-22 08:37:11',NULL,0,NULL,'2026-03-25 10:18:23','2026-04-22 08:37:11'),(5,2,2,'PS-2026-03-002',2026,3,31.00,31.00,0.00,15000.00,15000.00,1500.00,13500.00,'AED',NULL,'[{\"name\":\"Basic\",\"amount\":9000},{\"name\":\"HRA\",\"amount\":4500},{\"name\":\"Transport\",\"amount\":1500},{\"name\":\"Tax\",\"amount\":-1500}]',NULL,NULL,NULL,NULL,'revoked','2026-03-28 10:18:23',NULL,0,NULL,'2026-03-25 10:18:23','2026-04-22 08:37:11'),(6,2,3,'PS-2026-03-003',2026,3,28.00,31.00,3.00,15000.00,15000.00,1500.00,12000.00,'AED',NULL,'[{\"name\":\"Basic\",\"amount\":9000},{\"name\":\"HRA\",\"amount\":4500},{\"name\":\"Transport\",\"amount\":1500},{\"name\":\"LOP\",\"amount\":-1500},{\"name\":\"Tax\",\"amount\":-1500}]',NULL,NULL,NULL,NULL,'emailed','2026-04-22 08:41:47',NULL,1,NULL,'2026-03-25 10:18:23','2026-04-22 08:41:47');

--
-- Table structure for table `permissions`
--

CREATE TABLE `permissions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `slug` varchar(120) NOT NULL COMMENT 'module.action eg employees.view',
  `module` varchar(40) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_permissions_slug` (`slug`),
  KEY `idx_permissions_module` (`module`)
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `permissions`
--

INSERT INTO `permissions` (`id`, `slug`, `module`, `description`) VALUES (1,'employees.view','employees','View employee directory and profiles'),(2,'employees.create','employees','Create new employees'),(3,'employees.edit','employees','Edit employee details'),(4,'employees.delete','employees','Archive / offboard employees'),(5,'employees.bulk_import','employees','Bulk import employees'),(6,'attendance.view_own','attendance','View own attendance'),(7,'attendance.view_team','attendance','View team attendance'),(8,'attendance.view_all','attendance','View all-company attendance'),(9,'attendance.mark_manual','attendance','Manually mark attendance'),(10,'attendance.regularise','attendance','Approve regularisation requests'),(11,'leave.apply','leave','Apply for leave'),(12,'leave.approve_team','leave','Approve team leaves'),(13,'leave.approve_all','leave','Approve any leave'),(14,'leave.adjust_balance','leave','Adjust leave balances'),(15,'payroll.run','payroll','Run payroll'),(16,'payroll.approve','payroll','Approve payroll'),(17,'payroll.view_all','payroll','View all payslips'),(18,'payroll.generate_bank_file','payroll','Generate bank files'),(19,'payroll.corrections','payroll','Run corrections and reversals'),(20,'salary.assign','salary','Assign or revise salary'),(21,'salary.view_all','salary','View all salaries'),(22,'loans.apply','loans','Apply for loans'),(23,'loans.approve','loans','Approve loans'),(24,'expenses.submit','expenses','Submit expense claims'),(25,'expenses.approve_team','expenses','Approve team expenses'),(26,'expenses.approve_all','expenses','Approve any expense'),(27,'compliance.view','compliance','View compliance calendar'),(28,'compliance.manage','compliance','Add / edit deadlines'),(29,'compliance.complete','compliance','Mark deadlines complete'),(30,'documents.generate','documents','Generate letters'),(31,'documents.templates','documents','Manage letter templates'),(32,'announcements.post','announcements','Post announcements'),(33,'reports.view','reports','View reports'),(34,'reports.export','reports','Export reports'),(35,'ai.use','ai','Use AI features'),(36,'ai.configure','ai','Configure AI (BYOK key, budget)'),(37,'ai.view_audit','ai','View AI audit log'),(38,'settings.view','settings','View settings'),(39,'settings.edit','settings','Edit settings'),(40,'settings.users','settings','Manage users and roles'),(41,'settings.billing','settings','Manage billing and workspace');

--
-- Table structure for table `push_subscriptions`
--

CREATE TABLE `push_subscriptions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `endpoint` text NOT NULL,
  `endpoint_hash` char(64) NOT NULL COMMENT 'sha256 for lookup',
  `p256dh_key` varchar(255) NOT NULL,
  `auth_token` varchar(255) NOT NULL,
  `device_label` varchar(120) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `last_used_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_push_hash` (`endpoint_hash`),
  KEY `idx_push_user` (`user_id`),
  CONSTRAINT `fk_push_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `push_subscriptions`
--


--
-- Table structure for table `role_permissions`
--

CREATE TABLE `role_permissions` (
  `role_id` bigint(20) unsigned NOT NULL,
  `permission_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`role_id`,`permission_id`),
  KEY `idx_rp_permission` (`permission_id`),
  CONSTRAINT `fk_rp_permission` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rp_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `role_permissions`
--

INSERT INTO `role_permissions` (`role_id`, `permission_id`) VALUES (1,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),(2,1),(2,2),(2,3),(2,4),(2,5),(2,8),(2,9),(2,10),(2,13),(2,14),(2,15),(2,17),(2,19),(2,20),(2,21),(2,26),(2,27),(2,28),(2,29),(2,30),(2,31),(2,32),(2,33),(2,34),(2,35),(2,38),(3,15),(3,16),(3,17),(3,18),(3,19),(3,21),(3,23),(3,26),(3,33),(3,34),(3,35),(4,1),(4,7),(4,12),(4,25),(4,33),(4,35),(5,6),(5,11),(5,22),(5,24),(5,27),(5,35);

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

CREATE TABLE `roles` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned DEFAULT NULL COMMENT 'NULL = system default',
  `name` varchar(80) NOT NULL,
  `slug` varchar(80) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `is_system` tinyint(1) NOT NULL DEFAULT 0,
  `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 `uq_roles_company_slug` (`company_id`,`slug`),
  CONSTRAINT `fk_roles_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `roles` (`id`, `company_id`, `name`, `slug`, `description`, `is_system`, `created_at`, `updated_at`) VALUES (1,1,'Super Admin','super_admin','Full system access · billing · AI config',1,'2026-04-22 05:27:06','2026-04-22 05:27:06'),(2,1,'HR Manager','hr_manager','Employee lifecycle · payroll · approvals',1,'2026-04-22 05:27:06','2026-04-22 05:27:06'),(3,1,'Finance','finance','Payroll approval · expenses · bank files',1,'2026-04-22 05:27:06','2026-04-22 05:27:06'),(4,1,'Team Manager','team_manager','Approve direct-report leaves and expenses',1,'2026-04-22 05:27:06','2026-04-22 05:27:06'),(5,1,'Employee','employee','Self-service only',1,'2026-04-22 05:27:06','2026-04-22 05:27:06');

--
-- Table structure for table `salary_components`
--

CREATE TABLE `salary_components` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `code` varchar(30) NOT NULL,
  `name` varchar(80) NOT NULL,
  `type` enum('earning','deduction','reimbursement','statutory','employer') NOT NULL,
  `formula` varchar(500) DEFAULT NULL COMMENT 'whitelisted expression',
  `is_taxable` tinyint(1) DEFAULT 1,
  `statutory_mapping` varchar(60) DEFAULT NULL,
  `payslip_label` varchar(80) DEFAULT NULL,
  `display_order` smallint(5) unsigned NOT NULL DEFAULT 0,
  `status` enum('active','archived') NOT NULL 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 `uq_sc_company_code` (`company_id`,`code`),
  KEY `idx_sc_type` (`company_id`,`type`,`status`),
  CONSTRAINT `fk_sc_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `salary_components`
--

INSERT INTO `salary_components` (`id`, `company_id`, `code`, `name`, `type`, `formula`, `is_taxable`, `statutory_mapping`, `payslip_label`, `display_order`, `status`, `created_at`, `updated_at`) VALUES (1,1,'BASIC','Basic','earning','ctc * 0.40',1,NULL,'Basic',1,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(2,1,'HRA','HRA','earning','basic * 0.50',1,NULL,'House Rent Allowance',2,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(3,1,'CONV','Conveyance','earning','3200',1,NULL,'Conveyance Allowance',3,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(4,1,'SPECIAL','Special Allowance','earning','gross - basic - hra - conv',1,NULL,'Special Allowance',4,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(5,1,'BONUS','Performance Bonus','earning','0',1,NULL,'Performance Bonus',5,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(6,1,'PF_EMP','Provident Fund (employee)','deduction','min(basic * 0.12, 1800)',0,NULL,'PF',10,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(7,1,'PT','Professional Tax','deduction','200',0,NULL,'Professional Tax',11,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(8,1,'TDS','Income Tax','deduction','0',0,NULL,'Income Tax',12,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(9,1,'HI','Health Insurance','deduction','450',0,NULL,'Health Insurance',13,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(10,1,'EMI','Loan EMI','deduction','0',0,NULL,'Loan EMI',14,'active','2026-04-22 05:27:06','2026-04-22 05:27:06');

--
-- Table structure for table `salary_template_components`
--

CREATE TABLE `salary_template_components` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `template_id` bigint(20) unsigned NOT NULL,
  `component_id` bigint(20) unsigned NOT NULL,
  `formula_override` varchar(500) DEFAULT NULL,
  `display_order` smallint(5) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_stc` (`template_id`,`component_id`),
  KEY `idx_stc_component` (`component_id`),
  CONSTRAINT `fk_stc_component` FOREIGN KEY (`component_id`) REFERENCES `salary_components` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_stc_template` FOREIGN KEY (`template_id`) REFERENCES `salary_templates` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `salary_template_components`
--


--
-- Table structure for table `salary_templates`
--

CREATE TABLE `salary_templates` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `name` varchar(120) NOT NULL,
  `country_code` char(2) DEFAULT NULL,
  `currency` char(3) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `status` enum('active','archived') NOT NULL 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`),
  KEY `idx_st_company` (`company_id`,`status`),
  CONSTRAINT `fk_st_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `salary_templates`
--


--
-- Table structure for table `settings`
--

CREATE TABLE `settings` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned DEFAULT NULL COMMENT 'NULL = system-wide',
  `key_slug` varchar(100) NOT NULL,
  `value_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`value_json`)),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_settings_company_key` (`company_id`,`key_slug`),
  CONSTRAINT `fk_settings_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `settings`
--

INSERT INTO `settings` (`id`, `company_id`, `key_slug`, `value_json`, `updated_at`) VALUES (1,NULL,'demo_schedule','{\"last_reset_at\":\"2026-04-22T05:46:17+00:00\",\"next_reset_at\":\"2026-04-22T11:46:17+00:00\",\"interval_hours\":6}','2026-04-22 05:46:17'),(2,1,'modules','{\"chat\":false,\"ai\":true}','2026-04-22 09:45:17'),(3,1,'payroll_rules','{\"frequency\":\"monthly\",\"cutoff_day\":25,\"pay_day\":1,\"ot_weekday\":1.5}','2026-04-22 09:54:34'),(4,1,'attendance_rules','{\"grace_minutes\":15,\"auto_absent_minutes\":240,\"allow_wfh\":true}','2026-04-22 09:54:34'),(5,1,'notification_prefs','{\"leave.applied.email\":true,\"leave.applied.sms\":false}','2026-04-22 09:54:34'),(6,1,'theme','{\"mode\":\"dark\",\"sidebar\":\"remember\"}','2026-04-22 09:54:34'),(7,1,'country_packs','{\"AE\":true,\"IN\":true,\"PH\":false}','2026-04-22 09:54:34');

--
-- Table structure for table `shifts`
--

CREATE TABLE `shifts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `name` varchar(80) NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `break_minutes` smallint(5) unsigned NOT NULL DEFAULT 60,
  `grace_minutes` smallint(5) unsigned NOT NULL DEFAULT 15,
  `half_day_hours` decimal(4,2) NOT NULL DEFAULT 4.00,
  `weekly_off_days` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`weekly_off_days`)),
  `overtime_rule_id` bigint(20) unsigned DEFAULT NULL,
  `is_flexible` tinyint(1) NOT NULL DEFAULT 0,
  `status` enum('active','archived') NOT NULL 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`),
  KEY `idx_shifts_company` (`company_id`,`status`),
  CONSTRAINT `fk_shifts_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `shifts`
--

INSERT INTO `shifts` (`id`, `company_id`, `name`, `start_time`, `end_time`, `break_minutes`, `grace_minutes`, `half_day_hours`, `weekly_off_days`, `overtime_rule_id`, `is_flexible`, `status`, `created_at`, `updated_at`) VALUES (1,1,'Standard (UAE/IN)','09:00:00','18:00:00',60,15,4.00,'[6,0]',NULL,0,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(2,1,'Evening shift','14:00:00','23:00:00',60,10,4.00,'[6,0]',NULL,0,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(3,1,'Early shift (Manila)','07:00:00','16:00:00',45,15,4.00,'[6,0]',NULL,0,'active','2026-04-22 05:27:06','2026-04-22 05:27:06'),(4,1,'Night Ops (22-06)','22:00:00','06:00:00',60,10,4.00,'[0,6]',NULL,0,'archived','2026-04-22 08:09:59','2026-04-22 08:09:59'),(5,1,'ewafasfe','09:00:00','18:00:00',60,15,4.00,'[0,6]',NULL,0,'active','2026-04-22 08:10:45','2026-04-22 08:10:45');

--
-- Table structure for table `support_tickets`
--

CREATE TABLE `support_tickets` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `reference` varchar(20) NOT NULL,
  `category` varchar(40) NOT NULL,
  `priority` enum('normal','high','critical') NOT NULL DEFAULT 'normal',
  `subject` varchar(200) NOT NULL,
  `description` mediumtext NOT NULL,
  `attachment_path` varchar(255) DEFAULT NULL,
  `status` enum('open','in_review','resolved','closed') NOT NULL DEFAULT 'open',
  `resolved_at` timestamp NULL DEFAULT NULL,
  `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 `uq_ticket_ref` (`reference`),
  KEY `idx_ticket_company_status` (`company_id`,`status`),
  KEY `fk_ticket_user` (`user_id`),
  CONSTRAINT `fk_ticket_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ticket_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `support_tickets`
--


--
-- Table structure for table `two_factor_tokens`
--

CREATE TABLE `two_factor_tokens` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `token_hash` char(64) NOT NULL,
  `channel` enum('email','totp','sms','recovery') NOT NULL DEFAULT 'email',
  `expires_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `consumed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_2fa_user` (`user_id`,`consumed_at`),
  CONSTRAINT `fk_2fa_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `two_factor_tokens`
--


--
-- Table structure for table `user_roles`
--

CREATE TABLE `user_roles` (
  `user_id` bigint(20) unsigned NOT NULL,
  `role_id` bigint(20) unsigned NOT NULL,
  `assigned_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `assigned_by` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`user_id`,`role_id`),
  KEY `idx_ur_role` (`role_id`),
  CONSTRAINT `fk_ur_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ur_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `user_roles`
--

INSERT INTO `user_roles` (`user_id`, `role_id`, `assigned_at`, `assigned_by`) VALUES (1,1,'2026-04-22 05:27:06',NULL);

--
-- Table structure for table `user_sessions`
--

CREATE TABLE `user_sessions` (
  `id` char(40) NOT NULL COMMENT 'session token',
  `user_id` bigint(20) unsigned NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `payload` text DEFAULT NULL,
  `last_activity` int(10) unsigned NOT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_sessions_user` (`user_id`),
  KEY `idx_sessions_activity` (`last_activity`),
  CONSTRAINT `fk_sessions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `user_sessions`
--




