-- 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 SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- MariaDB dump 10.19 Distrib 10.4.28-MariaDB, for osx10.10 (x86_64) -- -- Host: localhost Database: workbee -- ------------------------------------------------------ -- Server version 10.4.28-MariaDB /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `activity_logs` -- DROP TABLE IF EXISTS `activity_logs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `activity_logs` -- LOCK TABLES `activity_logs` WRITE; /*!40000 ALTER TABLE `activity_logs` DISABLE KEYS */; /*!40000 ALTER TABLE `activity_logs` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `ai_policy_docs` -- DROP TABLE IF EXISTS `ai_policy_docs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `ai_policy_docs` -- LOCK TABLES `ai_policy_docs` WRITE; /*!40000 ALTER TABLE `ai_policy_docs` DISABLE KEYS */; /*!40000 ALTER TABLE `ai_policy_docs` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `ai_prompts` -- DROP TABLE IF EXISTS `ai_prompts`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `ai_prompts` -- LOCK TABLES `ai_prompts` WRITE; /*!40000 ALTER TABLE `ai_prompts` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `ai_prompts` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `ai_settings` -- DROP TABLE IF EXISTS `ai_settings`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `ai_settings` -- LOCK TABLES `ai_settings` WRITE; /*!40000 ALTER TABLE `ai_settings` DISABLE KEYS */; /*!40000 ALTER TABLE `ai_settings` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `ai_usage_logs` -- DROP TABLE IF EXISTS `ai_usage_logs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `ai_usage_logs` -- LOCK TABLES `ai_usage_logs` WRITE; /*!40000 ALTER TABLE `ai_usage_logs` DISABLE KEYS */; /*!40000 ALTER TABLE `ai_usage_logs` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `announcement_reads` -- DROP TABLE IF EXISTS `announcement_reads`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `announcement_reads` -- LOCK TABLES `announcement_reads` WRITE; /*!40000 ALTER TABLE `announcement_reads` DISABLE KEYS */; 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); /*!40000 ALTER TABLE `announcement_reads` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `announcements` -- DROP TABLE IF EXISTS `announcements`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `announcements` -- LOCK TABLES `announcements` WRITE; /*!40000 ALTER TABLE `announcements` DISABLE KEYS */; 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','

Team — Q1 wrap-up calls this Friday at 4pm GST. Strong revenue quarter, thanks to everyone!

','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','

Carry-forward cap is now 10 days/year (was 15). Please acknowledge by end of month so we have a record.

','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)','

Dubai office will be closed Monday for Eid. WFH if urgent.

','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','

Draft copy — finalize with Aetna contact details before publishing.

','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)','

Hello

','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'); /*!40000 ALTER TABLE `announcements` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `assets` -- DROP TABLE IF EXISTS `assets`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `assets` -- LOCK TABLES `assets` WRITE; /*!40000 ALTER TABLE `assets` DISABLE KEYS */; /*!40000 ALTER TABLE `assets` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `attendance` -- DROP TABLE IF EXISTS `attendance`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `attendance` -- LOCK TABLES `attendance` WRITE; /*!40000 ALTER TABLE `attendance` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `attendance` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `attendance_regularisation` -- DROP TABLE IF EXISTS `attendance_regularisation`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `attendance_regularisation` -- LOCK TABLES `attendance_regularisation` WRITE; /*!40000 ALTER TABLE `attendance_regularisation` DISABLE KEYS */; /*!40000 ALTER TABLE `attendance_regularisation` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `backups` -- DROP TABLE IF EXISTS `backups`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `backups` -- LOCK TABLES `backups` WRITE; /*!40000 ALTER TABLE `backups` DISABLE KEYS */; /*!40000 ALTER TABLE `backups` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `bank_file_exports` -- DROP TABLE IF EXISTS `bank_file_exports`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `bank_file_exports` -- LOCK TABLES `bank_file_exports` WRITE; /*!40000 ALTER TABLE `bank_file_exports` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `bank_file_exports` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `bank_formats` -- DROP TABLE IF EXISTS `bank_formats`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `bank_formats` -- LOCK TABLES `bank_formats` WRITE; /*!40000 ALTER TABLE `bank_formats` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `bank_formats` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `branches` -- DROP TABLE IF EXISTS `branches`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `branches` -- LOCK TABLES `branches` WRITE; /*!40000 ALTER TABLE `branches` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `branches` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `companies` -- DROP TABLE IF EXISTS `companies`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `companies` -- LOCK TABLES `companies` WRITE; /*!40000 ALTER TABLE `companies` DISABLE KEYS */; 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); /*!40000 ALTER TABLE `companies` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `company_bank_accounts` -- DROP TABLE IF EXISTS `company_bank_accounts`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `company_bank_accounts` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `company_id` bigint(20) unsigned NOT NULL, `nickname` varchar(80) NOT NULL, `bank_name` varchar(120) NOT NULL, `account_number_enc` varbinary(512) DEFAULT NULL, `account_last4` char(4) DEFAULT NULL, `iban_enc` varbinary(512) DEFAULT NULL, `swift_code` varchar(16) DEFAULT NULL, `currency` char(3) NOT NULL, `purpose` enum('operating','payroll','reserve','tax') NOT NULL DEFAULT 'operating', `is_default` 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`), KEY `idx_cba_company` (`company_id`), CONSTRAINT `fk_cba_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `company_bank_accounts` -- LOCK TABLES `company_bank_accounts` WRITE; /*!40000 ALTER TABLE `company_bank_accounts` DISABLE KEYS */; INSERT INTO `company_bank_accounts` (`id`, `company_id`, `nickname`, `bank_name`, `account_number_enc`, `account_last4`, `iban_enc`, `swift_code`, `currency`, `purpose`, `is_default`, `created_at`, `updated_at`) VALUES (1,1,'ENBD Operating','Emirates NBD',NULL,'4821',NULL,'EBILAEAD','AED','payroll',1,'2026-04-22 09:43:10','2026-04-22 09:43:10'),(2,1,'HDFC India','HDFC Bank',NULL,'9102',NULL,'HDFCINBB','INR','payroll',0,'2026-04-22 09:43:10','2026-04-22 09:43:10'),(3,1,'BPI Manila','BPI',NULL,'5533',NULL,'BOPIPHMM','PHP','payroll',0,'2026-04-22 09:43:10','2026-04-22 09:43:10'); /*!40000 ALTER TABLE `company_bank_accounts` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `compliance_completions` -- DROP TABLE IF EXISTS `compliance_completions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `compliance_completions` -- LOCK TABLES `compliance_completions` WRITE; /*!40000 ALTER TABLE `compliance_completions` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `compliance_completions` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `compliance_deadlines` -- DROP TABLE IF EXISTS `compliance_deadlines`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `compliance_deadlines` -- LOCK TABLES `compliance_deadlines` WRITE; /*!40000 ALTER TABLE `compliance_deadlines` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `compliance_deadlines` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `country_rule_packs` -- DROP TABLE IF EXISTS `country_rule_packs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `country_rule_packs` -- LOCK TABLES `country_rule_packs` WRITE; /*!40000 ALTER TABLE `country_rule_packs` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `country_rule_packs` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `departments` -- DROP TABLE IF EXISTS `departments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `departments` -- LOCK TABLES `departments` WRITE; /*!40000 ALTER TABLE `departments` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `departments` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `designations` -- DROP TABLE IF EXISTS `designations`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `designations` -- LOCK TABLES `designations` WRITE; /*!40000 ALTER TABLE `designations` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `designations` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `document_requests` -- DROP TABLE IF EXISTS `document_requests`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `document_requests` -- LOCK TABLES `document_requests` WRITE; /*!40000 ALTER TABLE `document_requests` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `document_requests` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `email_templates` -- DROP TABLE IF EXISTS `email_templates`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `email_templates` -- LOCK TABLES `email_templates` WRITE; /*!40000 ALTER TABLE `email_templates` DISABLE KEYS */; INSERT INTO `email_templates` (`id`, `company_id`, `code`, `subject`, `body_html`, `body_text`, `merge_fields_json`, `is_system`, `status`, `updated_at`) VALUES (1,1,'welcome','Welcome to {{company.name}}','

Hi {{employee.first_name}},

Welcome aboard!

',NULL,NULL,1,'active','2026-04-22 05:27:06'),(2,1,'leave_approved','Your leave was approved','

Hi {{employee.first_name}},

Your {{leave.type}} from {{leave.start_date}} to {{leave.end_date}} is approved.

',NULL,NULL,1,'active','2026-04-22 05:27:06'),(3,1,'leave_rejected','Your leave request was not approved','

Hi {{employee.first_name}},

Unfortunately your leave request was not approved. Reason: {{leave.reject_reason}}

',NULL,NULL,1,'active','2026-04-22 05:27:06'),(4,1,'payslip_ready','Your {{period.month}} payslip is ready','

Hi {{employee.first_name}},

Your payslip for {{period.month}} is now available.

',NULL,NULL,1,'active','2026-04-22 05:27:06'),(5,1,'password_reset','Reset your Workbee password','

Click the link below to reset your password. Valid for 1 hour.

Reset password

',NULL,NULL,1,'active','2026-04-22 05:27:06'),(6,1,'compliance_reminder','Compliance deadline: {{deadline.name}} due {{deadline.due_date}}','

A compliance deadline is coming up:

{{deadline.name}} due {{deadline.due_date}}

',NULL,NULL,1,'active','2026-04-22 05:27:06'); /*!40000 ALTER TABLE `email_templates` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `employee_assets` -- DROP TABLE IF EXISTS `employee_assets`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `employee_assets` -- LOCK TABLES `employee_assets` WRITE; /*!40000 ALTER TABLE `employee_assets` DISABLE KEYS */; /*!40000 ALTER TABLE `employee_assets` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `employee_bank` -- DROP TABLE IF EXISTS `employee_bank`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `employee_bank` -- LOCK TABLES `employee_bank` WRITE; /*!40000 ALTER TABLE `employee_bank` DISABLE KEYS */; /*!40000 ALTER TABLE `employee_bank` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `employee_documents` -- DROP TABLE IF EXISTS `employee_documents`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `employee_documents` -- LOCK TABLES `employee_documents` WRITE; /*!40000 ALTER TABLE `employee_documents` DISABLE KEYS */; 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); /*!40000 ALTER TABLE `employee_documents` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `employee_education` -- DROP TABLE IF EXISTS `employee_education`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `employee_education` -- LOCK TABLES `employee_education` WRITE; /*!40000 ALTER TABLE `employee_education` DISABLE KEYS */; /*!40000 ALTER TABLE `employee_education` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `employee_experience` -- DROP TABLE IF EXISTS `employee_experience`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `employee_experience` -- LOCK TABLES `employee_experience` WRITE; /*!40000 ALTER TABLE `employee_experience` DISABLE KEYS */; /*!40000 ALTER TABLE `employee_experience` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `employee_family` -- DROP TABLE IF EXISTS `employee_family`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `employee_family` -- LOCK TABLES `employee_family` WRITE; /*!40000 ALTER TABLE `employee_family` DISABLE KEYS */; /*!40000 ALTER TABLE `employee_family` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `employee_identity` -- DROP TABLE IF EXISTS `employee_identity`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `employee_identity` -- LOCK TABLES `employee_identity` WRITE; /*!40000 ALTER TABLE `employee_identity` DISABLE KEYS */; /*!40000 ALTER TABLE `employee_identity` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `employee_personal` -- DROP TABLE IF EXISTS `employee_personal`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `employee_personal` -- LOCK TABLES `employee_personal` WRITE; /*!40000 ALTER TABLE `employee_personal` DISABLE KEYS */; /*!40000 ALTER TABLE `employee_personal` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `employee_salaries` -- DROP TABLE IF EXISTS `employee_salaries`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `employee_salaries` -- LOCK TABLES `employee_salaries` WRITE; /*!40000 ALTER TABLE `employee_salaries` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `employee_salaries` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `employee_shifts` -- DROP TABLE IF EXISTS `employee_shifts`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `employee_shifts` -- LOCK TABLES `employee_shifts` WRITE; /*!40000 ALTER TABLE `employee_shifts` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `employee_shifts` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `employee_timeline` -- DROP TABLE IF EXISTS `employee_timeline`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `employee_timeline` -- LOCK TABLES `employee_timeline` WRITE; /*!40000 ALTER TABLE `employee_timeline` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `employee_timeline` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `employees` -- DROP TABLE IF EXISTS `employees`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `employees` -- LOCK TABLES `employees` WRITE; /*!40000 ALTER TABLE `employees` DISABLE KEYS */; 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); /*!40000 ALTER TABLE `employees` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `expense_categories` -- DROP TABLE IF EXISTS `expense_categories`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `expense_categories` -- LOCK TABLES `expense_categories` WRITE; /*!40000 ALTER TABLE `expense_categories` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `expense_categories` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `expense_claims` -- DROP TABLE IF EXISTS `expense_claims`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `expense_claims` -- LOCK TABLES `expense_claims` WRITE; /*!40000 ALTER TABLE `expense_claims` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `expense_claims` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `fx_rates` -- DROP TABLE IF EXISTS `fx_rates`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `fx_rates` -- LOCK TABLES `fx_rates` WRITE; /*!40000 ALTER TABLE `fx_rates` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `fx_rates` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `generated_letters` -- DROP TABLE IF EXISTS `generated_letters`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `generated_letters` -- LOCK TABLES `generated_letters` WRITE; /*!40000 ALTER TABLE `generated_letters` DISABLE KEYS */; 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','

This is to certify that Marcus Chen is employed with us and earns a gross monthly salary of 15,000 AED.

','{\"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','

This is to certify that Layla Mansour (BLS-DXB-0002) has been employed with us since 2024-03-22 as Senior Engineer.

',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','

Layla Mansour earns AED 15000/month.

',NULL,NULL,NULL,NULL,1,NULL,'2026-04-22 09:18:13'); /*!40000 ALTER TABLE `generated_letters` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `grades` -- DROP TABLE IF EXISTS `grades`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `grades` -- LOCK TABLES `grades` WRITE; /*!40000 ALTER TABLE `grades` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `grades` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `holidays` -- DROP TABLE IF EXISTS `holidays`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `holidays` -- LOCK TABLES `holidays` WRITE; /*!40000 ALTER TABLE `holidays` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `holidays` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `hr_chat_messages` -- DROP TABLE IF EXISTS `hr_chat_messages`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `hr_chat_messages` -- LOCK TABLES `hr_chat_messages` WRITE; /*!40000 ALTER TABLE `hr_chat_messages` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `hr_chat_messages` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `hr_chats` -- DROP TABLE IF EXISTS `hr_chats`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `hr_chats` -- LOCK TABLES `hr_chats` WRITE; /*!40000 ALTER TABLE `hr_chats` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `hr_chats` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `leave_balance_adjustments` -- DROP TABLE IF EXISTS `leave_balance_adjustments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `leave_balance_adjustments` -- LOCK TABLES `leave_balance_adjustments` WRITE; /*!40000 ALTER TABLE `leave_balance_adjustments` DISABLE KEYS */; /*!40000 ALTER TABLE `leave_balance_adjustments` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `leave_balances` -- DROP TABLE IF EXISTS `leave_balances`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `leave_balances` -- LOCK TABLES `leave_balances` WRITE; /*!40000 ALTER TABLE `leave_balances` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `leave_balances` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `leave_policies` -- DROP TABLE IF EXISTS `leave_policies`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `leave_policies` -- LOCK TABLES `leave_policies` WRITE; /*!40000 ALTER TABLE `leave_policies` DISABLE KEYS */; /*!40000 ALTER TABLE `leave_policies` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `leave_requests` -- DROP TABLE IF EXISTS `leave_requests`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `leave_requests` -- LOCK TABLES `leave_requests` WRITE; /*!40000 ALTER TABLE `leave_requests` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `leave_requests` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `leave_types` -- DROP TABLE IF EXISTS `leave_types`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `leave_types` -- LOCK TABLES `leave_types` WRITE; /*!40000 ALTER TABLE `leave_types` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `leave_types` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `letter_templates` -- DROP TABLE IF EXISTS `letter_templates`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `letter_templates` -- LOCK TABLES `letter_templates` WRITE; /*!40000 ALTER TABLE `letter_templates` DISABLE KEYS */; 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','

This is to certify that {{employee_name}} ({{employee_code}}) has been employed with us since {{join_date}} as {{designation}}.

Regards,
HR Team

','[\"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','

This is to certify that {{employee_name}} is employed with us and earns a gross monthly salary of {{salary}} {{currency}}.

','[\"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','

{{employee_name}} worked with us from {{join_date}} to {{exit_date}} as {{designation}}.

We wish them all the best.

','[\"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','

Dear {{employee_name}},

We are delighted to offer you the position of {{designation}} with a gross monthly salary of {{salary}} {{currency}}, effective {{join_date}}.

','[\"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','

We have no objection to {{employee_name}} applying for {{purpose}}.

','[\"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','

{{employee_name}}, bonus of {{amount}}.

','[\"employee_name\",\"amount\"]','warm',0,0,'archived',1,'2026-04-22 09:18:13','2026-04-22 09:18:13'); /*!40000 ALTER TABLE `letter_templates` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `loan_schedules` -- DROP TABLE IF EXISTS `loan_schedules`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `loan_schedules` -- LOCK TABLES `loan_schedules` WRITE; /*!40000 ALTER TABLE `loan_schedules` DISABLE KEYS */; 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); /*!40000 ALTER TABLE `loan_schedules` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `loan_types` -- DROP TABLE IF EXISTS `loan_types`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `loan_types` -- LOCK TABLES `loan_types` WRITE; /*!40000 ALTER TABLE `loan_types` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `loan_types` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `loans` -- DROP TABLE IF EXISTS `loans`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `loans` -- LOCK TABLES `loans` WRITE; /*!40000 ALTER TABLE `loans` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `loans` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `notification_preferences` -- DROP TABLE IF EXISTS `notification_preferences`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `notification_preferences` -- LOCK TABLES `notification_preferences` WRITE; /*!40000 ALTER TABLE `notification_preferences` DISABLE KEYS */; /*!40000 ALTER TABLE `notification_preferences` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `notifications` -- DROP TABLE IF EXISTS `notifications`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `notifications` -- LOCK TABLES `notifications` WRITE; /*!40000 ALTER TABLE `notifications` DISABLE KEYS */; /*!40000 ALTER TABLE `notifications` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `overtime_rules` -- DROP TABLE IF EXISTS `overtime_rules`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `overtime_rules` -- LOCK TABLES `overtime_rules` WRITE; /*!40000 ALTER TABLE `overtime_rules` DISABLE KEYS */; /*!40000 ALTER TABLE `overtime_rules` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `password_resets` -- DROP TABLE IF EXISTS `password_resets`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `password_resets` -- LOCK TABLES `password_resets` WRITE; /*!40000 ALTER TABLE `password_resets` DISABLE KEYS */; /*!40000 ALTER TABLE `password_resets` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `payroll_anomalies` -- DROP TABLE IF EXISTS `payroll_anomalies`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `payroll_anomalies` -- LOCK TABLES `payroll_anomalies` WRITE; /*!40000 ALTER TABLE `payroll_anomalies` DISABLE KEYS */; /*!40000 ALTER TABLE `payroll_anomalies` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `payroll_periods` -- DROP TABLE IF EXISTS `payroll_periods`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `payroll_periods` -- LOCK TABLES `payroll_periods` WRITE; /*!40000 ALTER TABLE `payroll_periods` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `payroll_periods` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `payroll_runs` -- DROP TABLE IF EXISTS `payroll_runs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `payroll_runs` -- LOCK TABLES `payroll_runs` WRITE; /*!40000 ALTER TABLE `payroll_runs` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `payroll_runs` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `payslips` -- DROP TABLE IF EXISTS `payslips`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `payslips` -- LOCK TABLES `payslips` WRITE; /*!40000 ALTER TABLE `payslips` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `payslips` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `permissions` -- DROP TABLE IF EXISTS `permissions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `permissions` -- LOCK TABLES `permissions` WRITE; /*!40000 ALTER TABLE `permissions` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `permissions` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `push_subscriptions` -- DROP TABLE IF EXISTS `push_subscriptions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `push_subscriptions` -- LOCK TABLES `push_subscriptions` WRITE; /*!40000 ALTER TABLE `push_subscriptions` DISABLE KEYS */; /*!40000 ALTER TABLE `push_subscriptions` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `role_permissions` -- DROP TABLE IF EXISTS `role_permissions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `role_permissions` -- LOCK TABLES `role_permissions` WRITE; /*!40000 ALTER TABLE `role_permissions` DISABLE KEYS */; 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); /*!40000 ALTER TABLE `role_permissions` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `roles` -- DROP TABLE IF EXISTS `roles`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `roles` -- LOCK TABLES `roles` WRITE; /*!40000 ALTER TABLE `roles` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `roles` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `salary_components` -- DROP TABLE IF EXISTS `salary_components`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `salary_components` -- LOCK TABLES `salary_components` WRITE; /*!40000 ALTER TABLE `salary_components` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `salary_components` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `salary_template_components` -- DROP TABLE IF EXISTS `salary_template_components`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `salary_template_components` -- LOCK TABLES `salary_template_components` WRITE; /*!40000 ALTER TABLE `salary_template_components` DISABLE KEYS */; /*!40000 ALTER TABLE `salary_template_components` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `salary_templates` -- DROP TABLE IF EXISTS `salary_templates`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `salary_templates` -- LOCK TABLES `salary_templates` WRITE; /*!40000 ALTER TABLE `salary_templates` DISABLE KEYS */; /*!40000 ALTER TABLE `salary_templates` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `settings` -- DROP TABLE IF EXISTS `settings`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `settings` -- LOCK TABLES `settings` WRITE; /*!40000 ALTER TABLE `settings` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `settings` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `shifts` -- DROP TABLE IF EXISTS `shifts`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `shifts` -- LOCK TABLES `shifts` WRITE; /*!40000 ALTER TABLE `shifts` DISABLE KEYS */; 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'); /*!40000 ALTER TABLE `shifts` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `support_tickets` -- DROP TABLE IF EXISTS `support_tickets`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `support_tickets` -- LOCK TABLES `support_tickets` WRITE; /*!40000 ALTER TABLE `support_tickets` DISABLE KEYS */; /*!40000 ALTER TABLE `support_tickets` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `two_factor_tokens` -- DROP TABLE IF EXISTS `two_factor_tokens`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `two_factor_tokens` -- LOCK TABLES `two_factor_tokens` WRITE; /*!40000 ALTER TABLE `two_factor_tokens` DISABLE KEYS */; /*!40000 ALTER TABLE `two_factor_tokens` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `user_roles` -- DROP TABLE IF EXISTS `user_roles`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `user_roles` -- LOCK TABLES `user_roles` WRITE; /*!40000 ALTER TABLE `user_roles` DISABLE KEYS */; INSERT INTO `user_roles` (`user_id`, `role_id`, `assigned_at`, `assigned_by`) VALUES (1,1,'2026-04-22 05:27:06',NULL); /*!40000 ALTER TABLE `user_roles` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `user_sessions` -- DROP TABLE IF EXISTS `user_sessions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `user_sessions` -- LOCK TABLES `user_sessions` WRITE; /*!40000 ALTER TABLE `user_sessions` DISABLE KEYS */; /*!40000 ALTER TABLE `user_sessions` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `users` -- DROP TABLE IF EXISTS `users`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `users` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `company_id` bigint(20) unsigned NOT NULL, `email` varchar(191) NOT NULL, `password_hash` char(60) NOT NULL COMMENT 'bcrypt hash', `first_name` varchar(80) NOT NULL, `last_name` varchar(80) DEFAULT NULL, `avatar_path` varchar(255) DEFAULT NULL, `phone` varchar(32) DEFAULT NULL, `timezone` varchar(64) DEFAULT NULL, `locale` varchar(10) NOT NULL DEFAULT 'en', `status` enum('active','invited','suspended','locked') NOT NULL DEFAULT 'invited', `is_super_admin` tinyint(1) NOT NULL DEFAULT 0, `two_factor_secret` varchar(255) DEFAULT NULL COMMENT 'encrypted TOTP secret', `two_factor_enabled_at` timestamp NULL DEFAULT NULL, `email_verified_at` timestamp NULL DEFAULT NULL, `last_login_at` timestamp NULL DEFAULT NULL, `last_login_ip` varchar(45) DEFAULT NULL, `failed_login_count` tinyint(3) unsigned NOT NULL DEFAULT 0, `locked_until` timestamp NULL DEFAULT NULL, `remember_token` char(60) DEFAULT NULL, `must_change_password` 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(), `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uq_users_company_email` (`company_id`,`email`), KEY `idx_users_email` (`email`), KEY `idx_users_status` (`status`,`deleted_at`), CONSTRAINT `fk_users_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `users` -- LOCK TABLES `users` WRITE; /*!40000 ALTER TABLE `users` DISABLE KEYS */; INSERT INTO `users` (`id`, `company_id`, `email`, `password_hash`, `first_name`, `last_name`, `avatar_path`, `phone`, `timezone`, `locale`, `status`, `is_super_admin`, `two_factor_secret`, `two_factor_enabled_at`, `email_verified_at`, `last_login_at`, `last_login_ip`, `failed_login_count`, `locked_until`, `remember_token`, `must_change_password`, `created_at`, `updated_at`, `deleted_at`) VALUES (1,1,'anita@brightline.studio','$2y$12$L7CnYQ0mMY0zt8j8V00wI.QvtGrBUucT8ADStHtedQEvgH6WhsVA2','Anita','Desai',NULL,NULL,'Asia/Dubai','en','active',1,NULL,NULL,'2026-04-22 05:28:45','2026-04-22 11:19:08','::1',0,NULL,NULL,0,'2026-04-22 05:27:06','2026-04-22 12:26:32',NULL),(2,1,'priya@brightline.studio','$2y$12$DfdCQiy7Onss8.Zb8.TkoOetQ07yCHXpKnzofaqnR15fXvkO7eMzu','Priya','Sharma',NULL,NULL,'Asia/Kolkata','en','active',0,NULL,NULL,'2026-04-22 05:28:45','2026-04-22 09:24:42','::1',0,NULL,NULL,0,'2026-04-22 05:28:45','2026-04-22 12:26:32',NULL); /*!40000 ALTER TABLE `users` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2026-04-22 13:27:10 SET FOREIGN_KEY_CHECKS = 1;