-- phpMyAdmin SQL Dump
-- version 5.2.1deb3
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: May 12, 2025 at 02:48 PM
/*
  - Added `updatedAt` TIMESTAMP column DEFAULT CURRENT_TIMESTAMP.
  - Removed all other timestamp columns that had defaults.
*/

-- Table: applications
CREATE TABLE `applications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `job_id` int(11) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `position_applied` varchar(255) NOT NULL,
  `application_date` date NOT NULL,
  `status` enum('New','Under Review','Interviewing','Rejected','Hired') NOT NULL,
  `shortlisted` tinyint(1) DEFAULT 0,
  `source` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `pendingSync` tinyint(1) DEFAULT 0,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: form_elements
CREATE TABLE `form_elements` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `form_id` int(11) NOT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `label` varchar(100) NOT NULL,
  `field_name` varchar(100) NOT NULL,
  `data_column` varchar(100) DEFAULT NULL,
  `json_column` varchar(100) DEFAULT NULL,
  `type_id` int(11) NOT NULL,
  `options_table` varchar(100) DEFAULT NULL,
  `options_label_column` varchar(100) DEFAULT NULL,
  `options_value_column` varchar(100) DEFAULT NULL,
  `is_required` tinyint(1) NOT NULL DEFAULT 0,
  `validation` json DEFAULT NULL,
  `condition_field` varchar(100) DEFAULT NULL,
  `condition_value` varchar(100) DEFAULT NULL,
  `order_index` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `form_idx` (`form_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: tenant_tables
CREATE TABLE tenant_tables (
  id SERIAL,
  tenant_id INTEGER,
  user_id INTEGER DEFAULT NULL,
  table_name varchar(100) NOT NULL,
  definition json NOT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
);
CREATE INDEX tenant_tables_tenant_idx ON tenant_tables (tenant_id);
CREATE INDEX tenant_tables_user_idx ON tenant_tables (user_id);
CREATE INDEX tenant_tables_name_idx ON tenant_tables (table_name);

-- Table: permit_signatures
CREATE TABLE `permit_signatures` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `permit_application_id` int(11) NOT NULL,
  `tenant_id` int(11),
  `branch_id` int(11) DEFAULT NULL,
  `role` varchar(20) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `file_path` text NOT NULL,
  `signed_at` datetime DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- Table: page_configs
CREATE TABLE `page_configs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `page` varchar(100) NOT NULL,
  `header_title` varchar(255) DEFAULT NULL,
  `cards` json DEFAULT NULL,
  `layout_type` varchar(20) NOT NULL DEFAULT 'grid',
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `page_idx` (`page`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ui_actions
CREATE TABLE `ui_actions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `page` varchar(100) NOT NULL,
  `action_key` varchar(100) NOT NULL,
  `button_label` varchar(100) DEFAULT NULL,
  `button_id` varchar(100) DEFAULT NULL,
  `form_id` int(11) DEFAULT NULL,
  `workflow_event` varchar(100) DEFAULT NULL,
  `target` varchar(255) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `page_action_idx` (`page`,`action_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



-- Table: gl_recurring_entries
CREATE TABLE `gl_recurring_entries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `description` varchar(255) NOT NULL,
  `account_id` int(11) NOT NULL,
  `type` enum('debit','credit') NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `frequency` enum('daily','weekly','monthly','yearly') NOT NULL,
  `next_run_date` date NOT NULL,
  `end_date` date DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `gl_recurring_entries_tenant_idx` (`tenant_id`),
  KEY `gl_recurring_entries_branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: gl_accounts
CREATE TABLE `gl_accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `code` varchar(50) NOT NULL,
  `name` varchar(255) NOT NULL,
  `type` varchar(50) NOT NULL,
  `tags` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `parent_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `gl_accounts_tenant_idx` (`tenant_id`),
  KEY `gl_accounts_branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: gl_transactions
CREATE TABLE `gl_transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `journal_entry_id` int(11) NOT NULL,
  `account_id` int(11) NOT NULL,
  `type` enum('debit','credit') NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `currency` varchar(10) DEFAULT 'USD',
  `rate` decimal(15,6) DEFAULT '1.000000',
  `timestamp` datetime NOT NULL,
  `memo` text DEFAULT NULL,
  `reference` varchar(100) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `gl_transactions_tenant_idx` (`tenant_id`),
  KEY `gl_transactions_branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ap_transactions
CREATE TABLE `ap_transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `gl_transaction_id` int(11) NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `ap_transactions_tenant_idx` (`tenant_id`),
  KEY `ap_transactions_branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ar_transactions
CREATE TABLE `ar_transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `gl_transaction_id` int(11) NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `ar_transactions_tenant_idx` (`tenant_id`),
  KEY `ar_transactions_branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: bank_transactions
CREATE TABLE `bank_transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `gl_transaction_id` int(11) NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `bank_transactions_tenant_idx` (`tenant_id`),
  KEY `bank_transactions_branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: application_documents
CREATE TABLE `application_documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `application_id` int(11) NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_type` varchar(50) NOT NULL,
  `file_path` text NOT NULL,
  `uploaded_by` int(11) NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `application_documents_tenant_idx` (`tenant_id`),
  KEY `application_documents_branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: application_custom_fields
CREATE TABLE `application_custom_fields` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `application_id` int(11) NOT NULL,
  `field_name` varchar(100) NOT NULL,
  `field_value` text DEFAULT NULL,
  `tenant_id` int(11) NOT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: application_field_definitions
CREATE TABLE `application_field_definitions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) NOT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `field_name` varchar(100) NOT NULL,
  `field_type` varchar(50) NOT NULL,
  `is_required` tinyint(1) NOT NULL DEFAULT 0,
  `is_filterable` tinyint(1) NOT NULL DEFAULT 0,
  `options` json DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: assessment_numbers
CREATE TABLE `assessment_numbers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `number` varchar(100) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: GL_Journal_Entries
CREATE TABLE `GL_Journal_Entries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `reference` varchar(100) DEFAULT NULL,
  `memo` text DEFAULT NULL,
  `timestamp` datetime NOT NULL,
  `created_by` int(11) NOT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: groups
CREATE TABLE `groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: roles
CREATE TABLE `roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: group_permissions
CREATE TABLE `group_permissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` int(11) NOT NULL,
  `resource_id` int(11) NOT NULL,
  `can_view` tinyint(1) DEFAULT 0,
  `can_edit` tinyint(1) DEFAULT 0,
  `can_create` tinyint(1) DEFAULT 0,
  `can_delete` tinyint(1) DEFAULT 0,
  `tenant_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: roles_permissions
CREATE TABLE `roles_permissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role` varchar(100) NOT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `inheritedFrom` varchar(100) DEFAULT NULL,
  `permissions` text NOT NULL,
  `createdBy` int(11) NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: jobs
CREATE TABLE `jobs` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: open_positions
CREATE TABLE `open_positions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `position` varchar(255) DEFAULT NULL,
  `applicant_type` enum('All','Internal Only','External Only') NULL DEFAULT 'All',
  `open_date` date DEFAULT NULL,
  `close_date` date DEFAULT NULL,
  `department` text DEFAULT NULL,
  `qualifications` text DEFAULT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
  `status` enum('Open','Closed','Pending') DEFAULT NULL,
  `createdBy` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: application_shortlists (already had auto-increment)
CREATE TABLE `application_shortlists` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `job_id` int(11) NOT NULL,
  `general_comment` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: shortlist_applications (already had auto-increment)
CREATE TABLE `shortlist_applications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `shortlist_id` int(11) NOT NULL,
  `application_id` int(11) NOT NULL,
  `comment` text DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: interview_stages
CREATE TABLE `interview_stages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `sequence` int(11) NOT NULL,
  `description` text DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: interviews (already had auto-increment)
CREATE TABLE `interviews` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `application_id` int(11) DEFAULT NULL,
  `stage_id` int(11) DEFAULT NULL,
  `calendar_event_id` int(11) DEFAULT NULL,
  `status` varchar(50) DEFAULT NULL,
  `scheduled_start` datetime DEFAULT NULL,
  `scheduled_end` datetime DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: interview_participants
CREATE TABLE `interview_participants` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `interview_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `role` varchar(100) DEFAULT NULL,
  `rsvp_status` varchar(50) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: interview_feedback
CREATE TABLE `interview_feedback` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `interview_id` int(11) NOT NULL,
  `reviewer_id` int(11) NOT NULL,
  `score` int(11) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: employees
CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `department` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
  `medical_restrictions` text DEFAULT NULL,
  `createdBy` int(11) DEFAULT NULL,
  `pay_cycle_id` int(11) DEFAULT NULL,
  `manager_id` int(11) DEFAULT NULL,
  `supervisor_type` varchar(50) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `legacy_resources` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: users
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `full_name` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `locations` varchar(255) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `is_admin` tinyint(1) NOT NULL DEFAULT 0,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: user_2fa
CREATE TABLE `user_2fa` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `secret` varchar(255) DEFAULT NULL,
  `enabled` tinyint(1) DEFAULT 0,
  `verified_at` datetime DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: user_auth_providers
CREATE TABLE `user_auth_providers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `provider` varchar(100) DEFAULT NULL,
  `provider_user_id` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `profile_pic` text DEFAULT NULL,
  `linked_at` datetime DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: user_emails
CREATE TABLE `user_emails` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: user_group_memberships
CREATE TABLE `user_group_memberships` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL,
  `role` varchar(100) DEFAULT NULL,
  `joined_at` datetime DEFAULT current_timestamp,
  `tenant_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: user_passkeys
CREATE TABLE `user_passkeys` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `credential_id` varchar(255) DEFAULT NULL,
  `public_key` text DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: user_phones
CREATE TABLE `user_phones` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `phone_number` varchar(50) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: user_tokens
CREATE TABLE `user_tokens` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `refresh_token` varchar(255) NOT NULL,
  `expires_at` datetime NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `user_idx` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: customers
CREATE TABLE `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customerType` enum('individual','corporate','government') NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `tin` varchar(100) DEFAULT NULL,
  `vat` varchar(100) DEFAULT NULL,
  `linkedEntityType` enum('ratepayer','businessRegistration') DEFAULT NULL,
  `linkedEntityId` varchar(255) DEFAULT NULL,
  `riskRating` enum('low','medium','high') DEFAULT NULL,
  `complianceStatus` enum('compliant','non-compliant','pending') DEFAULT NULL,
  `createdBy` int(11) NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: customer_history
CREATE TABLE `customer_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customerId` int(11) NOT NULL,
  `action` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `performedBy` int(11) NOT NULL,
  `performedAt` datetime DEFAULT current_timestamp,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: locations
CREATE TABLE `locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `latitude` decimal(10,6) DEFAULT NULL,
  `longitude` decimal(10,6) DEFAULT NULL,
  `radius_m` int(11) NOT NULL DEFAULT 50,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: time_clock_locations
CREATE TABLE `time_clock_locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) NOT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `shape` enum('circle','polygon') DEFAULT 'circle',
  `center_lat` decimal(10,6) DEFAULT NULL,
  `center_lng` decimal(10,6) DEFAULT NULL,
  `radius_m` int(11) DEFAULT NULL,
  `polygon_points` text DEFAULT NULL,
  `assigned_to_type` varchar(50) DEFAULT NULL,
  `assigned_to_ids` text DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: time_clocks
CREATE TABLE `time_clocks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) NOT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  `clock_type` varchar(50) NOT NULL,
  `lat` decimal(10,6) DEFAULT NULL,
  `lng` decimal(10,6) DEFAULT NULL,
  `valid_gps` tinyint(1) NOT NULL DEFAULT 0,
  `location_id` int(11) DEFAULT NULL,
  `locationApproved` tinyint(1) NOT NULL DEFAULT 0,
  `security_type` varchar(50) NOT NULL,
  `clocked_at` datetime DEFAULT current_timestamp,
  `supervisor_id` int(11) DEFAULT NULL,
  `supervisor_type` varchar(50) DEFAULT NULL,
  `work_id` int(11) DEFAULT NULL,
  `approval_comment` text DEFAULT NULL,
  `approved_by` int(11) DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: time_clock_documents
CREATE TABLE `time_clock_documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time_clock_id` int(11) NOT NULL,
  `document_id` int(11) NOT NULL,
  `tenant_id` int(11) NOT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `clock_idx` (`time_clock_id`),
  KEY `doc_idx` (`document_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- Table: timesheets
CREATE TABLE `timesheets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) NOT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  `date` date NOT NULL,
  `standard_hours` decimal(5,2) NOT NULL DEFAULT 0.00,
  `overtime_hours` decimal(5,2) NOT NULL DEFAULT 0.00,
  `break_hours` decimal(5,2) NOT NULL DEFAULT 0.00,
  `supervisor_id` int(11) DEFAULT NULL,
  `supervisor_type` varchar(50) DEFAULT NULL,
  `work_id` int(11) DEFAULT NULL,
  `approval_comment` text DEFAULT NULL,
  `approved` tinyint(1) NOT NULL DEFAULT 0,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `user_idx` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: leave_types
CREATE TABLE `leave_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `code` varchar(10) NOT NULL,
  `description` text DEFAULT NULL,
  `daysPerYear` int(11) NOT NULL,
  `accrualType` varchar(50) NOT NULL,
  `carryForward` tinyint(1) NOT NULL DEFAULT 0,
  `maxCarry` int(11) DEFAULT NULL,
  `approvalRequired` tinyint(1) NOT NULL DEFAULT 1,
  `paid` tinyint(1) NOT NULL DEFAULT 1,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `createdBy` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: leave_requests
CREATE TABLE `leave_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employeeName` varchar(100) NOT NULL,
  `leaveType` varchar(50) NOT NULL,
  `startDate` date NOT NULL,
  `endDate` date NOT NULL,
  `reason` text DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'Pending',
  `createdBy` int(11) DEFAULT NULL,
  `approvedAt` datetime DEFAULT NULL,
  `approvedBy` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: leave_balances
CREATE TABLE `leave_balances` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `leave_type_id` int(11) NOT NULL,
  `balance` decimal(5,2) NOT NULL DEFAULT 0,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- Table: settings (already had auto-increment)
CREATE TABLE `settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `value` text NOT NULL,
  `category` varchar(50) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `group_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_unique` (`name`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`),
  KEY `group_idx` (`group_id`),
  KEY `user_idx` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- Table: super_settings
CREATE TABLE `super_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `value` text NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_unique` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: tenant_settings
CREATE TABLE `tenant_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `value` text NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tenant_setting_unique` (`tenant_id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: department_settings
CREATE TABLE `department_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `department_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `value` text NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  UNIQUE KEY `dept_setting_unique` (`department_id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- Table: pay_cycles
CREATE TABLE `pay_cycles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `frequency` enum('weekly','fortnightly','bi-monthly','monthly') NOT NULL,
  `pay_day` varchar(20) DEFAULT NULL,
  `cutoff_offset` int(11) DEFAULT 0,
  `next_run_date` date NOT NULL,
  `locale` varchar(10) DEFAULT 'en_US',
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: compensation
CREATE TABLE `compensation` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `pay_type` enum('salary','hourly') NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `effective_from` date NOT NULL,
  `effective_to` date DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: payroll_periods
CREATE TABLE `payroll_periods` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pay_cycle_id` int(11) NOT NULL,
  `period_start` date NOT NULL,
  `period_end` date NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: payroll_runs
CREATE TABLE `payroll_runs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `payroll_period_id` int(11) NOT NULL,
  `run_date` date NOT NULL,
  `status` varchar(50) DEFAULT NULL,
  `currency` varchar(10) DEFAULT 'USD',
  `location_id` int(11) DEFAULT NULL,
  `submitted_by` int(11) DEFAULT NULL,
  `submitted_at` datetime DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: payslips
CREATE TABLE `payslips` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `payroll_run_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `currency` varchar(10) DEFAULT 'USD',
  `gross_pay` decimal(10,2) DEFAULT 0.00,
  `net_pay` decimal(10,2) DEFAULT 0.00,
  `document_path` text DEFAULT NULL,
  `verified_by` int(11) DEFAULT NULL,
  `verified_at` datetime DEFAULT NULL,
  `verification_notes` text DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: payslip_lines
CREATE TABLE `payslip_lines` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `payslip_id` int(11) NOT NULL,
  `description` varchar(255) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `line_type` enum('earning','deduction') NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: pay_rules
CREATE TABLE `pay_rules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rule_name` varchar(100) NOT NULL,
  `overtime_rate` decimal(5,2) DEFAULT 1.50,
  `threshold_hours` int(11) DEFAULT 40,
  `notes` text DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: tax_brackets
CREATE TABLE `tax_brackets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `min_income` decimal(10,2) NOT NULL,
  `max_income` decimal(10,2) DEFAULT NULL,
  `rate` decimal(5,2) NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: tax_settings
CREATE TABLE `tax_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `value` varchar(100) NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: superannuation_settings
CREATE TABLE `superannuation_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_rate` decimal(5,2) NOT NULL DEFAULT 0.00,
  `employer_rate` decimal(5,2) NOT NULL DEFAULT 0.00,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `deduction_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `start_salary` decimal(10,2) NOT NULL,
  `end_salary` decimal(10,2) DEFAULT NULL,
  `base_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `percent` decimal(5,2) NOT NULL DEFAULT 0.00,
  `company_contribution` decimal(5,2) NOT NULL DEFAULT 0.00,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `applies_to` enum('resident','non_resident') NOT NULL,
  `scope` enum('country','company') NOT NULL,
  `country_id` int(11) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: benefit_plans
CREATE TABLE `benefit_plans` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `deduction_amount` decimal(10,2) NOT NULL,
  `pre_tax` tinyint(1) DEFAULT 1,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: employee_benefits
CREATE TABLE `employee_benefits` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `benefit_plan_id` int(11) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: employee_benefit_documents
CREATE TABLE `employee_benefit_documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_benefit_id` int(11) NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_type` varchar(50) NOT NULL,
  `file_path` text NOT NULL,
  `uploaded_by` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: payroll_adjustments
CREATE TABLE `payroll_adjustments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `payroll_period_id` int(11) NOT NULL,
  `description` varchar(255) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `adjustment_type` enum('earning','deduction') NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: allowances
CREATE TABLE `allowances` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `allowance_name` varchar(100) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: commissions
CREATE TABLE `commissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `amount` decimal(10,2) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: procurement_requests
CREATE TABLE `procurement_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `description` text NOT NULL,
  `justification` text DEFAULT NULL,
  `estimated_cost` decimal(10,2) DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  `preferred_vendor` varchar(100) DEFAULT NULL,
  `needed_by` date DEFAULT NULL,
  `location_id` int(11) DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'Pending',
  `requester_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: purchase_requests
CREATE TABLE `purchase_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `description` text NOT NULL,
  `justification` text DEFAULT NULL,
  `estimated_cost` decimal(10,2) DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  `preferred_vendor` varchar(100) DEFAULT NULL,
  `needed_by` date DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: rfqs
CREATE TABLE `rfqs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `procurement_request_id` int(11) NOT NULL,
  `vendor_id` int(11) NOT NULL,
  `status` varchar(50) DEFAULT 'pending',
  `sent_at` datetime DEFAULT NULL,
  `due_at` datetime DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: rfq_items
CREATE TABLE `rfq_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rfq_id` int(11) NOT NULL,
  `description` text NOT NULL,
  `quantity` int(11) DEFAULT NULL,
  `unit_price` decimal(10,2) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: purchase_orders
CREATE TABLE `purchase_orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rfq_id` int(11) DEFAULT NULL,
  `vendor_id` int(11) NOT NULL,
  `order_date` date DEFAULT NULL,
  `status` varchar(50) DEFAULT 'Open',
  `total_amount` decimal(10,2) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: po_line_items
CREATE TABLE `po_line_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `purchase_order_id` int(11) NOT NULL,
  `description` text NOT NULL,
  `quantity` int(11) DEFAULT NULL,
  `unit_price` decimal(10,2) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-------------------------------DONE------------------
/*
  Modified SQL schema for additional tables:
  - `id` as INT NOT NULL AUTO_INCREMENT primary key.
  - Added `isDeleted` TINYINT(1) NOT NULL DEFAULT 0.
  - Added `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP.
  - Removed all other timestamp/default timestamp columns.
*/

-- Table: goods_receipts
CREATE TABLE `goods_receipts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `purchase_order_id` int(11) NOT NULL,
  `report_id` int(11) DEFAULT NULL,
  `received_date` date DEFAULT NULL,
  `quantity_received` int(11) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `goods_receipts_report_idx` (`report_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: vendor_invoices
CREATE TABLE `vendor_invoices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `purchase_order_id` int(11) NOT NULL,
  `invoice_number` varchar(100) NOT NULL,
  `invoice_date` date DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `status` varchar(50) DEFAULT 'unpaid',
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: vendor_quotes
CREATE TABLE `vendor_quotes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rfq_id` int(11) NOT NULL,
  `vendor_id` int(11) NOT NULL,
  `quote_amount` decimal(10,2) DEFAULT NULL,
  `document_path` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_incidents
CREATE TABLE `ohs_incidents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date DEFAULT NULL,
  `time` time DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `classification` varchar(100) NOT NULL DEFAULT 'injury',
  `severity` varchar(100) DEFAULT NULL,
  `investigator_id` int(11) DEFAULT NULL,
  `reviewer_id` int(11) DEFAULT NULL,
  `status` varchar(50) DEFAULT 'reported',
  `submitted_by` int(11) DEFAULT NULL,
  `form_data` jsonb DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_health_checks
CREATE TABLE `ohs_health_checks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `check_type` varchar(100) NOT NULL,
  `result` text DEFAULT NULL,
  `check_date` date NOT NULL,
  `notes` text DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `pendingSync` tinyint(1) DEFAULT 0,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_medicals
CREATE TABLE `ohs_medicals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `incident_id` int(11) NOT NULL,
  `health_check_id` int(11) DEFAULT NULL,
  `surname` varchar(100) DEFAULT NULL,
  `first_name` varchar(100) DEFAULT NULL,
  `other_initials` varchar(100) DEFAULT NULL,
  `sex` varchar(10) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `job_title` varchar(100) DEFAULT NULL,
  `occ_years` int(11) DEFAULT NULL,
  `occ_months` int(11) DEFAULT NULL,
  `occ_days` int(11) DEFAULT NULL,
  `employment_status` varchar(100) DEFAULT NULL,
  `incident_date` date DEFAULT NULL,
  `incident_time` time DEFAULT NULL,
  `incident_type` varchar(255) DEFAULT NULL,
  `agency` varchar(255) DEFAULT NULL,
  `incident_description` text DEFAULT NULL,
  `damage_location` text DEFAULT NULL,
  `corrective_action` varchar(255) DEFAULT NULL,
  `action_date` date DEFAULT NULL,
  `medical_treatment` varchar(255) DEFAULT NULL,
  `medical_practitioner` varchar(255) DEFAULT NULL,
  `date_notified` date DEFAULT NULL,
  `incapacity_years` int(11) DEFAULT NULL,
  `incapacity_months` int(11) DEFAULT NULL,
  `incapacity_days` int(11) DEFAULT NULL,
  `employer_signature` varchar(255) DEFAULT NULL,
  `employer_date` date DEFAULT NULL,
  `employee_signature` varchar(255) DEFAULT NULL,
  `employee_date` date DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `pendingSync` tinyint(1) DEFAULT 0,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_incident_files
CREATE TABLE `ohs_incident_files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `incident_id` int(11) NOT NULL,
  `file_path` varchar(255) DEFAULT NULL,
  `uploaded_by` int(11) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_root_causes
CREATE TABLE `ohs_root_causes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `incident_id` int(11) NOT NULL,
  `method` varchar(100) DEFAULT NULL,
  `analysis_notes` text DEFAULT NULL,
  `checklist` text DEFAULT NULL,
  `five_whys_id` int(11) DEFAULT NULL,
  `fishbone_file_id` int(11) DEFAULT NULL,
  `fault_tree_id` int(11) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_incident_custom_fields
CREATE TABLE `ohs_incident_custom_fields` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) NOT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `field_name` varchar(100) NOT NULL,
  `field_type` varchar(50) NOT NULL,
  `is_required` tinyint(1) NOT NULL DEFAULT 0,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_actions
CREATE TABLE `ohs_actions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `incident_id` int(11) NOT NULL,
  `description` text DEFAULT NULL,
  `responsible_user_id` int(11) DEFAULT NULL,
  `target_date` date DEFAULT NULL,
  `status` varchar(50) DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- Table: ohs_incident_logs
CREATE TABLE `ohs_incident_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `incident_id` int(11) NOT NULL,
  `action` varchar(50) NOT NULL,
  `details` text DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `logged_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `ohs_incident_logs_incident_idx` (`incident_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- Table: ohs_assignment_rules
CREATE TABLE `ohs_assignment_rules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `location` varchar(100) DEFAULT NULL,
  `type` varchar(50) DEFAULT NULL,
  `severity` varchar(50) DEFAULT NULL,
  `location_id` int(11) DEFAULT NULL,
  `investigator_id` int(11) NOT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: inspection_assignment_rules
CREATE TABLE `inspection_assignment_rules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `district_id` int(11) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `send_email` tinyint(1) NOT NULL DEFAULT 0,
  `phone` varchar(50) DEFAULT NULL,
  `send_sms` tinyint(1) NOT NULL DEFAULT 0,
  `send_push` tinyint(1) NOT NULL DEFAULT 0,
  `inspector_id` int(11) NOT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_hazard_types
CREATE TABLE `ohs_hazard_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_incident_statuses
CREATE TABLE `ohs_incident_statuses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `purpose` text DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_severities
CREATE TABLE `ohs_severities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_hazards
CREATE TABLE `ohs_hazards` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `description` text DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `hazard_type_id` int(11) DEFAULT NULL,
  `severity` int(11) DEFAULT NULL,
  `probability` int(11) DEFAULT NULL,
  `risk_level` int(11) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_risk_reviews
CREATE TABLE `ohs_risk_reviews` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hazard_id` int(11) NOT NULL,
  `reviewer_id` int(11) DEFAULT NULL,
  `review_date` date DEFAULT NULL,
  `residual_risk` int(11) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_risk_matrices
CREATE TABLE `ohs_risk_matrices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) NOT NULL,
  `severity` int(11) NOT NULL,
  `probability` int(11) NOT NULL,
  `risk_value` int(11) NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: risk_categories
CREATE TABLE `risk_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: risk_identifications
CREATE TABLE `risk_identifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `risk_description` text DEFAULT NULL,
  `risk_category_id` int(11) DEFAULT NULL,
  `root_cause` text DEFAULT NULL,
  `likelihood` int(11) DEFAULT NULL,
  `impact` int(11) DEFAULT NULL,
  `risk_rating` int(11) DEFAULT NULL,
  `current_controls` text DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `risk_identifications_tenant_idx` (`tenant_id`),
  KEY `risk_identifications_branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_inspections
CREATE TABLE `ohs_inspections` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `location` varchar(255) DEFAULT NULL,
  `inspector_id` int(11) NOT NULL,
  `inspection_type_id` int(11) DEFAULT NULL,
  `form_data` jsonb DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_inspection_items
CREATE TABLE `ohs_inspection_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `inspection_id` int(11) NOT NULL,
  `question` varchar(255) NOT NULL,
  `response` varchar(100) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `corrective_action` text DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_inspection_audits
CREATE TABLE `ohs_inspection_audits` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `inspection_id` int(11) NOT NULL,
  `action` varchar(20) NOT NULL,
  `changed_fields` text DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  `policy_version` int(11) DEFAULT 1,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `performed_at` datetime DEFAULT current_timestamp,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_inspection_templates
CREATE TABLE `ohs_inspection_templates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `template_name` varchar(255) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `form_id` int(11) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_emergency_drills
CREATE TABLE `ohs_emergency_drills` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `drill_type` varchar(100) NOT NULL,
  `scheduled_for` datetime NOT NULL,
  `conducted_by` int(11) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `reminder_sent` tinyint(1) DEFAULT 0,
  `pendingSync` tinyint(1) DEFAULT 0,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_emergency_contacts
CREATE TABLE `ohs_emergency_contacts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `contact_name` varchar(255) NOT NULL,
  `phone` varchar(50) NOT NULL,
  `relation` varchar(100) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `pendingSync` tinyint(1) DEFAULT 0,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_training_requirements
CREATE TABLE `ohs_training_requirements` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_id` int(11) DEFAULT NULL,
  `location_id` int(11) DEFAULT NULL,
  `project_id` int(11) DEFAULT NULL,
  `course_id` int(11) NOT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `role_idx` (`role_id`),
  KEY `location_idx` (`location_id`),
  KEY `project_idx` (`project_id`),
  KEY `course_idx` (`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_regulations
CREATE TABLE `ohs_regulations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `effective_date` date DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_compliance_tasks
CREATE TABLE `ohs_compliance_tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `regulation_id` int(11) DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `due_date` datetime DEFAULT NULL,
  `assigned_to` int(11) DEFAULT NULL,
  `status` enum('pending','completed') DEFAULT 'pending',
  `reminder_sent` tinyint(1) DEFAULT 0,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_compliance_logs
CREATE TABLE `ohs_compliance_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `task_id` int(11) NOT NULL,
  `log_date` datetime NOT NULL DEFAULT current_timestamp,
  `notes` text DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `task_idx` (`task_id`),
  KEY `tenant_idx` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_witness_statements
CREATE TABLE `ohs_witness_statements` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `incident_id` int(11) NOT NULL,
  `witness_name` varchar(100) DEFAULT NULL,
  `contact_info` varchar(255) DEFAULT NULL,
  `statement` text DEFAULT NULL,
  `document_id` int(11) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_investigation_checklists
CREATE TABLE `ohs_investigation_checklists` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `items` text DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: employee_items (already AUTO_INCREMENT)
CREATE TABLE `employee_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `item_name` varchar(100) NOT NULL,
  `is_ppe` tinyint(1) NOT NULL DEFAULT 0,
  `expiry_date` date DEFAULT NULL,
  `assignee_user_id` int(11) DEFAULT NULL,
  `assignee_group_id` int(11) DEFAULT NULL,
  `assigned_at` timestamp DEFAULT current_timestamp,
  `due_date` date DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: tasks (already AUTO_INCREMENT)
CREATE TABLE `tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `item_name` varchar(100) DEFAULT NULL,
  `manager_id` int(11) DEFAULT NULL,
  `status` enum('pending','completed') DEFAULT 'pending',
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: performance_goals
CREATE TABLE `performance_goals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `description` text NOT NULL,
  `kpi_id` int(11) DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `status` varchar(50) DEFAULT 'pending',
  `progress_pct` decimal(5,2) DEFAULT 0.00,
  `score_type` varchar(20) DEFAULT 'numeric',
  `milestone` varchar(255) DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: goal_milestones
CREATE TABLE `goal_milestones` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `goal_id` int(11) NOT NULL,
  `milestone` varchar(255) NOT NULL,
  `due_date` date DEFAULT NULL,
  `progress_pct` decimal(5,2) DEFAULT 0.00,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `goal_idx` (`goal_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: goal_documents
CREATE TABLE `goal_documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `goal_id` int(11) NOT NULL,
  `document_id` int(11) NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `goal_idx` (`goal_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: review_documents
CREATE TABLE `review_documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `review_id` int(11) NOT NULL,
  `document_id` int(11) NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `review_idx` (`review_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- Table: goal_score_labels
CREATE TABLE `goal_score_labels` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `country_code` varchar(5) NOT NULL,
  `score_value` int(11) NOT NULL,
  `label` varchar(100) NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `country_idx` (`country_code`),
  KEY `score_idx` (`score_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: review_cycles
CREATE TABLE `review_cycles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: performance_reviews
CREATE TABLE `performance_reviews` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `cycle_id` int(11) NOT NULL,
  `self_rating` decimal(3,2) DEFAULT NULL,
  `manager_rating` decimal(3,2) DEFAULT NULL,
  `comments` text DEFAULT NULL,
  `status` varchar(50) DEFAULT 'draft',
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- Table: review_schedules
CREATE TABLE `review_schedules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `country_code` varchar(5) DEFAULT NULL,
  `role_id` int(11) DEFAULT NULL,
  `review_type` varchar(50) NOT NULL,
  `interval_days` int(11) NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: feedback_requests
CREATE TABLE `feedback_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `requestor_id` int(11) NOT NULL,
  `target_id` int(11) NOT NULL,
  `type` varchar(20) NOT NULL,
  `anonymous` tinyint(1) DEFAULT 0,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `req_idx` (`requestor_id`),
  KEY `tgt_idx` (`target_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- Table: feedback_notes
CREATE TABLE `feedback_notes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `from_user_id` int(11) NOT NULL,
  `to_user_id` int(11) NOT NULL,
  `request_id` int(11) DEFAULT NULL,
  `goal_id` int(11) DEFAULT NULL,
  `review_id` int(11) DEFAULT NULL,
  `note` text NOT NULL,
  `sentiment_score` decimal(3,2) DEFAULT NULL,
  `is_private` tinyint(1) DEFAULT 1,
  `moderated` tinyint(1) DEFAULT 0,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `req_idx` (`request_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: check_ins
CREATE TABLE `check_ins` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `manager_id` int(11) DEFAULT NULL,
  `scheduled_at` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: employee_exits
CREATE TABLE `employee_exits` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `employee_id` int(11) NOT NULL,
  `resignation_date` date NOT NULL,
  `status` varchar(50) DEFAULT 'pending',
  `final_pay` decimal(10,2) DEFAULT 0.00,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: exit_clearances
CREATE TABLE `exit_clearances` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `exit_id` int(11) NOT NULL,
  `item` varchar(100) DEFAULT NULL,
  `returned` tinyint(1) DEFAULT 0,
  `access_revoked` tinyint(1) DEFAULT 0,
  `notes` text DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: exit_approvals
CREATE TABLE `exit_approvals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `exit_id` int(11) NOT NULL,
  `approver_id` int(11) NOT NULL,
  `level` int(11) NOT NULL,
  `status` varchar(50) DEFAULT 'pending',
  `approved_at` datetime DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: exit_interviews
CREATE TABLE `exit_interviews` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `exit_id` int(11) NOT NULL,
  `interviewer_id` int(11) DEFAULT NULL,
  `scheduled_at` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: exit_feedback
CREATE TABLE `exit_feedback` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `exit_interview_id` int(11) NOT NULL,
  `question` varchar(255) DEFAULT NULL,
  `response` text DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: final_settlements
CREATE TABLE `final_settlements` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `exit_id` int(11) NOT NULL,
  `outstanding_salary` decimal(10,2) DEFAULT 0.00,
  `unused_leave` decimal(10,2) DEFAULT 0.00,
  `deductions` decimal(10,2) DEFAULT 0.00,
  `net_amount` decimal(10,2) DEFAULT 0.00,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: employee_types (already AUTO_INCREMENT)
CREATE TABLE `employee_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(50) NOT NULL,
  `default_items` text,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: supervisor_types
CREATE TABLE `supervisor_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: workflows (already AUTO_INCREMENT)
CREATE TABLE `workflows` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `department_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `trigger_event` varchar(100) NOT NULL,
  `definition` json NOT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  UNIQUE KEY `workflow_name_unique` (`name`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `dept_idx` (`department_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: workflow_steps
CREATE TABLE `workflow_steps` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `workflow_id` int(11) NOT NULL,
  `step_order` int(11) NOT NULL,
  `step_type` varchar(50) NOT NULL,
  `config` json DEFAULT '{"groupBy":"","aggregateFunc":"","timeUnit":""}',
  `tenant_id` int(11) DEFAULT NULL,
  `department_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `wf_idx` (`workflow_id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `dept_idx` (`department_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- Table: workflow_approvals
CREATE TABLE `workflow_approvals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `workflow_id` int(11) NOT NULL,
  `step_order` int(11) NOT NULL,
  `approver_role` varchar(100) NOT NULL,
  `status` varchar(50) DEFAULT 'pending',
  `record_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `wf_idx` (`workflow_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- Table: workflow_states
CREATE TABLE `workflow_states` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `workflow_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `state_order` int(11) NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `wf_idx` (`workflow_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: workflow_transitions
CREATE TABLE `workflow_transitions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `workflow_id` int(11) NOT NULL,
  `from_state_id` int(11) NOT NULL,
  `to_state_id` int(11) NOT NULL,
  `action` varchar(100) DEFAULT NULL,
  `conditions` json DEFAULT NULL,
  `transition_order` int(11) NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `wf_idx` (`workflow_id`),
  KEY `from_idx` (`from_state_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: workflow_instances
CREATE TABLE `workflow_instances` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `workflow_id` int(11) NOT NULL,
  `record_id` int(11) NOT NULL,
  `current_state_id` int(11) DEFAULT NULL,
  `started_by` int(11) DEFAULT NULL,
  `status` varchar(50) DEFAULT 'pending',
  `started_at` datetime NOT NULL DEFAULT current_timestamp,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `wf_idx` (`workflow_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: workflow_tasks
CREATE TABLE `workflow_tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `instance_id` int(11) NOT NULL,
  `state_id` int(11) NOT NULL,
  `assignee_user_id` int(11) DEFAULT NULL,
  `assignee_role` varchar(100) DEFAULT NULL,
  `status` varchar(50) DEFAULT 'pending',
  `due_at` datetime DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `instance_idx` (`instance_id`),
  KEY `assignee_idx` (`assignee_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: notification_log
CREATE TABLE `notification_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(50) NOT NULL,
  `recipient` varchar(255) NOT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `message` text,
  `status` varchar(20) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: notification_queue
CREATE TABLE `notification_queue` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `entity` varchar(50) NOT NULL,
  `record_id` int(11) DEFAULT NULL,
  `event_type` varchar(20) NOT NULL,
  `payload` json DEFAULT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'queued',
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: scheduled_reports
CREATE TABLE `scheduled_reports` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `report_id` int(11) NOT NULL,
  `format` varchar(10) NOT NULL DEFAULT 'csv',
  `recipients` text DEFAULT NULL,
  `next_run` datetime NOT NULL,
  `interval_days` int(11) NOT NULL DEFAULT 1,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `rep_idx` (`report_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: account_ai_feedback (already AUTO_INCREMENT)
CREATE TABLE `account_ai_feedback` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `transaction_id` int(11) DEFAULT NULL,
  `suggested_code` varchar(50) DEFAULT NULL,
  `chosen_code` varchar(50) DEFAULT NULL,
  `rationale` text,
  `accepted` tinyint(1) DEFAULT 0,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: payroll_anomalies (already AUTO_INCREMENT)
CREATE TABLE `payroll_anomalies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `payroll_run_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `message` varchar(255) NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `run_idx` (`payroll_run_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: tax_ledger (already AUTO_INCREMENT)
CREATE TABLE `tax_ledger` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `payroll_run_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `tax_type` varchar(50) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `ytd_total` decimal(10,2) NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `run_idx` (`payroll_run_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: attendance (already AUTO_INCREMENT)
CREATE TABLE `attendance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee` varchar(255) NOT NULL,
  `date` date NOT NULL,
  `restricted_start` time DEFAULT NULL,
  `restricted_end` time DEFAULT NULL,
  `hours_worked` decimal(5,2) NOT NULL DEFAULT 0.00,
  `overtime` decimal(5,2) NOT NULL DEFAULT 0.00,
  `injury_flag` tinyint(1) NOT NULL DEFAULT 0,
  `restricted_duty` tinyint(1) NOT NULL DEFAULT 0,
  `pay_code` varchar(20) DEFAULT NULL,
  `approval_status` varchar(20) DEFAULT 'Approved',
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: vendors (SYSTEM VERSIONING)
CREATE TABLE `vendors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vendorName` varchar(255) NOT NULL,
  `tradingName` varchar(255) DEFAULT NULL,
  `businessRegNumber` varchar(255) DEFAULT NULL,
  `tin` varchar(255) DEFAULT NULL,
  `vatNumber` varchar(255) DEFAULT NULL,
  `businessType` varchar(255) DEFAULT NULL,
  `incorporationDate` date DEFAULT NULL,
  `registrationCountry` varchar(255) DEFAULT NULL,
  `status` varchar(100) DEFAULT 'pending',
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `version` int(11) NOT NULL DEFAULT 1,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) WITH SYSTEM VERSIONING;

-- Table: vendor_contacts (SYSTEM VERSIONING)
CREATE TABLE `vendor_contacts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vendor_id` int(11) NOT NULL,
  `primaryContactName` varchar(255) DEFAULT NULL,
  `designation` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `altContactDetails` text DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) WITH SYSTEM VERSIONING;

-- Table: vendor_addresses (SYSTEM VERSIONING)
CREATE TABLE `vendor_addresses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vendor_id` int(11) NOT NULL,
  `type` enum('office','postal') NOT NULL,
  `address` text NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) WITH SYSTEM VERSIONING;

-- Table: vendor_documents (SYSTEM VERSIONING)
CREATE TABLE `vendor_documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vendor_id` int(11) NOT NULL,
  `filetype` varchar(50) NOT NULL,
  `type` varchar(100) NOT NULL,
  `file_url` text NOT NULL,
  `validity` date DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) WITH SYSTEM VERSIONING;

-- Table: vendor_bank_accounts (SYSTEM VERSIONING)
CREATE TABLE `vendor_bank_accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vendor_id` int(11) NOT NULL,
  `bankName` varchar(255) DEFAULT NULL,
  `bankBranch` varchar(255) DEFAULT NULL,
  `accountName` varchar(255) DEFAULT NULL,
  `accountNumber` varchar(255) DEFAULT NULL,
  `bankCode` varchar(255) DEFAULT NULL,
  `bankAddress` text DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) WITH SYSTEM VERSIONING;

-- Table: vendor_capabilities (SYSTEM VERSIONING)
CREATE TABLE `vendor_capabilities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vendor_id` int(11) NOT NULL,
  `goodsServices` text DEFAULT NULL,
  `categoryTags` text DEFAULT NULL,
  `pastClients` text DEFAULT NULL,
  `experienceYears` int(11) DEFAULT NULL,
  `certifications` text DEFAULT NULL,
  `capacity` text DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) WITH SYSTEM VERSIONING;

-- Table: vendor_supporting_docs (SYSTEM VERSIONING)
CREATE TABLE `vendor_supporting_docs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vendor_id` int(11) NOT NULL,
  `filetype` varchar(50) NOT NULL,
  `type` varchar(100) NOT NULL,
  `file_url` text NOT NULL,
  `validity` date DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) WITH SYSTEM VERSIONING;

-- Table: training_categories
CREATE TABLE `training_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: training_courses
CREATE TABLE `training_courses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `duration` int(11) DEFAULT NULL,
  `recertification_interval` int(11) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  `prerequisites` text DEFAULT NULL,
  `target_audience` text DEFAULT NULL,
  `language_code` varchar(10) DEFAULT NULL,
  `country_id` int(11) DEFAULT NULL,
  `content_host` varchar(50) DEFAULT NULL,
  `external_lms_url` varchar(255) DEFAULT NULL,
  `lms_host` varchar(255) DEFAULT NULL,
  `lms_api_key` varchar(255) DEFAULT NULL,
  `archived` tinyint(1) NOT NULL DEFAULT 0,
  `mandatory` tinyint(1) NOT NULL DEFAULT 0,
  `createdBy` int(11) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: training_sessions
CREATE TABLE `training_sessions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `course_id` int(11) NOT NULL,
  `start_date` datetime NOT NULL,
  `end_date` datetime DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `createdBy` int(11) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: training_contents
CREATE TABLE `training_contents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `course_id` int(11) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `file_path` varchar(255) DEFAULT NULL,
  `caption_path` varchar(255) DEFAULT NULL,
  `language_code` varchar(10) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `course_idx` (`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- Table: training_enrollments
CREATE TABLE `training_enrollments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `session_id` int(11) NOT NULL,
  `status` enum('Enrolled','Cancelled','Completed') DEFAULT 'Enrolled',
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: training_results
CREATE TABLE `training_results` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `enrollment_id` int(11) NOT NULL,
  `score` decimal(5,2) DEFAULT NULL,
  `completed` tinyint(1) DEFAULT 0,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: training_feedback
CREATE TABLE `training_feedback` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `enrollment_id` int(11) NOT NULL,
  `rating` int(11) DEFAULT NULL,
  `comments` text DEFAULT NULL,
  `signature_path` varchar(255) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: training_certificates
CREATE TABLE `training_certificates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `issued_at` date NOT NULL,
  `expires_at` date DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `renewal_required` tinyint(1) NOT NULL DEFAULT 0,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- Table: scorm_progress
CREATE TABLE `scorm_progress` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `content_id` int(11) NOT NULL,
  `suspend_data` text DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: training_bookmarks
CREATE TABLE `training_bookmarks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `content_id` int(11) NOT NULL,
  `position` varchar(255) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- Table: training_badges
CREATE TABLE `training_badges` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `badge_name` varchar(255) NOT NULL,
  `issued_at` date NOT NULL,
  `expires_at` date DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: training_paths
CREATE TABLE `training_paths` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `country_id` int(11) DEFAULT NULL,
  `department_id` int(11) DEFAULT NULL,
  `role_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `country_idx` (`country_id`),
  KEY `dept_idx` (`department_id`),
  KEY `role_idx` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: training_path_courses
CREATE TABLE `training_path_courses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `path_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `sequence` int(11) NOT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `path_idx` (`path_id`),
  KEY `course_idx` (`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: training_quizzes
CREATE TABLE `training_quizzes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `course_id` int(11) NOT NULL,
  `passing_score` decimal(5,2) DEFAULT 0,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `course_idx` (`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: training_questions
CREATE TABLE `training_questions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `quiz_id` int(11) NOT NULL,
  `question` text NOT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `quiz_idx` (`quiz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: training_answers
CREATE TABLE `training_answers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `question_id` int(11) NOT NULL,
  `answer` text NOT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `is_correct` tinyint(1) NOT NULL DEFAULT 0,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `question_idx` (`question_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: violation_types
CREATE TABLE `violation_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `handbook_ref` varchar(255) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: disciplinary_cases
CREATE TABLE `disciplinary_cases` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `violation_id` int(11) NOT NULL,
  `severity` enum('minor','major','critical') NOT NULL,
  `summary` text DEFAULT NULL,
  `status` enum('open','under_review','closed') DEFAULT 'open',
  `assigned_to` int(11) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: case_evidence
CREATE TABLE `case_evidence` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `case_id` int(11) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: case_tasks
CREATE TABLE `case_tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `matter_id` int(11) NOT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `due_date` datetime DEFAULT NULL,
  `assigned_to` int(11) DEFAULT NULL,
  `status` varchar(50) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: case_participants
CREATE TABLE `case_participants` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `matter_id` int(11) NOT NULL,
  `person_id` int(11) NOT NULL,
  `role` varchar(100) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: case_notes
CREATE TABLE `case_notes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `matter_id` int(11) NOT NULL,
  `note` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: interview_logs
CREATE TABLE `interview_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `case_id` int(11) NOT NULL,
  `person` varchar(255) NOT NULL,
  `notes` text DEFAULT NULL,
  `logged_at` datetime DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: disciplinary_actions
CREATE TABLE `disciplinary_actions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `case_id` int(11) NOT NULL,
  `action` varchar(255) NOT NULL,
  `effective_date` date NOT NULL,
  `approved_by` int(11) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: appeals
CREATE TABLE `appeals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `case_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `reason` text DEFAULT NULL,
  `status` enum('submitted','review','resolved') DEFAULT 'submitted',
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: reimbursement_claims
CREATE TABLE `reimbursement_claims` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `claim_date` date NOT NULL,
  `description` text DEFAULT NULL,
  `amount` decimal(10,2) NOT NULL,
  `status` enum('Pending','Approved','Rejected') DEFAULT 'Pending',
  `approved_at` datetime DEFAULT NULL,
  `approved_by` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: bank_detail_changes
CREATE TABLE `bank_detail_changes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `bankName` varchar(255) DEFAULT NULL,
  `accountName` varchar(255) DEFAULT NULL,
  `accountNumber` varchar(255) DEFAULT NULL,
  `status` enum('Pending','Approved','Rejected') DEFAULT 'Pending',
  `approved_at` datetime DEFAULT NULL,
  `approved_by` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: permit_types
CREATE TABLE `permit_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `control_options` json DEFAULT NULL,
  `ppe_options` json DEFAULT NULL,
  `required_training_ids` json DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: permit_applications
CREATE TABLE `permit_applications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `permit_type_id` int(11) NOT NULL,
  `applicant_id` int(11) NOT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `status` varchar(50) DEFAULT 'Initiated',
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `fee_amount` decimal(10,2) DEFAULT 0.00,
  `description` text DEFAULT NULL,
  `work_location` varchar(255) DEFAULT NULL,
  `equipment` varchar(255) DEFAULT NULL,
  `hazard_assessment` text DEFAULT NULL,
  `control_measures` text DEFAULT NULL,
  `ppe_required` text DEFAULT NULL,
  `control_items` json DEFAULT NULL,
  `ppe_items` json DEFAULT NULL,
  `loto_instructions` text DEFAULT NULL,
  `emergency_response` text DEFAULT NULL,
  `issuer_id` int(11) DEFAULT NULL,
  `supervisor_id` int(11) DEFAULT NULL,
  `issuer_signed_at` datetime DEFAULT NULL,
  `supervisor_signed_at` datetime DEFAULT NULL,
  `holder_signed_at` datetime DEFAULT NULL,
  `signed_off_at` datetime DEFAULT NULL,
  `expires_at` date DEFAULT NULL,
  `archived_at` datetime DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: permit_documents
CREATE TABLE `permit_documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `permit_application_id` int(11) NOT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_type` varchar(50) NOT NULL,
  `file_path` text NOT NULL,
  `uploaded_by` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: permit_inspections
CREATE TABLE `permit_inspections` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `permit_application_id` int(11) NOT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `inspector_id` int(11) NOT NULL,
  `scheduled_for` datetime DEFAULT NULL,
  `result` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `hours_logged` decimal(5,2) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: properties (already AUTO_INCREMENT)
CREATE TABLE `properties` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `CouncilID` int(11) DEFAULT NULL,
  `PropertyID` int(11) NOT NULL,
  `AssessmentNumber` int(11) NOT NULL,
  `RatepayerID` int(11) DEFAULT NULL,
  `Zone` varchar(100) DEFAULT NULL,
  `Address` text DEFAULT NULL,
  `Street` varchar(255) DEFAULT NULL,
  `Ward` varchar(100) DEFAULT NULL,
  `LegalDescription` text DEFAULT NULL,
  `NoOfFlats` int(11) DEFAULT NULL,
  `IsActive` tinyint(1) DEFAULT 1,
  `CreatedBy` int(11) DEFAULT NULL,
  `propertyHoldersName` varchar(255) DEFAULT NULL,
  `UCV` decimal(10,2) NOT NULL,
  `RatesStrick` decimal(3,2) NOT NULL,
  `Arrears` decimal(10,2) NOT NULL,
  `AdvancePayment` decimal(10,2) NOT NULL,
  `GarbageFee` decimal(10,2) NOT NULL,
  `GarbageArrears` decimal(10,2) NOT NULL,
  `AdvancePay` decimal(10,2) NOT NULL,
  `LegalFee` decimal(10,2) NOT NULL,
  `UpdatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ratepayers (already AUTO_INCREMENT)
CREATE TABLE `ratepayers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `CouncilID` int(11) DEFAULT NULL,
  `RatepayerID` int(11) NOT NULL,
  `FullName` varchar(255) DEFAULT NULL,
  `PreviousOwner` varchar(255) DEFAULT NULL,
  `TINNumber` varchar(100) DEFAULT NULL,
  `Notes` text DEFAULT NULL,
  `IsDeceased` tinyint(1) DEFAULT 0,
  `IsOverseasOwner` tinyint(1) DEFAULT 0,
  `WorkPlace` varchar(255) DEFAULT NULL,
  `UpdatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: property_rates
CREATE TABLE `property_rates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Zone` varchar(100) DEFAULT NULL,
  `SubZone` varchar(100) DEFAULT NULL,
  `Category` varchar(100) DEFAULT NULL,
  `BaseRate` decimal(10,2) DEFAULT NULL,
  `RatePer100` decimal(10,2) DEFAULT NULL,
  `RateType` varchar(50) DEFAULT NULL,
  `RateStrick2024` decimal(10,2) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: rate_invoices (already AUTO_INCREMENT)
CREATE TABLE `rate_invoices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `CouncilID` int(11) DEFAULT NULL,
  `RateID` int(11) NOT NULL,
  `PropertyID` int(11) DEFAULT NULL,
  `Period` date DEFAULT NULL,
  `Arrears` decimal(10,2) DEFAULT NULL,
  `CurrentCharges` decimal(10,2) DEFAULT NULL,
  `Penalty` decimal(10,2) DEFAULT NULL,
  `TotalDue` decimal(10,2) DEFAULT NULL,
  `ReceiptsTotal` decimal(10,2) DEFAULT NULL,
  `Balance` decimal(10,2) DEFAULT NULL,
  `CreatedBy` int(11) DEFAULT NULL,
  `customerId` int(11) DEFAULT NULL,
  `gl_transaction_id` int(11) DEFAULT NULL,
  `UpdatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: rate_payments (already AUTO_INCREMENT)
CREATE TABLE `rate_payments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `CouncilID` int(11) DEFAULT NULL,
  `ReceiptID` int(11) NOT NULL,
  `PropertyID` int(11) DEFAULT NULL,
  `RateID` int(11) DEFAULT NULL,
  `ReferenceNo` varchar(100) DEFAULT NULL,
  `Amount` decimal(10,2) DEFAULT NULL,
  `ReceiptDate` date DEFAULT NULL,
  `Method` varchar(50) DEFAULT NULL,
  `UploadedBy` varchar(100) DEFAULT NULL,
  `ScreenshotPath` text DEFAULT NULL,
  `CreatedBy` int(11) DEFAULT NULL,
  `gl_transaction_id` int(11) DEFAULT NULL,
  `UpdatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: receipts
CREATE TABLE `receipts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `invoice_id` int(11) DEFAULT NULL,
  `receipt_number` varchar(100) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `payment_method` varchar(50) DEFAULT NULL,
  `cashier_id` int(11) DEFAULT NULL,
  `session_id` int(11) DEFAULT NULL,
  `payer_name` varchar(255) DEFAULT NULL,
  `receipt_date` datetime DEFAULT NULL,
  `reference` varchar(255) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: cash_sessions
CREATE TABLE `cash_sessions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cashier_id` int(11) NOT NULL,
  `opened_at` datetime NOT NULL,
  `closed_at` datetime DEFAULT NULL,
  `opening_balance` decimal(10,2) NOT NULL,
  `closing_balance` decimal(10,2) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: countries (already AUTO_INCREMENT)
CREATE TABLE `countries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `name_idx` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: companies (already AUTO_INCREMENT)
CREATE TABLE `companies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `country_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `country_idx` (`country_id`),
  KEY `name_idx` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: tenants
CREATE TABLE `tenants` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `subdomain` varchar(100) DEFAULT NULL,
  `custom_domain` varchar(255) DEFAULT NULL,
  `settings` json DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  UNIQUE KEY `subdomain_unique` (`subdomain`),
  UNIQUE KEY `custom_domain_unique` (`custom_domain`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: branches
CREATE TABLE `branches` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `settings` json DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: departments
CREATE TABLE `departments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: districts
CREATE TABLE `districts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: country_holidays (already AUTO_INCREMENT)
CREATE TABLE `country_holidays` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `country_id` int(11) NOT NULL,
  `holiday_name` varchar(100) NOT NULL,
  `holiday_date` date NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `country_idx` (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: user_settings
CREATE TABLE `user_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `value` text NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_setting_unique` (`user_id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: customer_settings
CREATE TABLE `customer_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `value` text NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  UNIQUE KEY `customer_setting_unique` (`customer_id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: employees2users
CREATE TABLE `employees2users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `user_idx` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: employees2locations
CREATE TABLE `employees2locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `location_id` int(11) NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `location_idx` (`location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: overtime_rules
CREATE TABLE `overtime_rules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `scope` enum('country','company') DEFAULT 'company',
  `country_id` int(11) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `shift_hours` int(11) NOT NULL,
  `first4_rate` decimal(5,2) DEFAULT 1.50,
  `after4_rate` decimal(5,2) DEFAULT 2.00,
  `holiday_rate` decimal(5,2) DEFAULT 2.00,
  `sixth_day_rate` decimal(5,2) DEFAULT 2.00,
  `seventh_day_rate` decimal(5,2) DEFAULT 2.00,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `country_idx` (`country_id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `employee_authorized_locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `location_id` int(11) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `employee_idx` (`employee_id`),
  KEY `location_idx` (`location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `employee_location_changes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `stations` varchar(255) NOT NULL,
  `action` varchar(20) NOT NULL,
  `effective_date` date NOT NULL,
  `processed_at` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `emp_idx` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: modules
CREATE TABLE `modules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `slug` varchar(100) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: tenant_modules
CREATE TABLE `tenant_modules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) NOT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `module_id` int(11) NOT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT 1,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`),
  KEY `module_idx` (`module_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: tenant_module_settings
CREATE TABLE `tenant_module_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) NOT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `module_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `value` text NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`),
  KEY `module_idx` (`module_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: tenant_admins
CREATE TABLE `tenant_admins` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) NOT NULL,
  `admin_user_id` int(11) NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `user_idx` (`admin_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

\n-- Table: storage_connectors
CREATE TABLE `storage_connectors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `type` varchar(50) NOT NULL,
  `credentials` text NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`),
  KEY `category_idx` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: form_categories
CREATE TABLE `form_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- Table: event_categories
CREATE TABLE `event_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- Table: service_connections
CREATE TABLE `service_connections` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `type` varchar(50) NOT NULL,
  `credentials` text NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `user_idx` (`user_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: document_categories
CREATE TABLE `document_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: document_folders
CREATE TABLE `document_folders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `parent_idx` (`parent_id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`),
  CONSTRAINT `document_folders_parent_fk` FOREIGN KEY (`parent_id`) REFERENCES `document_folders` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: documents
CREATE TABLE `documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `folder_id` int(11) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `language_code` varchar(10) DEFAULT 'en',
  `owner_id` int(11) NOT NULL,
  `connector_id` int(11) DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `folder_idx` (`folder_id`),
  KEY `category_idx` (`category_id`),
  CONSTRAINT `documents_folder_fk` FOREIGN KEY (`folder_id`) REFERENCES `document_folders` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: document_versions
CREATE TABLE `document_versions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `document_id` int(11) NOT NULL,
  `storage_key` varchar(255) NOT NULL,
  `version_number` int(11) NOT NULL,
  `version_hash` varchar(64) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `doc_idx` (`document_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: document_metadata
CREATE TABLE `document_metadata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `file_path` varchar(255) NOT NULL,
  `meta_key` varchar(100) NOT NULL,
  `meta_value` text DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `file_idx` (`file_path`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: document_archive_entries
CREATE TABLE `document_archive_entries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `document_version_id` int(11) NOT NULL,
  `entry_name` varchar(255) NOT NULL,
  `mime_type` varchar(100) DEFAULT NULL,
  `file_size` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `version_idx` (`document_version_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- Table: document_permissions
CREATE TABLE `document_permissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `document_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `can_view` tinyint(1) NOT NULL DEFAULT 0,
  `can_edit` tinyint(1) NOT NULL DEFAULT 0,
  `can_comment` tinyint(1) NOT NULL DEFAULT 0,
  `tenant_id` int(11) NOT NULL,
  `branch_id` int(11) NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `doc_user_idx` (`document_id`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: document_comments
CREATE TABLE `document_comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `document_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `entry_name` varchar(255) DEFAULT NULL,
  `comment` text NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `doc_idx` (`document_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: calendar_categories
CREATE TABLE `calendar_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- Table: calendar_events
CREATE TABLE `calendar_events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `start_time` datetime NOT NULL,
  `end_time` datetime DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `recurrence_rule` varchar(255) DEFAULT NULL,
  `recurrence_end` datetime DEFAULT NULL,
  `parent_event_id` int(11) DEFAULT NULL,
  `kanban_task_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `category_idx` (`category_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: meeting_links
CREATE TABLE `meeting_links` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `event_id` int(11) NOT NULL,
  `url` varchar(255) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `event_idx` (`event_id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: calendar_accounts
CREATE TABLE `calendar_accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `provider` varchar(50) NOT NULL,
  `access_token` text DEFAULT NULL,
  `refresh_token` text DEFAULT NULL,
  `token_expires` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `uid_idx` (`user_id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: calendar_availability
CREATE TABLE `calendar_availability` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `start_time` datetime NOT NULL,
  `end_time` datetime NOT NULL,
  `type` enum('meeting','vacation') NOT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `uid_idx` (`user_id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: calendar_event_permissions
CREATE TABLE `calendar_event_permissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `event_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `role` enum("viewer","editor","admin") NOT NULL DEFAULT "viewer",
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `event_user_idx` (`event_id`,`user_id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- Table: calendar_event_documents
CREATE TABLE `calendar_event_documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `event_id` int(11) NOT NULL,
  `document_id` int(11) NOT NULL,
  `uploaded_by` int(11) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `event_idx` (`event_id`),
  KEY `doc_idx` (`document_id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: employee_custom_fields
CREATE TABLE `employee_custom_fields` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) NOT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `field_name` varchar(100) NOT NULL,
  `field_type` varchar(50) NOT NULL,
  `is_required` tinyint(1) NOT NULL DEFAULT 0,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: employee_custom_values
CREATE TABLE `employee_custom_values` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `field_id` int(11) NOT NULL,
  `field_value` text DEFAULT NULL,
  `tenant_id` int(11) NOT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `emp_idx` (`employee_id`),
  KEY `field_idx` (`field_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: push_subscriptions
CREATE TABLE `push_subscriptions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `endpoint` text NOT NULL,
  `p256dh` varchar(255) NOT NULL,
  `auth` varchar(255) NOT NULL,
  `browser` varchar(100) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `uid_idx` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: employee_documents
CREATE TABLE `employee_documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `doc_type` varchar(100) NOT NULL,
  `file_name` varchar(255) DEFAULT NULL,
  `file_path` text NOT NULL,
  `signed_at` datetime DEFAULT NULL,
  `uploaded_by` int(11) DEFAULT NULL,
  `uploaded_at` datetime DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `emp_idx` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: employee_certificates
CREATE TABLE `employee_certificates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `cert_name` varchar(255) DEFAULT NULL,
  `file_path` varchar(255) DEFAULT NULL,
  `issued_date` date DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `emp_idx` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: policies
CREATE TABLE `policies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `content` text DEFAULT NULL,
  `content_type` enum('html','pdf') NOT NULL DEFAULT 'html',
  `policy_type` varchar(50) DEFAULT NULL,
  `pdf_url` text DEFAULT NULL,
  `language_code` varchar(10) DEFAULT 'en',
  `expiry_date` date DEFAULT NULL,
  `renewal_status` varchar(50) DEFAULT NULL,
  `visible_to_roles` varchar(255) DEFAULT NULL,
  `visible_to_group` varchar(255) DEFAULT NULL,
  `visible_to_all` tinyint(1) DEFAULT 0,
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `version` int(11) NOT NULL DEFAULT 1,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: policy_acknowledgments
CREATE TABLE `policy_acknowledgments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `policy_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `policy_version` int(11) DEFAULT 1,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `acknowledged_at` datetime NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `browser` varchar(100) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `duration_seconds` int(11) DEFAULT NULL,
  `scrolled_to_bottom` tinyint(1) DEFAULT 0,
  `checkbox_confirmed` tinyint(1) DEFAULT 0,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `pid_idx` (`policy_id`),
  KEY `uid_idx` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: policy_approvals
CREATE TABLE `policy_approvals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `document_id` int(11) NOT NULL,
  `step_order` int(11) NOT NULL,
  `approver_role` varchar(100) NOT NULL,
  `status` varchar(50) DEFAULT 'pending',
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `doc_idx` (`document_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: policy_targets
CREATE TABLE `policy_targets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `policy_id` int(11) NOT NULL,
  `group_id` int(11) DEFAULT NULL,
  `location_id` int(11) DEFAULT NULL,
  `job_id` int(11) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `policy_idx` (`policy_id`),
  KEY `group_idx` (`group_id`),
  KEY `location_idx` (`location_id`),
  KEY `job_idx` (`job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: policy_templates
CREATE TABLE `policy_templates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `content` text DEFAULT NULL,
  `content_type` enum('html','pdf') DEFAULT 'html',
  `pdf_url` text DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- Table: contracts
CREATE TABLE `contracts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `contract_type` varchar(50) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `renewal_status` varchar(50) DEFAULT NULL,
  `status` varchar(50) DEFAULT NULL,
  `file_path` varchar(255) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `emp_idx` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: contract_versions
CREATE TABLE `contract_versions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contract_id` int(11) NOT NULL,
  `storage_key` varchar(255) NOT NULL,
  `version_number` int(11) NOT NULL,
  `version_hash` varchar(64) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `cid_idx` (`contract_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: contract_approvals
CREATE TABLE `contract_approvals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `document_id` int(11) NOT NULL,
  `step_order` int(11) NOT NULL,
  `approver_role` varchar(100) NOT NULL,
  `status` varchar(50) DEFAULT 'pending',
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `doc_idx` (`document_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- Table: shifts
CREATE TABLE `shifts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `overtime_cap` decimal(5,2) DEFAULT 0.00,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: employee_shifts
CREATE TABLE `employee_shifts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `shift_id` int(11) NOT NULL,
  `shift_date` date NOT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `emp_idx` (`employee_id`),
  KEY `shift_idx` (`shift_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: shift_swaps
CREATE TABLE `shift_swaps` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `requester_id` int(11) NOT NULL,
  `target_employee_id` int(11) NOT NULL,
  `requester_shift_id` int(11) NOT NULL,
  `target_shift_id` int(11) NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT "Pending",
  `approved_by` int(11) DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `req_idx` (`requester_id`),
  KEY `tgt_idx` (`target_employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: goal_templates
CREATE TABLE `goal_templates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `department_id` int(11) DEFAULT NULL,
  `role_id` int(11) DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `kpi_id` int(11) DEFAULT NULL,
  `language_code` varchar(10) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `dept_idx` (`department_id`),
  KEY `role_idx` (`role_id`),
  KEY `tenant_idx` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- Table: job_opening_templates
CREATE TABLE `job_opening_templates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `form_fields` json DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: appraisal_templates
CREATE TABLE `appraisal_templates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `role_id` int(11) DEFAULT NULL,
  `language_code` varchar(10) DEFAULT NULL,
  `template_json` json DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `role_idx` (`role_id`),
  KEY `lang_idx` (`language_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: goal_alignment
CREATE TABLE `goal_alignment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_goal_id` int(11) DEFAULT NULL,
  `child_goal_id` int(11) NOT NULL,
  `department_id` int(11) DEFAULT NULL,
  `strategy_id` int(11) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `parent_idx` (`parent_goal_id`),
  KEY `child_idx` (`child_goal_id`),

  KEY `tenant_idx` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: competencies
CREATE TABLE `competencies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `UpdatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `name_idx` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: role_competencies
CREATE TABLE `role_competencies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_id` int(11) NOT NULL,
  `competency_id` int(11) NOT NULL,
  `required_level` int(11) NOT NULL,
  `UpdatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `role_idx` (`role_id`),
  KEY `comp_idx` (`competency_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: employee_competencies
CREATE TABLE `employee_competencies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `competency_id` int(11) NOT NULL,
  `self_rating` int(11) DEFAULT NULL,
  `manager_rating` int(11) DEFAULT NULL,
  `UpdatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `emp_idx` (`employee_id`),
  KEY `comp_idx` (`competency_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: competency_assessments
CREATE TABLE `competency_assessments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `competency_id` int(11) NOT NULL,
  `assessor_id` int(11) NOT NULL,
  `rating` int(11) NOT NULL,
  `notes` text DEFAULT NULL,
  `assessment_date` datetime DEFAULT NULL,
  `UpdatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `emp_idx` (`employee_id`),
  KEY `comp_idx` (`competency_id`),
  KEY `assessor_idx` (`assessor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: safety_documents
CREATE TABLE `safety_documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `folder_id` int(11) DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `owner_id` int(11) NOT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `connector_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `visible_to_roles` varchar(255) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `folder_idx` (`folder_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: safety_document_versions
CREATE TABLE `safety_document_versions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `document_id` int(11) NOT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `storage_key` varchar(255) NOT NULL,
  `version_number` int(11) NOT NULL,
  `version_hash` varchar(64) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `doc_idx` (`document_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: form_definitions
CREATE TABLE `form_definitions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `base_form_id` int(11) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: field_definitions
CREATE TABLE `field_definitions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `form_id` int(11) NOT NULL,
  `table_name` varchar(100) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `label` varchar(100) NOT NULL,
  `field_name` varchar(100) NOT NULL,
  `field_type` varchar(50) NOT NULL,
  `options` json DEFAULT NULL,
  `is_required` tinyint(1) NOT NULL DEFAULT 0,
  `order_index` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `form_idx` (`form_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: table_views
CREATE TABLE `table_views` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `form_id` int(11) NOT NULL,
  `table_name` varchar(100) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `columns` json DEFAULT NULL,
  `sort_by` varchar(100) DEFAULT NULL,
  `sort_dir` varchar(4) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `form_idx` (`form_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: view_filters
CREATE TABLE `view_filters` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `view_id` int(11) NOT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `field_name` varchar(100) NOT NULL,
  `operator` varchar(10) NOT NULL,
  `value` varchar(255) DEFAULT NULL,
  `order_index` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `view_idx` (`view_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: report_definitions
CREATE TABLE `report_definitions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `table_name` varchar(100) NOT NULL,
  `selected_fields` json DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: report_filters
CREATE TABLE `report_filters` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `report_id` int(11) NOT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `field_name` varchar(100) NOT NULL,
  `operator` varchar(10) NOT NULL,
  `value` varchar(255) DEFAULT NULL,
  `order_index` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `report_idx` (`report_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- Table: custom_fields
CREATE TABLE `custom_fields` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) NOT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `model` varchar(100) NOT NULL,
  `field_name` varchar(100) NOT NULL,
  `field_type` varchar(50) NOT NULL,
  `is_required` tinyint(1) NOT NULL DEFAULT 0,
  `options` json DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: custom_field_values
CREATE TABLE `custom_field_values` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `field_id` int(11) NOT NULL,
  `record_id` int(11) NOT NULL,
  `field_value` text DEFAULT NULL,
  `tenant_id` int(11) NOT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `field_idx` (`field_id`),
  KEY `record_idx` (`record_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: forms
CREATE TABLE `forms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `mapped_table` varchar(100) DEFAULT NULL,
  `on_submit_workflow_id` int(11) DEFAULT NULL,
  `columns` int(11) NOT NULL DEFAULT 1,
  `layout_type` varchar(10) NOT NULL DEFAULT 'grid',
  `elements` jsonb DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- Table: element_types
CREATE TABLE `element_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `html_type` varchar(50) DEFAULT NULL,
  `display_class` varchar(100) DEFAULT NULL,
  `sql_type` varchar(50) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: element_options
CREATE TABLE `element_options` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `element_id` int(11) NOT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `display_text` varchar(100) NOT NULL,
  `value` varchar(100) NOT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `element_idx` (`element_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: form_buttons
CREATE TABLE `form_buttons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `form_id` int(11) NOT NULL,
  `label` varchar(100) NOT NULL,
  `action_type` varchar(50) NOT NULL,
  `action_payload` text DEFAULT NULL,
  `order_index` int(11) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `form_idx` (`form_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: form_submissions
CREATE TABLE `form_submissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `form_id` int(11) NOT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `submitted_by` int(11) DEFAULT NULL,
  `submitted_at` datetime NOT NULL DEFAULT current_timestamp,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `form_idx` (`form_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: form_submission_values
CREATE TABLE `form_submission_values` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `submission_id` int(11) NOT NULL,
  `element_id` int(11) NOT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `value` text DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `sub_idx` (`submission_id`),
  KEY `element_idx` (`element_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: form_wizards
CREATE TABLE `form_wizards` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: form_wizard_steps
CREATE TABLE `form_wizard_steps` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `wizard_id` int(11) NOT NULL,
  `step_number` int(11) NOT NULL,
  `form_id` int(11) NOT NULL,
  `condition_field` varchar(100) DEFAULT NULL,
  `condition_value` varchar(100) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `wizard_idx` (`wizard_id`),
  KEY `form_idx` (`form_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: career_paths
CREATE TABLE `career_paths` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `role_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: career_steps
CREATE TABLE `career_steps` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `path_id` int(11) NOT NULL,
  `step_number` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `role_id` int(11) DEFAULT NULL,
  `requirements` text DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: review_calibrations
CREATE TABLE `review_calibrations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `review_id` int(11) NOT NULL,
  `department` varchar(255) NOT NULL,
  `country_code` varchar(5) DEFAULT NULL,
  `original_rating` decimal(3,2) DEFAULT NULL,
  `adjusted_rating` decimal(3,2) DEFAULT NULL,
  `status` varchar(20) DEFAULT 'pending',
  `moderator_id` int(11) DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: projects
CREATE TABLE `projects` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `status` varchar(50) DEFAULT 'active',
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: kanban_boards
CREATE TABLE `kanban_boards` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` int(11) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `calendar_category_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `project_idx` (`project_id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`),
  KEY `kanban_boards_cat_idx` (`calendar_category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: kanban_columns
CREATE TABLE `kanban_columns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `board_id` int(11) NOT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `position` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `board_idx` (`board_id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: kanban_tasks
CREATE TABLE `kanban_tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `column_id` int(11) NOT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `assignee_id` int(11) DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `record_table` varchar(255) DEFAULT NULL,
  `record_id` int(11) DEFAULT NULL,
  `status` varchar(50) DEFAULT 'pending',
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `column_idx` (`column_id`),
  KEY `assignee_idx` (`assignee_id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- Table: kanban_comments
CREATE TABLE `kanban_comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `task_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `comment` text NOT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `task_idx` (`task_id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_jsas
CREATE TABLE `ohs_jsas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `doc_path` varchar(255) DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `department_id` int(11) DEFAULT NULL,
  `analysis_date` date DEFAULT NULL,
  `review_date` datetime DEFAULT NULL,
  `prepared_by` int(11) DEFAULT NULL,
  `team_member_ids` json DEFAULT NULL,
  `permit_requirements` json DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_jsa_steps
CREATE TABLE `ohs_jsa_steps` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `jsa_id` int(11) NOT NULL,
  `step_order` int(11) DEFAULT NULL,
  `step` text DEFAULT NULL,
  `hazard_id` int(11) DEFAULT NULL,
  `responsible_user_id` int(11) DEFAULT NULL,
  `likelihood` int(11) DEFAULT NULL,
  `severity` int(11) DEFAULT NULL,
  `risk_before` int(11) DEFAULT NULL,
  `risk_after` int(11) DEFAULT NULL,
  `controls` text DEFAULT NULL,
  `control_type` varchar(20) DEFAULT NULL,
  `ppe_required` json DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `ohs_jsa_steps_jsa_idx` (`jsa_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_jsa_signoffs
CREATE TABLE `ohs_jsa_signoffs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `jsa_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `signed_at` datetime DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `ohs_jsa_signoffs_jsa_idx` (`jsa_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_jsa_reviewers
CREATE TABLE `ohs_jsa_reviewers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `jsa_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `ohs_jsa_reviewers_jsa_idx` (`jsa_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_jsa_step_files
CREATE TABLE `ohs_jsa_step_files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `jsa_step_id` int(11) NOT NULL,
  `document_id` int(11) NOT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `ohs_jsa_step_files_step_idx` (`jsa_step_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_jsa_training
CREATE TABLE `ohs_jsa_training` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `jsa_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `trained_at` datetime DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `ohs_jsa_training_jsa_idx` (`jsa_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_jsa_versions
CREATE TABLE `ohs_jsa_versions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `jsa_id` int(11) NOT NULL,
  `version_no` int(11) NOT NULL,
  `data_json` json NOT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `ohs_jsa_versions_jsa_idx` (`jsa_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: user_board_settings
CREATE TABLE `user_board_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `board_id` int(11) NOT NULL,
  `column_order` json DEFAULT NULL,
  `hidden_columns` json DEFAULT NULL,
  `selected` tinyint(1) NOT NULL DEFAULT 0,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `user_idx` (`user_id`),
  KEY `board_idx` (`board_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ohs_action_signoffs
CREATE TABLE `ohs_action_signoffs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(11) NOT NULL,
  `item_type` varchar(20) NOT NULL,
  `user_id` int(11) NOT NULL,
  `signature` text DEFAULT NULL,
  `document_id` int(11) DEFAULT NULL,
  `signed_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE INDEX `ohs_action_signoffs_item_idx` ON `ohs_action_signoffs` (`item_id`);

-- Table: ohs_incident_signoffs
CREATE TABLE `ohs_incident_signoffs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `incident_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `signature` text DEFAULT NULL,
  `document_id` int(11) DEFAULT NULL,
  `signed_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE INDEX `ohs_incident_signoffs_idx` ON `ohs_incident_signoffs` (`incident_id`);


-- Table: safety_incident_types
CREATE TABLE `safety_incident_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `form_id` int(11) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- Table: safety_incidents
CREATE TABLE `safety_incidents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `incident_type` varchar(100) DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `gps` varchar(255) DEFAULT NULL,
  `document_ids` varchar(255) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: safety_incident_files
CREATE TABLE `safety_incident_files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `incident_id` int(11) NOT NULL,
  `file_path` varchar(255) DEFAULT NULL,
  `uploaded_by` int(11) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: risk_assessment_types
CREATE TABLE `risk_assessment_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: risk_assessments
CREATE TABLE `risk_assessments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `risk_assessment_type_id` int(11) DEFAULT NULL,
  `incident_id` int(11) DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `department_id` int(11) DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `assessment_date` date DEFAULT NULL,
  `review_date` date DEFAULT NULL,
  `form_data` jsonb DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `risk_assessments_type_idx` (`risk_assessment_type_id`),
  KEY `risk_assessments_incident_idx` (`incident_id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: risk_assessment_hazards
CREATE TABLE `risk_assessment_hazards` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `risk_assessment_id` int(11) NOT NULL,
  `hazard_description` text DEFAULT NULL,
  `who_might_be_harmed` text DEFAULT NULL,
  `existing_control_measures` text DEFAULT NULL,
  `risk_rating_before` int(11) DEFAULT NULL,
  `additional_controls` text DEFAULT NULL,
  `residual_risk_rating` int(11) DEFAULT NULL,
  `responsible_person` varchar(255) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `risk_assessment_hazards_ra_idx` (`risk_assessment_id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: risk_assessment_ppe
CREATE TABLE `risk_assessment_ppe` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `risk_assessment_id` int(11) NOT NULL,
  `checklist_item` varchar(255) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `risk_assessment_ppe_ra_idx` (`risk_assessment_id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: risk_assessment_signoffs
CREATE TABLE `risk_assessment_signoffs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `risk_assessment_id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `position` varchar(255) DEFAULT NULL,
  `signature` varchar(255) DEFAULT NULL,
  `signed_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `risk_assessment_signoffs_ra_idx` (`risk_assessment_id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: risk_assessment_files
CREATE TABLE `risk_assessment_files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `risk_assessment_id` int(11) NOT NULL,
  `file_path` varchar(255) DEFAULT NULL,
  `uploaded_by` int(11) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `risk_assessment_files_ra_idx` (`risk_assessment_id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- Table: `incident_alert_rules`
CREATE TABLE `incident_alert_rules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `incident_type_id` int(11) DEFAULT NULL,
  `severity` varchar(50) DEFAULT NULL,
  `location_id` int(11) DEFAULT NULL,
  `send_alert_to` int(11) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `send_email` tinyint(1) DEFAULT 0,
  `phone` varchar(50) DEFAULT NULL,
  `send_sms` tinyint(1) DEFAULT 0,
  `send_push` tinyint(1) DEFAULT 0,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: safety_inductions
CREATE TABLE `safety_inductions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `induction_date` date NOT NULL,
  `document_id` int(11) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `emp_idx` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: employee_licenses
CREATE TABLE `employee_licenses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `license_type` varchar(100) NOT NULL,
  `license_number` varchar(100) NOT NULL,
  `expiry_date` date DEFAULT NULL,
  `document_id` int(11) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `emp_idx` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: audit_logs
CREATE TABLE `audit_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `action` varchar(255) NOT NULL,
  `entity` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: ui_modals
CREATE TABLE `ui_modals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `config` json DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `icon` varchar(50) DEFAULT NULL,
  `aria_label` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `ui_modals_tenant_idx` (`tenant_id`),
  KEY `ui_modals_user_idx` (`user_id`),
  KEY `ui_modals_branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: vehicles
CREATE TABLE vehicles (
  id SERIAL,
  make varchar(100) DEFAULT NULL,
  model varchar(100) DEFAULT NULL,
  vehicle_number varchar(100) DEFAULT NULL,
  vin varchar(100) DEFAULT NULL,
  engine_number varchar(100) DEFAULT NULL,
  mileage INTEGER DEFAULT NULL,
  year INTEGER DEFAULT NULL,
  type varchar(100) DEFAULT NULL,
  fitness_expires DATE DEFAULT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
);
CREATE INDEX vehicles_tenant_idx ON vehicles (tenant_id);
CREATE INDEX vehicles_branch_idx ON vehicles (branch_id);

-- Table: vehicle_makes
CREATE TABLE vehicle_makes (
  id SERIAL,
  name varchar(100) NOT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
);
CREATE INDEX vehicle_makes_tenant_idx ON vehicle_makes (tenant_id);
CREATE INDEX vehicle_makes_branch_idx ON vehicle_makes (branch_id);

-- Table: vehicle_models
CREATE TABLE vehicle_models (
  id SERIAL,
  make_id INTEGER DEFAULT NULL,
  name varchar(100) NOT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
);
CREATE INDEX vehicle_models_make_idx ON vehicle_models (make_id);
CREATE INDEX vehicle_models_tenant_idx ON vehicle_models (tenant_id);
CREATE INDEX vehicle_models_branch_idx ON vehicle_models (branch_id);

-- Table: vehicle_types
CREATE TABLE vehicle_types (
  id SERIAL,
  name varchar(100) NOT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
);
CREATE INDEX vehicle_types_tenant_idx ON vehicle_types (tenant_id);
CREATE INDEX vehicle_types_branch_idx ON vehicle_types (branch_id);

-- Table: service_types
CREATE TABLE service_types (
  id SERIAL,
  name varchar(100) NOT NULL,
  description text DEFAULT NULL,
  make varchar(100) DEFAULT NULL,
  model varchar(100) DEFAULT NULL,
  type varchar(100) DEFAULT NULL,
  km_interval INTEGER DEFAULT NULL,
  month_interval INTEGER DEFAULT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
);
CREATE INDEX service_types_tenant_idx ON service_types (tenant_id);
CREATE INDEX service_types_branch_idx ON service_types (branch_id);

-- Table: vehicle_services
CREATE TABLE vehicle_services (
  id SERIAL,
  vehicle_id INTEGER NOT NULL,
  service_type_id INTEGER NOT NULL,
  service_date date DEFAULT NULL,
  odometer INTEGER DEFAULT NULL,
  notes text DEFAULT NULL,
  cost DECIMAL(10,2) DEFAULT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
);
CREATE INDEX vehicle_services_tenant_idx ON vehicle_services (tenant_id);
CREATE INDEX vehicle_services_branch_idx ON vehicle_services (branch_id);

-- Table: vehicle_trips
CREATE TABLE vehicle_trips (
  id SERIAL,
  vehicle_id INTEGER NOT NULL,
  driver_id INTEGER DEFAULT NULL,
  trip_date date DEFAULT NULL,
  start_time timestamp DEFAULT NULL,
  end_time timestamp DEFAULT NULL,
  start_location varchar(255) DEFAULT NULL,
  start_latitude decimal(10,6) DEFAULT NULL,
  start_longitude decimal(10,6) DEFAULT NULL,
  end_location varchar(255) DEFAULT NULL,
  end_latitude decimal(10,6) DEFAULT NULL,
  end_longitude decimal(10,6) DEFAULT NULL,
  distance_km INTEGER DEFAULT NULL,
  status varchar(20) DEFAULT 'Draft',
  start_mileage INTEGER DEFAULT NULL,
  end_mileage INTEGER DEFAULT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
);
CREATE INDEX vehicle_trips_tenant_idx ON vehicle_trips (tenant_id);
CREATE INDEX vehicle_trips_branch_idx ON vehicle_trips (branch_id);
CREATE INDEX vehicle_trips_driver_idx ON vehicle_trips (driver_id);
CREATE INDEX vehicle_trips_status_idx ON vehicle_trips (status);

-- Table: vehicle_trip_points
CREATE TABLE vehicle_trip_points (
  id SERIAL,
  trip_id INTEGER NOT NULL,
  latitude DECIMAL(10,6) NOT NULL,
  longitude DECIMAL(10,6) NOT NULL,
  recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
);
CREATE INDEX vehicle_trip_points_trip_idx ON vehicle_trip_points (trip_id);
CREATE INDEX vehicle_trip_points_tenant_idx ON vehicle_trip_points (tenant_id);
CREATE INDEX vehicle_trip_points_branch_idx ON vehicle_trip_points (branch_id);
-- Table: vehicle_downtime
CREATE TABLE vehicle_downtime (
  id SERIAL,
  vehicle_id INTEGER NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE DEFAULT NULL,
  reason text DEFAULT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
);
CREATE INDEX vehicle_downtime_vehicle_idx ON vehicle_downtime (vehicle_id);
CREATE INDEX vehicle_downtime_tenant_idx ON vehicle_downtime (tenant_id);
CREATE INDEX vehicle_downtime_branch_idx ON vehicle_downtime (branch_id);
-- Table: dashboard_card_permissions
CREATE TABLE dashboard_card_permissions (
  id SERIAL,
  card_slug varchar(255) NOT NULL,
  group_id INTEGER DEFAULT NULL,
  user_id INTEGER DEFAULT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  can_view BOOLEAN NOT NULL DEFAULT TRUE,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
);
CREATE INDEX dcp_tenant_idx ON dashboard_card_permissions (tenant_id);
CREATE INDEX dcp_branch_idx ON dashboard_card_permissions (branch_id);
CREATE INDEX dcp_user_idx ON dashboard_card_permissions (user_id);
CREATE INDEX dcp_group_idx ON dashboard_card_permissions (group_id);
CREATE INDEX dcp_slug_idx ON dashboard_card_permissions (card_slug);

-- Table: table_column_settings
CREATE TABLE table_column_settings (
  id SERIAL,
  table_name varchar(100) NOT NULL,
  user_id INTEGER DEFAULT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  hidden_columns json DEFAULT NULL,
  column_order json DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
);
CREATE INDEX tcs_table_idx ON table_column_settings (table_name);
CREATE INDEX tcs_tenant_idx ON table_column_settings (tenant_id);
CREATE INDEX tcs_branch_idx ON table_column_settings (branch_id);
CREATE INDEX tcs_user_idx ON table_column_settings (user_id);

-- Table: vehicle_reports
CREATE TABLE vehicle_reports (
  id SERIAL,
  vehicle_id INTEGER NOT NULL,
  driver_id INTEGER DEFAULT NULL,
  report_type varchar(100) DEFAULT NULL,
  description text DEFAULT NULL,
  status varchar(50) DEFAULT NULL,
  assigned_to INTEGER DEFAULT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  created_at timestamp DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
);
CREATE INDEX vehicle_reports_vehicle_idx ON vehicle_reports (vehicle_id);
CREATE INDEX vehicle_reports_assigned_idx ON vehicle_reports (assigned_to);
CREATE INDEX vehicle_reports_tenant_idx ON vehicle_reports (tenant_id);
CREATE INDEX vehicle_reports_updated_idx ON vehicle_reports (updatedAt);

-- Table: vehicle_report_comments
CREATE TABLE vehicle_report_comments (
  id SERIAL,
  report_id INTEGER NOT NULL,
  user_id INTEGER NOT NULL,
  comment text NOT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  created_at timestamp DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
);
CREATE INDEX vehicle_report_comments_tenant_idx ON vehicle_report_comments (tenant_id);
CREATE INDEX vehicle_report_comments_branch_idx ON vehicle_report_comments (branch_id);

-- Table: vehicle_report_parts
CREATE TABLE vehicle_report_parts (
  id SERIAL,
  report_id INTEGER NOT NULL,
  part_name varchar(255) NOT NULL,
  quantity INTEGER DEFAULT NULL,
  repair_cost DECIMAL(10,2) DEFAULT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
);
CREATE INDEX vehicle_report_parts_tenant_idx ON vehicle_report_parts (tenant_id);
CREATE INDEX vehicle_report_parts_branch_idx ON vehicle_report_parts (branch_id);

-- Table: clients
CREATE TABLE `clients` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tenant_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `country` varchar(100) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `comments` text DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `clients_tenant_idx` (`tenant_id`),
  KEY `clients_user_idx` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: cases
CREATE TABLE `cases` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_id` int(11) DEFAULT NULL,
  `case_number` varchar(100) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `status` varchar(50) DEFAULT NULL,
  `opened_at` timestamp NULL DEFAULT NULL,
  `closed_at` timestamp NULL DEFAULT NULL,
  `lead_rep_id` int(11) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `initial_instructions` text DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `cases_client_idx` (`client_id`),
  KEY `cases_rep_idx` (`lead_rep_id`),
  KEY `cases_tenant_idx` (`tenant_id`),
  KEY `cases_user_idx` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- Table: case_hours
CREATE TABLE `case_hours` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `case_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `hours` decimal(8,2) DEFAULT 0,
  `rate` decimal(10,2) DEFAULT 0,
  `description` text DEFAULT NULL,
  `billed_to_client` tinyint(1) DEFAULT 0,
  `approved` tinyint(1) DEFAULT 0,
  `work_type` varchar(50) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `instructions` text DEFAULT NULL,
  `posted` tinyint(1) DEFAULT 0,
  `posted_at` datetime DEFAULT NULL,
  `approval_comment` text DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `case_hours_case_idx` (`case_id`),
  KEY `case_hours_user_idx` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: case_hour_documents
CREATE TABLE `case_hour_documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `case_hour_id` int(11) NOT NULL,
  `document_id` int(11) NOT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `hour_idx` (`case_hour_id`),
  KEY `doc_idx` (`document_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- Table: case_instructions
CREATE TABLE `case_instructions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `case_id` int(11) NOT NULL,
  `instruction_date` datetime DEFAULT NULL,
  `instruction` text,
  `document_ids` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `case_idx` (`case_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- Table: invoice_types
CREATE TABLE invoice_types (
  id SERIAL,
  name varchar(100) NOT NULL,
  description text DEFAULT NULL,
  tenant_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: expense_types
CREATE TABLE expense_types (
  id SERIAL,
  name varchar(100) NOT NULL,
  description text DEFAULT NULL,
  tenant_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: payment_types
CREATE TABLE payment_types (
  id SERIAL,
  name varchar(100) NOT NULL,
  description text DEFAULT NULL,
  tenant_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: invoices
CREATE TABLE invoices (
  id SERIAL,
  type_id INTEGER NOT NULL,
  reference_table varchar(100) DEFAULT NULL,
  reference_id INTEGER DEFAULT NULL,
  name varchar(100) DEFAULT NULL,
  invoice_number varchar(100) NOT NULL,
  invoice_date date DEFAULT NULL,
  amount decimal(10,2) DEFAULT 0.00,
  status varchar(50) DEFAULT NULL,
  sent_at datetime DEFAULT NULL,
  invoice_template_id INTEGER DEFAULT NULL,
  line_items json DEFAULT NULL,
  details json DEFAULT NULL,
  tenant_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: expenses
CREATE TABLE expenses (
  id SERIAL,
  type_id INTEGER NOT NULL,
  reference_table varchar(100) DEFAULT NULL,
  reference_id INTEGER DEFAULT NULL,
  expense_date date DEFAULT NULL,
  amount decimal(10,2) DEFAULT 0.00,
  description text DEFAULT NULL,
  approved tinyint(1) DEFAULT 0,
  billed_to_client tinyint(1) DEFAULT 0,
  billable tinyint(1) DEFAULT 0,
  employee_id INTEGER DEFAULT NULL,
  user_id INTEGER NOT NULL,
  document_ids text DEFAULT NULL,
  tenant_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id),
  KEY `expenses_employee_idx` (employee_id),
  KEY `expenses_user_idx` (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: payments
CREATE TABLE payments (
  id SERIAL,
  type_id INTEGER NOT NULL,
  invoice_id INTEGER NOT NULL,
  payment_date date DEFAULT NULL,
  amount decimal(10,2) DEFAULT 0.00,
  payment_method varchar(50) DEFAULT NULL,
  document_ids text DEFAULT NULL,
  screenshot_ids text DEFAULT NULL,
  tenant_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- Table: workflow_event_triggers
CREATE TABLE `workflow_event_triggers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `event_name` varchar(100) NOT NULL,
  `table_name` varchar(100) NOT NULL,
  `trigger_type` varchar(10) NOT NULL,
  `form_id` int(11) DEFAULT NULL,
  `operations` json DEFAULT NULL,
  `field_map` json DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `form_idx` (`form_id`),
  KEY `table_idx` (`table_name`),
  KEY `event_idx` (`event_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- Table: workflow_step_types
CREATE TABLE `workflow_step_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `metadata` json DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- Table: workflow_api_triggers
CREATE TABLE `workflow_api_triggers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `workflow_id` int(11) NOT NULL,
  `token` varchar(100) NOT NULL,
  `method` varchar(10) DEFAULT 'POST',
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `wf_idx` (`workflow_id`),
  KEY `token_idx` (`token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: workflow_triggers
CREATE TABLE workflow_triggers (
  id SERIAL,
  workflow_id INTEGER NOT NULL,
  event_name varchar(100) NOT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE INDEX workflow_triggers_wf_idx ON workflow_triggers (workflow_id);
CREATE INDEX workflow_triggers_event_idx ON workflow_triggers (event_name);

-- Table: invoice_templates
CREATE TABLE invoice_templates (
  id SERIAL,
  name varchar(100) NOT NULL,
  file_path text NOT NULL,
  tenant_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- Table: certificate_templates
CREATE TABLE certificate_templates (
  id SERIAL,
  name varchar(100) NOT NULL,
  file_path text NOT NULL,
  tenant_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: form_certificates
CREATE TABLE form_certificates (
  id SERIAL,
  form_id INTEGER NOT NULL,
  submission_id INTEGER NOT NULL,
  template_id INTEGER NOT NULL,
  status varchar(100) DEFAULT NULL,
  issued_at date DEFAULT NULL,
  expiry_date date DEFAULT NULL,
  file_path text DEFAULT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: invoice_settings
CREATE TABLE invoice_settings (
  id SERIAL,
  tenant_id INTEGER DEFAULT NULL,
  template_id INTEGER DEFAULT NULL,
  case_template_id INTEGER DEFAULT NULL,
  logo_document_id INTEGER DEFAULT NULL,
  name varchar(100) DEFAULT NULL,
  instructions text DEFAULT NULL,
  disclaimer text DEFAULT NULL,
  bank_details text DEFAULT NULL,
  number_format varchar(50) DEFAULT NULL,
  starting_number INTEGER DEFAULT NULL,
  rollover_date date DEFAULT NULL,
  isDeleted BOOLEAN DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- Table: detail_views
CREATE TABLE detail_views (
  id SERIAL,
  name varchar(100) NOT NULL,
  config json DEFAULT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE INDEX detail_views_tenant_idx ON detail_views (tenant_id);
CREATE INDEX detail_views_branch_idx ON detail_views (branch_id);

-- Table: detail_view_elements
CREATE TABLE detail_view_elements (
  id SERIAL,
  detail_view_id INTEGER NOT NULL,
  config json DEFAULT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  order_index INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE INDEX dve_view_idx ON detail_view_elements (detail_view_id);
CREATE INDEX dve_tenant_idx ON detail_view_elements (tenant_id);
CREATE INDEX dve_branch_idx ON detail_view_elements (branch_id);

-- Table: fishbone_diagrams
CREATE TABLE fishbone_diagrams (
  id SERIAL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  problem varchar(255) NOT NULL,
  diagram_data json DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE INDEX fishbone_diagrams_tenant_idx ON fishbone_diagrams (tenant_id);
CREATE INDEX fishbone_diagrams_branch_idx ON fishbone_diagrams (branch_id);

-- Table: ohs_needs_expectations
CREATE TABLE ohs_needs_expectations (
  id SERIAL,
  interested_party varchar(255) NOT NULL,
  needs text DEFAULT NULL,
  expectations text DEFAULT NULL,
  compliance_required BOOLEAN DEFAULT FALSE,
  source varchar(255) DEFAULT NULL,
  status varchar(50) DEFAULT NULL,
  notes text DEFAULT NULL,
  review_date date DEFAULT NULL,
  review_by INTEGER DEFAULT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE INDEX one_tenant_idx ON ohs_needs_expectations (tenant_id);
CREATE INDEX one_branch_idx ON ohs_needs_expectations (branch_id);


-- Table: ohs_stakeholders
CREATE TABLE `ohs_stakeholders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stakeholder_name` varchar(255) NOT NULL,
  `category` varchar(100) DEFAULT NULL,
  `role_influence` text DEFAULT NULL,
  `needs_expectations` text DEFAULT NULL,
  `impact` text DEFAULT NULL,
  `communication_method` varchar(100) DEFAULT NULL,
  `review_date` date DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE INDEX `ohs_stakeholders_tenant_idx` ON `ohs_stakeholders` (`tenant_id`);
CREATE INDEX `ohs_stakeholders_branch_idx` ON `ohs_stakeholders` (`branch_id`);

-- Table: form_submission_reviews
CREATE TABLE form_submission_reviews (
  id SERIAL,
  submission_id INTEGER NOT NULL,
  reviewer_id INTEGER NOT NULL,
  review_date DATE DEFAULT NULL,
  status varchar(50) DEFAULT 'pending',
  notes text DEFAULT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE INDEX form_submission_reviews_tenant_idx ON form_submission_reviews (tenant_id);
CREATE INDEX form_submission_reviews_branch_idx ON form_submission_reviews (branch_id);

-- Tenant Table Manager modal
INSERT INTO ui_modals (name, config, tenant_id, branch_id, icon, aria_label, description) VALUES (
  'tenant_table_manager',
  '{"title":"Tenant Table Manager","body":[{"type":"table","options":{"tableName":"tenant_tables","showGlobalSearch":false,"addButton":false}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL,
  'ic_table_list',
  'Tenant Table Manager',
  'Tenant Table Manager'
);

-- Supervisor Types modal
INSERT INTO ui_modals (name, config, tenant_id, branch_id) VALUES (
  'Supervisor Types',
  '{"title":"Supervisor Types","body":[{"type":"table","options":{"tableName":"supervisor_types","showGlobalSearch":false}}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}',
  NULL,
  NULL
);

-- Table: ohs_issues
CREATE TABLE `ohs_issues` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `description` text DEFAULT NULL,
  `type` varchar(100) DEFAULT NULL,
  `category` varchar(100) DEFAULT NULL,
  `impact` varchar(255) DEFAULT NULL,
  `risk_or_opportunity` varchar(255) DEFAULT NULL,
  `action_taken` text DEFAULT NULL,
  `owner` varchar(100) DEFAULT NULL,
  `review_date` date DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `tenant_idx` (`tenant_id`),
  KEY `branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: graphs
CREATE TABLE `graphs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `config` json DEFAULT '{"groupBy": "", "aggregateFunc": "", "timeUnit": ""}',
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `createdAt` timestamp NOT NULL DEFAULT current_timestamp,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `graphs_tenant_idx` (`tenant_id`),
  KEY `graphs_branch_idx` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: demo_graph_data
CREATE TABLE `demo_graph_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `x_value` decimal(10,2) DEFAULT NULL,
  `y_value` decimal(10,2) DEFAULT NULL,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- Table: ohs_fault_trees
CREATE TABLE ohs_fault_trees (
  id SERIAL,
  incident_id INTEGER NOT NULL,
  tree_data jsonb DEFAULT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE INDEX ohs_fault_trees_tenant_idx ON ohs_fault_trees (tenant_id);
CREATE INDEX ohs_fault_trees_branch_idx ON ohs_fault_trees (branch_id);
-- Table: risk_categories
CREATE TABLE risk_categories (
  id SERIAL,
  name varchar(255) NOT NULL,
  description text DEFAULT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE INDEX risk_categories_tenant_idx ON risk_categories (tenant_id);
CREATE INDEX risk_categories_branch_idx ON risk_categories (branch_id);

-- Table: risk_identifications
CREATE TABLE risk_identifications (
  id SERIAL,
  risk_description text DEFAULT NULL,
  risk_category_id INTEGER DEFAULT NULL,
  root_cause text DEFAULT NULL,
  likelihood INTEGER DEFAULT NULL,
  impact INTEGER DEFAULT NULL,
  risk_rating varchar(255) DEFAULT NULL,
  current_controls text DEFAULT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE INDEX risk_identifications_tenant_idx ON risk_identifications (tenant_id);
CREATE INDEX risk_identifications_branch_idx ON risk_identifications (branch_id);-- Table: ohs_five_whys
CREATE TABLE ohs_five_whys (
  id SERIAL,
  incident_id INT DEFAULT NULL,
  whys jsonb NOT NULL,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: corrective_actions
CREATE TABLE corrective_actions (
  id SERIAL,
  incident_id INTEGER NOT NULL,
  name varchar(255) DEFAULT NULL,
  description text DEFAULT NULL,
  steps text DEFAULT NULL,
  responsible_user_id INTEGER DEFAULT NULL,
  status varchar(50) DEFAULT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE INDEX corrective_actions_incident_idx ON corrective_actions (incident_id);

-- Table: mobile_config
CREATE TABLE mobile_config (
  id SERIAL,
  forms jsonb DEFAULT NULL,
  wizards jsonb DEFAULT NULL,
  modals jsonb DEFAULT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: resource_types
CREATE TABLE resource_types (
  id SERIAL,
  code varchar(50) NOT NULL UNIQUE,
  name varchar(100) NOT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: resources
CREATE TABLE resources (
  id SERIAL,
  resource_type_id INTEGER NOT NULL,
  resource_id INTEGER NOT NULL,
  name varchar(255) DEFAULT NULL,
  reference_table varchar(100) DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id),
  UNIQUE KEY resources_unique (resource_type_id, resource_id),
  KEY resources_type_idx (resource_type_id),
  CONSTRAINT resources_type_fk FOREIGN KEY (resource_type_id) REFERENCES resource_types(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: access_policies
CREATE TABLE access_policies (
  id SERIAL,
  resource_id INTEGER NOT NULL,
  role_id INTEGER NOT NULL,
  can_view BOOLEAN DEFAULT FALSE,
  can_edit BOOLEAN DEFAULT FALSE,
  can_delete BOOLEAN DEFAULT FALSE,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id),
  KEY access_policies_resource_idx (resource_id),
  KEY access_policies_role_idx (role_id),
  KEY access_policies_tenant_idx (tenant_id),
  KEY access_policies_branch_idx (branch_id),
  CONSTRAINT access_policies_resource_fk FOREIGN KEY (resource_id) REFERENCES resources(id),
  CONSTRAINT access_policies_role_fk FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: user_admin_roles
CREATE TABLE user_admin_roles (
  id SERIAL,
  user_id INTEGER NOT NULL,
  role_id INTEGER NOT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id),
  KEY user_admin_roles_user_idx (user_id),
  KEY user_admin_roles_role_idx (role_id),
  KEY user_admin_roles_tenant_idx (tenant_id),
  KEY user_admin_roles_branch_idx (branch_id),
  CONSTRAINT user_admin_roles_user_fk FOREIGN KEY (user_id) REFERENCES users(id),
  CONSTRAINT user_admin_roles_role_fk FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: permissions
CREATE TABLE permissions (
  id SERIAL,
  resource_id INTEGER NOT NULL,
  resource_type_id INTEGER DEFAULT NULL,
  subject_type varchar(50) NOT NULL,
  subject_id INTEGER NOT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  department_id INTEGER DEFAULT NULL,
  district_id INTEGER DEFAULT NULL,
  can_view BOOLEAN DEFAULT FALSE,
  can_edit BOOLEAN DEFAULT FALSE,
  can_create BOOLEAN DEFAULT FALSE,
  can_delete BOOLEAN DEFAULT FALSE,
  can_delegate BOOLEAN DEFAULT FALSE,
  effect varchar(10) DEFAULT 'allow',
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id),
  KEY permissions_resource_idx (resource_id),
  KEY permissions_resource_type_idx (resource_type_id),
  KEY permissions_tenant_idx (tenant_id),
  KEY permissions_branch_idx (branch_id),
  KEY permissions_department_idx (department_id),
  KEY permissions_district_idx (district_id),
  CONSTRAINT permissions_resource_fk FOREIGN KEY (resource_id) REFERENCES resources(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: admin_scopes
CREATE TABLE admin_scopes (
  id SERIAL,
  user_id INTEGER NOT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  department_id INTEGER DEFAULT NULL,
  district_id INTEGER DEFAULT NULL,
  can_delegate BOOLEAN DEFAULT FALSE,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id),
  KEY admin_scopes_user_idx (user_id),
  CONSTRAINT admin_scopes_user_fk FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: workplace_registrations
CREATE TABLE workplace_registrations (
  id SERIAL,
  company_name VARCHAR(255) NOT NULL,
  address TEXT,
  tin VARCHAR(50),
  contact_person VARCHAR(255),
  contact_email VARCHAR(255),
  contact_phone VARCHAR(50),
  invoice_id INTEGER,
  status VARCHAR(50) DEFAULT 'pending',
  branch_id INTEGER DEFAULT NULL,
  assigned_reviewer_id INTEGER,
  tenant_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id),
  KEY workplace_registrations_status_idx (status),
  KEY workplace_registrations_reviewer_idx (assigned_reviewer_id),
  KEY workplace_registrations_email_idx (contact_email),
  KEY workplace_registrations_invoice_id_idx (invoice_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- Table: districts
CREATE TABLE districts (
  id SERIAL,
  name varchar(100) NOT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
) ;


-- Table: safety_alerts
CREATE TABLE `safety_alerts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date DEFAULT NULL,
  `time` time DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `classification` varchar(100) NOT NULL DEFAULT 'general',
  `description` text DEFAULT NULL,
  `status` varchar(50) DEFAULT 'open',
  `investigator_id` int(11) DEFAULT NULL,
  `submitted_by` int(11) DEFAULT NULL,
  `form_data` jsonb DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: safety_alert_files
CREATE TABLE `safety_alert_files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `alert_id` int(11) NOT NULL,
  `file_path` varchar(255) DEFAULT NULL,
  `uploaded_by` int(11) DEFAULT NULL,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: safety_alert_logs
CREATE TABLE `safety_alert_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `alert_id` int(11) NOT NULL,
  `action` varchar(50) NOT NULL,
  `details` text DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `logged_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `tenant_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `isDeleted` tinyint(1) NOT NULL DEFAULT 0,
  `updatedAt` timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (`id`),
  KEY `safety_alert_logs_alert_idx` (`alert_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: inspection_requests
CREATE TABLE inspection_requests (
  id SERIAL,
  workplace_id INTEGER,
  description TEXT,
  preferred_date DATE,
  status VARCHAR DEFAULT 'Pending',
  requested_by INTEGER,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);
CREATE INDEX inspection_requests_workplace_idx ON inspection_requests (workplace_id);

-- Table: document_tags
CREATE TABLE document_tags (
  id SERIAL,
  document_id INTEGER NOT NULL,
  tag VARCHAR(255) NOT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);
CREATE INDEX document_tags_document_idx ON document_tags (document_id);
CREATE INDEX document_tags_tenant_idx ON document_tags (tenant_id);
CREATE INDEX document_tags_branch_idx ON document_tags (branch_id);

-- Table: workcom_inspection_types
CREATE TABLE workcom_inspection_types (
  id SERIAL,
  name varchar(100) NOT NULL,
  form_id INTEGER DEFAULT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table: workcom_inspections
CREATE TABLE workcom_inspections (
  id SERIAL,
  date date NOT NULL,
  location varchar(255) DEFAULT NULL,
  short_description varchar(255) DEFAULT NULL,
  inspector_id INTEGER DEFAULT NULL,
  inspection_type_id INTEGER DEFAULT NULL,
  workplace_id INTEGER DEFAULT NULL,
  inspection_request_id INTEGER DEFAULT NULL,
  form_data jsonb DEFAULT NULL,
  tenant_id INTEGER DEFAULT NULL,
  branch_id INTEGER DEFAULT NULL,
  isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
  updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE INDEX workcom_inspections_workplace_idx ON workcom_inspections (workplace_id);
CREATE INDEX workcom_inspections_request_idx ON workcom_inspections (inspection_request_id);

-- Default OHS checklist forms
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id) VALUES
  (100, 'Workplace Safety Inspection Checklist', 'Checklist for routine workplace safety inspections', 'ohs_inspections',
   '[{"label":"workplace_id","field_name":"workplace_id","data_column":"workplace_id","type_id":4,"options_table":"workplace_registrations","options_label_column":"company_name","options_value_column":"id","order_index":1},
     {"label":"inspection_id","field_name":"inspection_id","data_column":"inspection_id","type_id":16,"order_index":2},
     {"label":"Emergency Exits Clear","field_name":"emergency_exits","json_column":"form_data","type_id":5,"order_index":3},
     {"label":"Fire Extinguishers Serviced","field_name":"fire_extinguishers","json_column":"form_data","type_id":5,"order_index":4},
     {"label":"Walkways Clear","field_name":"walkways_clear","json_column":"form_data","type_id":5,"order_index":5}]',
   NULL, NULL),
  (101, 'Occupational Health Services Audit Form', 'Audit of occupational health services and programs', 'ohs_inspections',
   '[{"label":"workplace_id","field_name":"workplace_id","data_column":"workplace_id","type_id":4,"options_table":"workplace_registrations","options_label_column":"company_name","options_value_column":"id","order_index":1},
     {"label":"inspection_id","field_name":"inspection_id","data_column":"inspection_id","type_id":16,"order_index":2},
     {"label":"Wellness Programs Available","field_name":"wellness_programs","json_column":"form_data","type_id":5,"order_index":3},
     {"label":"First Aid Kits Stocked","field_name":"first_aid_kits","json_column":"form_data","type_id":5,"order_index":4},
     {"label":"PPE Provided","field_name":"ppe_provided","json_column":"form_data","type_id":5,"order_index":5},
     {"label":"Training Records Up To Date","field_name":"training_records","json_column":"form_data","type_id":5,"order_index":6}]',
   NULL, NULL),
  (102, 'Compliance Certificate Verification Checklist', 'Verification of workplace compliance certificates', 'ohs_inspections',
   '[{"label":"workplace_id","field_name":"workplace_id","data_column":"workplace_id","type_id":4,"options_table":"workplace_registrations","options_label_column":"company_name","options_value_column":"id","order_index":1},
     {"label":"inspection_id","field_name":"inspection_id","data_column":"inspection_id","type_id":16,"order_index":2},
     {"label":"Certificate Valid","field_name":"certificate_valid","json_column":"form_data","type_id":5,"order_index":3},
     {"label":"Permit Displayed","field_name":"permit_displayed","json_column":"form_data","type_id":5,"order_index":4},
     {"label":"Insurance Current","field_name":"insurance_current","json_column":"form_data","type_id":5,"order_index":5}]',
   NULL, NULL);
