-- XAMPP-Lite
-- version 8.5.5
-- https://xampplite.sf.net/
--
-- phpMyAdmin SQL Dump
-- version 5.2.3
-- https://www.phpmyadmin.net/
--
-- Host: configured through DB_HOST in backend/.env
-- Generation Time: Jul 15, 2026 at 09:08 AM
-- Server version: 11.4.10-MariaDB-log
-- PHP Version: 8.5.5

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS;
SET FOREIGN_KEY_CHECKS=0;


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `notion_ops`
--

-- --------------------------------------------------------

--
-- Table structure for table `account_details`
--

CREATE TABLE `account_details` (
  `id` int(10) UNSIGNED NOT NULL,
  `employee_id` int(10) UNSIGNED DEFAULT NULL,
  `pos_id` int(10) UNSIGNED DEFAULT NULL,
  `account_holder_name` varchar(255) NOT NULL,
  `bank_name` varchar(255) NOT NULL,
  `account_number` varchar(50) NOT NULL,
  `ifsc_code` varchar(20) NOT NULL,
  `branch_name` varchar(255) DEFAULT NULL,
  `account_type` varchar(50) DEFAULT NULL,
  `updated_by` int(11) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- --------------------------------------------------------

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

CREATE TABLE `departments` (
  `id` int(10) UNSIGNED NOT NULL,
  `department_name` varchar(100) NOT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `departments` (`id`, `department_name`, `created_by`, `created_at`, `updated_at`) VALUES
(1, 'Sales', 1, '2026-07-10 05:37:34', '2026-07-10 05:37:34'),
(2, 'POS Management', 1, '2026-07-10 05:37:34', '2026-07-10 05:37:34'),
(3, 'Operations', 1, '2026-07-10 05:37:34', '2026-07-10 05:37:34'),
(4, 'Underwriting', 1, '2026-07-10 05:37:34', '2026-07-10 05:37:34'),
(5, 'Claims', 1, '2026-07-10 05:37:34', '2026-07-10 05:37:34'),
(6, 'Customer Support', 1, '2026-07-10 05:37:34', '2026-07-10 05:37:34'),
(7, 'Renewal', 1, '2026-07-10 05:37:34', '2026-07-10 05:37:34'),
(8, 'Finance', 1, '2026-07-10 05:37:34', '2026-07-10 05:37:34'),
(9, 'Accounts', 1, '2026-07-10 05:37:34', '2026-07-10 05:37:34'),
(10, 'Human Resources (HR)', 1, '2026-07-10 05:37:34', '2026-07-10 05:37:34'),
(11, 'Administration', 1, '2026-07-10 05:37:34', '2026-07-10 05:37:34'),
(12, 'Information Technology (IT)', 1, '2026-07-10 05:37:34', '2026-07-10 05:37:34'),
(13, 'Marketing', 1, '2026-07-10 05:37:34', '2026-07-10 05:37:34'),
(14, 'Compliance', 1, '2026-07-10 05:37:34', '2026-07-10 05:37:34'),
(15, 'Legal', 1, '2026-07-10 05:37:34', '2026-07-10 05:37:34'),
(16, 'Training', 1, '2026-07-10 05:37:34', '2026-07-10 05:37:34'),
(17, 'Business Development', 1, '2026-07-10 05:37:34', '2026-07-10 05:37:34'),
(18, 'Audit', 1, '2026-07-10 05:37:34', '2026-07-10 05:37:34'),
(19, 'Risk Management', 1, '2026-07-10 05:37:34', '2026-07-10 05:37:34'),
(20, 'CRM (Customer Relationship Management)', 1, '2026-07-10 05:37:34', '2026-07-10 05:37:34');

-- --------------------------------------------------------

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

CREATE TABLE `designations` (
  `id` int(10) UNSIGNED NOT NULL,
  `department` int(10) UNSIGNED NOT NULL,
  `designation_name` varchar(100) NOT NULL,
  `parent_designation_id` int(10) UNSIGNED DEFAULT NULL,
  `hierarchy_level` int(10) UNSIGNED NOT NULL DEFAULT 1,
  `status` enum('Active','Inactive') NOT NULL DEFAULT 'Active',
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `designations` (`id`, `department`, `designation_name`, `created_by`, `created_at`, `updated_at`) VALUES
(1, 1, 'Sales Executive', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(2, 1, 'Sales Manager', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(3, 1, 'Regional Sales Manager', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(4, 2, 'POS Executive', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(5, 2, 'POS Manager', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(6, 3, 'Operations Executive', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(7, 3, 'Operations Manager', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(8, 4, 'Underwriter', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(9, 4, 'Senior Underwriter', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(10, 5, 'Claims Executive', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(11, 5, 'Claims Manager', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(12, 6, 'Customer Support Executive', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(13, 6, 'Support Team Leader', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(14, 7, 'Renewal Executive', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(15, 8, 'Finance Executive', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(16, 8, 'Finance Manager', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(17, 9, 'Accountant', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(18, 9, 'Senior Accountant', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(19, 10, 'HR Executive', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(20, 10, 'HR Manager', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(21, 11, 'Admin Executive', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(22, 11, 'Admin Manager', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(23, 12, 'Software Developer', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(24, 12, 'System Administrator', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(25, 12, 'Network Engineer', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(26, 13, 'Marketing Executive', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(27, 13, 'Marketing Manager', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(28, 14, 'Compliance Officer', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(29, 15, 'Legal Executive', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(30, 15, 'Legal Manager', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(31, 16, 'Training Executive', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(32, 17, 'Business Development Executive', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(33, 17, 'Business Development Manager', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(34, 18, 'Internal Auditor', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(35, 19, 'Risk Analyst', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(36, 19, 'Risk Manager', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(37, 20, 'CRM Executive', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51'),
(38, 20, 'CRM Manager', 1, '2026-07-10 05:40:51', '2026-07-10 05:40:51');

-- --------------------------------------------------------

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

CREATE TABLE `employees` (
  `id` int(10) UNSIGNED NOT NULL,
  `employee_code` varchar(50) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `gender` enum('Male','Female','Other') DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `personal_email` varchar(255) DEFAULT NULL,
  `aadhaar_number` varchar(20) DEFAULT NULL,
  `pan_number` varchar(20) DEFAULT NULL,
  `mobile` varchar(20) DEFAULT NULL,
  `emergency_contact` varchar(20) DEFAULT NULL,
  `current_address` text DEFAULT NULL,
  `state` varchar(100) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `pin_code` varchar(10) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `user_login` varchar(50) DEFAULT NULL,
  `user_type` varchar(50) DEFAULT NULL,
  `level` int(11) DEFAULT NULL,
  `status` varchar(20) DEFAULT 'Active',
  `is_bqp` enum('Yes','No') NOT NULL DEFAULT 'No',
  `document_status` varchar(50) DEFAULT NULL,
  `designation` int(11) UNSIGNED DEFAULT NULL,
  `department` int(11) UNSIGNED DEFAULT NULL,
  `bqp` int(11) UNSIGNED DEFAULT 3,
  `reporting_manager` int(11) UNSIGNED DEFAULT 3,
  `relationship_manager` int(11) UNSIGNED DEFAULT NULL,
  `reporting_branch` int(11) UNSIGNED DEFAULT NULL,
  `joining_date` date DEFAULT NULL,
  `relieving_date` date DEFAULT NULL,
  `father_name` varchar(255) DEFAULT NULL,
  `father_occupation` varchar(255) DEFAULT NULL,
  `mother_name` varchar(255) DEFAULT NULL,
  `marital_status` varchar(20) DEFAULT NULL,
  `category` varchar(50) DEFAULT NULL,
  `qualification` varchar(255) DEFAULT NULL,
  `year_of_passing` smallint(6) DEFAULT NULL,
  `created_by` int(11) UNSIGNED NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `employee_pos`
--

CREATE TABLE `employee_pos` (
  `id` int(10) UNSIGNED NOT NULL,
  `pos_code` varchar(50) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `aadhaar_number` varchar(20) DEFAULT NULL,
  `pan_number` varchar(20) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `mobile` varchar(20) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `status` enum('Active','Inactive') DEFAULT 'Active',
  `verification` enum('Verify','Unverify') DEFAULT 'Unverify',
  `pan_verification` enum('Verify','Unverify') DEFAULT 'Unverify',
  `document_status` enum('verify','Unverify') DEFAULT 'Unverify',
  `veri` enum('Yes','No') DEFAULT 'No',
  `bqp` int(11) UNSIGNED DEFAULT 3,
  `reporting_manager` int(11) UNSIGNED NOT NULL DEFAULT 3,
  `relationship_manager` int(11) UNSIGNED NOT NULL DEFAULT 3,
  `reporting_branch` int(11) UNSIGNED NOT NULL DEFAULT 1,
  `gender` enum('Male','Female','Other') DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `qualification` varchar(255) DEFAULT NULL,
  `year_of_passing` int(11) DEFAULT NULL,
  `board_name` varchar(255) DEFAULT NULL,
  `father_name` varchar(255) DEFAULT NULL,
  `current_address` text DEFAULT NULL,
  `state` varchar(100) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `pin_code` varchar(10) DEFAULT NULL,
  `joining_date` date DEFAULT NULL,
  `valid_upto` date DEFAULT NULL,
  `relieving_date` date DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `refer_by` int(11) UNSIGNED NOT NULL,
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `employee_references`
--

CREATE TABLE `employee_references` (
  `id` int(10) UNSIGNED NOT NULL,
  `bqp_id` int(11) UNSIGNED NOT NULL,
  `reporting_id` int(11) UNSIGNED NOT NULL,
  `relationship_id` int(11) UNSIGNED NOT NULL,
  `pos_id` int(10) UNSIGNED NOT NULL,
  `ref_name` varchar(255) NOT NULL,
  `ref_mobile` varchar(20) DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `insurance_branch`
--

CREATE TABLE `insurance_branch` (
  `id` int(10) UNSIGNED NOT NULL,
  `gst_no` varchar(30) DEFAULT NULL,
  `insurer` varchar(100) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `state` varchar(100) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `pin_code` varchar(20) DEFAULT NULL,
  `contact` varchar(50) DEFAULT NULL,
  `support_email` varchar(100) DEFAULT NULL,
  `brockercode` varchar(100) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `designation` varchar(100) DEFAULT NULL,
  `department` varchar(100) DEFAULT NULL,
  `mobile` varchar(20) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `status` enum('Active','Inactive') NOT NULL DEFAULT 'Active',
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `insurance_branch`
--

-- --------------------------------------------------------

--
-- Table structure for table `insurance_company`
--

CREATE TABLE `insurance_company` (
  `id` int(10) UNSIGNED NOT NULL,
  `insurer` varchar(255) DEFAULT NULL,
  `link` text DEFAULT NULL,
  `type` varchar(100) DEFAULT NULL,
  `status` enum('Active','Inactive') NOT NULL DEFAULT 'Active',
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `insurance_company`
--

-- --------------------------------------------------------

--
-- Table structure for table `our_branch`
--

CREATE TABLE `our_branch` (
  `id` int(10) UNSIGNED NOT NULL,
  `branch_name` varchar(100) DEFAULT NULL,
  `gst_no` varchar(30) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `state` varchar(100) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `pin_code` varchar(20) DEFAULT NULL,
  `contact_number` varchar(50) DEFAULT NULL,
  `support_email` varchar(100) DEFAULT NULL,
  `broker_code` varchar(100) DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `our_branch`
--

-- --------------------------------------------------------

--
-- Table structure for table `policies`
--

CREATE TABLE `policies` (
  `id` int(11) NOT NULL,
  `bqp` int(11) UNSIGNED DEFAULT NULL,
  `reporting_manager` int(11) UNSIGNED DEFAULT NULL,
  `relationship_manager` int(11) UNSIGNED DEFAULT NULL,
  `pos_id` int(50) UNSIGNED DEFAULT NULL,
  `ref_id` int(50) UNSIGNED DEFAULT NULL,
  `business_type` varchar(50) DEFAULT NULL,
  `insurance_company` varchar(150) DEFAULT NULL,
  `policy_number` varchar(100) NOT NULL,
  `policy_type` varchar(100) DEFAULT NULL,
  `vehicle_category` varchar(100) DEFAULT NULL,
  `insurer_branch` varchar(255) DEFAULT NULL,
  `insured_name` varchar(150) DEFAULT NULL,
  `pan` varchar(20) DEFAULT NULL,
  `gstin` varchar(30) DEFAULT NULL,
  `contact` varchar(20) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `od_expiry` date DEFAULT NULL,
  `tp_expiry` date DEFAULT NULL,
  `issue_date` date DEFAULT NULL,
  `idv` decimal(15,2) DEFAULT NULL,
  `previous_insurer` varchar(150) DEFAULT NULL,
  `previous_policy` varchar(100) DEFAULT NULL,
  `first_year_od` decimal(15,2) DEFAULT NULL,
  `first_year_tp` decimal(15,2) DEFAULT NULL,
  `total_od` decimal(15,2) DEFAULT NULL,
  `total_tp` decimal(15,2) DEFAULT NULL,
  `net_premium` decimal(15,2) DEFAULT NULL,
  `gst` decimal(15,2) DEFAULT NULL,
  `total_payable` decimal(15,2) DEFAULT NULL,
  `registration_number` varchar(50) DEFAULT NULL,
  `rto` varchar(10) DEFAULT NULL,
  `manufacturing_year` int(11) DEFAULT NULL,
  `commercial_vehicle_type` varchar(100) DEFAULT NULL,
  `ncb` varchar(30) DEFAULT NULL,
  `chassis_number` varchar(150) DEFAULT NULL,
  `sub_type` varchar(100) DEFAULT NULL,
  `engine_number` varchar(150) DEFAULT NULL,
  `fuel` varchar(50) DEFAULT NULL,
  `gvw` varchar(50) DEFAULT NULL,
  `make_name` varchar(150) DEFAULT NULL,
  `cc` varchar(50) DEFAULT NULL,
  `model_name` varchar(150) DEFAULT NULL,
  `seating_capacity` varchar(30) DEFAULT NULL,
  `variant_name` varchar(150) DEFAULT NULL,
  `financier` varchar(150) DEFAULT NULL,
  `irda_od` varchar(100) DEFAULT NULL,
  `irda_tp` varchar(100) DEFAULT NULL,
  `irda_net` varchar(100) DEFAULT NULL,
  `pos_od` varchar(100) DEFAULT NULL,
  `pos_tp` varchar(100) DEFAULT NULL,
  `pos_net` varchar(100) DEFAULT NULL,
  `verify_remark` text DEFAULT NULL,
  `account_remark` text DEFAULT NULL,
  `payment_status` varchar(50) DEFAULT NULL,
  `created_by` int(11) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `insurer_statement_rows` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `policy_number` varchar(150) NOT NULL,
  `insurance_company` varchar(255) DEFAULT NULL,
  `insured_name` varchar(150) DEFAULT NULL,
  `issue_date` date DEFAULT NULL,
  `total_od` decimal(15,2) DEFAULT NULL,
  `total_tp` decimal(15,2) DEFAULT NULL,
  `net_premium` decimal(15,2) DEFAULT NULL,
  `irda_od` decimal(10,2) DEFAULT NULL,
  `irda_tp` decimal(10,2) DEFAULT NULL,
  `irda_net` decimal(10,2) DEFAULT NULL,
  `remark` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `insurer_statement_policy_number` (`policy_number`),
  KEY `insurer_statement_issue_date` (`issue_date`),
  KEY `insurer_statement_created_by` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `payout_grid_rows` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `company` varchar(255) NOT NULL,
  `payout_month` char(7) NOT NULL,
  `business_type` varchar(100) DEFAULT NULL,
  `category` varchar(100) DEFAULT NULL,
  `classification` varchar(150) DEFAULT NULL,
  `product_type` varchar(150) DEFAULT NULL,
  `rto` varchar(255) DEFAULT NULL,
  `od_comm` decimal(10,4) DEFAULT NULL,
  `tp_comm` decimal(10,4) DEFAULT NULL,
  `net_comm` decimal(10,4) DEFAULT NULL,
  `cc` varchar(255) DEFAULT NULL,
  `fuel_type` varchar(255) DEFAULT NULL,
  `make` text DEFAULT NULL,
  `decline_make` text DEFAULT NULL,
  `model` text DEFAULT NULL,
  `decline_model` text DEFAULT NULL,
  `ncb` varchar(255) DEFAULT NULL,
  `seat` varchar(255) DEFAULT NULL,
  `gvw` varchar(255) DEFAULT NULL,
  `source_file_name` varchar(255) DEFAULT NULL,
  `source_row_number` int(10) UNSIGNED DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `payout_grid_company_month` (`company`,`payout_month`),
  KEY `payout_grid_category_classification` (`category`,`classification`),
  KEY `payout_grid_created_by` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `department_work_items` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `department_slug` varchar(80) NOT NULL,
  `policy_id` int(11) DEFAULT NULL,
  `policy_number` varchar(150) DEFAULT NULL,
  `customer_name` varchar(255) DEFAULT NULL,
  `work_type` varchar(100) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `priority` enum('Low','Normal','High','Critical') NOT NULL DEFAULT 'Normal',
  `status` enum('Open','In Progress','Pending','Approved','Completed','Rejected') NOT NULL DEFAULT 'Open',
  `assigned_to` int(10) UNSIGNED DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`payload`)),
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `department_work_items_department` (`department_slug`,`status`),
  KEY `department_work_items_policy` (`policy_id`),
  KEY `department_work_items_assignee` (`assigned_to`),
  KEY `department_work_items_due_date` (`due_date`),
  KEY `department_work_items_created_by` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `department_work_item_history` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `work_item_id` bigint(20) UNSIGNED NOT NULL,
  `from_status` varchar(30) DEFAULT NULL,
  `to_status` varchar(30) NOT NULL,
  `note` varchar(1000) DEFAULT NULL,
  `changed_by` int(10) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `department_work_history_item` (`work_item_id`,`created_at`),
  KEY `department_work_history_user` (`changed_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS password_reset_tokens (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id INT UNSIGNED NOT NULL,
    token_hash CHAR(64) NOT NULL UNIQUE,
    expires_at DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_password_reset_employee (employee_id),
    INDEX idx_password_reset_expiry (expires_at)
);

--
-- Indexes for table `account_details`
--
ALTER TABLE `account_details`
  ADD PRIMARY KEY (`id`),
  ADD KEY `account_employee_id` (`employee_id`),
  ADD KEY `account_pos_id` (`pos_id`),
  ADD KEY `account_updated_by` (`updated_by`);

--
-- Indexes for table `departments`
--
ALTER TABLE `departments`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `designations`
--
ALTER TABLE `designations`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `employees`
--
ALTER TABLE `employees`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `employee_code` (`employee_code`),
  ADD UNIQUE KEY `personal_email` (`personal_email`),
  ADD UNIQUE KEY `aadhaar_number` (`aadhaar_number`),
  ADD UNIQUE KEY `pan_number` (`pan_number`),
  ADD UNIQUE KEY `mobile` (`mobile`),
  ADD KEY `employee_bqp` (`bqp`),
  ADD KEY `reporting_manager` (`reporting_manager`),
  ADD KEY `relationship_manager` (`relationship_manager`),
  ADD KEY `reporting_branch` (`reporting_branch`),
  ADD KEY `departments` (`department`),
  ADD KEY `designation` (`designation`),
  ADD KEY `employee_created_by` (`created_by`);

--
-- Indexes for table `employee_pos`
--
ALTER TABLE `employee_pos`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `employee_code` (`pos_code`),
  ADD UNIQUE KEY `email` (`email`),
  ADD UNIQUE KEY `mobile` (`mobile`),
  ADD UNIQUE KEY `aadhaar_number` (`aadhaar_number`),
  ADD UNIQUE KEY `pan_number` (`pan_number`),
  ADD KEY `bqp` (`bqp`),
  ADD KEY `pos_reporting_manager` (`reporting_manager`),
  ADD KEY `pos_relationship_manager` (`relationship_manager`),
  ADD KEY `pos_reporting_branch` (`reporting_branch`),
  ADD KEY `pos_code` (`pos_code`);

--
-- Indexes for table `employee_references`
--
ALTER TABLE `employee_references`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `ref_mobile` (`ref_mobile`),
  ADD KEY `ref_created_by` (`created_by`),
  ADD KEY `ref_bqp` (`bqp_id`),
  ADD KEY `ref_reporting` (`reporting_id`),
  ADD KEY `ref_pos` (`pos_id`),
  ADD KEY `ref_relationship` (`relationship_id`);

--
-- Indexes for table `insurance_branch`
--
ALTER TABLE `insurance_branch`
  ADD PRIMARY KEY (`id`),
  ADD KEY `insurance_branch_created_by` (`created_by`);

--
-- Indexes for table `insurance_company`
--
ALTER TABLE `insurance_company`
  ADD PRIMARY KEY (`id`),
  ADD KEY `insurance_company_created_by` (`created_by`);

--
-- Indexes for table `our_branch`
--
ALTER TABLE `our_branch`
  ADD PRIMARY KEY (`id`),
  ADD KEY `our_branch_created_by` (`created_by`);

--
-- Indexes for table `policies`
--
ALTER TABLE `policies`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `policy_number` (`policy_number`),
  ADD KEY `policy_bqp` (`bqp`),
  ADD KEY `policy_reporting_manager` (`reporting_manager`),
  ADD KEY `policy_relationship_manager` (`relationship_manager`),
  ADD KEY `policy_pos_id` (`pos_id`),
  ADD KEY `policy_ref_id` (`ref_id`),
  ADD KEY `policy_created_by` (`created_by`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `account_details`
--
ALTER TABLE `account_details`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=100;

--
-- AUTO_INCREMENT for table `departments`
--
ALTER TABLE `departments`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;

--
-- AUTO_INCREMENT for table `designations`
--
ALTER TABLE `designations`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=39;

--
-- AUTO_INCREMENT for table `employees`
--
ALTER TABLE `employees`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=38;

--
-- AUTO_INCREMENT for table `employee_pos`
--
ALTER TABLE `employee_pos`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=85;

--
-- AUTO_INCREMENT for table `employee_references`
--
ALTER TABLE `employee_references`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `insurance_branch`
--
ALTER TABLE `insurance_branch`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=96;

--
-- AUTO_INCREMENT for table `insurance_company`
--
ALTER TABLE `insurance_company`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=60;

--
-- AUTO_INCREMENT for table `our_branch`
--
ALTER TABLE `our_branch`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=23;

--
-- AUTO_INCREMENT for table `policies`
--
ALTER TABLE `policies`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `account_details`
--
ALTER TABLE `account_details`
  ADD CONSTRAINT `fk_account_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_account_pos` FOREIGN KEY (`pos_id`) REFERENCES `employee_pos` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_account_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `employees` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `departments`
--
ALTER TABLE `departments`
  ADD CONSTRAINT `fk_departments_created_by` FOREIGN KEY (`created_by`) REFERENCES `employees` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `designations`
--
ALTER TABLE `designations`
  ADD CONSTRAINT `fk_designation_department` FOREIGN KEY (`department`) REFERENCES `departments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_designations_created_by` FOREIGN KEY (`created_by`) REFERENCES `employees` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `employees`
--
ALTER TABLE `employees`
  ADD CONSTRAINT `departments` FOREIGN KEY (`department`) REFERENCES `departments` (`id`),
  ADD CONSTRAINT `designation` FOREIGN KEY (`designation`) REFERENCES `designations` (`id`),
  ADD CONSTRAINT `employee_bqp` FOREIGN KEY (`bqp`) REFERENCES `employees` (`id`),
  ADD CONSTRAINT `employee_created_by` FOREIGN KEY (`created_by`) REFERENCES `employees` (`id`),
  ADD CONSTRAINT `relationship_manager` FOREIGN KEY (`relationship_manager`) REFERENCES `employees` (`id`),
  ADD CONSTRAINT `reporting_branch` FOREIGN KEY (`reporting_branch`) REFERENCES `our_branch` (`id`),
  ADD CONSTRAINT `reporting_manager` FOREIGN KEY (`reporting_manager`) REFERENCES `employees` (`id`);

-- --------------------------------------------------------
-- HR module tables. Runtime upgrades are also handled by ensureHrSchema().
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `hr_leave_types` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `code` varchar(20) NOT NULL,
  `annual_quota` decimal(6,2) NOT NULL DEFAULT 0,
  `paid` tinyint(1) NOT NULL DEFAULT 1,
  `carry_forward` tinyint(1) NOT NULL DEFAULT 0,
  `status` enum('Active','Inactive') NOT NULL DEFAULT 'Active',
  `created_by` int UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`), UNIQUE KEY `hr_leave_type_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `hr_leave_requests` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `employee_id` int UNSIGNED NOT NULL,
  `leave_type_id` int UNSIGNED NOT NULL,
  `from_date` date NOT NULL, `to_date` date NOT NULL, `days` decimal(6,2) NOT NULL,
  `reason` varchar(1000) NOT NULL,
  `status` enum('Pending','Approved','Rejected','Cancelled') NOT NULL DEFAULT 'Pending',
  `approver_id` int UNSIGNED DEFAULT NULL, `approver_note` varchar(1000) DEFAULT NULL,
  `decided_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`), KEY `hr_leave_employee` (`employee_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `hr_payroll` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `employee_id` int UNSIGNED NOT NULL, `payroll_month` char(7) NOT NULL,
  `basic` decimal(15,2) NOT NULL DEFAULT 0, `hra` decimal(15,2) NOT NULL DEFAULT 0,
  `allowances` decimal(15,2) NOT NULL DEFAULT 0, `bonus` decimal(15,2) NOT NULL DEFAULT 0,
  `deductions` decimal(15,2) NOT NULL DEFAULT 0, `tax` decimal(15,2) NOT NULL DEFAULT 0,
  `net_pay` decimal(15,2) NOT NULL DEFAULT 0,
  `payment_status` enum('Draft','Processed','Paid','On Hold') NOT NULL DEFAULT 'Draft',
  `payment_date` date DEFAULT NULL, `notes` varchar(1000) DEFAULT NULL,
  `created_by` int UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`), UNIQUE KEY `hr_payroll_employee_month` (`employee_id`,`payroll_month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `hr_increments` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT, `employee_id` int UNSIGNED NOT NULL,
  `effective_date` date NOT NULL, `previous_ctc` decimal(15,2) NOT NULL DEFAULT 0,
  `revised_ctc` decimal(15,2) NOT NULL DEFAULT 0, `increment_percent` decimal(8,2) NOT NULL DEFAULT 0,
  `reason` varchar(500) DEFAULT NULL,
  `status` enum('Proposed','Approved','Effective','Rejected') NOT NULL DEFAULT 'Proposed',
  `approved_by` int UNSIGNED DEFAULT NULL, `created_by` int UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`), KEY `hr_increment_employee` (`employee_id`,`effective_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `hr_documents` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT, `employee_id` int UNSIGNED NOT NULL,
  `document_type` enum('Offer Letter','Appointment Letter','Confirmation Letter','Increment Letter','Experience Certificate','Relieving Letter','Termination Letter','Salary Certificate','Employment Certificate','Warning Letter','NOC','Other') NOT NULL,
  `document_number` varchar(80) NOT NULL,
  `issue_date` date NOT NULL, `effective_date` date DEFAULT NULL,
  `subject` varchar(255) NOT NULL, `body` longtext NOT NULL,
  `status` enum('Draft','Issued','Acknowledged','Revoked') NOT NULL DEFAULT 'Draft',
  `issued_by` int UNSIGNED NOT NULL, `issued_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`), UNIQUE KEY `hr_document_number` (`document_number`),
  KEY `hr_document_employee` (`employee_id`,`document_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `hr_employee_events` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT, `employee_id` int UNSIGNED NOT NULL,
  `event_type` enum('Onboarding','Probation','Confirmation','Transfer','Promotion','Resignation','Termination','Relieving','Retirement','Other') NOT NULL, `event_date` date NOT NULL,
  `status` enum('Planned','In Progress','Completed','Cancelled') NOT NULL DEFAULT 'Planned',
  `notes` varchar(1000) DEFAULT NULL, `document_id` bigint UNSIGNED DEFAULT NULL,
  `created_by` int UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`), KEY `hr_event_employee` (`employee_id`,`event_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `designations`
  ADD CONSTRAINT `fk_designation_parent` FOREIGN KEY (`parent_designation_id`) REFERENCES `designations` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `hr_leave_types`
  ADD CONSTRAINT `fk_hr_leave_type_creator` FOREIGN KEY (`created_by`) REFERENCES `employees` (`id`) ON UPDATE CASCADE;

ALTER TABLE `hr_leave_requests`
  ADD CONSTRAINT `fk_hr_leave_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_hr_leave_type` FOREIGN KEY (`leave_type_id`) REFERENCES `hr_leave_types` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_hr_leave_approver` FOREIGN KEY (`approver_id`) REFERENCES `employees` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `hr_payroll`
  ADD CONSTRAINT `fk_hr_payroll_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_hr_payroll_creator` FOREIGN KEY (`created_by`) REFERENCES `employees` (`id`) ON UPDATE CASCADE;

ALTER TABLE `hr_increments`
  ADD CONSTRAINT `fk_hr_increment_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_hr_increment_approver` FOREIGN KEY (`approved_by`) REFERENCES `employees` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_hr_increment_creator` FOREIGN KEY (`created_by`) REFERENCES `employees` (`id`) ON UPDATE CASCADE;

ALTER TABLE `hr_documents`
  ADD CONSTRAINT `fk_hr_document_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_hr_document_issuer` FOREIGN KEY (`issued_by`) REFERENCES `employees` (`id`) ON UPDATE CASCADE;

ALTER TABLE `hr_employee_events`
  ADD CONSTRAINT `fk_hr_event_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_hr_event_document` FOREIGN KEY (`document_id`) REFERENCES `hr_documents` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_hr_event_creator` FOREIGN KEY (`created_by`) REFERENCES `employees` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `employee_pos`
--
ALTER TABLE `employee_pos`
  ADD CONSTRAINT `bqp` FOREIGN KEY (`bqp`) REFERENCES `employees` (`id`),
  ADD CONSTRAINT `employee_pos_refer_by` FOREIGN KEY (`refer_by`) REFERENCES `employees` (`employee_code`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_employee_pos_refer_by` FOREIGN KEY (`refer_by`) REFERENCES `employees` (`employee_code`) ON UPDATE CASCADE,
  ADD CONSTRAINT `pos_relationship_manager` FOREIGN KEY (`relationship_manager`) REFERENCES `employees` (`id`),
  ADD CONSTRAINT `pos_reporting_branch` FOREIGN KEY (`reporting_branch`) REFERENCES `our_branch` (`id`),
  ADD CONSTRAINT `pos_reporting_manager` FOREIGN KEY (`reporting_manager`) REFERENCES `employees` (`id`);

--
-- Constraints for table `employee_references`
--
ALTER TABLE `employee_references`
  ADD CONSTRAINT `ref_bqp` FOREIGN KEY (`bqp_id`) REFERENCES `employees` (`id`),
  ADD CONSTRAINT `ref_created_by` FOREIGN KEY (`created_by`) REFERENCES `employees` (`id`),
  ADD CONSTRAINT `ref_pos` FOREIGN KEY (`pos_id`) REFERENCES `employee_pos` (`id`),
  ADD CONSTRAINT `ref_relationship` FOREIGN KEY (`relationship_id`) REFERENCES `employees` (`id`),
  ADD CONSTRAINT `ref_reporting` FOREIGN KEY (`reporting_id`) REFERENCES `employees` (`id`);

--
-- Constraints for table `insurance_branch`
--
ALTER TABLE `insurance_branch`
  ADD CONSTRAINT `insurance_branch_created_by` FOREIGN KEY (`created_by`) REFERENCES `employees` (`id`);

--
-- Constraints for table `insurance_company`
--
ALTER TABLE `insurance_company`
  ADD CONSTRAINT `insurance_company_created_by` FOREIGN KEY (`created_by`) REFERENCES `employees` (`id`);

--
-- Constraints for table `our_branch`
--
ALTER TABLE `our_branch`
  ADD CONSTRAINT `our_branch_created_by` FOREIGN KEY (`created_by`) REFERENCES `employees` (`id`);

--
-- Constraints for table `policies`
--
ALTER TABLE `policies`
  ADD CONSTRAINT `policy_bqp` FOREIGN KEY (`bqp`) REFERENCES `employees` (`id`),
  ADD CONSTRAINT `policy_created_by` FOREIGN KEY (`created_by`) REFERENCES `employees` (`id`),
  ADD CONSTRAINT `policy_pos_id` FOREIGN KEY (`pos_id`) REFERENCES `employee_pos` (`id`),
  ADD CONSTRAINT `policy_ref_id` FOREIGN KEY (`ref_id`) REFERENCES `employee_references` (`id`),
  ADD CONSTRAINT `policy_relationship_manager` FOREIGN KEY (`relationship_manager`) REFERENCES `employees` (`id`),
  ADD CONSTRAINT `policy_reporting_manager` FOREIGN KEY (`reporting_manager`) REFERENCES `employees` (`id`);
COMMIT;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
