-- https://www.phpmyadmin.net/
--

START TRANSACTION;



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

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

CREATE TABLE `attendance` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `employee_id` int(10) UNSIGNED NOT NULL,
  `attendance_date` date NOT NULL,
  `status` varchar(30) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `attendance_rules`
--

CREATE TABLE `attendance_rules` (
  `id` int(11) NOT NULL,
  `rule_name` varchar(120) NOT NULL,
  `shift_start` time NOT NULL,
  `shift_end` time NOT NULL,
  `grace_minutes` int(11) DEFAULT 15,
  `half_day_after_minutes` int(11) DEFAULT 240,
  `full_day_hours` decimal(5,2) DEFAULT 8.00,
  `late_deduction_mode` enum('None','Pro-rata','Fixed') DEFAULT 'None',
  `late_fixed_amount` decimal(10,2) DEFAULT 0.00,
  `ot_rate` decimal(10,2) DEFAULT 0.00,
  `status` tinyint(4) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `attendance_rules`
--

INSERT INTO `attendance_rules` (`id`, `rule_name`, `shift_start`, `shift_end`, `grace_minutes`, `half_day_after_minutes`, `full_day_hours`, `late_deduction_mode`, `late_fixed_amount`, `ot_rate`, `status`) VALUES
(1, 'Standard Office', '09:30:00', '18:30:00', 15, 240, 8.00, 'Pro-rata', 0.00, 100.00, 1);

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

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

CREATE TABLE `branches` (
  `id` int(10) UNSIGNED NOT NULL,
  `branch_code` varchar(50) NOT NULL,
  `branch_name` varchar(150) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `state` varchar(100) DEFAULT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `email` varchar(190) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `branches` (`id`, `branch_code`, `branch_name`, `address`, `city`, `state`, `phone`, `email`, `status`, `created_at`, `updated_at`) VALUES
(1, 'RVP', 'RAVULAPALEM', '2-171/41, PALLAPU COLONY , VEDIRESWARAM', 'RAVULAPALEM', 'Andhra Pradesh', '+918374278077', 'ramboyidi1@gmail.com', 'active', '2026-09-03 06:06:58', '2026-09-03 06:06:58'),
(2, 'MKP', 'MAL', '2-241, Near Vinayaka Temple', 'East Godavari', 'Andhra Pradesh', '9999999999', 'brach@gmail.com', 'active', '2026-09-03 06:16:03', '2026-09-03 06:21:38'),
(3, '2-RVP', '2RAVULAPALEM', '2-171/41, PALLAPU COLONY , VEDIRESWARAM', 'RAVULAPALEM', 'Andhra Pradesh', '9966332255', 'ramboyidi1@gmail.com', 'active', '2026-09-03 12:07:01', '2026-09-03 12:07:01');

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

--
-- Table structure for table `branch_manpower_plans`
--

CREATE TABLE `branch_manpower_plans` (
  `id` int(10) UNSIGNED NOT NULL,
  `branch_id` int(10) UNSIGNED NOT NULL,
  `designation` varchar(120) NOT NULL,
  `planned_count` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `monthly_budget_per_employee` decimal(12,2) NOT NULL DEFAULT 0.00,
  `notes` varchar(500) DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `branch_manpower_plans`
--

INSERT INTO `branch_manpower_plans` (`id`, `branch_id`, `designation`, `planned_count`, `monthly_budget_per_employee`, `notes`, `created_by`, `created_at`, `updated_at`) VALUES
(1, 1, 'Team Member', 10, 0.00, NULL, 1, '2026-09-04 03:44:08', '2026-09-04 03:44:08'),
(2, 1, 'Store Manager', 1, 0.00, NULL, 1, '2026-09-04 03:44:08', '2026-09-04 03:44:08'),
(3, 1, 'Assistant Store Manager', 1, 0.00, NULL, 1, '2026-09-04 03:44:08', '2026-09-04 03:44:08'),
(4, 1, 'Senior Team Member', 3, 0.00, NULL, 1, '2026-09-04 03:44:08', '2026-09-04 03:44:08'),
(5, 1, 'Supervisor', 2, 0.00, NULL, 1, '2026-09-04 03:44:08', '2026-09-04 03:44:08'),
(6, 1, 'Trainee Assistant Store Manager', 0, 0.00, NULL, 1, '2026-09-04 03:44:08', '2026-09-04 03:44:08'),
(7, 1, 'Trainee Store Manager', 0, 0.00, NULL, 1, '2026-09-04 03:44:08', '2026-09-04 03:44:08'),
(8, 1, 'Trainee Supervisor', 0, 0.00, NULL, 1, '2026-09-04 03:44:08', '2026-09-04 03:44:08'),
(9, 1, 'HOUSE KEEPING TM', 0, 0.00, NULL, 1, '2026-09-04 03:44:08', '2026-09-04 03:44:08'),
(10, 1, 'PART TIME', 0, 0.00, NULL, 1, '2026-09-04 03:44:08', '2026-09-04 03:44:08');

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

--
-- Table structure for table `candidates`
--

CREATE TABLE `candidates` (
  `id` int(11) NOT NULL,
  `code` varchar(30) NOT NULL,
  `full_name` varchar(120) NOT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `email` varchar(120) DEFAULT NULL,
  `position` varchar(120) DEFAULT NULL,
  `source` varchar(80) DEFAULT NULL,
  `status` enum('Applied','Shortlisted','Interview','Selected','Rejected','Joined') DEFAULT 'Applied',
  `applied_on` date DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `candidates`
--

INSERT INTO `candidates` (`id`, `code`, `full_name`, `phone`, `email`, `position`, `source`, `status`, `applied_on`, `notes`, `created_at`) VALUES
(1, 'CAN-1001', 'Rahul Kumar', '9876543210', 'rahul@example.com', 'HR Executive', NULL, 'Selected', '2026-09-03', NULL, '2026-09-03 03:57:34'),
(2, 'CAN-1002', 'Priya Sharma', '9876501234', 'priya@example.com', 'Sales Executive', NULL, 'Interview', '2026-09-03', NULL, '2026-09-03 03:57:34');

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

--
-- Table structure for table `dashboard_events`
--

CREATE TABLE `dashboard_events` (
  `id` int(10) UNSIGNED NOT NULL,
  `title` varchar(180) NOT NULL,
  `description` varchar(500) DEFAULT NULL,
  `event_date` date NOT NULL,
  `branch_id` int(10) UNSIGNED DEFAULT NULL,
  `image_path` varchar(255) DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `dashboard_events`
--

INSERT INTO `dashboard_events` (`id`, `title`, `description`, `event_date`, `branch_id`, `image_path`, `created_by`, `created_at`) VALUES
(2, 'VICTORY BAZARS SUPER MARKET', 'dd', '2026-09-04', 0, 'uploads/dashboard_events/EVENT_20260904125028_7f764ef194d8a8c4_ChatGPT_Image_Sep_4__2026__09_59_56_AM.png', 1, '2026-09-04 10:50:28');

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

--
-- Table structure for table `dashboard_event_branches`
--

CREATE TABLE `dashboard_event_branches` (
  `id` int(10) UNSIGNED NOT NULL,
  `event_id` int(10) UNSIGNED NOT NULL,
  `branch_id` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

CREATE TABLE `departments` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `status` tinyint(4) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `departments` (`id`, `name`, `status`) VALUES
(1, 'Human Resources', 1),
(2, 'Sales', 1),
(3, 'IT', 1),
(4, 'Finance', 1);

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

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

CREATE TABLE `designations` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `status` tinyint(4) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `designations` (`id`, `name`, `status`) VALUES
(1, 'HR Executive', 1),
(2, 'Sales Executive', 1),
(3, 'Software Engineer', 1),
(4, 'Accounts Executive', 1);

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

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

CREATE TABLE `employees` (
  `id` int(11) NOT NULL,
  `branch_id` int(10) UNSIGNED DEFAULT NULL,
  `employee_code` varchar(30) NOT NULL,
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `gender` varchar(30) DEFAULT NULL,
  `candidate_id` int(11) DEFAULT NULL,
  `full_name` varchar(120) NOT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `address` varchar(500) DEFAULT NULL,
  `aadhaar_number` varchar(20) DEFAULT NULL,
  `pan_number` varchar(20) DEFAULT NULL,
  `designation` varchar(120) DEFAULT NULL,
  `email` varchar(120) DEFAULT NULL,
  `department_id` int(11) DEFAULT NULL,
  `designation_id` int(11) DEFAULT NULL,
  `joining_date` date NOT NULL,
  `employment_type` varchar(40) DEFAULT 'Regular',
  `pf_type` enum('PF','Non-PF') DEFAULT 'PF',
  `status` enum('Active','Inactive','Suspended','Separated') DEFAULT 'Active',
  `bank_name` varchar(120) DEFAULT NULL,
  `account_no` varchar(60) DEFAULT NULL,
  `ifsc` varchar(30) DEFAULT NULL,
  `pan` varchar(30) DEFAULT NULL,
  `aadhaar` varchar(30) DEFAULT NULL,
  `device_ref` varchar(80) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `salary_type` varchar(30) NOT NULL DEFAULT 'Monthly',
  `payroll_plan` varchar(50) NOT NULL DEFAULT 'Standard Payroll',
  `pf_option` enum('PF','Non-PF') NOT NULL DEFAULT 'PF',
  `basic_salary` decimal(12,2) NOT NULL DEFAULT 0.00,
  `hra` decimal(12,2) NOT NULL DEFAULT 0.00,
  `hcs` decimal(12,2) NOT NULL DEFAULT 0.00,
  `pf_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `esi_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `gross_salary` decimal(12,2) NOT NULL DEFAULT 0.00,
  `net_salary` decimal(12,2) NOT NULL DEFAULT 0.00,
  `payroll_notes` varchar(1000) DEFAULT NULL,
  `joining_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `increment_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `current_salary` decimal(12,2) NOT NULL DEFAULT 0.00,
  `pf_percentage` decimal(5,2) NOT NULL DEFAULT 0.00,
  `esi_percentage` decimal(5,2) NOT NULL DEFAULT 0.00,
  `pf_percent` decimal(5,2) NOT NULL DEFAULT 12.00,
  `esi_percent` decimal(5,2) NOT NULL DEFAULT 0.75,
  `company_document` varchar(255) DEFAULT NULL,
  `certificate_document` varchar(255) DEFAULT NULL,
  `aadhaar_document` varchar(255) DEFAULT NULL,
  `pan_document` varchar(255) DEFAULT NULL,
  `bank_book_document` varchar(255) DEFAULT NULL,
  `passport_photo` varchar(255) DEFAULT NULL,
  `education_qualification` varchar(180) DEFAULT NULL,
  `education_institution` varchar(180) DEFAULT NULL,
  `education_year` varchar(10) DEFAULT NULL,
  `next_promotion` varchar(120) DEFAULT NULL,
  `next_promotion_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `employees` (`id`, `branch_id`, `employee_code`, `first_name`, `last_name`, `date_of_birth`, `gender`, `candidate_id`, `full_name`, `phone`, `address`, `aadhaar_number`, `pan_number`, `designation`, `email`, `department_id`, `designation_id`, `joining_date`, `employment_type`, `pf_type`, `status`, `bank_name`, `account_no`, `ifsc`, `pan`, `aadhaar`, `device_ref`, `created_at`, `salary_type`, `payroll_plan`, `pf_option`, `basic_salary`, `hra`, `hcs`, `pf_amount`, `esi_amount`, `gross_salary`, `net_salary`, `payroll_notes`, `joining_amount`, `increment_amount`, `current_salary`, `pf_percentage`, `esi_percentage`, `pf_percent`, `esi_percent`, `company_document`, `certificate_document`, `aadhaar_document`, `pan_document`, `bank_book_document`, `passport_photo`, `education_qualification`, `education_institution`, `education_year`, `next_promotion`, `next_promotion_date`) VALUES
(8, 1, 'EMP1001', 'BOYIDI HEMANJALI', NULL, '1996-12-27', 'Female', NULL, '', '08374278077', '2-241, Near Vinayaka Temple', '324894240758', 'DBZPR2720F', 'Team Member', 'ramboyidi1@gmail.com', NULL, NULL, '2026-09-03', 'Regular', 'PF', 'Active', NULL, NULL, NULL, NULL, NULL, NULL, '2026-09-03 09:42:29', 'Monthly', 'Special Payroll', 'Non-PF', 50000.00, 6000.00, 0.00, 0.00, 420.00, 56000.00, 55580.00, '', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.75, 'uploads/employee_docs/REQ1_company_document_d6fd54e19690ee67_ChatGPT_Image_Aug_31__2026__04_24_11_PM.png', 'uploads/employee_docs/REQ1_certificate_document_a0b9031398929b00_WhatsApp_Image_2026-08-31_at_4_22_25_PM.jpeg', 'uploads/employee_docs/REQ1_aadhaar_document_8809dd5bb9ac2387_WhatsApp_Image_2026-08-31_at_4_22_16_PM.jpeg', 'uploads/employee_docs/REQ1_pan_document_90eadcc060fd95a6_ChatGPT_Image_Aug_31__2026__04_17_36_PM.png', 'uploads/employee_docs/REQ1_bank_book_document_6db7b3dd7124afd5_TIRUVU.png', NULL, NULL, NULL, NULL, NULL, NULL),
(9, 2, 'EMP1002', 'NANU', NULL, '1991-01-01', 'Male', NULL, '', '1234567890', '1', '123456789998', 'BQSPV2122B', 'Store Manager', 'naanaai@gmail.com', NULL, NULL, '2026-09-05', 'Regular', 'PF', 'Active', NULL, NULL, NULL, NULL, NULL, NULL, '2026-09-03 10:04:44', 'Monthly', 'Standard Payroll', 'Non-PF', 10000.00, 0.00, 0.00, 0.00, 0.00, 10000.00, 10000.00, NULL, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 'uploads/employee_docs/REQ2_company_document_9fb7e621e97b2f89_ChatGPT_Image_Aug_31__2026__04_24_11_PM.png', 'uploads/employee_docs/REQ2_certificate_document_1f8f3432ded3a73e_WhatsApp_Image_2026-08-31_at_4_22_25_PM.jpeg', 'uploads/employee_docs/REQ2_aadhaar_document_94fec4c4184680bb_WhatsApp_Image_2026-08-31_at_4_22_16_PM.jpeg', 'uploads/employee_docs/REQ2_pan_document_17b0ac6e6a46f9f5_ChatGPT_Image_Aug_31__2026__04_17_36_PM.png', 'uploads/employee_docs/REQ2_bank_book_document_820dd2faed6a4069_TIRUVU.png', NULL, NULL, NULL, NULL, NULL, NULL),
(10, 1, 'EMP1003', 'ram', NULL, '1991-01-01', 'Male', NULL, '', '9874563210', '1', '332211445577', 'DBZPE2720F', 'Store Manager', 'prasadboyidi123@gmail.com', NULL, NULL, '2026-09-03', 'Regular', 'PF', 'Active', NULL, NULL, NULL, NULL, NULL, NULL, '2026-09-03 10:52:20', 'Monthly', 'Standard Payroll', 'PF', 5000.00, 4000.00, 100.00, 600.00, 75.75, 10100.00, 9424.25, NULL, 0.00, 0.00, 0.00, 0.00, 0.00, 12.00, 0.75, 'uploads/employee_docs/REQ10_company_document_1ada921ee5b0eafd_ChatGPT_Image_Aug_31__2026__04_24_11_PM.png', 'uploads/employee_docs/REQ10_certificate_document_1a1254049169c91a_WhatsApp_Image_2026-08-31_at_4_22_25_PM.jpeg', 'uploads/employee_docs/REQ10_aadhaar_document_184cf619bcb47810_ChatGPT_Image_Aug_31__2026__04_17_36_PM.png', 'uploads/employee_docs/REQ10_pan_document_4f60ee4569eeaa8a_ChatGPT_Image_Aug_31__2026__04_17_36_PM.png', 'uploads/employee_docs/REQ10_bank_book_document_7465f5b7cb6daaa9_TIRUVU.png', 'uploads/employee_docs/REQ10_passport_photo_791f6a8de20c1a3b_ChatGPT_Image_Aug_31__2026__04_24_11_PM.png', NULL, NULL, NULL, NULL, NULL),
(11, 1, 'EMP1004', 'ram 654', NULL, '2002-01-01', 'Male', NULL, '', '08374278077', '2-171/41, PALLAPU COLONY , VEDIRESWARAM', '154784777474', 'DBZPR2720A', 'Team Member', 'ramboyidi1@gmail.com', NULL, NULL, '2026-09-04', 'Regular', 'PF', 'Active', NULL, NULL, NULL, NULL, NULL, NULL, '2026-09-04 10:56:15', 'Monthly', 'Standard Payroll', 'Non-PF', 10000.00, 0.00, 0.00, 0.00, 0.00, 10000.00, 10000.00, NULL, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 'uploads/employee_docs/REQ3_company_document_f459568fbfb6eff4_ChatGPT_Image_Sep_4__2026__03_01_26_PM.png', 'uploads/employee_docs/REQ3_certificate_document_5111f0e72545e7fc_ChatGPT_Image_Sep_4__2026__12_22_48_PM.png', 'uploads/employee_docs/REQ3_aadhaar_document_30cf9771aac4a1fe_ChatGPT_Image_Sep_4__2026__12_22_48_PM.png', 'uploads/employee_docs/REQ3_pan_document_bc9696de74fb041d_ChatGPT_Image_Sep_4__2026__12_26_30_PM.png', 'uploads/employee_docs/REQ3_bank_book_document_f2294a849ebfe6d8_ChatGPT_Image_Sep_4__2026__09_59_56_AM.png', 'uploads/employee_docs/REQ3_passport_photo_120a018e8d306ee5_ChatGPT_Image_Sep_4__2026__12_26_30_PM.png', NULL, NULL, NULL, NULL, NULL);

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

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

CREATE TABLE `employee_documents` (
  `id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `document_type` varchar(80) NOT NULL,
  `file_name` varchar(255) DEFAULT NULL,
  `file_path` varchar(255) DEFAULT NULL,
  `verified` tinyint(4) DEFAULT 0,
  `uploaded_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `employee_increments`
--

CREATE TABLE `employee_increments` (
  `id` int(10) UNSIGNED NOT NULL,
  `employee_id` int(10) UNSIGNED NOT NULL,
  `change_type` varchar(40) NOT NULL DEFAULT 'salary_increment',
  `increment_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `previous_gross` decimal(12,2) NOT NULL DEFAULT 0.00,
  `new_gross` decimal(12,2) NOT NULL DEFAULT 0.00,
  `previous_branch_id` int(10) UNSIGNED DEFAULT NULL,
  `new_branch_id` int(10) UNSIGNED DEFAULT NULL,
  `previous_designation` varchar(120) DEFAULT NULL,
  `new_designation` varchar(120) DEFAULT NULL,
  `reason` varchar(500) DEFAULT NULL,
  `effective_date` date DEFAULT NULL,
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `employee_increments`
--

INSERT INTO `employee_increments` (`id`, `employee_id`, `change_type`, `increment_amount`, `previous_gross`, `new_gross`, `previous_branch_id`, `new_branch_id`, `previous_designation`, `new_designation`, `reason`, `effective_date`, `created_by`, `created_at`) VALUES
(1, 9, 'designation_change', 0.00, 10000.00, 10000.00, 1, 1, 'Team Member', 'Store Manager', '', '2026-09-03', 1, '2026-09-03 10:14:51'),
(2, 9, 'branch_transfer', 0.00, 10000.00, 10000.00, 1, 2, 'Store Manager', 'Store Manager', '', '2026-09-03', 1, '2026-09-03 10:15:04'),
(3, 10, 'salary_increment', 1000.00, 9100.00, 10100.00, 1, 1, 'Store Manager', 'Store Manager', '', '2026-09-04', 1, '2026-09-04 10:49:05');

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

--
-- Table structure for table `employee_requests`
--

CREATE TABLE `employee_requests` (
  `id` int(10) UNSIGNED NOT NULL,
  `branch_id` int(10) UNSIGNED NOT NULL,
  `full_name` varchar(180) NOT NULL,
  `date_of_birth` date DEFAULT NULL,
  `gender` varchar(30) DEFAULT NULL,
  `email` varchar(190) DEFAULT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `address` varchar(500) NOT NULL,
  `aadhaar_number` varchar(20) NOT NULL,
  `pan_number` varchar(20) NOT NULL,
  `designation` varchar(120) NOT NULL DEFAULT 'Team Member',
  `status` enum('pending','approved','rejected','docs_submitted','completed') NOT NULL DEFAULT 'pending',
  `submitted_by` int(10) UNSIGNED DEFAULT NULL,
  `reviewed_by` int(10) UNSIGNED DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `documents_submitted_at` datetime DEFAULT NULL,
  `employee_id` int(10) UNSIGNED DEFAULT NULL,
  `company_document` varchar(255) DEFAULT NULL,
  `certificate_document` varchar(255) DEFAULT NULL,
  `aadhaar_document` varchar(255) DEFAULT NULL,
  `pan_document` varchar(255) DEFAULT NULL,
  `bank_book_document` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `salary_type` varchar(30) NOT NULL DEFAULT 'Monthly',
  `payroll_plan` varchar(50) NOT NULL DEFAULT 'Standard Payroll',
  `pf_option` enum('PF','Non-PF') NOT NULL DEFAULT 'PF',
  `basic_salary` decimal(12,2) NOT NULL DEFAULT 0.00,
  `hra` decimal(12,2) NOT NULL DEFAULT 0.00,
  `hcs` decimal(12,2) NOT NULL DEFAULT 0.00,
  `pf_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `esi_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `gross_salary` decimal(12,2) NOT NULL DEFAULT 0.00,
  `net_salary` decimal(12,2) NOT NULL DEFAULT 0.00,
  `payroll_notes` varchar(1000) DEFAULT NULL,
  `final_reviewed_by` int(10) UNSIGNED DEFAULT NULL,
  `final_reviewed_at` datetime DEFAULT NULL,
  `offer_letter_path` varchar(255) DEFAULT NULL,
  `joining_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `pf_percent` decimal(5,2) NOT NULL DEFAULT 12.00,
  `esi_percent` decimal(5,2) NOT NULL DEFAULT 0.75,
  `increment_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `passport_photo` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `employee_requests`
--

INSERT INTO `employee_requests` (`id`, `branch_id`, `full_name`, `date_of_birth`, `gender`, `email`, `phone`, `address`, `aadhaar_number`, `pan_number`, `designation`, `status`, `submitted_by`, `reviewed_by`, `reviewed_at`, `documents_submitted_at`, `employee_id`, `company_document`, `certificate_document`, `aadhaar_document`, `pan_document`, `bank_book_document`, `created_at`, `salary_type`, `payroll_plan`, `pf_option`, `basic_salary`, `hra`, `hcs`, `pf_amount`, `esi_amount`, `gross_salary`, `net_salary`, `payroll_notes`, `final_reviewed_by`, `final_reviewed_at`, `offer_letter_path`, `joining_amount`, `pf_percent`, `esi_percent`, `increment_amount`, `passport_photo`) VALUES
(1, 1, 'BOYIDI HEMANJALI', '1996-12-27', 'Female', 'ramboyidi1@gmail.com', '08374278077', '2-241, Near Vinayaka Temple', '324894240758', 'DBZPR2720F', 'Team Member', 'completed', 5, 1, '2026-09-03 15:11:09', '2026-09-03 15:11:39', 8, 'uploads/employee_docs/REQ1_company_document_d6fd54e19690ee67_ChatGPT_Image_Aug_31__2026__04_24_11_PM.png', 'uploads/employee_docs/REQ1_certificate_document_a0b9031398929b00_WhatsApp_Image_2026-08-31_at_4_22_25_PM.jpeg', 'uploads/employee_docs/REQ1_aadhaar_document_8809dd5bb9ac2387_WhatsApp_Image_2026-08-31_at_4_22_16_PM.jpeg', 'uploads/employee_docs/REQ1_pan_document_90eadcc060fd95a6_ChatGPT_Image_Aug_31__2026__04_17_36_PM.png', 'uploads/employee_docs/REQ1_bank_book_document_6db7b3dd7124afd5_TIRUVU.png', '2026-09-03 09:40:55', 'Monthly', 'Special Payroll', 'Non-PF', 50000.00, 6000.00, 0.00, 0.00, 420.00, 56000.00, 55580.00, '', 1, '2026-09-03 15:12:29', 'offer_letter.php?id=1', 0.00, 0.00, 0.75, 0.00, NULL),
(2, 1, 'NANU', '1991-01-01', 'Male', 'naanaai@gmail.com', '1234567890', '1', '123456789998', 'BQSPV2122B', 'Team Member', 'completed', 5, 1, '2026-09-03 15:24:55', '2026-09-03 15:25:32', 9, 'uploads/employee_docs/REQ2_company_document_9fb7e621e97b2f89_ChatGPT_Image_Aug_31__2026__04_24_11_PM.png', 'uploads/employee_docs/REQ2_certificate_document_1f8f3432ded3a73e_WhatsApp_Image_2026-08-31_at_4_22_25_PM.jpeg', 'uploads/employee_docs/REQ2_aadhaar_document_94fec4c4184680bb_WhatsApp_Image_2026-08-31_at_4_22_16_PM.jpeg', 'uploads/employee_docs/REQ2_pan_document_17b0ac6e6a46f9f5_ChatGPT_Image_Aug_31__2026__04_17_36_PM.png', 'uploads/employee_docs/REQ2_bank_book_document_820dd2faed6a4069_TIRUVU.png', '2026-09-03 09:54:45', 'Monthly', 'Standard Payroll', 'Non-PF', 10000.00, 0.00, 0.00, 0.00, 0.00, 10000.00, 10000.00, NULL, 1, '2026-09-03 15:34:44', 'offer_letter.php?id=2', 0.00, 0.00, 0.00, 0.00, NULL),
(3, 1, 'ram 654', '2002-01-01', 'Male', 'ramboyidi1@gmail.com', '08374278077', '2-171/41, PALLAPU COLONY , VEDIRESWARAM', '154784777474', 'DBZPR2720A', 'Team Member', 'completed', 5, 1, '2026-09-04 16:12:46', '2026-09-04 16:13:53', 11, 'uploads/employee_docs/REQ3_company_document_f459568fbfb6eff4_ChatGPT_Image_Sep_4__2026__03_01_26_PM.png', 'uploads/employee_docs/REQ3_certificate_document_5111f0e72545e7fc_ChatGPT_Image_Sep_4__2026__12_22_48_PM.png', 'uploads/employee_docs/REQ3_aadhaar_document_30cf9771aac4a1fe_ChatGPT_Image_Sep_4__2026__12_22_48_PM.png', 'uploads/employee_docs/REQ3_pan_document_bc9696de74fb041d_ChatGPT_Image_Sep_4__2026__12_26_30_PM.png', 'uploads/employee_docs/REQ3_bank_book_document_f2294a849ebfe6d8_ChatGPT_Image_Sep_4__2026__09_59_56_AM.png', '2026-09-04 10:41:59', 'Monthly', 'Standard Payroll', 'Non-PF', 10000.00, 0.00, 0.00, 0.00, 0.00, 10000.00, 10000.00, NULL, 8, '2026-09-04 16:26:15', 'offer_letter.php?id=3', 0.00, 0.00, 0.00, 0.00, 'uploads/employee_docs/REQ3_passport_photo_120a018e8d306ee5_ChatGPT_Image_Sep_4__2026__12_26_30_PM.png');

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

--
-- Table structure for table `interviews`
--

CREATE TABLE `interviews` (
  `id` int(11) NOT NULL,
  `candidate_id` int(11) NOT NULL,
  `interview_date` datetime NOT NULL,
  `round_name` varchar(100) DEFAULT NULL,
  `panel` varchar(255) DEFAULT NULL,
  `result` enum('Pending','Selected','Rejected','Hold') DEFAULT 'Pending',
  `score` decimal(5,2) DEFAULT 0.00,
  `remarks` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `job_positions`
--

CREATE TABLE `job_positions` (
  `id` int(10) UNSIGNED NOT NULL,
  `title` varchar(180) NOT NULL,
  `openings` int(10) UNSIGNED NOT NULL DEFAULT 1,
  `status` varchar(30) NOT NULL DEFAULT 'open',
  `closing_date` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `leaves`
--

CREATE TABLE `leaves` (
  `id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  `leave_type` varchar(50) NOT NULL,
  `days` decimal(5,1) NOT NULL,
  `reason` text DEFAULT NULL,
  `status` enum('Pending','Approved','Rejected') DEFAULT 'Pending',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

CREATE TABLE `leave_requests` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `employee_id` int(10) UNSIGNED NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `status` varchar(30) NOT NULL DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `offers`
--

CREATE TABLE `offers` (
  `id` int(11) NOT NULL,
  `candidate_id` int(11) DEFAULT NULL,
  `employee_id` int(11) DEFAULT NULL,
  `offer_no` varchar(40) NOT NULL,
  `designation` varchar(120) DEFAULT NULL,
  `salary` decimal(12,2) DEFAULT NULL,
  `joining_date` date DEFAULT NULL,
  `content` text DEFAULT NULL,
  `status` enum('Draft','Issued','Accepted','Declined') DEFAULT 'Draft',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `payroll_items`
--

CREATE TABLE `payroll_items` (
  `id` int(11) NOT NULL,
  `run_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `working_days` decimal(5,1) DEFAULT NULL,
  `present_days` decimal(5,1) DEFAULT NULL,
  `leave_days` decimal(5,1) DEFAULT NULL,
  `lop_days` decimal(5,1) DEFAULT NULL,
  `basic` decimal(12,2) DEFAULT NULL,
  `earnings` decimal(12,2) DEFAULT NULL,
  `gross` decimal(12,2) DEFAULT NULL,
  `pf` decimal(12,2) DEFAULT NULL,
  `esi` decimal(12,2) DEFAULT NULL,
  `pt` decimal(12,2) DEFAULT NULL,
  `other_deductions` decimal(12,2) DEFAULT NULL,
  `lop_deduction` decimal(12,2) DEFAULT NULL,
  `overtime_pay` decimal(12,2) DEFAULT NULL,
  `payable` decimal(12,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

CREATE TABLE `payroll_runs` (
  `id` int(11) NOT NULL,
  `payroll_month` char(7) NOT NULL,
  `status` enum('Draft','Processed','Approved','Paid') DEFAULT 'Draft',
  `processed_at` datetime DEFAULT NULL,
  `total_gross` decimal(14,2) DEFAULT 0.00,
  `total_deductions` decimal(14,2) DEFAULT 0.00,
  `total_payable` decimal(14,2) DEFAULT 0.00
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `salary_structures`
--

CREATE TABLE `salary_structures` (
  `id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `effective_from` date NOT NULL,
  `basic` decimal(12,2) DEFAULT 0.00,
  `hra` decimal(12,2) DEFAULT 0.00,
  `allowances` decimal(12,2) DEFAULT 0.00,
  `other_earnings` decimal(12,2) DEFAULT 0.00,
  `pf_rate` decimal(5,2) DEFAULT 12.00,
  `esi_rate` decimal(5,2) DEFAULT 0.00,
  `professional_tax` decimal(12,2) DEFAULT 0.00,
  `status` tinyint(4) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

CREATE TABLE `settings` (
  `id` int(11) NOT NULL,
  `setting_key` varchar(80) NOT NULL,
  `setting_value` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `settings` (`id`, `setting_key`, `setting_value`) VALUES
(1, 'company_name', 'Victory HRMS'),
(2, 'currency', 'INR'),
(3, 'payroll_basis', 'Calendar month');

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

--
-- Table structure for table `user_branches`
--

CREATE TABLE `user_branches` (
  `user_id` int(10) UNSIGNED NOT NULL,
  `branch_id` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `user_branches`
--

INSERT INTO `user_branches` (`user_id`, `branch_id`) VALUES
(5, 1),
(9, 2);

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

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

--
-- Indexes for table `branches`
--
ALTER TABLE `branches`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `branch_code` (`branch_code`),
  ADD KEY `idx_branch_status` (`status`),
  ADD KEY `idx_branch_city` (`city`);

--
-- Indexes for table `branch_manpower_plans`
--
ALTER TABLE `branch_manpower_plans`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_branch_designation` (`branch_id`,`designation`),
  ADD KEY `idx_plan_branch` (`branch_id`);

--
-- Indexes for table `candidates`
--
ALTER TABLE `candidates`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `code` (`code`);

--
-- Indexes for table `dashboard_events`
--
ALTER TABLE `dashboard_events`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_event_date` (`event_date`),
  ADD KEY `idx_event_branch` (`branch_id`);

--
-- Indexes for table `dashboard_event_branches`
--
ALTER TABLE `dashboard_event_branches`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_event_branch` (`event_id`,`branch_id`),
  ADD KEY `idx_deb_event` (`event_id`),
  ADD KEY `idx_deb_branch` (`branch_id`);

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

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

--
-- Indexes for table `employees`
--
ALTER TABLE `employees`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `employee_code` (`employee_code`),
  ADD UNIQUE KEY `uq_employee_aadhaar` (`aadhaar_number`),
  ADD KEY `candidate_id` (`candidate_id`),
  ADD KEY `department_id` (`department_id`),
  ADD KEY `designation_id` (`designation_id`),
  ADD KEY `idx_employee_branch` (`branch_id`);

--
-- Indexes for table `employee_documents`
--
ALTER TABLE `employee_documents`
  ADD PRIMARY KEY (`id`),
  ADD KEY `employee_id` (`employee_id`);

--
-- Indexes for table `employee_increments`
--
ALTER TABLE `employee_increments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_inc_employee` (`employee_id`),
  ADD KEY `idx_inc_type` (`change_type`),
  ADD KEY `idx_inc_date` (`effective_date`);

--
-- Indexes for table `employee_requests`
--
ALTER TABLE `employee_requests`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_er_branch_status` (`branch_id`,`status`),
  ADD KEY `idx_er_status` (`status`),
  ADD KEY `idx_er_aadhaar` (`aadhaar_number`);

--
-- Indexes for table `interviews`
--
ALTER TABLE `interviews`
  ADD PRIMARY KEY (`id`),
  ADD KEY `candidate_id` (`candidate_id`);

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

--
-- Indexes for table `leaves`
--
ALTER TABLE `leaves`
  ADD PRIMARY KEY (`id`),
  ADD KEY `employee_id` (`employee_id`);

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

--
-- Indexes for table `offers`
--
ALTER TABLE `offers`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `offer_no` (`offer_no`),
  ADD KEY `candidate_id` (`candidate_id`),
  ADD KEY `employee_id` (`employee_id`);

--
-- Indexes for table `payroll_items`
--
ALTER TABLE `payroll_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `run_id` (`run_id`),
  ADD KEY `employee_id` (`employee_id`);

--
-- Indexes for table `payroll_runs`
--
ALTER TABLE `payroll_runs`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `payroll_month` (`payroll_month`);

--
-- Indexes for table `salary_structures`
--
ALTER TABLE `salary_structures`
  ADD PRIMARY KEY (`id`),
  ADD KEY `employee_id` (`employee_id`);

--
-- Indexes for table `settings`
--
ALTER TABLE `settings`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `setting_key` (`setting_key`);

--
-- Indexes for table `user_branches`
--
ALTER TABLE `user_branches`
  ADD PRIMARY KEY (`user_id`,`branch_id`),
  ADD KEY `idx_ub_branch` (`branch_id`);

--
-- AUTO_INCREMENT for table `attendance`
--
ALTER TABLE `attendance`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `attendance_rules`
--
ALTER TABLE `attendance_rules`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

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

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

--
-- AUTO_INCREMENT for table `candidates`
--
ALTER TABLE `candidates`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

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

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

--
-- AUTO_INCREMENT for table `departments`
--
ALTER TABLE `departments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `designations`
--
ALTER TABLE `designations`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `employees`
--
ALTER TABLE `employees`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;

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

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

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

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

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

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

--
-- AUTO_INCREMENT for table `leave_requests`
--
ALTER TABLE `leave_requests`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

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

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

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

--
-- AUTO_INCREMENT for table `salary_structures`
--
ALTER TABLE `salary_structures`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `settings`
--
ALTER TABLE `settings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `branch_manpower_plans`
--
ALTER TABLE `branch_manpower_plans`
  ADD CONSTRAINT `fk_plan_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `dashboard_event_branches`
--
ALTER TABLE `dashboard_event_branches`
  ADD CONSTRAINT `fk_deb_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_deb_event` FOREIGN KEY (`event_id`) REFERENCES `dashboard_events` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `employees`
--
ALTER TABLE `employees`
  ADD CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`candidate_id`) REFERENCES `candidates` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `employees_ibfk_2` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `employees_ibfk_3` FOREIGN KEY (`designation_id`) REFERENCES `designations` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `employee_documents`
--
ALTER TABLE `employee_documents`
  ADD CONSTRAINT `employee_documents_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `interviews`
--
ALTER TABLE `interviews`
  ADD CONSTRAINT `interviews_ibfk_1` FOREIGN KEY (`candidate_id`) REFERENCES `candidates` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `leaves`
--
ALTER TABLE `leaves`
  ADD CONSTRAINT `leaves_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `offers`
--
ALTER TABLE `offers`
  ADD CONSTRAINT `offers_ibfk_1` FOREIGN KEY (`candidate_id`) REFERENCES `candidates` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `offers_ibfk_2` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `payroll_items`
--
ALTER TABLE `payroll_items`
  ADD CONSTRAINT `payroll_items_ibfk_1` FOREIGN KEY (`run_id`) REFERENCES `payroll_runs` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `payroll_items_ibfk_2` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `salary_structures`
--
ALTER TABLE `salary_structures`
  ADD CONSTRAINT `salary_structures_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `user_branches`
--
ALTER TABLE `user_branches`
  ADD CONSTRAINT `fk_ub_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_ub_user` FOREIGN KEY (`user_id`) REFERENCES `admin_users` (`id`) ON DELETE CASCADE;
COMMIT;

