-- phpMyAdmin SQL Dump
-- version 5.2.2
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Jan 12, 2026 at 10:54 AM
-- Server version: 8.0.44
-- PHP Version: 8.4.16

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: `xesstech_dayloan`
--

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

--
-- Table structure for table `agent_attendance`
--

CREATE TABLE `agent_attendance` (
  `id` int NOT NULL,
  `agent_id` int NOT NULL,
  `com_id` int NOT NULL,
  `work_date` date NOT NULL,
  `check_in_time` datetime NOT NULL,
  `check_out_time` datetime DEFAULT NULL,
  `status` enum('checked_in','checked_out') NOT NULL DEFAULT 'checked_in',
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT NULL,
  `location` varchar(500) DEFAULT NULL,
  `notes` text,
  `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_0900_ai_ci;

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

--
-- Table structure for table `agent_location_tracking`
--

CREATE TABLE `agent_location_tracking` (
  `id` int NOT NULL,
  `agent_id` int NOT NULL,
  `com_id` int NOT NULL,
  `latitude` decimal(10,8) NOT NULL,
  `longitude` decimal(11,8) NOT NULL,
  `accuracy` decimal(10,2) DEFAULT NULL,
  `altitude` decimal(10,2) DEFAULT NULL,
  `altitude_accuracy` decimal(10,2) DEFAULT NULL,
  `heading` decimal(5,2) DEFAULT NULL,
  `speed` decimal(10,2) DEFAULT NULL,
  `address` text,
  `activity_type` enum('collection','travel','check_in','check_out','other') NOT NULL DEFAULT 'collection',
  `receipt_id` int DEFAULT NULL,
  `loan_id` varchar(50) DEFAULT NULL,
  `remark` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `area`
--

CREATE TABLE `area` (
  `id` int NOT NULL,
  `com_id` int NOT NULL,
  `main_area` varchar(255) NOT NULL,
  `sub_area` varchar(255) NOT NULL,
  `createdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `audit_log`
--

CREATE TABLE `audit_log` (
  `id` bigint NOT NULL,
  `user_id` int NOT NULL,
  `company_id` int DEFAULT NULL,
  `action` varchar(20) NOT NULL,
  `entity` varchar(100) NOT NULL,
  `entity_id` bigint NOT NULL,
  `changes` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `collection_agent`
--

CREATE TABLE `collection_agent` (
  `id` int NOT NULL,
  `com_id` int NOT NULL,
  `name` varchar(255) NOT NULL,
  `mobile_no` varchar(15) NOT NULL,
  `address` text NOT NULL,
  `aadhar_no` varchar(30) NOT NULL,
  `photo` longblob NOT NULL,
  `report_in` time NOT NULL,
  `reporting_time` time NOT NULL,
  `status` varchar(100) NOT NULL,
  `last_login` datetime DEFAULT NULL,
  `device_id` varchar(255) DEFAULT NULL,
  `device_token` varchar(255) DEFAULT NULL,
  `device_type` enum('android','ios','web') DEFAULT NULL,
  `auth_token` varchar(255) DEFAULT NULL,
  `token_expires_at` datetime DEFAULT NULL,
  `is_logged_in` tinyint(1) DEFAULT '0',
  `login_attempts` int DEFAULT '0',
  `last_attempt` datetime DEFAULT NULL,
  `line` varchar(300) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `profile_image` varchar(225) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `collection_agent_otp`
--

CREATE TABLE `collection_agent_otp` (
  `id` int NOT NULL,
  `agent_id` int NOT NULL,
  `mobile_no` varchar(15) NOT NULL,
  `otp` varchar(6) NOT NULL,
  `is_verified` tinyint(1) NOT NULL DEFAULT '0',
  `expires_at` datetime NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `company`
--

CREATE TABLE `company` (
  `com_id` int NOT NULL,
  `reg_id` varchar(50) NOT NULL,
  `com_name` varchar(255) NOT NULL,
  `mob_num` varchar(15) NOT NULL,
  `off_num` varchar(15) DEFAULT NULL,
  `logo` longtext,
  `profile` text,
  `street` varchar(255) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `district` varchar(100) DEFAULT NULL,
  `pincode` varchar(10) DEFAULT NULL,
  `gst_no` varchar(20) DEFAULT NULL,
  `email` varchar(255) NOT NULL DEFAULT '',
  `user_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `Status` varchar(50) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `customers`
--

CREATE TABLE `customers` (
  `id` int NOT NULL,
  `com_id` int NOT NULL,
  `cust_name` varchar(255) NOT NULL,
  `cust_mobile` varchar(15) NOT NULL,
  `cust_mobile2` varchar(15) NOT NULL,
  `building_no` varchar(100) NOT NULL,
  `street` varchar(200) NOT NULL,
  `area` varchar(200) NOT NULL,
  `city` varchar(100) NOT NULL,
  `address` varchar(500) NOT NULL,
  `district` varchar(100) NOT NULL,
  `pincode` varchar(20) NOT NULL,
  `image` longblob NOT NULL,
  `aadhar_no` varchar(50) NOT NULL,
  `pan_no` varchar(50) NOT NULL,
  `latitude` decimal(10,0) NOT NULL,
  `longitude` decimal(10,0) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `daily_entry`
--

CREATE TABLE `daily_entry` (
  `id` int NOT NULL,
  `com_id` int NOT NULL,
  `uid` int NOT NULL,
  `agent_id` varchar(11) NOT NULL,
  `purpose` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `voucher_id` varchar(100) NOT NULL,
  `advance_id` varchar(11) NOT NULL,
  `date` date NOT NULL,
  `from_ledger` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `to_ledger` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `remark` text,
  `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_0900_ai_ci;

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

--
-- Table structure for table `deposit_entry`
--

CREATE TABLE `deposit_entry` (
  `id` int NOT NULL,
  `deposit_no` varchar(50) NOT NULL,
  `profile_image` varchar(255) DEFAULT NULL,
  `com_id` int NOT NULL,
  `uid` int NOT NULL,
  `name` varchar(100) NOT NULL,
  `mobile_no` varchar(15) DEFAULT NULL,
  `pan_no` varchar(20) DEFAULT NULL,
  `deposit_date` date NOT NULL,
  `exist_flag` tinyint(1) DEFAULT '0',
  `address` varchar(255) DEFAULT NULL,
  `deposit_amt` decimal(12,2) NOT NULL,
  `interest_percent` decimal(5,2) DEFAULT NULL,
  `deposit_type` varchar(50) DEFAULT NULL,
  `ledger_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `cheque_no` varchar(50) DEFAULT NULL,
  `bank_name` varchar(100) DEFAULT NULL,
  `interest_type` varchar(50) DEFAULT NULL,
  `cheque_date` date DEFAULT NULL,
  `int_amt` decimal(12,2) DEFAULT NULL,
  `type` varchar(50) DEFAULT NULL,
  `remarks` text,
  `status` varchar(20) NOT NULL,
  `signature` blob,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `expense`
--

CREATE TABLE `expense` (
  `id` int NOT NULL,
  `com_id` int NOT NULL,
  `agent_id` int NOT NULL,
  `expense_date` date NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `reason` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `ledger_day_wise`
--

CREATE TABLE `ledger_day_wise` (
  `id` int NOT NULL,
  `com_id` varchar(11) NOT NULL,
  `uid` varchar(11) NOT NULL,
  `date` date NOT NULL,
  `ledger` varchar(200) NOT NULL,
  `particulars` varchar(200) NOT NULL,
  `voucher_type` varchar(200) NOT NULL,
  `voucher_number` varchar(200) NOT NULL,
  `ledger_name` varchar(100) NOT NULL,
  `credit` decimal(10,0) NOT NULL,
  `debit` decimal(10,0) NOT NULL,
  `createdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `ledger_entry`
--

CREATE TABLE `ledger_entry` (
  `id` int NOT NULL,
  `uid` int NOT NULL,
  `com_id` int NOT NULL,
  `date` date NOT NULL,
  `ledger_name` varchar(255) NOT NULL,
  `ledger_under` varchar(100) NOT NULL,
  `opening` decimal(10,0) NOT NULL,
  `acc_type` varchar(50) NOT NULL,
  `status` varchar(50) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `ledger_under`
--

CREATE TABLE `ledger_under` (
  `id` int NOT NULL,
  `com_id` int NOT NULL,
  `under_name` varchar(255) NOT NULL,
  `createdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `line_entry`
--

CREATE TABLE `line_entry` (
  `id` int NOT NULL,
  `com_id` int NOT NULL,
  `area` varchar(200) NOT NULL,
  `line` varchar(200) NOT NULL,
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `loan_entry`
--

CREATE TABLE `loan_entry` (
  `id` int NOT NULL,
  `loan_id` varchar(50) NOT NULL,
  `com_id` int NOT NULL,
  `loan_date` date NOT NULL,
  `cust_id` int NOT NULL,
  `cus_name` varchar(100) NOT NULL,
  `cust_mobile` varchar(15) NOT NULL,
  `cust_contact` varchar(15) NOT NULL,
  `aadhaar_no` varchar(100) NOT NULL,
  `cust_address` varchar(500) NOT NULL,
  `cust_address2` varchar(500) NOT NULL,
  `area` varchar(255) NOT NULL,
  `loan_type` varchar(20) NOT NULL,
  `loan_amt` decimal(10,0) NOT NULL,
  `dues` varchar(50) NOT NULL,
  `interest` decimal(10,0) NOT NULL,
  `interest_amt` decimal(10,0) NOT NULL,
  `ledger` varchar(50) NOT NULL,
  `due_schedule` json DEFAULT NULL,
  `createdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `total_amt` decimal(10,0) NOT NULL,
  `per_due_amt` decimal(10,0) NOT NULL,
  `next_due_date` date NOT NULL,
  `last_due_date` date NOT NULL,
  `pre_interest` varchar(50) NOT NULL,
  `extra_amount` varchar(200) NOT NULL,
  `status` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `loan_receipt`
--

CREATE TABLE `loan_receipt` (
  `id` int NOT NULL,
  `com_id` int NOT NULL,
  `loan_id` varchar(50) NOT NULL,
  `rec_no` varchar(50) NOT NULL,
  `rec_date` date NOT NULL,
  `cust_id` int NOT NULL,
  `cust_name` varchar(255) NOT NULL,
  `cust_mobile` varchar(15) NOT NULL,
  `num_dues` int NOT NULL,
  `due_amnt` decimal(10,0) NOT NULL,
  `paid_amnt` decimal(10,0) NOT NULL,
  `paid_dues` int NOT NULL,
  `balance_dues` int NOT NULL,
  `pending_dues` int NOT NULL,
  `ledger_name` varchar(110) NOT NULL,
  `next_date` date NOT NULL,
  `remark` text NOT NULL,
  `agent` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `loan_type`
--

CREATE TABLE `loan_type` (
  `id` int NOT NULL,
  `com_id` int NOT NULL,
  `loan_type` varchar(50) NOT NULL,
  `due_count` varchar(200) NOT NULL,
  `createdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `mask`
--

CREATE TABLE `mask` (
  `id` int NOT NULL,
  `com_id` int NOT NULL,
  `status` enum('yes','no') DEFAULT 'yes',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `payment`
--

CREATE TABLE `payment` (
  `id` int NOT NULL,
  `payment_no` varchar(50) NOT NULL,
  `com_id` int NOT NULL,
  `deposit_no` int DEFAULT NULL,
  `date` date DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `mobile_no` varchar(15) DEFAULT NULL,
  `add_less` enum('Add','Less') DEFAULT 'Less',
  `principal_amt` decimal(15,2) DEFAULT '0.00',
  `int_pay_for` varchar(50) DEFAULT NULL,
  `amount` decimal(15,2) DEFAULT '0.00',
  `int_amt` decimal(15,2) DEFAULT '0.00',
  `pay_amt` decimal(15,2) DEFAULT '0.00',
  `deposit_amt` decimal(15,2) DEFAULT '0.00',
  `interest_type` varchar(50) DEFAULT NULL,
  `deposit_date` date DEFAULT NULL,
  `amt` decimal(15,2) DEFAULT '0.00',
  `from_date` date DEFAULT NULL,
  `to_date` date DEFAULT NULL,
  `cash_ac` varchar(100) DEFAULT NULL,
  `total_amount` decimal(15,2) DEFAULT '0.00',
  `remarks` text,
  `close_flag` tinyint(1) DEFAULT '0',
  `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_0900_ai_ci;

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

--
-- Table structure for table `payment_entry`
--

CREATE TABLE `payment_entry` (
  `id` int NOT NULL,
  `com_id` int NOT NULL,
  `payment_no` varchar(50) NOT NULL,
  `deposit_no` varchar(50) NOT NULL,
  `customer_name` varchar(150) NOT NULL,
  `mobile` varchar(20) DEFAULT NULL,
  `deposit_amount` decimal(12,2) DEFAULT '0.00',
  `deposit_type` varchar(50) DEFAULT NULL,
  `interest_type` varchar(50) DEFAULT NULL,
  `interest_percent` decimal(6,2) DEFAULT '0.00',
  `interest_deposit_amt` decimal(12,2) DEFAULT '0.00',
  `deposit_date` date DEFAULT NULL,
  `date` date DEFAULT NULL,
  `add_less` varchar(10) DEFAULT NULL,
  `principal_amt` decimal(12,2) DEFAULT '0.00',
  `int_pay_months` int DEFAULT '0',
  `int_pay_days` int DEFAULT '0',
  `int_pay_amount` decimal(12,2) DEFAULT '0.00',
  `pay_amt` decimal(12,2) DEFAULT '0.00',
  `total_amount` decimal(12,2) DEFAULT '0.00',
  `from_date` date DEFAULT NULL,
  `to_date` date DEFAULT NULL,
  `next_due_date` date DEFAULT NULL,
  `remarks` text,
  `ledger_ac` varchar(150) DEFAULT NULL,
  `is_close` tinyint(1) DEFAULT '0',
  `createdOn` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `primary_account`
--

CREATE TABLE `primary_account` (
  `id` int NOT NULL,
  `com_id` int NOT NULL,
  `uid` int NOT NULL,
  `ledger_under` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ledger_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `create_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modify_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `registration`
--

CREATE TABLE `registration` (
  `id` int NOT NULL,
  `com_name` varchar(255) NOT NULL,
  `mob_num` varchar(15) NOT NULL,
  `email` varchar(255) NOT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `report_time`
--

CREATE TABLE `report_time` (
  `id` int NOT NULL,
  `com_id` int NOT NULL,
  `time_from` time NOT NULL,
  `uid` int NOT NULL,
  `time_to` time NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

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

CREATE TABLE `settings` (
  `id` int NOT NULL,
  `key` varchar(100) NOT NULL,
  `value` text,
  `description` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `staff`
--

CREATE TABLE `staff` (
  `uid` int NOT NULL,
  `com_id` int NOT NULL,
  `reg_id` varchar(50) NOT NULL,
  `name` varchar(200) NOT NULL,
  `user_type` varchar(200) NOT NULL,
  `user_name` varchar(200) NOT NULL,
  `password` varchar(300) NOT NULL,
  `permissions` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `fcm_token` varchar(500) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `staff_advance`
--

CREATE TABLE `staff_advance` (
  `id` int NOT NULL,
  `uid` int NOT NULL,
  `com_id` int NOT NULL,
  `staff_name` varchar(150) NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `ledger` varchar(150) NOT NULL,
  `source` varchar(20) NOT NULL,
  `date` date NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'given',
  `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_0900_ai_ci;

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

--
-- Table structure for table `staff_check_in_out`
--

CREATE TABLE `staff_check_in_out` (
  `id` int NOT NULL,
  `staff_id` int NOT NULL,
  `com_id` int NOT NULL,
  `check_in_time` datetime NOT NULL,
  `check_out_time` datetime DEFAULT NULL,
  `check_in_location` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `check_out_location` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `check_in_latitude` decimal(10,8) DEFAULT NULL,
  `check_in_longitude` decimal(11,8) DEFAULT NULL,
  `check_out_latitude` decimal(10,8) DEFAULT NULL,
  `check_out_longitude` decimal(11,8) DEFAULT NULL,
  `status` enum('checked_in','checked_out') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'checked_in',
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `staff_salary`
--

CREATE TABLE `staff_salary` (
  `id` int UNSIGNED NOT NULL,
  `com_id` int UNSIGNED NOT NULL COMMENT 'Company ID',
  `uid` int UNSIGNED NOT NULL COMMENT 'Staff / Agent ID',
  `source` enum('staff','agent') NOT NULL COMMENT 'Entry source',
  `name` varchar(100) NOT NULL COMMENT 'Staff / Agent name',
  `salary_amount` decimal(10,2) NOT NULL COMMENT 'Total salary',
  `advance_deduction` decimal(10,2) DEFAULT '0.00' COMMENT 'Advance deducted',
  `payable_amount` decimal(10,2) NOT NULL COMMENT 'Salary after deduction',
  `ledger` varchar(100) NOT NULL COMMENT 'Ledger account',
  `notes` text,
  `salary_date` date NOT NULL COMMENT 'Salary date',
  `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_0900_ai_ci;

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

--
-- Table structure for table `suspense_entries`
--

CREATE TABLE `suspense_entries` (
  `id` int NOT NULL,
  `com_id` int NOT NULL,
  `date` date NOT NULL,
  `loan_no` varchar(50) NOT NULL,
  `party_name` varchar(150) NOT NULL,
  `rec_no` varchar(50) DEFAULT NULL,
  `amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `status` varchar(50) DEFAULT 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_0900_ai_ci;

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

--
-- Table structure for table `voucher`
--

CREATE TABLE `voucher` (
  `id` int NOT NULL,
  `uid` varchar(11) NOT NULL,
  `com_id` varchar(11) NOT NULL,
  `voucher_id` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `date` date NOT NULL,
  `from` varchar(45) NOT NULL,
  `to` varchar(45) NOT NULL,
  `purpose` varchar(45) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `notes` varchar(200) NOT NULL,
  `photo_url` varchar(500) DEFAULT NULL,
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `year_posting`
--

CREATE TABLE `year_posting` (
  `id` int NOT NULL,
  `uid` varchar(11) NOT NULL,
  `com_id` varchar(11) NOT NULL,
  `year` varchar(11) NOT NULL,
  `status` varchar(45) NOT NULL,
  `create_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `agent_attendance`
--
ALTER TABLE `agent_attendance`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_agent_id` (`agent_id`),
  ADD KEY `idx_com_id` (`com_id`),
  ADD KEY `idx_work_date` (`work_date`);

--
-- Indexes for table `agent_location_tracking`
--
ALTER TABLE `agent_location_tracking`
  ADD PRIMARY KEY (`id`),
  ADD KEY `agent_id` (`agent_id`),
  ADD KEY `com_id` (`com_id`),
  ADD KEY `receipt_id` (`receipt_id`),
  ADD KEY `loan_id` (`loan_id`),
  ADD KEY `activity_type` (`activity_type`),
  ADD KEY `created_at` (`created_at`);

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

--
-- Indexes for table `audit_log`
--
ALTER TABLE `audit_log`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_audit_entity` (`entity`,`entity_id`,`created_at`),
  ADD KEY `idx_audit_company` (`company_id`,`created_at`);

--
-- Indexes for table `collection_agent`
--
ALTER TABLE `collection_agent`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_device_id` (`device_id`),
  ADD UNIQUE KEY `unique_auth_token` (`auth_token`);

--
-- Indexes for table `collection_agent_otp`
--
ALTER TABLE `collection_agent_otp`
  ADD PRIMARY KEY (`id`),
  ADD KEY `mobile_no_idx` (`mobile_no`);

--
-- Indexes for table `company`
--
ALTER TABLE `company`
  ADD PRIMARY KEY (`com_id`);

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

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

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

--
-- Indexes for table `expense`
--
ALTER TABLE `expense`
  ADD PRIMARY KEY (`id`),
  ADD KEY `com_id` (`com_id`),
  ADD KEY `agent_id` (`agent_id`),
  ADD KEY `expense_date` (`expense_date`);

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

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

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

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

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

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

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

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

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

--
-- Indexes for table `primary_account`
--
ALTER TABLE `primary_account`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_company_ledger` (`com_id`,`ledger_under`,`ledger_name`),
  ADD KEY `idx_com_id` (`com_id`),
  ADD KEY `idx_ledger_under` (`ledger_under`),
  ADD KEY `idx_ledger_name` (`ledger_name`),
  ADD KEY `idx_com_id_ledger_under` (`com_id`,`ledger_under`);

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

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

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

--
-- Indexes for table `staff`
--
ALTER TABLE `staff`
  ADD PRIMARY KEY (`uid`),
  ADD KEY `idx_staff_fcm_token` (`fcm_token`);

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

--
-- Indexes for table `staff_check_in_out`
--
ALTER TABLE `staff_check_in_out`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_staff_id` (`staff_id`),
  ADD KEY `idx_com_id` (`com_id`),
  ADD KEY `idx_check_in_time` (`check_in_time`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `staff_salary`
--
ALTER TABLE `staff_salary`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uniq_salary` (`com_id`,`uid`,`source`,`salary_date`),
  ADD KEY `idx_company` (`com_id`),
  ADD KEY `idx_user` (`uid`),
  ADD KEY `idx_source` (`source`),
  ADD KEY `idx_salary_date` (`salary_date`);

--
-- Indexes for table `suspense_entries`
--
ALTER TABLE `suspense_entries`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_com_date` (`com_id`,`date`),
  ADD KEY `idx_com_loan` (`com_id`,`loan_no`);

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

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

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `agent_attendance`
--
ALTER TABLE `agent_attendance`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `agent_location_tracking`
--
ALTER TABLE `agent_location_tracking`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `area`
--
ALTER TABLE `area`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `audit_log`
--
ALTER TABLE `audit_log`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `collection_agent`
--
ALTER TABLE `collection_agent`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `collection_agent_otp`
--
ALTER TABLE `collection_agent_otp`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `company`
--
ALTER TABLE `company`
  MODIFY `com_id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `customers`
--
ALTER TABLE `customers`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `daily_entry`
--
ALTER TABLE `daily_entry`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `deposit_entry`
--
ALTER TABLE `deposit_entry`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `expense`
--
ALTER TABLE `expense`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `ledger_day_wise`
--
ALTER TABLE `ledger_day_wise`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `ledger_entry`
--
ALTER TABLE `ledger_entry`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `ledger_under`
--
ALTER TABLE `ledger_under`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `line_entry`
--
ALTER TABLE `line_entry`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `loan_entry`
--
ALTER TABLE `loan_entry`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `loan_receipt`
--
ALTER TABLE `loan_receipt`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `loan_type`
--
ALTER TABLE `loan_type`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `payment`
--
ALTER TABLE `payment`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `payment_entry`
--
ALTER TABLE `payment_entry`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `primary_account`
--
ALTER TABLE `primary_account`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `registration`
--
ALTER TABLE `registration`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `report_time`
--
ALTER TABLE `report_time`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `settings`
--
ALTER TABLE `settings`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `staff`
--
ALTER TABLE `staff`
  MODIFY `uid` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `staff_advance`
--
ALTER TABLE `staff_advance`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `staff_check_in_out`
--
ALTER TABLE `staff_check_in_out`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `staff_salary`
--
ALTER TABLE `staff_salary`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `suspense_entries`
--
ALTER TABLE `suspense_entries`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `voucher`
--
ALTER TABLE `voucher`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `year_posting`
--
ALTER TABLE `year_posting`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `agent_attendance`
--
ALTER TABLE `agent_attendance`
  ADD CONSTRAINT `fk_attendance_agent` FOREIGN KEY (`agent_id`) REFERENCES `collection_agent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_attendance_company` FOREIGN KEY (`com_id`) REFERENCES `company` (`com_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `agent_location_tracking`
--
ALTER TABLE `agent_location_tracking`
  ADD CONSTRAINT `fk_tracking_agent` FOREIGN KEY (`agent_id`) REFERENCES `collection_agent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_tracking_receipt` FOREIGN KEY (`receipt_id`) REFERENCES `loan_receipt` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Constraints for table `staff_check_in_out`
--
ALTER TABLE `staff_check_in_out`
  ADD CONSTRAINT `fk_staff_check_in_out_company` FOREIGN KEY (`com_id`) REFERENCES `company` (`com_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_staff_check_in_out_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE;
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 */;