BRANCHES:
CREATE TABLE `branches` (
  `branch_id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `brand_id` int(11) NOT NULL,
  `branch_name` varchar(100) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `assigned_employee_id` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`branch_id`),
  KEY `idx_branches_company_brand` (`company_id`,`brand_id`),
  KEY `idx_branches_assigned_employee` (`assigned_employee_id`),
  KEY `idx_branches_brand_name` (`brand_id`,`branch_name`),
  CONSTRAINT `fk_branches_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`brand_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_branches_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`company_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

EMPLOYEES:
CREATE TABLE `employees` (
  `employee_id` varchar(15) NOT NULL,
  `biometric_employee_no` varchar(50) DEFAULT NULL,
  `company_id` int(11) NOT NULL DEFAULT 1,
  `brand_id` int(11) NOT NULL DEFAULT 1,
  `first_name` varchar(50) NOT NULL,
  `middle_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `contact_number` varchar(20) DEFAULT NULL,
  `hire_date` date DEFAULT NULL,
  `termination_date` date DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `department_id` varchar(11) DEFAULT NULL,
  `position_id` varchar(50) DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `description` varchar(255) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `base_salary` decimal(10,2) NOT NULL DEFAULT 0.00,
  `salary_type` enum('daily','monthly') DEFAULT 'daily',
  `monthly_rate` decimal(10,2) DEFAULT 0.00,
  `hourly_rate` decimal(10,2) DEFAULT 0.00,
  `reset_code` varchar(10) DEFAULT NULL,
  `reset_code_expires_at` datetime DEFAULT NULL,
  `employee_type` enum('Regular','Probationary','Part-time','OJT','Contractual','Project-Based','Fixed-Term','Seasonal','Casual','Temporary','Reliever') DEFAULT 'Regular',
  `payroll_type_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `branch_name` varchar(100) DEFAULT NULL,
  `employee_punching_method` enum('biometrics','mobile') DEFAULT NULL,
  PRIMARY KEY (`employee_id`),
  KEY `fk_employees_payroll_type` (`payroll_type_id`),
  KEY `idx_emp_status_type_name` (`status`,`employee_type`,`last_name`,`first_name`,`employee_id`),
  KEY `idx_employees_company_brand_status` (`company_id`,`brand_id`,`status`,`last_name`,`first_name`),
  KEY `idx_employees_branch_id` (`branch_id`),
  KEY `idx_employees_brand_id` (`brand_id`),
  KEY `idx_employees_brand_status` (`company_id`,`brand_id`,`status`),
  KEY `idx_employees_brand_status_branch_dept_type` (`company_id`,`brand_id`,`status`,`branch_id`,`department_id`,`employee_type`),
  KEY `idx_employees_company_brand_branch` (`company_id`,`brand_id`,`branch_id`),
  KEY `idx_employees_employee_brand` (`employee_id`,`company_id`,`brand_id`),
  KEY `idx_employees_reset_code_expiry` (`reset_code_expires_at`),
  CONSTRAINT `fk_employees_payroll_type` FOREIGN KEY (`payroll_type_id`) REFERENCES `payroll_types` (`payroll_type_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci