-- phpMyAdmin SQL Dump
-- version 5.2.0
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Feb 12, 2025 at 07:26 PM
-- Server version: 8.0.31
-- PHP Version: 8.1.13

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!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: `payrollmgt`
--

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

--
-- Table structure for table `bank_details`
--

DROP TABLE IF EXISTS `bank_details`;
CREATE TABLE IF NOT EXISTS `bank_details` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `teacher_uid` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `account_number` int DEFAULT NULL,
  `bank_name` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `branch_name` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `branch_address` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `routing_number` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `account_type` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `bank_details`
--

INSERT INTO `bank_details` (`id`, `teacher_uid`, `account_number`, `bank_name`, `branch_name`, `branch_address`, `routing_number`, `account_type`, `created_at`, `updated_at`) VALUES
(1, '1782238839746420', 123, 'butch bangla', 'badda', 'badda, dhaka', '123', 'saving', '2025-01-12 05:49:11', '2025-01-12 05:49:11'),
(2, '1780612344945604', 123, 'butch bangla', 'badda', 'badda, dhaka', '123', 'saving', '2025-02-03 05:47:12', '2025-02-03 05:47:12'),
(3, '1780612344945604', 123, 'butch bangla', 'badda', 'badda, dhaka', '123', 'saving', '2025-02-03 05:48:55', '2025-02-03 05:48:55'),
(4, '11', 124, 'butch bangla', 'badda', 'badda, dhaka', '123', 'saving', '2025-02-05 23:29:11', '2025-02-05 23:29:11');

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

--
-- Table structure for table `educational_infos`
--

DROP TABLE IF EXISTS `educational_infos`;
CREATE TABLE IF NOT EXISTS `educational_infos` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `teacher_uid` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `exam_name` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `subject` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `institute_name` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `result` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `passing_year` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_by` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `educational_infos`
--

INSERT INTO `educational_infos` (`id`, `teacher_uid`, `exam_name`, `subject`, `institute_name`, `result`, `passing_year`, `created_by`, `created_at`, `updated_at`) VALUES
(1, '1782238839746420', 'ssc', 'science', 'polash sar kar khana', 'gpa-5', '1988-12-10', '1782238839746420', '2025-01-12 05:49:18', '2025-01-12 05:49:18'),
(2, '1', 'hsc', 'bba', 'polash sar kar khana', 'gpa-4', '1988-12-10', '1782238839746420', '2025-01-12 05:49:18', '2025-01-12 05:49:18'),
(5, '1780612344945604', 'ssc', 'science', 'polash sar kar khana', 'gpa-5', '1988-12-10', '1780612344945604', '2025-02-03 05:50:09', '2025-02-03 05:50:09'),
(4, '1', 'hsc', 'bba', 'polash sar kar khana', 'gpa-4', '1988-12-10', '1780612344945604', '2025-02-03 05:49:28', '2025-02-03 05:49:28'),
(6, '1780612344945604', 'hsc', 'bba', 'polash sar kar khana', 'gpa-4', '1988-12-10', '1780612344945604', '2025-02-03 05:50:09', '2025-02-03 05:50:09');

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

--
-- Table structure for table `employee_advances`
--

DROP TABLE IF EXISTS `employee_advances`;
CREATE TABLE IF NOT EXISTS `employee_advances` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `employee_id` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `advance_amount` int NOT NULL,
  `remark` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `date` date DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `employee_advances_employee_id_foreign` (`employee_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `employee_advances`
--

INSERT INTO `employee_advances` (`id`, `employee_id`, `advance_amount`, `remark`, `date`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, '11', 10000, 'advance remark 2', '2025-02-09', '2025-02-10 04:18:24', '2025-02-10 04:19:17', NULL),
(2, '11', 5000, 'advance remark 2', '2025-02-09', '2025-02-10 04:18:24', '2025-02-10 04:18:24', NULL),
(3, '1780612344945604', 6000, 'advance remark 3', '2025-02-09', '2025-02-10 04:19:17', '2025-02-10 04:19:17', NULL);

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

--
-- Table structure for table `employee_details`
--

DROP TABLE IF EXISTS `employee_details`;
CREATE TABLE IF NOT EXISTS `employee_details` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `teacher_uid` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `department_id` int DEFAULT NULL,
  `department_name` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `caid` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `designation_id` int DEFAULT NULL,
  `designation_name` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `eiin` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `employment_type_id` int DEFAULT NULL,
  `joining_date` date DEFAULT NULL,
  `permanent_date` date DEFAULT NULL,
  `contract_type` enum('permanent','temporary','freelance','pending') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `status` tinyint NOT NULL DEFAULT '0' COMMENT '0=inative,1=active,2=not-exist',
  `image` text COLLATE utf8mb4_unicode_ci,
  `father_name` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mother_name` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `father_occupation` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mother_occupation` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date_of_birth` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `place_of_birth` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `gender` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `religion` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `nationality` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `nid_no` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `blood_group` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `marital_status` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `tin_no` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mobile_no` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `telephone_no` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `licence_no` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `permanent_district_id` int DEFAULT NULL,
  `present_district_id` int DEFAULT NULL,
  `permanent_upazila_id` int DEFAULT NULL,
  `present_upazila_id` int DEFAULT NULL,
  `permanent_post_office` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `present_post_office` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `permanent_post_code` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `present_post_code` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `permanent_address` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `present_address` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `emergency_contact_name` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `relation_with` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `emergency_contact_no` char(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `emergency_contact_email` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `emergency_contact_address` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_by` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `updated_by` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `employee_details`
--

INSERT INTO `employee_details` (`id`, `teacher_uid`, `name`, `department_id`, `department_name`, `caid`, `designation_id`, `designation_name`, `eiin`, `employment_type_id`, `joining_date`, `permanent_date`, `contract_type`, `status`, `image`, `father_name`, `mother_name`, `father_occupation`, `mother_occupation`, `date_of_birth`, `place_of_birth`, `gender`, `religion`, `nationality`, `nid_no`, `blood_group`, `marital_status`, `tin_no`, `mobile_no`, `telephone_no`, `licence_no`, `permanent_district_id`, `present_district_id`, `permanent_upazila_id`, `present_upazila_id`, `permanent_post_office`, `present_post_office`, `permanent_post_code`, `present_post_code`, `permanent_address`, `present_address`, `emergency_contact_name`, `relation_with`, `emergency_contact_no`, `emergency_contact_email`, `emergency_contact_address`, `created_by`, `updated_by`, `deleted_at`, `created_at`, `updated_at`) VALUES
(1, '1780612344945604', 'MOHAMMAD ABDUL HAFIZ', 2, NULL, '110577020230001', 1, 'Head Teacher', '40', NULL, '2025-01-01', NULL, 'pending', 1, '20250204_1738668229.png', 'delwar', 'selina', 'deputy', 'grihini', '10-12-1988', 'polash', 'mal', 'islam', NULL, '1234', 'C+', NULL, '12345', '01534770999', '+02-887766', '123456', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1780612344945604', '1780612344945604', NULL, '2025-02-03 05:45:45', '2025-02-04 05:23:49'),
(2, '11', 'Md Mizanur Rahman', 2, NULL, '11', 2, 'officer', '40', NULL, '2025-01-01', NULL, 'pending', 0, '20250206_1738819715.png', 'ishak', 'tofuri', 'deputy', 'grihini', '10-12-1988', 'polash', 'mal', 'islam', NULL, '1234', 'C+', NULL, '12345', '01534770999', '+02-887766', '123456', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '11', '11', NULL, '2025-02-05 23:28:35', '2025-02-05 23:28:35');

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

--
-- Table structure for table `employee_penalties`
--

DROP TABLE IF EXISTS `employee_penalties`;
CREATE TABLE IF NOT EXISTS `employee_penalties` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `employee_id` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `penalty_amount` int NOT NULL,
  `penalty_purpose` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `penalty_type` int DEFAULT NULL COMMENT '1=general;2=absence',
  `penalty_date` date DEFAULT NULL,
  `status` tinyint NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `employee_penalties_employee_id_foreign` (`employee_id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `employee_penalties`
--

INSERT INTO `employee_penalties` (`id`, `employee_id`, `penalty_amount`, `penalty_purpose`, `penalty_type`, `penalty_date`, `status`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, '11', 10, 'purpose-1', 1, '2025-01-08', 1, '2025-02-08 02:21:08', '2025-02-10 00:48:17', NULL),
(2, '11', 20, 'purpose-2', 1, '2025-02-09', 1, '2025-02-08 02:21:08', '2025-02-10 00:48:17', NULL),
(3, '1780612344945604', 2500, 'purpose-11', 1, '2025-01-06', 1, '2025-02-08 12:38:22', '2025-02-10 00:01:30', NULL),
(4, '11', 30, 'purpose-3', 1, '2025-02-06', 1, '2025-02-08 12:38:22', '2025-02-10 00:48:17', NULL),
(5, '11', 40, 'purpose-4', 1, '2025-01-15', 1, '2025-02-09 23:13:59', '2025-02-10 00:48:17', NULL),
(6, '11', 50, 'purpose-5', 1, '2025-01-06', 1, '2025-02-09 23:13:59', '2025-02-10 00:48:17', NULL),
(7, '1780612344945604', 100, 'purpose-12', 2, '2025-02-01', 1, '2025-02-10 04:05:02', '2025-02-10 04:05:02', NULL),
(8, '1780612344945604', 300, 'purpose-13', 1, '2025-02-08', 1, '2025-02-10 04:05:02', '2025-02-10 04:05:02', NULL);

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

--
-- Table structure for table `employee_salary_expense_records`
--

DROP TABLE IF EXISTS `employee_salary_expense_records`;
CREATE TABLE IF NOT EXISTS `employee_salary_expense_records` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `employee_id` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `salary_expense_type_id` bigint UNSIGNED NOT NULL,
  `basic_salary` decimal(10,2) NOT NULL,
  `allowance_details` json NOT NULL,
  `deduction_details` json NOT NULL,
  `total_allowance_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `total_deduction_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `payable_bill` decimal(10,2) NOT NULL DEFAULT '0.00',
  `effective_from` date DEFAULT NULL,
  `is_revision` tinyint DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `employee_salary_expense_records_employee_id_foreign` (`employee_id`),
  KEY `employee_salary_expense_records_salary_expense_type_id_foreign` (`salary_expense_type_id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `employee_salary_expense_records`
--

INSERT INTO `employee_salary_expense_records` (`id`, `employee_id`, `salary_expense_type_id`, `basic_salary`, `allowance_details`, `deduction_details`, `total_allowance_amount`, `total_deduction_amount`, `payable_bill`, `effective_from`, `is_revision`, `created_at`, `updated_at`) VALUES
(1, '1780612344945604', 1, '50000.00', '[{\"amount\": 2000, \"head_id\": 1}, {\"amount\": 1500, \"head_id\": 2}, {\"amount\": 1700, \"head_id\": 3}]', '[{\"amount\": 500, \"head_id\": 1}]', '10000.00', '10000.00', '60000.00', '2025-01-01', 0, '2025-02-04 08:19:16', '2025-02-04 14:16:43'),
(3, '1780612344945604', 2, '23000.00', '[{\"amount\": 2000, \"head_id\": 1}, {\"amount\": 1500, \"head_id\": 2}]', '[{\"amount\": 500, \"head_id\": 3}, {\"amount\": 300, \"head_id\": 4}]', '10000.00', '10000.00', '22000.00', '2025-02-04', 1, '2024-11-04 10:10:41', '2025-02-04 10:10:41'),
(4, '1780612344945604', 2, '10000.00', '[{\"amount\": 2000, \"head_id\": 1}, {\"amount\": 1500, \"head_id\": 2}]', '[{\"amount\": 500, \"head_id\": 1}, {\"amount\": 500, \"head_id\": 3}]', '10000.00', '10000.00', '10000.00', '2025-03-03', 0, '2024-12-04 14:32:01', '2025-02-04 14:32:01'),
(5, '11', 1, '22000.00', '[{\"amount\": 2000, \"head_id\": 1}, {\"amount\": 1500, \"head_id\": 2}, {\"amount\": 2000, \"head_id\": 3}]', '[{\"amount\": 500, \"head_id\": 3}]', '14000.00', '11000.00', '30000.00', '2025-02-02', 1, '2025-02-05 23:34:26', '2025-02-05 23:34:26'),
(6, '11', 1, '19000.00', '[{\"amount\": 2000, \"head_id\": 1}, {\"amount\": 1500, \"head_id\": 2}, {\"amount\": 2000, \"head_id\": 3}]', '[{\"amount\": 500, \"head_id\": 3}]', '14000.00', '11000.00', '20000.00', '2025-03-02', 1, '2025-01-10 22:16:33', '2025-02-10 22:16:33');

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

--
-- Table structure for table `employment_types`
--

DROP TABLE IF EXISTS `employment_types`;
CREATE TABLE IF NOT EXISTS `employment_types` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `type_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_by` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `employment_types`
--

INSERT INTO `employment_types` (`id`, `type_name`, `created_by`, `created_at`, `updated_at`) VALUES
(1, 'permanent', 'created_by_sso_auth_user', '2025-01-13 11:50:11', '2025-01-13 11:50:11'),
(2, 'fuck2', 'created_by_sso_auth_user', '2025-01-13 11:50:28', '2025-01-13 12:15:51'),
(3, 'permanent', 'created_by_sso_auth_user', '2025-01-13 11:52:26', '2025-01-13 11:52:26'),
(4, 'temporary', 'created_by_sso_auth_user', '2025-01-13 11:53:22', '2025-01-13 11:53:22'),
(5, 'freelance', 'created_by_sso_auth_user', '2025-01-13 11:54:01', '2025-01-13 11:54:01'),
(6, 'freelance2', 'created_by_sso_auth_user', '2025-01-13 11:54:57', '2025-01-13 11:54:57'),
(7, 'freelance2', 'created_by_sso_auth_user', '2025-01-13 11:57:16', '2025-01-13 11:57:16');

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

--
-- Table structure for table `failed_jobs`
--

DROP TABLE IF EXISTS `failed_jobs`;
CREATE TABLE IF NOT EXISTS `failed_jobs` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `connection` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `queue` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `exception` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `migrations`
--

DROP TABLE IF EXISTS `migrations`;
CREATE TABLE IF NOT EXISTS `migrations` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `migration` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `batch` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=93 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `migrations`
--

INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES
(1, '2014_10_12_100000_create_password_reset_tokens_table', 1),
(2, '2014_10_12_100000_create_password_resets_table', 1),
(3, '2019_08_19_000000_create_failed_jobs_table', 1),
(4, '2019_12_14_000001_create_personal_access_tokens_table', 1),
(23, '2025_01_07_141627_create_educational_infos_table', 7),
(22, '2025_01_07_133808_create_nominee_infos_table', 7),
(21, '2025_01_07_133750_create_bank_details_table', 7),
(81, '2025_01_06_083028_create_employee_details_table', 16),
(13, '2025_01_09_191948_create_employment_types_table', 6),
(72, '2025_01_19_121829_create_type_wise_expenses_table', 13),
(71, '2025_01_15_054953_create_salary_expense_types_table', 13),
(83, '2025_01_13_114455_create_paygrades_table', 17),
(53, '2025_01_13_110421_create_salary_allowance_heads_table', 9),
(88, '2025_01_18_174813_create_employee_salary_expenses_records_table', 19),
(62, '2025_01_23_041601_create_salary_deduction_heads_table', 12),
(86, '2025_01_27_112550_create_payfixations_table', 18),
(90, '2025_02_06_113641_create_employee_penalties_table', 20),
(92, '2025_02_09_125535_create_employee_advances_table', 21);

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

--
-- Table structure for table `nominee_infos`
--

DROP TABLE IF EXISTS `nominee_infos`;
CREATE TABLE IF NOT EXISTS `nominee_infos` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `teacher_uid` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `nominee_name` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `nominee_relation` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `nominee_nid` int DEFAULT NULL,
  `nominee_mobile` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `nominee_address` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date_of_birth` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `nominee_email` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `nominee_percent_allocation` int DEFAULT NULL,
  `added_by` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `nominee_infos`
--

INSERT INTO `nominee_infos` (`id`, `teacher_uid`, `nominee_name`, `nominee_relation`, `nominee_nid`, `nominee_mobile`, `nominee_address`, `date_of_birth`, `nominee_email`, `nominee_percent_allocation`, `added_by`, `created_at`, `updated_at`) VALUES
(1, '1782238839746420', 'ishak', 'uncle', 12345, '015234', 'cumilla,bangladesh', '10/12/1988', 'nominee@gmail.com', 50, '200', '2025-01-12 05:49:11', '2025-01-12 05:49:11'),
(2, '1782238839746420', 'ishak2', 'uncle2', 123452, '0152342', 'cumilla,bangladesh,2', '10/12/1988', 'nominee2@gmail.com', 70, '2002', '2025-01-12 05:49:11', '2025-01-12 05:49:11'),
(3, '1780612344945604', 'ishak', 'uncle', 12345, '015234', 'cumilla,bangladesh', '10/12/1988', 'nominee@gmail.com', 50, '200', '2025-02-03 05:47:12', '2025-02-03 05:47:12'),
(4, '1780612344945604', 'ishak2', 'uncle2', 123452, '0152342', 'cumilla,bangladesh,2', '10/12/1988', 'nominee2@gmail.com', 70, '2002', '2025-02-03 05:47:12', '2025-02-03 05:47:12'),
(5, '1780612344945604', 'ishak', 'uncle', 12345, '015234', 'cumilla,bangladesh', '10/12/1988', 'nominee@gmail.com', 50, '200', '2025-02-03 05:48:55', '2025-02-03 05:48:55'),
(6, '1780612344945604', 'ishak2', 'uncle2', 123452, '0152342', 'cumilla,bangladesh,2', '10/12/1988', 'nominee2@gmail.com', 70, '2002', '2025-02-03 05:48:55', '2025-02-03 05:48:55'),
(7, '11', 'ishak', 'uncle', 12345, '015234', 'cumilla,bangladesh', '10/12/1988', 'nominee@gmail.com', 50, '200', '2025-02-05 23:29:11', '2025-02-05 23:29:11'),
(8, '11', 'ishak2', 'uncle2', 123452, '0152342', 'cumilla,bangladesh,2', '10/12/1988', 'nominee2@gmail.com', 70, '2002', '2025-02-05 23:29:11', '2025-02-05 23:29:11');

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

--
-- Table structure for table `password_resets`
--

DROP TABLE IF EXISTS `password_resets`;
CREATE TABLE IF NOT EXISTS `password_resets` (
  `email` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `token` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  KEY `password_resets_email_index` (`email`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `password_reset_tokens`
--

DROP TABLE IF EXISTS `password_reset_tokens`;
CREATE TABLE IF NOT EXISTS `password_reset_tokens` (
  `email` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `token` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`email`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `payfixations`
--

DROP TABLE IF EXISTS `payfixations`;
CREATE TABLE IF NOT EXISTS `payfixations` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `employee_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `salary_expense_type_id` int NOT NULL,
  `basic_salary` int NOT NULL,
  `effective_from` date DEFAULT NULL,
  `comment` text COLLATE utf8mb4_unicode_ci,
  `created_by` bigint NOT NULL,
  `updated_by` bigint DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `payfixations_salary_expense_type_id_index` (`salary_expense_type_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `payfixations`
--

INSERT INTO `payfixations` (`id`, `employee_id`, `salary_expense_type_id`, `basic_salary`, `effective_from`, `comment`, `created_by`, `updated_by`, `created_at`, `updated_at`) VALUES
(1, '1780612344945604', 1, 12000, '2025-01-01', 'payfixation comment part', 1780612344945604, NULL, '2025-02-04 02:59:51', '2025-02-04 02:59:51');

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

--
-- Table structure for table `paygrades`
--

DROP TABLE IF EXISTS `paygrades`;
CREATE TABLE IF NOT EXISTS `paygrades` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `paygrade_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `basic_salary` double NOT NULL,
  `increment_rule` tinyint NOT NULL,
  `all_basic_amount` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'All basic ammounts only.',
  `increment_amount` int DEFAULT NULL,
  `max_payscale` double NOT NULL,
  `status` tinyint NOT NULL DEFAULT '1',
  `created_by` int DEFAULT NULL,
  `modified_by` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `paygrades`
--

INSERT INTO `paygrades` (`id`, `paygrade_name`, `basic_salary`, `increment_rule`, `all_basic_amount`, `increment_amount`, `max_payscale`, `status`, `created_by`, `modified_by`, `created_at`, `updated_at`) VALUES
(1, 'Grade-1', 80000, 1, '80000', NULL, 80000, 1, NULL, NULL, '2025-02-04 01:16:07', '2025-02-04 01:21:47'),
(2, 'Grade-2', 66000, 1, '66000-68480-71050-73720-76490', NULL, 76490, 1, NULL, NULL, '2025-02-04 01:24:36', '2025-02-04 01:24:36'),
(3, 'Grade-3', 56500, 1, '56500-58760-61120-63570-66120-68770-71530-74400', NULL, 74400, 1, NULL, NULL, '2025-02-04 01:26:07', '2025-02-04 01:26:07'),
(5, 'Grade-4', 50000, 2, NULL, 10, 71200, 1, NULL, NULL, '2025-02-04 01:28:03', '2025-02-04 01:28:03');

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

--
-- Table structure for table `personal_access_tokens`
--

DROP TABLE IF EXISTS `personal_access_tokens`;
CREATE TABLE IF NOT EXISTS `personal_access_tokens` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `tokenable_type` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `tokenable_id` bigint UNSIGNED NOT NULL,
  `name` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `token` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `abilities` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `last_used_at` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `personal_access_tokens_token_unique` (`token`),
  KEY `personal_access_tokens_tokenable_type_tokenable_id_index` (`tokenable_type`,`tokenable_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `salary_allowance_heads`
--

DROP TABLE IF EXISTS `salary_allowance_heads`;
CREATE TABLE IF NOT EXISTS `salary_allowance_heads` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `allowance_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'house,medical,education',
  `status` tinyint NOT NULL DEFAULT '1',
  `sort_order` int NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `salary_allowance_heads_allowance_name_unique` (`allowance_name`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `salary_allowance_heads`
--

INSERT INTO `salary_allowance_heads` (`id`, `allowance_name`, `status`, `sort_order`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 'home_allowance', 1, 7, '2025-01-23 07:40:54', '2025-01-23 07:40:54', NULL),
(2, 'education_allowance', 1, 7, '2025-01-23 07:41:14', '2025-01-23 07:41:14', NULL),
(3, 'transport_allowance', 1, 1, '2025-01-23 07:41:54', '2025-01-23 07:41:54', NULL),
(4, 'water_allowance', 1, 2, '2025-01-23 07:42:41', '2025-01-23 07:42:41', NULL);

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

--
-- Table structure for table `salary_deduction_heads`
--

DROP TABLE IF EXISTS `salary_deduction_heads`;
CREATE TABLE IF NOT EXISTS `salary_deduction_heads` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `deduction_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'pfd,incometex,loans,house_loan',
  `status` tinyint NOT NULL DEFAULT '1',
  `sort_order` int NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `salary_deduction_heads_deduction_name_unique` (`deduction_name`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `salary_deduction_heads`
--

INSERT INTO `salary_deduction_heads` (`id`, `deduction_name`, `status`, `sort_order`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 'home_loan', 1, 1, '2025-01-23 07:35:46', '2025-01-23 07:35:46', NULL),
(2, 'car_loan', 1, 2, '2025-01-23 07:36:30', '2025-01-23 07:36:30', NULL),
(3, 'general_loan', 1, 2, '2025-01-23 07:37:13', '2025-01-23 07:37:13', NULL),
(4, 'business_loan', 1, 2, '2025-01-23 07:39:36', '2025-01-23 08:43:08', NULL),
(5, 'delivery_loan', 1, 2, '2025-01-23 08:42:23', '2025-01-23 08:42:23', NULL);

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

--
-- Table structure for table `salary_expense_types`
--

DROP TABLE IF EXISTS `salary_expense_types`;
CREATE TABLE IF NOT EXISTS `salary_expense_types` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `expense_type_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'like regular,temporary, freelance,',
  `description` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` tinyint NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `salary_expense_types`
--

INSERT INTO `salary_expense_types` (`id`, `expense_type_name`, `description`, `status`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 'regular_type', 'des-1200', 1, '2025-01-26 04:37:52', '2025-01-26 04:37:52', NULL),
(2, 'freelance_type_2', 'des-1200', 1, '2025-01-26 04:43:59', '2025-01-26 04:50:10', NULL),
(3, 'freelance_type', 'des-1200', 1, '2025-02-04 13:37:04', '2025-02-04 13:37:04', NULL);

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

--
-- Table structure for table `type_wise_expenses`
--

DROP TABLE IF EXISTS `type_wise_expenses`;
CREATE TABLE IF NOT EXISTS `type_wise_expenses` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `salary_expense_type_id` int NOT NULL,
  `allowance_or_deduction_head_id` int DEFAULT NULL,
  `type` int DEFAULT NULL COMMENT 'for allowance=1 ; for deduction=0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `type_wise_expenses`
--

INSERT INTO `type_wise_expenses` (`id`, `salary_expense_type_id`, `allowance_or_deduction_head_id`, `type`, `created_at`, `updated_at`) VALUES
(1, 1, 1, 1, '2025-01-26 04:37:52', '2025-01-26 04:37:52'),
(2, 1, 2, 1, '2025-01-26 04:37:52', '2025-01-26 04:37:52'),
(3, 1, 1, 0, '2025-01-26 04:37:52', '2025-01-26 04:37:52'),
(5, 2, 1, 1, '2025-01-26 04:43:59', '2025-01-26 04:43:59'),
(6, 2, 3, 1, '2025-01-26 04:43:59', '2025-01-26 04:43:59'),
(7, 2, 3, 0, '2025-01-26 04:43:59', '2025-01-26 04:43:59'),
(8, 2, 4, 0, '2025-01-26 04:43:59', '2025-01-26 04:43:59'),
(10, 3, 1, 1, '2025-02-04 13:37:04', '2025-02-04 13:37:04'),
(11, 3, 3, 1, '2025-02-04 13:37:04', '2025-02-04 13:37:04'),
(12, 3, 3, 0, '2025-02-04 13:37:04', '2025-02-04 13:37:04'),
(13, 3, 4, 0, '2025-02-04 13:37:04', '2025-02-04 13:37:04'),
(14, 3, 5, 0, '2025-02-04 13:37:04', '2025-02-04 13:37:04'),
(15, 1, 3, 1, '2025-02-04 13:38:40', '2025-02-04 13:38:40');
COMMIT;

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