-- phpMyAdmin SQL Dump
-- version 5.0.2
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Nov 09, 2025 at 06:34 AM
-- Server version: 8.0.21
-- PHP Version: 7.4.9

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: `uzrs-inventorydb`
--

DELIMITER $$
--
-- Procedures
--
DROP PROCEDURE IF EXISTS `sp_calculate_loyalty_points`$$
CREATE PROCEDURE `sp_calculate_loyalty_points` (IN `p_customer_id` INT, IN `p_sale_amount` DECIMAL(10,2), IN `p_sale_id` INT, IN `p_user_id` INT)  BEGIN
    DECLARE v_points_per_100 DECIMAL(10,2);
    DECLARE v_points_earned DECIMAL(10,2);
    DECLARE v_current_balance DECIMAL(10,2);
    
    SELECT CAST(SettingValue AS DECIMAL(10,2)) INTO v_points_per_100
    FROM tblsettings_loyalty
    WHERE SettingKey = 'points_per_100';
    
    SET v_points_earned = FLOOR(p_sale_amount / 100) * v_points_per_100;
    
    SELECT COALESCE(LoyaltyPoints, 0) INTO v_current_balance
    FROM tblcustomers
    WHERE ID = p_customer_id;
    
    IF v_points_earned > 0 THEN
        INSERT INTO tblcustomer_loyalty_points 
        (CustomerID, TransactionType, Points, SaleID, CurrentBalance, CreatedBy, Notes)
        VALUES 
        (p_customer_id, 'EARNED', v_points_earned, p_sale_id, 
         v_current_balance + v_points_earned, p_user_id,
         CONCAT('Points earned from sale #', p_sale_id));
        
        UPDATE tblcustomers
        SET LoyaltyPoints = LoyaltyPoints + v_points_earned,
            TotalPointsEarned = TotalPointsEarned + v_points_earned
        WHERE ID = p_customer_id;
    END IF;
END$$

DELIMITER ;

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

--
-- Table structure for table `blfont_settings`
--

DROP TABLE IF EXISTS `blfont_settings`;
CREATE TABLE IF NOT EXISTS `blfont_settings` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `StoreCode` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'ST0002',
  `CompanyNameFont` varchar(100) COLLATE utf8mb4_general_ci DEFAULT 'Arial',
  `CompanyNameSize` int DEFAULT '20',
  `AddressFont` varchar(100) COLLATE utf8mb4_general_ci DEFAULT 'Arial',
  `AddressSize` int DEFAULT '10',
  `ContactFont` varchar(100) COLLATE utf8mb4_general_ci DEFAULT 'Arial',
  `ContactSize` int DEFAULT '10',
  `InvoiceTitleFont` varchar(100) COLLATE utf8mb4_general_ci DEFAULT 'Arial',
  `InvoiceTitleSize` int DEFAULT '16',
  `SectionTitleFont` varchar(100) COLLATE utf8mb4_general_ci DEFAULT 'Arial',
  `SectionTitleSize` int DEFAULT '11',
  `TableHeaderFont` varchar(100) COLLATE utf8mb4_general_ci DEFAULT 'Arial',
  `TableHeaderSize` int DEFAULT '10',
  `TableBodyFont` varchar(100) COLLATE utf8mb4_general_ci DEFAULT 'Arial',
  `TableBodySize` int DEFAULT '10',
  `DeclarationFont` varchar(100) COLLATE utf8mb4_general_ci DEFAULT 'Arial',
  `DeclarationSize` int DEFAULT '9',
  `FooterFont` varchar(100) COLLATE utf8mb4_general_ci DEFAULT 'Arial',
  `FooterSize` int DEFAULT '11',
  `DateAdded` datetime DEFAULT NULL,
  `LastUpdated` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `StoreCode` (`StoreCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `tbladmin`
--

DROP TABLE IF EXISTS `tbladmin`;
CREATE TABLE IF NOT EXISTS `tbladmin` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `AdminName` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `AdminuserName` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `MobileNumber` int NOT NULL,
  `Email` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `Password` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `AdminRegdate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `idx_username` (`AdminuserName`),
  KEY `idx_email` (`Email`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tbladmin`
--

INSERT INTO `tbladmin` (`ID`, `AdminName`, `AdminuserName`, `MobileNumber`, `Email`, `Password`, `AdminRegdate`) VALUES
(2, 'Admin', 'admin', 1234567890, 'admin@gmail.com', 'f925916e2754e5e03f75dd58a5733251', '2021-04-19 18:30:00');

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

--
-- Table structure for table `tblbrands`
--

DROP TABLE IF EXISTS `tblbrands`;
CREATE TABLE IF NOT EXISTS `tblbrands` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `BrandName` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `BrandCode` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Manufacturer` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `ContactInfo` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Description` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Status` enum('Active','Inactive') COLLATE utf8mb4_general_ci DEFAULT 'Active',
  `DateAdded` datetime DEFAULT CURRENT_TIMESTAMP,
  `LastUpdated` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `BrandName` (`BrandName`)
) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tblbrands`
--

INSERT INTO `tblbrands` (`ID`, `BrandName`, `BrandCode`, `Manufacturer`, `ContactInfo`, `Description`, `Status`, `DateAdded`, `LastUpdated`) VALUES
(15, 'Generic', 'BRD-GEN', NULL, NULL, 'Generic/No Brand', 'Active', '2025-11-03 14:20:12', NULL),
(16, 'Local', 'BRD-LOC', NULL, NULL, 'Local Products', 'Active', '2025-11-03 14:20:12', NULL),
(17, 'Premium', 'BRD-PREM', NULL, NULL, 'Premium Brand Products', 'Active', '2025-11-03 14:20:12', NULL);

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

--
-- Table structure for table `tblcategories`
--

DROP TABLE IF EXISTS `tblcategories`;
CREATE TABLE IF NOT EXISTS `tblcategories` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `CategoryName` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `CategoryCode` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Description` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Status` enum('Active','Inactive') COLLATE utf8mb4_general_ci DEFAULT 'Active',
  `DateAdded` datetime DEFAULT CURRENT_TIMESTAMP,
  `LastUpdated` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `CategoryName` (`CategoryName`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tblcategories`
--

INSERT INTO `tblcategories` (`ID`, `CategoryName`, `CategoryCode`, `Description`, `Status`, `DateAdded`, `LastUpdated`) VALUES
(17, 'Groceries', 'CAT-GRO', 'Grocery items including food, beverages, and household essentials', 'Active', '2025-11-03 14:20:12', NULL),
(18, 'Clothings', 'CAT-CLO', 'Clothing and apparel items', 'Active', '2025-11-03 14:20:12', NULL),
(19, 'Gifts', 'CAT-GIF', 'Gift items and decorative products', 'Active', '2025-11-03 14:20:12', NULL),
(20, 'cattt', '', '', 'Active', '2025-11-06 21:54:37', NULL);

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

--
-- Table structure for table `tblcustomers`
--

DROP TABLE IF EXISTS `tblcustomers`;
CREATE TABLE IF NOT EXISTS `tblcustomers` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `CustomerID` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `CustomerCode` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `CustomerName` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `ContactPerson` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Mobile` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Email` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Address` text COLLATE utf8mb4_general_ci,
  `City` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `State` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `PinCode` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `GSTNumber` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `PANNumber` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `CustomerType` varchar(50) COLLATE utf8mb4_general_ci DEFAULT 'Individual',
  `CreditLimit` decimal(15,2) DEFAULT '0.00',
  `CreditDays` int DEFAULT '0',
  `CurrentBalance` decimal(15,2) DEFAULT '0.00',
  `LoyaltyPoints` decimal(10,2) DEFAULT '0.00',
  `TotalPointsEarned` decimal(10,2) DEFAULT '0.00',
  `TotalPointsRedeemed` decimal(10,2) DEFAULT '0.00',
  `Status` varchar(20) COLLATE utf8mb4_general_ci DEFAULT 'Active',
  `IsActive` tinyint(1) DEFAULT '1',
  `DateAdded` datetime DEFAULT CURRENT_TIMESTAMP,
  `LastUpdated` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `CustomerID` (`CustomerID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tblcustomers`
--

INSERT INTO `tblcustomers` (`ID`, `CustomerID`, `CustomerCode`, `CustomerName`, `ContactPerson`, `Mobile`, `Email`, `Address`, `City`, `State`, `PinCode`, `GSTNumber`, `PANNumber`, `CustomerType`, `CreditLimit`, `CreditDays`, `CurrentBalance`, `LoyaltyPoints`, `TotalPointsEarned`, `TotalPointsRedeemed`, `Status`, `IsActive`, `DateAdded`) VALUES
(1, 'CUST001', 'C0001', 'Jegan Sekar', 'Jegan Sekar', '08883888012', 'uzrssoft2016@gmail.com', 'Ramuni Nagar', 'Madurai', 'Tamil Nadu', '625532', '', '', 'Individual', '0.00', 0, '750.00', '388.00', '388.00', '0.00', 'Active', 1, '2025-11-04 02:33:25');

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

--
-- Table structure for table `tblcustomer_accounts`
--

DROP TABLE IF EXISTS `tblcustomer_accounts`;
CREATE TABLE IF NOT EXISTS `tblcustomer_accounts` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `CustomerID` int NOT NULL,
  `InvoiceNumber` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `TransactionType` varchar(50) COLLATE utf8mb4_general_ci NOT NULL COMMENT '??????????????? (Debit) or ???????????? (Credit)',
  `TransactionDate` date NOT NULL,
  `Amount` decimal(15,2) NOT NULL,
  `BalanceAmount` decimal(15,2) NOT NULL,
  `Description` text COLLATE utf8mb4_general_ci,
  `PaymentMode` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `ReferenceNumber` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `AddedBy` int NOT NULL,
  `CreatedDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `idx_customer` (`CustomerID`),
  KEY `idx_invoice` (`InvoiceNumber`),
  KEY `idx_date` (`TransactionDate`),
  KEY `idx_type` (`TransactionType`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tblcustomer_accounts`
--

INSERT INTO `tblcustomer_accounts` (`ID`, `CustomerID`, `InvoiceNumber`, `TransactionType`, `TransactionDate`, `Amount`, `BalanceAmount`, `Description`, `PaymentMode`, `ReferenceNumber`, `AddedBy`, `CreatedDate`) VALUES
(13, 1, 'GST/2025-26/0001', 'Debit', '2025-11-04', '1500.00', '1500.00', 'Sales Bill', 'Cash', NULL, 1, '2025-11-04 13:58:09'),
(14, 1, 'PAY-GST/2025-26/0001', 'Credit', '2025-11-04', '1500.00', '1500.00', 'Payment for Bill GST/2025-26/0001', 'Cash', 'GST/2025-26/0001', 1, '2025-11-04 13:58:09'),
(15, 1, 'GST/2025-26/0002', 'Debit', '2025-11-04', '2835.00', '2835.00', 'Sales Bill', 'Cash', NULL, 1, '2025-11-04 14:20:52'),
(16, 1, 'PAY-GST/2025-26/0002', 'Credit', '2025-11-04', '2835.00', '2835.00', 'Payment for Bill GST/2025-26/0002', 'Cash', 'GST/2025-26/0002', 1, '2025-11-04 14:20:52'),
(17, 1, 'GST/2025-26/0003', 'Debit', '2025-11-04', '1200.00', '1200.00', 'Sales Bill', 'Cash:₹500.00 + Credit:₹700.00', NULL, 1, '2025-11-04 15:12:04'),
(18, 1, 'PAY-GST/2025-26/0003', 'Credit', '2025-11-04', '500.00', '500.00', 'Payment for Bill GST/2025-26/0003', 'Cash:₹500.00 + Credit:₹700.00', 'GST/2025-26/0003', 1, '2025-11-04 15:12:04'),
(19, 1, 'PAY-GST/2025-26/0003-1762269141', 'Credit', '2025-11-04', '200.00', '200.00', 'Payment for Invoice GST/2025-26/0003', 'Cash', '', 1, '2025-11-04 15:12:21'),
(20, 1, 'GST/2025-26/0004', 'Debit', '2025-11-04', '3750.00', '3750.00', 'Sales Bill', 'Cash:₹2000.00 + Credit:₹1750.00', NULL, 1, '2025-11-04 15:12:38'),
(21, 1, 'PAY-GST/2025-26/0004', 'Credit', '2025-11-04', '2000.00', '2000.00', 'Payment for Bill GST/2025-26/0004', 'Cash:₹2000.00 + Credit:₹1750.00', 'GST/2025-26/0004', 1, '2025-11-04 15:12:38'),
(22, 1, 'PAY-GST/2025-26/0003-1762269173', 'Credit', '2025-11-04', '500.00', '500.00', 'Payment for Invoice GST/2025-26/0003', 'Cash', '', 1, '2025-11-04 15:12:53'),
(23, 1, 'PAY-GST/2025-26/0004-1762269183', 'Credit', '2025-11-04', '1000.00', '1000.00', 'Payment for Invoice GST/2025-26/0004', 'Cash', '', 1, '2025-11-04 15:13:03'),
(24, 1, 'GST/2025-26/0005', 'Debit', '2025-11-05', '12000.00', '12000.00', 'Sales Bill', 'Cash', NULL, 1, '2025-11-05 07:26:10'),
(25, 1, 'PAY-GST/2025-26/0005', 'Credit', '2025-11-05', '12000.00', '12000.00', 'Payment for Bill GST/2025-26/0005', 'Cash', 'GST/2025-26/0005', 1, '2025-11-05 07:26:10'),
(26, 1, 'GST/2025-26/0006', 'Debit', '2025-11-05', '1050.00', '1050.00', 'Sales Bill', 'Cash', NULL, 1, '2025-11-05 10:01:11'),
(27, 1, 'PAY-GST/2025-26/0006', 'Credit', '2025-11-05', '1050.00', '1050.00', 'Payment for Bill GST/2025-26/0006', 'Cash', 'GST/2025-26/0006', 1, '2025-11-05 10:01:11');

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

--
-- Table structure for table `tblcustomer_loyalty_points`
--

DROP TABLE IF EXISTS `tblcustomer_loyalty_points`;
CREATE TABLE IF NOT EXISTS `tblcustomer_loyalty_points` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `CustomerID` int NOT NULL,
  `TransactionDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `TransactionType` enum('EARNED','REDEEMED','ADJUSTED','EXPIRED') COLLATE utf8mb4_general_ci DEFAULT 'EARNED',
  `Points` decimal(10,2) NOT NULL DEFAULT '0.00',
  `SaleID` int DEFAULT NULL,
  `Reference` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Notes` text COLLATE utf8mb4_general_ci,
  `CurrentBalance` decimal(10,2) DEFAULT '0.00',
  `CreatedBy` int NOT NULL,
  `CreatedAt` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `idx_customer` (`CustomerID`),
  KEY `idx_transaction_date` (`TransactionDate`),
  KEY `idx_sale_id` (`SaleID`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tblcustomer_loyalty_points`
--

INSERT INTO `tblcustomer_loyalty_points` (`ID`, `CustomerID`, `TransactionDate`, `TransactionType`, `Points`, `SaleID`, `Reference`, `Notes`, `CurrentBalance`, `CreatedBy`, `CreatedAt`) VALUES
(24, 1, '2025-11-04 19:28:09', 'EARNED', '15.00', 7, 'GST/2025-26/0001', 'Points earned from sale', '114.00', 1, '2025-11-04 13:58:09'),
(25, 1, '2025-11-04 19:50:52', 'EARNED', '28.00', 8, 'GST/2025-26/0002', 'Points earned from sale', '142.00', 1, '2025-11-04 14:20:52'),
(26, 1, '2025-11-04 20:42:04', 'EARNED', '12.00', 9, 'GST/2025-26/0003', 'Points earned from sale', '154.00', 1, '2025-11-04 15:12:04'),
(27, 1, '2025-11-04 20:42:38', 'EARNED', '37.00', 10, 'GST/2025-26/0004', 'Points earned from sale', '191.00', 1, '2025-11-04 15:12:38'),
(28, 1, '2025-11-05 12:56:10', 'EARNED', '120.00', 11, 'GST/2025-26/0005', 'Points earned from sale', '311.00', 1, '2025-11-05 07:26:10'),
(29, 1, '2025-11-05 15:31:11', 'EARNED', '10.00', 12, 'GST/2025-26/0006', 'Points earned from sale', '321.00', 1, '2025-11-05 10:01:11'),
(31, 1, '2025-11-06 16:11:41', 'EARNED', '15.00', 13, 'GST/2025-26/0007', 'Points earned from sale', '336.00', 1, '2025-11-06 10:41:41'),
(32, 1, '2025-11-06 21:32:34', 'EARNED', '21.00', 14, 'GST/2025-26/0008', 'Points earned from sale', '357.00', 1, '2025-11-06 16:02:34'),
(33, 1, '2025-11-06 21:42:53', 'EARNED', '30.00', 15, 'GST/2025-26/0009', 'Points earned from sale', '387.00', 1, '2025-11-06 16:12:53'),
(34, 1, '2025-11-06 21:47:23', 'EARNED', '1.00', 16, 'GST/2025-26/0010', 'Points earned from sale', '388.00', 1, '2025-11-06 16:17:23');

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

--
-- Table structure for table `tblexpenses`
--

DROP TABLE IF EXISTS `tblexpenses`;
CREATE TABLE IF NOT EXISTS `tblexpenses` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `ExpenseDate` date NOT NULL,
  `ExpenseCategory` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `ExpenseDescription` text COLLATE utf8mb4_general_ci,
  `Amount` decimal(10,2) NOT NULL,
  `PaymentMode` varchar(50) COLLATE utf8mb4_general_ci DEFAULT 'Cash',
  `Reference` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `CreatedBy` int NOT NULL,
  `CreatedAt` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `UpdatedAt` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `idx_expense_date` (`ExpenseDate`),
  KEY `idx_category` (`ExpenseCategory`),
  KEY `idx_created_by` (`CreatedBy`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tblexpenses`
--

INSERT INTO `tblexpenses` (`ID`, `ExpenseDate`, `ExpenseCategory`, `ExpenseDescription`, `Amount`, `PaymentMode`, `Reference`, `CreatedBy`, `CreatedAt`) VALUES
(1, '2025-11-05', 'Rent', '', '10000.00', 'Cash', '', 1, '2025-11-05 06:14:04'),
(2, '2025-11-05', 'Salaries', '', '1000.00', 'Cash', '', 1, '2025-11-05 06:14:11'),
(3, '2025-11-05', 'Transport', '', '100000.00', 'Cash', '', 1, '2025-11-05 06:14:18'),
(4, '2025-11-05', 'Marketing', '', '4500.00', 'Cash', '', 1, '2025-11-05 06:14:23');

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

--
-- Table structure for table `tblfont_settings`
--

DROP TABLE IF EXISTS `tblfont_settings`;
CREATE TABLE IF NOT EXISTS `tblfont_settings` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `StoreCode` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'ST0002',
  `CompanyNameFont` varchar(100) COLLATE utf8mb4_general_ci DEFAULT 'Arial',
  `CompanyNameSize` int DEFAULT '20',
  `CompanyNameColor` varchar(20) COLLATE utf8mb4_general_ci DEFAULT '#000000',
  `CompanyNameWeight` varchar(20) COLLATE utf8mb4_general_ci DEFAULT 'normal',
  `AddressFont` varchar(100) COLLATE utf8mb4_general_ci DEFAULT 'Arial',
  `AddressSize` int DEFAULT '10',
  `AddressColor` varchar(20) COLLATE utf8mb4_general_ci DEFAULT '#000000',
  `AddressWeight` varchar(20) COLLATE utf8mb4_general_ci DEFAULT 'normal',
  `ContactFont` varchar(100) COLLATE utf8mb4_general_ci DEFAULT 'Arial',
  `ContactSize` int DEFAULT '10',
  `ContactColor` varchar(20) COLLATE utf8mb4_general_ci DEFAULT '#000000',
  `ContactWeight` varchar(20) COLLATE utf8mb4_general_ci DEFAULT 'normal',
  `InvoiceTitleFont` varchar(100) COLLATE utf8mb4_general_ci DEFAULT 'Arial',
  `InvoiceTitleSize` int DEFAULT '16',
  `InvoiceTitleColor` varchar(20) COLLATE utf8mb4_general_ci DEFAULT '#000000',
  `InvoiceTitleWeight` varchar(20) COLLATE utf8mb4_general_ci DEFAULT 'normal',
  `SectionTitleFont` varchar(100) COLLATE utf8mb4_general_ci DEFAULT 'Arial',
  `SectionTitleSize` int DEFAULT '11',
  `SectionTitleColor` varchar(20) COLLATE utf8mb4_general_ci DEFAULT '#000000',
  `SectionTitleWeight` varchar(20) COLLATE utf8mb4_general_ci DEFAULT 'normal',
  `TableHeaderFont` varchar(100) COLLATE utf8mb4_general_ci DEFAULT 'Arial',
  `TableHeaderSize` int DEFAULT '10',
  `TableHeaderColor` varchar(20) COLLATE utf8mb4_general_ci DEFAULT '#000000',
  `TableHeaderWeight` varchar(20) COLLATE utf8mb4_general_ci DEFAULT 'normal',
  `TableHeaderBgColor` varchar(20) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '#f0f0f0',
  `TableBodyFont` varchar(100) COLLATE utf8mb4_general_ci DEFAULT 'Arial',
  `TableBodySize` int DEFAULT '10',
  `TableBodyColor` varchar(20) COLLATE utf8mb4_general_ci DEFAULT '#000000',
  `TableBodyWeight` varchar(20) COLLATE utf8mb4_general_ci DEFAULT 'normal',
  `DeclarationFont` varchar(100) COLLATE utf8mb4_general_ci DEFAULT 'Arial',
  `DeclarationSize` int DEFAULT '9',
  `DeclarationColor` varchar(20) COLLATE utf8mb4_general_ci DEFAULT '#000000',
  `DeclarationWeight` varchar(20) COLLATE utf8mb4_general_ci DEFAULT 'normal',
  `FooterFont` varchar(100) COLLATE utf8mb4_general_ci DEFAULT 'Arial',
  `FooterSize` int DEFAULT '11',
  `FooterColor` varchar(20) COLLATE utf8mb4_general_ci DEFAULT '#000000',
  `FooterWeight` varchar(20) COLLATE utf8mb4_general_ci DEFAULT 'normal',
  `DateAdded` datetime DEFAULT NULL,
  `LastUpdated` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `StoreCode` (`StoreCode`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tblfont_settings`
--

INSERT INTO `tblfont_settings` (`ID`, `StoreCode`, `CompanyNameFont`, `CompanyNameSize`, `CompanyNameColor`, `CompanyNameWeight`, `AddressFont`, `AddressSize`, `AddressColor`, `AddressWeight`, `ContactFont`, `ContactSize`, `ContactColor`, `ContactWeight`, `InvoiceTitleFont`, `InvoiceTitleSize`, `InvoiceTitleColor`, `InvoiceTitleWeight`, `SectionTitleFont`, `SectionTitleSize`, `SectionTitleColor`, `SectionTitleWeight`, `TableHeaderFont`, `TableHeaderSize`, `TableHeaderColor`, `TableHeaderWeight`, `TableHeaderBgColor`, `TableBodyFont`, `TableBodySize`, `TableBodyColor`, `TableBodyWeight`, `DeclarationFont`, `DeclarationSize`, `DeclarationColor`, `DeclarationWeight`, `FooterFont`, `FooterSize`, `FooterColor`, `FooterWeight`, `DateAdded`, `LastUpdated`) VALUES
(1, 'ST0002', 'Tahoma', 28, '#f40101', 'bold', 'Tahoma', 14, '#000000', 'normal', 'Tahoma', 12, '#000000', 'normal', 'Tahoma', 12, '#000000', 'normal', 'Tahoma', 12, '#000000', 'normal', 'Tahoma', 12, '#000000', 'bold', '#d8fbd5', 'Tahoma', 12, '#000000', 'normal', 'Tahoma', 11, '#000000', 'normal', 'Tahoma', 11, '#000000', 'normal', '2025-11-05 14:19:41', '2025-11-06 14:36:00');

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

--
-- Table structure for table `tblhold_bills`
--

DROP TABLE IF EXISTS `tblhold_bills`;
CREATE TABLE IF NOT EXISTS `tblhold_bills` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `HoldNumber` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `CustomerID` int DEFAULT NULL,
  `CustomerName` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `SalesType` varchar(50) COLLATE utf8mb4_general_ci DEFAULT 'GST',
  `InvoiceNumber` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `ItemsJSON` longtext COLLATE utf8mb4_general_ci NOT NULL,
  `SubTotal` decimal(10,2) DEFAULT '0.00',
  `TaxAmount` decimal(10,2) DEFAULT '0.00',
  `GrandTotal` decimal(10,2) DEFAULT '0.00',
  `Discount` decimal(10,2) DEFAULT '0.00',
  `RoundOff` decimal(10,2) DEFAULT '0.00',
  `TotalItems` int DEFAULT '0',
  `HeldBy` int NOT NULL,
  `HeldDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `Notes` text COLLATE utf8mb4_general_ci,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `HoldNumber` (`HoldNumber`),
  KEY `idx_held_by` (`HeldBy`),
  KEY `idx_hold_date` (`HeldDate`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `tblhold_purchase_bills`
--

DROP TABLE IF EXISTS `tblhold_purchase_bills`;
CREATE TABLE IF NOT EXISTS `tblhold_purchase_bills` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `HoldNumber` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `SupplierID` int DEFAULT NULL,
  `SupplierName` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `PurchaseType` varchar(50) COLLATE utf8mb4_general_ci DEFAULT 'GST',
  `InvoiceNumber` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `ItemsJSON` longtext COLLATE utf8mb4_general_ci NOT NULL,
  `SubTotal` decimal(10,2) DEFAULT '0.00',
  `TaxAmount` decimal(10,2) DEFAULT '0.00',
  `GrandTotal` decimal(10,2) DEFAULT '0.00',
  `Discount` decimal(10,2) DEFAULT '0.00',
  `RoundOff` decimal(10,2) DEFAULT '0.00',
  `TotalItems` int DEFAULT '0',
  `HeldBy` int NOT NULL,
  `HeldDate` datetime DEFAULT CURRENT_TIMESTAMP,
  `Notes` text COLLATE utf8mb4_general_ci,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `HoldNumber` (`HoldNumber`),
  KEY `idx_held_by` (`HeldBy`),
  KEY `idx_hold_date` (`HeldDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `tblinvoice_settings`
--

DROP TABLE IF EXISTS `tblinvoice_settings`;
CREATE TABLE IF NOT EXISTS `tblinvoice_settings` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `StoreCode` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `ShowSerialNumber` tinyint(1) DEFAULT '1',
  `ShowDescription` tinyint(1) DEFAULT '1',
  `OrderDescription` int DEFAULT '2',
  `OrderSerialNumber` int DEFAULT '1',
  `ShowHSN` tinyint(1) DEFAULT '1',
  `OrderHSN` int DEFAULT '2',
  `ShowQuantity` tinyint(1) DEFAULT '1',
  `OrderQuantity` int DEFAULT '3',
  `ShowMRP` tinyint(1) DEFAULT '1',
  `OrderMRP` int DEFAULT '4',
  `ShowPrice` tinyint(1) DEFAULT '1',
  `OrderPrice` int DEFAULT '5',
  `ShowDiscount` tinyint(1) DEFAULT '1',
  `OrderDiscount` int DEFAULT '6',
  `ShowBasicRate` tinyint(1) DEFAULT '1',
  `OrderBasicRate` int DEFAULT '7',
  `ShowTaxable` tinyint(1) DEFAULT '1',
  `OrderTaxable` int DEFAULT '8',
  `ShowGSTPercent` tinyint(1) DEFAULT '1',
  `OrderGSTPercent` int DEFAULT '9',
  `ShowGSTAmount` tinyint(1) DEFAULT '1',
  `OrderGSTAmount` int DEFAULT '10',
  `ShowTotal` tinyint(1) DEFAULT '1',
  `OrderTotal` int DEFAULT '11',
  `ShowShipTo` tinyint(1) DEFAULT '1',
  `ShowOtherDetails` tinyint(1) DEFAULT '1',
  `HeaderSr` varchar(50) COLLATE utf8mb4_general_ci DEFAULT 'Sr',
  `HeaderDescription` varchar(100) COLLATE utf8mb4_general_ci DEFAULT 'Goods & Service Description',
  `HeaderHSN` varchar(50) COLLATE utf8mb4_general_ci DEFAULT 'HSN',
  `HeaderQuantity` varchar(50) COLLATE utf8mb4_general_ci DEFAULT 'Quantity',
  `HeaderMRP` varchar(50) COLLATE utf8mb4_general_ci DEFAULT 'MRP',
  `HeaderPrice` varchar(50) COLLATE utf8mb4_general_ci DEFAULT 'Price',
  `HeaderDiscount` varchar(50) COLLATE utf8mb4_general_ci DEFAULT 'Discount',
  `HeaderBasicRate` varchar(50) COLLATE utf8mb4_general_ci DEFAULT 'Basic Rate',
  `HeaderTaxable` varchar(50) COLLATE utf8mb4_general_ci DEFAULT 'Taxable',
  `HeaderGSTPercent` varchar(50) COLLATE utf8mb4_general_ci DEFAULT '%',
  `HeaderGSTAmount` varchar(50) COLLATE utf8mb4_general_ci DEFAULT 'GST Amt.',
  `HeaderTotal` varchar(50) COLLATE utf8mb4_general_ci DEFAULT 'Total',
  `DeclarationText` text COLLATE utf8mb4_general_ci,
  `FooterText` text COLLATE utf8mb4_general_ci,
  `GstInvoiceTemplate` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'sale-invoice-template1.php',
  `NonGstInvoiceTemplate` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'nongst-sale-invoice-template1.php',
  `ShowDeclaration` tinyint(1) DEFAULT '1',
  `ShowFooter` tinyint(1) DEFAULT '1',
  `DateAdded` datetime DEFAULT CURRENT_TIMESTAMP,
  `LastUpdated` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `unique_store` (`StoreCode`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tblinvoice_settings`
--

INSERT INTO `tblinvoice_settings` (`ID`, `StoreCode`, `ShowSerialNumber`, `ShowDescription`, `OrderDescription`, `OrderSerialNumber`, `ShowHSN`, `OrderHSN`, `ShowQuantity`, `OrderQuantity`, `ShowMRP`, `OrderMRP`, `ShowPrice`, `OrderPrice`, `ShowDiscount`, `OrderDiscount`, `ShowBasicRate`, `OrderBasicRate`, `ShowTaxable`, `OrderTaxable`, `ShowGSTPercent`, `OrderGSTPercent`, `ShowGSTAmount`, `OrderGSTAmount`, `ShowTotal`, `OrderTotal`, `ShowShipTo`, `ShowOtherDetails`, `HeaderSr`, `HeaderDescription`, `HeaderHSN`, `HeaderQuantity`, `HeaderMRP`, `HeaderPrice`, `HeaderDiscount`, `HeaderBasicRate`, `HeaderTaxable`, `HeaderGSTPercent`, `HeaderGSTAmount`, `HeaderTotal`, `DeclarationText`, `FooterText`, `GstInvoiceTemplate`, `NonGstInvoiceTemplate`, `ShowDeclaration`, `ShowFooter`, `DateAdded`) VALUES
(1, 'ST0002', 1, 1, 2, 1, 1, 3, 1, 4, 1, 5, 1, 6, 1, 7, 1, 8, 1, 9, 1, 10, 1, 11, 1, 12, 1, 1, 'SlNo', 'Description', 'HSN', 'Qty', 'MRP', 'Rate', 'Disc', 'Basic Rs', 'Taxable Amt', 'GST%', 'Tax Amt', 'Total Amount', '', '', 'sale-invoice-template1.php', 'nongst-sale-invoice-template1.php', 1, 1, '2025-11-05 13:00:02');

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

--
-- Table structure for table `tblitems`
--

DROP TABLE IF EXISTS `tblitems`;
CREATE TABLE IF NOT EXISTS `tblitems` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `ItemCode` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `ItemName` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `ItemSearchName` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `ItemDescription2` text COLLATE utf8mb4_general_ci,
  `BrandID` int DEFAULT NULL,
  `CategoryID` int DEFAULT NULL,
  `SupplierID` int DEFAULT NULL,
  `UnitID` int DEFAULT NULL,
  `HSN` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `AlertQuantity` decimal(10,2) DEFAULT '0.00',
  `Barcode` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `ItemImage` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `TaxabilityType` enum('Taxable','Non-Taxable') COLLATE utf8mb4_general_ci DEFAULT 'Taxable',
  `TaxID` int DEFAULT NULL,
  `PurchasePrice` decimal(10,2) NOT NULL,
  `ProfitMarginPercent` decimal(10,2) DEFAULT NULL,
  `SalesPrice` decimal(10,2) DEFAULT NULL,
  `ShopRate` decimal(10,2) DEFAULT NULL,
  `WholesaleRate` decimal(10,2) DEFAULT NULL,
  `MRP` decimal(10,2) DEFAULT NULL,
  `LocationID` int DEFAULT NULL,
  `RackID` int DEFAULT NULL,
  `OpeningStock` decimal(10,2) DEFAULT '0.00',
  `CurrentStock` decimal(10,2) DEFAULT '0.00',
  `Status` enum('Active','Inactive') COLLATE utf8mb4_general_ci DEFAULT 'Active',
  `CreatedAt` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `LastUpdated` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ItemCode` (`ItemCode`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tblitems`
--

INSERT INTO `tblitems` (`ID`, `ItemCode`, `ItemName`, `ItemSearchName`, `ItemDescription2`, `BrandID`, `CategoryID`, `SupplierID`, `UnitID`, `HSN`, `AlertQuantity`, `Barcode`, `ItemImage`, `TaxabilityType`, `TaxID`, `PurchasePrice`, `ProfitMarginPercent`, `SalesPrice`, `ShopRate`, `WholesaleRate`, `MRP`, `LocationID`, `RackID`, `OpeningStock`, `CurrentStock`, `Status`, `CreatedAt`) VALUES
(1, 'ITEM000001', 'Test 1', NULL, NULL, 15, 18, 1, 47, '48219090', '10.00', '12345678', '', 'Taxable', 53, '100.00', '50.00', '150.00', '130.00', '120.00', '200.00', 24, 41, '10.00', '11.00', 'Active', '2025-11-04 12:20:36'),
(2, 'ITEM000002', 'Test3', NULL, NULL, 16, 18, 1, 49, '48219090', '10.00', '8901234560131', '', 'Taxable', 54, '200.00', '50.00', '300.00', '280.00', '270.00', '400.00', 22, 42, '10.00', '-21.00', 'Active', '2025-11-04 14:19:40'),
(3, 'ITEM000003', 'Test 4', NULL, NULL, 16, 19, 1, 48, '84433290', '100.00', 'PROD0026', '', 'Taxable', 55, '500.00', '50.00', '750.00', '700.00', '680.00', '1000.00', 24, 42, '10.00', '5.00', 'Active', '2025-11-04 14:20:29');

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

--
-- Table structure for table `tblitemvariants`
--

DROP TABLE IF EXISTS `tblitemvariants`;
CREATE TABLE IF NOT EXISTS `tblitemvariants` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `ItemID` int NOT NULL,
  `VariantName` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `VariantCode` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `SKU` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Barcode` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Price` decimal(10,2) DEFAULT NULL,
  `PurchasePrice` decimal(10,2) DEFAULT NULL,
  `SalesPrice` decimal(10,2) DEFAULT NULL,
  `ShopRate` decimal(10,2) DEFAULT NULL,
  `WholesaleRate` decimal(10,2) DEFAULT NULL,
  `MRP` decimal(10,2) DEFAULT NULL,
  `LocationID` int DEFAULT NULL,
  `RackID` int DEFAULT NULL,
  `OpeningStock` decimal(10,2) DEFAULT '0.00',
  `CurrentStock` decimal(10,2) DEFAULT '0.00',
  `AlertQuantity` decimal(10,2) DEFAULT '0.00',
  `Status` enum('Active','Inactive') COLLATE utf8mb4_general_ci DEFAULT 'Active',
  `CreatedAt` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `LastUpdated` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `tbllocations`
--

DROP TABLE IF EXISTS `tbllocations`;
CREATE TABLE IF NOT EXISTS `tbllocations` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `LocationName` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `LocationCode` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `LocationType` enum('Warehouse','Store','Site','Office','Other') COLLATE utf8mb4_general_ci DEFAULT 'Warehouse',
  `Address` text COLLATE utf8mb4_general_ci,
  `ContactPerson` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `PhoneNumber` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Status` enum('Active','Inactive') COLLATE utf8mb4_general_ci DEFAULT 'Active',
  `DateAdded` datetime DEFAULT CURRENT_TIMESTAMP,
  `LastUpdated` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `LocationName` (`LocationName`)
) ENGINE=MyISAM AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tbllocations`
--

INSERT INTO `tbllocations` (`ID`, `LocationName`, `LocationCode`, `LocationType`, `Address`, `ContactPerson`, `PhoneNumber`, `Status`, `DateAdded`, `LastUpdated`) VALUES
(21, 'Main Store', 'LOC-MAIN', 'Store', NULL, NULL, NULL, 'Active', '2025-11-03 14:20:12', NULL),
(22, 'Grocery Section', 'LOC-GRO', 'Store', NULL, NULL, NULL, 'Active', '2025-11-03 14:20:12', NULL),
(23, 'Clothing Section', 'LOC-CLO', 'Store', NULL, NULL, NULL, 'Active', '2025-11-03 14:20:12', NULL),
(24, 'Gift Section', 'LOC-GIF', 'Store', NULL, NULL, NULL, 'Active', '2025-11-03 14:20:12', NULL),
(25, 'Warehouse', 'LOC-WH', 'Warehouse', NULL, NULL, NULL, 'Active', '2025-11-03 14:20:12', NULL);

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

--
-- Table structure for table `tblpermissions`
--

DROP TABLE IF EXISTS `tblpermissions`;
CREATE TABLE IF NOT EXISTS `tblpermissions` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `PermissionName` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `PermissionCode` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `Category` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `CreatedDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `unique_permission_code` (`PermissionCode`),
  KEY `idx_category` (`Category`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tblpermissions`
--

INSERT INTO `tblpermissions` (`ID`, `PermissionName`, `PermissionCode`, `Category`, `Description`, `CreatedDate`) VALUES
(1, 'View Dashboard', 'view_dashboard', 'Dashboard', 'Access to main dashboard', '2025-10-13 05:10:10'),
(4, 'View Users', 'view_users', 'User Management', 'View list of users', '2025-10-13 05:10:10'),
(5, 'Create Users', 'create_users', 'User Management', 'Create new users', '2025-10-13 05:10:10'),
(6, 'Edit Users', 'edit_users', 'User Management', 'Edit existing users', '2025-10-13 05:10:10'),
(7, 'Delete Users', 'delete_users', 'User Management', 'Delete users', '2025-10-13 05:10:10'),
(8, 'View Roles', 'view_roles', 'Role Management', 'View list of roles', '2025-10-13 05:10:10'),
(9, 'Create Roles', 'create_roles', 'Role Management', 'Create new roles', '2025-10-13 05:10:10'),
(10, 'Edit Roles', 'edit_roles', 'Role Management', 'Edit existing roles', '2025-10-13 05:10:10'),
(11, 'Delete Roles', 'delete_roles', 'Role Management', 'Delete roles', '2025-10-13 05:10:10'),
(12, 'Assign Permissions', 'assign_permissions', 'Role Management', 'Assign permissions to roles', '2025-10-13 05:10:10'),
(13, 'View Branches', 'view_branches', 'Branch Management', 'View list of branches', '2025-10-13 05:10:10'),
(14, 'Create Branches', 'create_branches', 'Branch Management', 'Create new branches', '2025-10-13 05:10:10'),
(15, 'Edit Branches', 'edit_branches', 'Branch Management', 'Edit existing branches', '2025-10-13 05:10:10'),
(16, 'Delete Branches', 'delete_branches', 'Branch Management', 'Delete branches', '2025-10-13 05:10:10'),
(33, 'View Financial Reports', 'view_financial_reports', 'Reports', 'Access financial reports', '2025-10-13 05:10:10'),
(36, 'Export Reports', 'export_reports', 'Reports', 'Export reports to PDF/Excel', '2025-10-13 05:10:10');

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

--
-- Table structure for table `tblpos_settings`
--

DROP TABLE IF EXISTS `tblpos_settings`;
CREATE TABLE IF NOT EXISTS `tblpos_settings` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `SettingKey` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `SettingValue` text COLLATE utf8mb4_general_ci NOT NULL,
  `Description` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `UpdatedBy` int DEFAULT NULL,
  `UpdatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `CreatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `SettingKey` (`SettingKey`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tblpos_settings`
--

INSERT INTO `tblpos_settings` (`ID`, `SettingKey`, `SettingValue`, `Description`, `UpdatedBy`, `CreatedAt`) VALUES
(1, 'barcode_scan_mode', 'disabled', 'Auto-scan mode for barcode scanner (enabled/disabled)', 1, '2025-11-04 08:06:49'),
(2, 'language', 'en', 'Interface language preference (en/ta)', 1, '2025-11-04 08:11:48');

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

--
-- Table structure for table `tblpurchases`
--

DROP TABLE IF EXISTS `tblpurchases`;
CREATE TABLE IF NOT EXISTS `tblpurchases` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `InvoiceNumber` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `PurchaseType` enum('GST','NON-GST','IMPORT','GST-PURCHASE-RETURN','NON-GST-PURCHASE-RETURN','IMPORT-PURCHASE-RETURN') COLLATE utf8mb4_general_ci DEFAULT 'GST',
  `SupplierID` int NOT NULL,
  `PurchaseDate` date NOT NULL,
  `SubTotal` decimal(12,2) NOT NULL DEFAULT '0.00',
  `FreightAmount` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT 'Freight/Transport Amount',
  `TransportName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `VehicleNumber` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `TransportGSTPercent` decimal(5,2) DEFAULT '0.00',
  `TransportGSTType` enum('inclusive','exclusive') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'inclusive',
  `TransportGSTAmount` decimal(10,2) DEFAULT '0.00',
  `TotalAmount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `BillDiscount` decimal(10,2) DEFAULT '0.00',
  `BillDiscountType` enum('PERCENTAGE','AMOUNT') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'PERCENTAGE',
  `DiscountAmount` decimal(10,2) DEFAULT '0.00',
  `RoundOffAmount` decimal(10,2) DEFAULT '0.00',
  `FinalAmount` decimal(10,2) DEFAULT '0.00',
  `PaymentMode` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `PaymentAmount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `PaymentStatus` enum('Paid','Unpaid','Partial') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'Paid',
  `CashAmount` decimal(12,2) DEFAULT '0.00',
  `UpiAmount` decimal(12,2) DEFAULT '0.00',
  `CreditAmount` decimal(12,2) DEFAULT '0.00',
  `ReferenceNumber` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `AddedBy` int NOT NULL,
  `CreatedDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `UpdatedDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `SupplierInvoiceNumber` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `SupplierInvoiceDate` date DEFAULT NULL,
  `DeliveryDate` date DEFAULT NULL,
  `DueDate` date DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `InvoiceNumber` (`InvoiceNumber`),
  KEY `idx_supplier` (`SupplierID`),
  KEY `idx_invoice` (`InvoiceNumber`),
  KEY `idx_date` (`PurchaseDate`),
  KEY `idx_purchase_type` (`PurchaseType`),
  KEY `idx_invoice_number` (`InvoiceNumber`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tblpurchases`
--

INSERT INTO `tblpurchases` (`ID`, `InvoiceNumber`, `PurchaseType`, `SupplierID`, `PurchaseDate`, `SubTotal`, `FreightAmount`, `TransportName`, `VehicleNumber`, `TransportGSTPercent`, `TransportGSTType`, `TransportGSTAmount`, `TotalAmount`, `BillDiscount`, `BillDiscountType`, `DiscountAmount`, `RoundOffAmount`, `FinalAmount`, `PaymentMode`, `PaymentAmount`, `PaymentStatus`, `CashAmount`, `UpiAmount`, `CreditAmount`, `ReferenceNumber`, `Notes`, `AddedBy`, `CreatedDate`, `SupplierInvoiceNumber`, `SupplierInvoiceDate`, `DeliveryDate`, `DueDate`) VALUES
(5, 'PGST/2025-26/0001', 'GST', 1, '2025-11-04', '7500.00', '0.00', '', '', '0.00', 'inclusive', '0.00', '7500.00', '0.00', 'PERCENTAGE', '0.00', '0.00', '7500.00', 'Cash:₹2000.00 + Credit:₹5500.00', '2000.00', 'Partial', '2000.00', '0.00', '5500.00', '', '', 1, '2025-11-04 12:54:26', NULL, NULL, NULL, NULL),
(6, 'PGST/2025-26/0002', 'GST', 1, '2025-11-05', '8537.95', '0.00', '', '', '0.00', 'inclusive', '0.00', '10500.00', '0.00', 'PERCENTAGE', '0.00', '0.00', '10500.00', 'Cash', '10500.00', 'Paid', '10500.00', '0.00', '0.00', '', '', 1, '2025-11-05 06:27:34', NULL, NULL, NULL, NULL);

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

--
-- Table structure for table `tblpurchases_details`
--

DROP TABLE IF EXISTS `tblpurchases_details`;
CREATE TABLE IF NOT EXISTS `tblpurchases_details` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `PurchaseID` int NOT NULL,
  `ProductID` int NOT NULL,
  `ProductCode` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `ProductName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `Rate` decimal(12,2) NOT NULL,
  `MRP` decimal(12,2) DEFAULT '0.00',
  `Quantity` decimal(10,2) NOT NULL,
  `Discount` decimal(10,2) DEFAULT '0.00',
  `DiscountType` enum('PERCENTAGE','AMOUNT') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'PERCENTAGE',
  `DiscountAmount` decimal(10,2) DEFAULT '0.00',
  `FinalRate` decimal(10,2) DEFAULT '0.00',
  `FinalTotal` decimal(10,2) DEFAULT '0.00',
  `Amount` decimal(12,2) NOT NULL,
  `CreatedDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `idx_purchase` (`PurchaseID`),
  KEY `idx_product` (`ProductID`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tblpurchases_details`
--

INSERT INTO `tblpurchases_details` (`ID`, `PurchaseID`, `ProductID`, `ProductCode`, `ProductName`, `Rate`, `MRP`, `Quantity`, `Discount`, `DiscountType`, `DiscountAmount`, `FinalRate`, `FinalTotal`, `Amount`, `CreatedDate`) VALUES
(6, 5, 1, 'ITEM000001', 'Test 1', '150.00', '0.00', '50.00', '0.00', 'PERCENTAGE', '0.00', '150.00', '7500.00', '7500.00', '2025-11-04 12:54:50'),
(7, 6, 1, 'ITEM000001', 'Test 1', '150.00', '0.00', '20.00', '0.00', 'PERCENTAGE', '0.00', '150.00', '3000.00', '3000.00', '2025-11-05 06:27:34'),
(8, 6, 3, 'ITEM000003', 'Test 4', '750.00', '0.00', '10.00', '0.00', 'PERCENTAGE', '0.00', '750.00', '7500.00', '7500.00', '2025-11-05 06:27:34');

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

--
-- Table structure for table `tblpurchases_gst_details`
--

DROP TABLE IF EXISTS `tblpurchases_gst_details`;
CREATE TABLE IF NOT EXISTS `tblpurchases_gst_details` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `PurchaseID` int NOT NULL,
  `InvoiceNumber` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `ProductID` int NOT NULL,
  `ProductCode` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `ProductName` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `HSNCode` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Rate` decimal(12,2) NOT NULL,
  `Quantity` decimal(10,2) NOT NULL,
  `DiscountAmount` decimal(10,2) DEFAULT '0.00',
  `TaxableAmount` decimal(12,2) NOT NULL COMMENT 'Amount after discount, before tax',
  `TaxPercentage` decimal(5,2) NOT NULL COMMENT 'Total tax percentage (CGST+SGST or IGST)',
  `TaxType` enum('INTRA','INTER') COLLATE utf8mb4_general_ci NOT NULL COMMENT 'INTRA = CGST+SGST, INTER = IGST',
  `CGSTPercent` decimal(5,2) DEFAULT '0.00',
  `CGSTAmount` decimal(12,2) DEFAULT '0.00',
  `SGSTPercent` decimal(5,2) DEFAULT '0.00',
  `SGSTAmount` decimal(12,2) DEFAULT '0.00',
  `IGSTPercent` decimal(5,2) DEFAULT '0.00',
  `IGSTAmount` decimal(12,2) DEFAULT '0.00',
  `TotalTaxAmount` decimal(12,2) NOT NULL COMMENT 'Total GST amount',
  `FinalAmount` decimal(12,2) NOT NULL COMMENT 'Taxable amount + Tax amount',
  `CreatedDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `idx_purchase_id` (`PurchaseID`),
  KEY `idx_invoice` (`InvoiceNumber`),
  KEY `idx_product` (`ProductID`),
  KEY `idx_tax_type` (`TaxType`),
  KEY `idx_created_date` (`CreatedDate`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='GST breakdown details for each product in purchases';

--
-- Dumping data for table `tblpurchases_gst_details`
--

INSERT INTO `tblpurchases_gst_details` (`ID`, `PurchaseID`, `InvoiceNumber`, `ProductID`, `ProductCode`, `ProductName`, `HSNCode`, `Rate`, `Quantity`, `DiscountAmount`, `TaxableAmount`, `TaxPercentage`, `TaxType`, `CGSTPercent`, `CGSTAmount`, `SGSTPercent`, `SGSTAmount`, `IGSTPercent`, `IGSTAmount`, `TotalTaxAmount`, `FinalAmount`, `CreatedDate`) VALUES
(6, 5, 'PGST/2025-26/0001', 1, 'ITEM000001', 'Test 1', '48219090', '150.00', '50.00', '0.00', '6696.43', '12.00', 'INTRA', '6.00', '401.79', '6.00', '401.79', '0.00', '0.00', '803.57', '7500.00', '2025-11-04 12:54:50'),
(7, 6, 'PGST/2025-26/0002', 1, 'ITEM000001', 'Test 1', '48219090', '150.00', '20.00', '0.00', '2678.57', '12.00', 'INTRA', '6.00', '160.71', '6.00', '160.71', '0.00', '0.00', '321.43', '3000.00', '2025-11-05 06:27:34'),
(8, 6, 'PGST/2025-26/0002', 3, 'ITEM000003', 'Test 4', '84433290', '750.00', '10.00', '0.00', '5859.38', '28.00', 'INTRA', '14.00', '820.31', '14.00', '820.31', '0.00', '0.00', '1640.63', '7500.00', '2025-11-05 06:27:34');

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

--
-- Table structure for table `tblpurchase_expenses`
--

DROP TABLE IF EXISTS `tblpurchase_expenses`;
CREATE TABLE IF NOT EXISTS `tblpurchase_expenses` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `PurchaseID` int DEFAULT NULL,
  `ExpenseDate` date NOT NULL,
  `ExpenseCategory` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `ExpenseDescription` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `Amount` decimal(10,2) NOT NULL,
  `PaymentMode` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'Cash',
  `Reference` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `CreatedBy` int NOT NULL,
  `CreatedAt` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `UpdatedAt` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `idx_purchase_id` (`PurchaseID`),
  KEY `idx_expense_date` (`ExpenseDate`),
  KEY `idx_category` (`ExpenseCategory`),
  KEY `idx_created_by` (`CreatedBy`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `tblpurchase_type_config`
--

DROP TABLE IF EXISTS `tblpurchase_type_config`;
CREATE TABLE IF NOT EXISTS `tblpurchase_type_config` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `PurchaseType` enum('GST','NON-GST','IMPORT','GST-PURCHASE-RETURN','NON-GST-PURCHASE-RETURN','IMPORT-PURCHASE-RETURN') COLLATE utf8mb4_general_ci NOT NULL,
  `InvoicePrefix` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `Description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `IsActive` tinyint(1) DEFAULT '1',
  `CreatedAt` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `UpdatedAt` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `PurchaseType` (`PurchaseType`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tblpurchase_type_config`
--

INSERT INTO `tblpurchase_type_config` (`ID`, `PurchaseType`, `InvoicePrefix`, `Description`, `IsActive`, `CreatedAt`) VALUES
(1, 'GST', 'PGST', 'GST Purchase', 1, '2025-11-04 10:41:52'),
(2, 'NON-GST', 'PNGST', 'Non GST Purchase', 1, '2025-11-04 10:41:52'),
(3, 'IMPORT', 'PIMP', 'Import Purchase', 1, '2025-11-04 10:41:52'),
(4, 'GST-PURCHASE-RETURN', 'PGSTR', 'GST Purchase Return', 1, '2025-11-04 13:28:58'),
(5, 'NON-GST-PURCHASE-RETURN', 'PNGSTR', 'Non GST Purchase Return', 1, '2025-11-04 13:28:58'),
(6, 'IMPORT-PURCHASE-RETURN', 'PIMPR', 'Import Purchase Return', 1, '2025-11-04 13:28:58');

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

--
-- Table structure for table `tblracks`
--

DROP TABLE IF EXISTS `tblracks`;
CREATE TABLE IF NOT EXISTS `tblracks` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `RackName` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `RackCode` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `LocationID` int DEFAULT NULL,
  `Capacity` decimal(10,2) DEFAULT NULL,
  `Description` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Status` enum('Active','Inactive') COLLATE utf8mb4_general_ci DEFAULT 'Active',
  `DateAdded` datetime DEFAULT CURRENT_TIMESTAMP,
  `LastUpdated` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `LocationID` (`LocationID`)
) ENGINE=MyISAM AUTO_INCREMENT=51 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tblracks`
--

INSERT INTO `tblracks` (`ID`, `RackName`, `RackCode`, `LocationID`, `Capacity`, `Description`, `Status`, `DateAdded`, `LastUpdated`) VALUES
(41, 'Rack A1', 'RA1', 1, NULL, 'Main store rack 1', 'Active', '2025-11-03 14:20:12', NULL),
(42, 'Rack A2', 'RA2', 1, NULL, 'Main store rack 2', 'Active', '2025-11-03 14:20:12', NULL),
(43, 'Rack B1', 'RB1', 2, NULL, 'Grocery section rack 1', 'Active', '2025-11-03 14:20:12', NULL),
(44, 'Rack B2', 'RB2', 2, NULL, 'Grocery section rack 2', 'Active', '2025-11-03 14:20:12', NULL),
(45, 'Rack C1', 'RC1', 3, NULL, 'Clothing section rack 1', 'Active', '2025-11-03 14:20:12', NULL),
(46, 'Rack C2', 'RC2', 3, NULL, 'Clothing section rack 2', 'Active', '2025-11-03 14:20:12', NULL),
(47, 'Rack D1', 'RD1', 4, NULL, 'Gift section rack 1', 'Active', '2025-11-03 14:20:12', NULL),
(48, 'Rack D2', 'RD2', 4, NULL, 'Gift section rack 2', 'Active', '2025-11-03 14:20:12', NULL),
(49, 'Rack W1', 'RW1', 5, NULL, 'Warehouse rack 1', 'Active', '2025-11-03 14:20:12', NULL),
(50, 'Rack W2', 'RW2', 5, NULL, 'Warehouse rack 2', 'Active', '2025-11-03 14:20:12', NULL);

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

--
-- Table structure for table `tblrolepermissions`
--

DROP TABLE IF EXISTS `tblrolepermissions`;
CREATE TABLE IF NOT EXISTS `tblrolepermissions` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `RoleID` int NOT NULL,
  `PermissionID` int NOT NULL,
  `CreatedDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `unique_role_permission` (`RoleID`,`PermissionID`),
  KEY `idx_role` (`RoleID`),
  KEY `idx_permission` (`PermissionID`)
) ENGINE=InnoDB AUTO_INCREMENT=140 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tblrolepermissions`
--

INSERT INTO `tblrolepermissions` (`ID`, `RoleID`, `PermissionID`, `CreatedDate`) VALUES
(3, 1, 12, '2025-10-13 05:10:10'),
(6, 1, 14, '2025-10-13 05:10:10'),
(7, 1, 9, '2025-10-13 05:10:10'),
(9, 1, 5, '2025-10-13 05:10:10'),
(10, 1, 16, '2025-10-13 05:10:10'),
(13, 1, 11, '2025-10-13 05:10:10'),
(15, 1, 7, '2025-10-13 05:10:10'),
(17, 1, 15, '2025-10-13 05:10:10'),
(20, 1, 10, '2025-10-13 05:10:10'),
(22, 1, 6, '2025-10-13 05:10:10'),
(23, 1, 36, '2025-10-13 05:10:10'),
(26, 1, 13, '2025-10-13 05:10:10'),
(29, 1, 1, '2025-10-13 05:10:10'),
(31, 1, 33, '2025-10-13 05:10:10'),
(34, 1, 8, '2025-10-13 05:10:10'),
(36, 1, 4, '2025-10-13 05:10:10'),
(71, 2, 15, '2025-10-13 05:10:10'),
(75, 2, 36, '2025-10-13 05:10:10'),
(78, 2, 13, '2025-10-13 05:10:10'),
(81, 2, 1, '2025-10-13 05:10:10'),
(83, 2, 33, '2025-10-13 05:10:10');

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

--
-- Table structure for table `tblroles`
--

DROP TABLE IF EXISTS `tblroles`;
CREATE TABLE IF NOT EXISTS `tblroles` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `RoleName` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `RoleCode` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `Description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `Status` enum('Active','Inactive') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'Active',
  `CreatedBy` int DEFAULT NULL,
  `CreatedDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `UpdatedBy` int DEFAULT NULL,
  `UpdatedDate` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `unique_role_code` (`RoleCode`),
  KEY `idx_status` (`Status`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tblroles`
--

INSERT INTO `tblroles` (`ID`, `RoleName`, `RoleCode`, `Description`, `Status`, `CreatedBy`, `CreatedDate`, `UpdatedBy`, `UpdatedDate`) VALUES
(1, 'Super Admin', 'super_admin', 'Full system access with all permissions', 'Active', NULL, '2025-10-13 05:10:10', NULL, NULL),
(2, 'Manager', 'manager', 'Branch manager with operational permissions', 'Active', NULL, '2025-10-13 05:10:10', NULL, NULL);

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

--
-- Table structure for table `tblsales`
--

DROP TABLE IF EXISTS `tblsales`;
CREATE TABLE IF NOT EXISTS `tblsales` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `InvoiceNumber` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `SalesType` enum('GST','NON-GST','ONLINE','GST-RETURN','NON-GST-RETURN','ONLINE-RETURN','QUOTATION') COLLATE utf8mb4_general_ci DEFAULT NULL,
  `CustomerID` int NOT NULL,
  `SaleDate` date NOT NULL,
  `SubTotal` decimal(12,2) NOT NULL DEFAULT '0.00',
  `CooliAmount` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT 'Transport Amount',
  `TransportName` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `VehicleNumber` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `TransportGSTPercent` decimal(5,2) DEFAULT '0.00',
  `TransportGSTType` enum('inclusive','exclusive') COLLATE utf8mb4_general_ci DEFAULT 'inclusive',
  `TransportGSTAmount` decimal(10,2) DEFAULT '0.00',
  `TotalAmount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `BillDiscount` decimal(10,2) DEFAULT '0.00',
  `BillDiscountType` enum('PERCENTAGE','AMOUNT') COLLATE utf8mb4_general_ci DEFAULT 'PERCENTAGE',
  `DiscountAmount` decimal(10,2) DEFAULT '0.00',
  `RoundOffAmount` decimal(10,2) DEFAULT '0.00',
  `FinalAmount` decimal(10,2) DEFAULT '0.00',
  `PaymentMode` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `PaymentAmount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `PaymentStatus` enum('Paid','Unpaid','Partial') COLLATE utf8mb4_general_ci DEFAULT 'Paid',
  `CashAmount` decimal(12,2) DEFAULT '0.00',
  `UpiAmount` decimal(12,2) DEFAULT '0.00',
  `CreditAmount` decimal(12,2) DEFAULT '0.00',
  `ReferenceNumber` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Notes` text COLLATE utf8mb4_general_ci,
  `AddedBy` int NOT NULL,
  `CreatedDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `UpdatedDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ShippingAddress` varchar(500) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `ShippingCity` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `ShippingState` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `ShippingPinCode` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `PurchaseOrderNo` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `PurchaseOrderDate` date DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `InvoiceNumber` (`InvoiceNumber`),
  KEY `idx_customer` (`CustomerID`),
  KEY `idx_invoice` (`InvoiceNumber`),
  KEY `idx_date` (`SaleDate`),
  KEY `idx_sales_type` (`SalesType`),
  KEY `idx_invoice_number` (`InvoiceNumber`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tblsales`
--

INSERT INTO `tblsales` (`ID`, `InvoiceNumber`, `SalesType`, `CustomerID`, `SaleDate`, `SubTotal`, `CooliAmount`, `TransportName`, `VehicleNumber`, `TransportGSTPercent`, `TransportGSTType`, `TransportGSTAmount`, `TotalAmount`, `BillDiscount`, `BillDiscountType`, `DiscountAmount`, `RoundOffAmount`, `FinalAmount`, `PaymentMode`, `PaymentAmount`, `PaymentStatus`, `CashAmount`, `UpiAmount`, `CreditAmount`, `ReferenceNumber`, `Notes`, `AddedBy`, `CreatedDate`, `ShippingAddress`, `ShippingCity`, `ShippingState`, `ShippingPinCode`, `PurchaseOrderNo`, `PurchaseOrderDate`) VALUES
(7, 'GST/2025-26/0001', 'GST', 1, '2025-11-04', '1339.29', '0.00', '', '', '0.00', 'inclusive', '0.00', '1500.00', '0.00', 'PERCENTAGE', '0.00', '0.00', '1500.00', 'Cash', '1500.00', 'Paid', '1500.00', '0.00', '0.00', NULL, '', 1, '2025-11-04 13:58:09', NULL, NULL, NULL, NULL, NULL, NULL),
(8, 'GST/2025-26/0002', 'GST', 1, '2025-11-04', '2604.23', '0.00', '', '', '0.00', 'inclusive', '0.00', '2835.00', '0.00', 'PERCENTAGE', '0.00', '0.00', '2835.00', 'Cash', '2835.00', 'Paid', '2835.00', '0.00', '0.00', NULL, '', 1, '2025-11-04 14:20:52', NULL, NULL, NULL, NULL, NULL, NULL),
(9, 'GST/2025-26/0003', 'GST', 1, '2025-11-04', '1030.57', '0.00', '', '', '0.00', 'inclusive', '0.00', '1200.00', '0.00', 'PERCENTAGE', '0.00', '0.00', '1200.00', 'Cash:₹500.00 + Credit:₹700.00', '1200.00', 'Paid', '500.00', '0.00', '700.00', NULL, '', 1, '2025-11-04 15:12:04', NULL, NULL, NULL, NULL, NULL, NULL),
(10, 'GST/2025-26/0004', 'GST', 1, '2025-11-04', '3097.10', '0.00', '', '', '0.00', 'inclusive', '0.00', '3750.00', '0.00', 'PERCENTAGE', '0.00', '0.00', '3750.00', 'Cash:₹2000.00 + Credit:₹1750.00', '3000.00', 'Paid', '2000.00', '0.00', '1750.00', NULL, '', 1, '2025-11-04 15:12:38', NULL, NULL, NULL, NULL, NULL, NULL),
(11, 'GST/2025-26/0005', 'GST', 1, '2025-11-05', '9741.03', '0.00', '', '', '0.00', 'inclusive', '0.00', '12000.00', '0.00', 'PERCENTAGE', '0.00', '0.00', '12000.00', 'Cash', '12000.00', 'Paid', '12000.00', '0.00', '0.00', NULL, '', 1, '2025-11-05 07:26:10', NULL, NULL, NULL, NULL, NULL, NULL),
(12, 'GST/2025-26/0006', 'GST', 1, '2025-11-05', '896.64', '0.00', '', '', '0.00', 'inclusive', '0.00', '1050.00', '0.00', 'PERCENTAGE', '0.00', '0.00', '1050.00', 'Cash', '1050.00', 'Paid', '1050.00', '0.00', '0.00', NULL, '', 1, '2025-11-05 10:01:11', NULL, NULL, NULL, NULL, NULL, NULL),
(13, 'GST/2025-26/0007', 'GST', 1, '2025-11-06', '1500.00', '0.00', '', '', '0.00', 'inclusive', '0.00', '1500.00', '0.00', 'PERCENTAGE', '0.00', '0.00', '1500.00', 'Cash', '1500.00', 'Paid', '1500.00', '0.00', '0.00', NULL, '', 1, '2025-11-06 09:03:12', NULL, NULL, NULL, NULL, NULL, NULL),
(14, 'GST/2025-26/0008', 'GST', 1, '2025-11-06', '1847.76', '0.00', '', '', '0.00', 'inclusive', '0.00', '2100.00', '0.00', 'PERCENTAGE', '0.00', '0.00', '2100.00', 'Cash', '2100.00', 'Paid', '2100.00', '0.00', '0.00', NULL, '', 1, '2025-11-06 16:02:34', NULL, NULL, NULL, NULL, NULL, NULL),
(15, 'GST/2025-26/0009', 'GST', 1, '2025-11-06', '2542.37', '0.00', '', '', '0.00', 'inclusive', '0.00', '3000.00', '0.00', 'PERCENTAGE', '0.00', '0.00', '3000.00', 'Cash', '3000.00', 'Paid', '3000.00', '0.00', '0.00', NULL, '', 1, '2025-11-06 16:12:53', NULL, NULL, NULL, NULL, NULL, NULL),
(16, 'GST/2025-26/0010', 'GST', 1, '2025-11-06', '133.93', '0.00', '', '', '0.00', 'inclusive', '0.00', '150.00', '0.00', 'PERCENTAGE', '0.00', '0.00', '150.00', 'Cash', '150.00', 'Paid', '150.00', '0.00', '0.00', NULL, '', 1, '2025-11-06 16:17:23', NULL, NULL, NULL, NULL, NULL, NULL),
(17, 'QTN/2025-26/0001', 'QUOTATION', 1, '2025-11-08', '1500.00', '0.00', '', '', '0.00', 'inclusive', '0.00', '1500.00', '0.00', 'PERCENTAGE', '0.00', '0.00', '1500.00', 'Quotation', '0.00', '', '0.00', '0.00', '0.00', NULL, '', 1, '2025-11-08 03:39:09', NULL, NULL, NULL, NULL, NULL, NULL);

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

--
-- Table structure for table `tblsales_details`
--

DROP TABLE IF EXISTS `tblsales_details`;
CREATE TABLE IF NOT EXISTS `tblsales_details` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `SaleID` int NOT NULL,
  `ProductID` int NOT NULL,
  `ProductCode` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `ProductName` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `Rate` decimal(12,2) NOT NULL,
  `PurchaseRate` decimal(12,2) DEFAULT '0.00',
  `Quantity` decimal(10,2) NOT NULL,
  `Discount` decimal(10,2) DEFAULT '0.00',
  `DiscountType` enum('PERCENTAGE','AMOUNT') COLLATE utf8mb4_general_ci DEFAULT 'PERCENTAGE',
  `DiscountAmount` decimal(10,2) DEFAULT '0.00',
  `FinalRate` decimal(10,2) DEFAULT '0.00',
  `FinalTotal` decimal(10,2) DEFAULT '0.00',
  `Amount` decimal(12,2) NOT NULL,
  `CreatedDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `ProfitAmount` decimal(12,2) GENERATED ALWAYS AS (((`Rate` - `PurchaseRate`) * `Quantity`)) STORED,
  PRIMARY KEY (`ID`),
  KEY `idx_sale` (`SaleID`),
  KEY `idx_product` (`ProductID`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tblsales_details`
--

INSERT INTO `tblsales_details` (`ID`, `SaleID`, `ProductID`, `ProductCode`, `ProductName`, `Rate`, `PurchaseRate`, `Quantity`, `Discount`, `DiscountType`, `DiscountAmount`, `FinalRate`, `FinalTotal`, `Amount`, `CreatedDate`) VALUES
(12, 7, 1, 'ITEM000001', 'Test 1', '150.00', '100.00', '10.00', '0.00', 'PERCENTAGE', '0.00', '150.00', '1500.00', '1500.00', '2025-11-04 13:58:09'),
(13, 8, 1, 'ITEM000001', 'Test 1', '150.00', '100.00', '5.00', '0.00', 'PERCENTAGE', '0.00', '150.00', '750.00', '675.00', '2025-11-04 14:20:52'),
(14, 8, 2, 'ITEM000002', 'Test3', '300.00', '200.00', '3.00', '0.00', 'PERCENTAGE', '0.00', '300.00', '900.00', '810.00', '2025-11-04 14:20:52'),
(15, 8, 3, 'ITEM000003', 'Test 4', '750.00', '500.00', '2.00', '0.00', 'PERCENTAGE', '0.00', '750.00', '1500.00', '1350.00', '2025-11-04 14:20:52'),
(16, 9, 1, 'ITEM000001', 'Test 1', '150.00', '100.00', '2.00', '0.00', 'PERCENTAGE', '0.00', '150.00', '300.00', '300.00', '2025-11-04 15:12:04'),
(17, 9, 2, 'ITEM000002', 'Test3', '300.00', '200.00', '3.00', '0.00', 'PERCENTAGE', '0.00', '300.00', '900.00', '900.00', '2025-11-04 15:12:04'),
(18, 10, 1, 'ITEM000001', 'Test 1', '150.00', '100.00', '10.00', '0.00', 'PERCENTAGE', '0.00', '150.00', '1500.00', '1500.00', '2025-11-04 15:12:38'),
(19, 10, 3, 'ITEM000003', 'Test 4', '750.00', '500.00', '3.00', '0.00', 'PERCENTAGE', '0.00', '750.00', '2250.00', '2250.00', '2025-11-04 15:12:38'),
(20, 11, 1, 'ITEM000001', 'Test 1', '150.00', '100.00', '10.00', '0.00', 'PERCENTAGE', '0.00', '150.00', '1500.00', '1500.00', '2025-11-05 07:26:10'),
(21, 11, 2, 'ITEM000002', 'Test3', '300.00', '200.00', '10.00', '0.00', 'PERCENTAGE', '0.00', '300.00', '3000.00', '3000.00', '2025-11-05 07:26:10'),
(22, 11, 3, 'ITEM000003', 'Test 4', '750.00', '500.00', '10.00', '0.00', 'PERCENTAGE', '0.00', '750.00', '7500.00', '7500.00', '2025-11-05 07:26:10'),
(23, 12, 1, 'ITEM000001', 'Test 1', '150.00', '100.00', '1.00', '0.00', 'PERCENTAGE', '0.00', '150.00', '150.00', '150.00', '2025-11-05 10:01:11'),
(24, 12, 2, 'ITEM000002', 'Test3', '300.00', '200.00', '3.00', '0.00', 'PERCENTAGE', '0.00', '300.00', '900.00', '900.00', '2025-11-05 10:01:11'),
(26, 13, 1, 'ITEM000001', 'Test 1', '150.00', '100.00', '10.00', '0.00', 'PERCENTAGE', '0.00', '150.00', '1500.00', '1500.00', '2025-11-06 10:41:41'),
(27, 14, 1, 'ITEM000001', 'Test 1', '150.00', '100.00', '10.00', '0.00', 'PERCENTAGE', '0.00', '150.00', '1500.00', '1500.00', '2025-11-06 16:02:34'),
(28, 14, 2, 'ITEM000002', 'Test3', '300.00', '200.00', '2.00', '0.00', 'PERCENTAGE', '0.00', '300.00', '600.00', '600.00', '2025-11-06 16:02:34'),
(29, 15, 2, 'ITEM000002', 'Test3', '300.00', '200.00', '10.00', '0.00', 'PERCENTAGE', '0.00', '300.00', '3000.00', '3000.00', '2025-11-06 16:12:53'),
(30, 16, 1, 'ITEM000001', 'Test 1', '150.00', '100.00', '1.00', '0.00', 'PERCENTAGE', '0.00', '150.00', '150.00', '150.00', '2025-11-06 16:17:23'),
(33, 17, 1, 'ITEM000001', 'Test 1', '150.00', '100.00', '10.00', '0.00', 'PERCENTAGE', '0.00', '150.00', '1500.00', '1500.00', '2025-11-08 03:41:33');

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

--
-- Table structure for table `tblsales_gst_details`
--

DROP TABLE IF EXISTS `tblsales_gst_details`;
CREATE TABLE IF NOT EXISTS `tblsales_gst_details` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `SaleID` int NOT NULL,
  `InvoiceNumber` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `ProductID` int NOT NULL,
  `ProductCode` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `ProductName` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `HSNCode` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Rate` decimal(12,2) NOT NULL,
  `Quantity` decimal(10,2) NOT NULL,
  `MRP` decimal(10,2) DEFAULT '0.00',
  `DiscountAmount` decimal(10,2) DEFAULT '0.00',
  `TaxableAmount` decimal(12,2) NOT NULL COMMENT 'Amount after discount, before tax',
  `TaxPercentage` decimal(5,2) NOT NULL COMMENT 'Total tax percentage (CGST+SGST or IGST)',
  `TaxType` enum('INTRA','INTER') COLLATE utf8mb4_general_ci NOT NULL COMMENT 'INTRA = CGST+SGST, INTER = IGST',
  `CGSTPercent` decimal(5,2) DEFAULT '0.00',
  `CGSTAmount` decimal(12,2) DEFAULT '0.00',
  `SGSTPercent` decimal(5,2) DEFAULT '0.00',
  `SGSTAmount` decimal(12,2) DEFAULT '0.00',
  `IGSTPercent` decimal(5,2) DEFAULT '0.00',
  `IGSTAmount` decimal(12,2) DEFAULT '0.00',
  `TotalTaxAmount` decimal(12,2) NOT NULL COMMENT 'Total GST amount',
  `FinalAmount` decimal(12,2) NOT NULL COMMENT 'Taxable amount + Tax amount',
  `CreatedDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `idx_sale_id` (`SaleID`),
  KEY `idx_invoice` (`InvoiceNumber`),
  KEY `idx_product` (`ProductID`),
  KEY `idx_tax_type` (`TaxType`),
  KEY `idx_created_date` (`CreatedDate`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='GST breakdown details for each product in sales';

--
-- Dumping data for table `tblsales_gst_details`
--

INSERT INTO `tblsales_gst_details` (`ID`, `SaleID`, `InvoiceNumber`, `ProductID`, `ProductCode`, `ProductName`, `HSNCode`, `Rate`, `Quantity`, `MRP`, `DiscountAmount`, `TaxableAmount`, `TaxPercentage`, `TaxType`, `CGSTPercent`, `CGSTAmount`, `SGSTPercent`, `SGSTAmount`, `IGSTPercent`, `IGSTAmount`, `TotalTaxAmount`, `FinalAmount`, `CreatedDate`) VALUES
(12, 7, 'GST/2025-26/0001', 1, 'ITEM000001', 'Test 1', '48219090', '150.00', '10.00', '0.00', '0.00', '1339.29', '12.00', 'INTRA', '6.00', '80.36', '6.00', '80.36', '0.00', '0.00', '160.71', '1500.00', '2025-11-04 13:58:09'),
(13, 8, 'GST/2025-26/0002', 1, 'ITEM000001', 'Test 1', '48219090', '150.00', '5.00', '0.00', '75.00', '602.68', '12.00', 'INTRA', '6.00', '36.16', '6.00', '36.16', '0.00', '0.00', '72.32', '675.00', '2025-11-04 14:20:52'),
(14, 8, 'GST/2025-26/0002', 2, 'ITEM000002', 'Test3', '48219090', '300.00', '3.00', '0.00', '90.00', '686.44', '18.00', 'INTRA', '9.00', '61.78', '9.00', '61.78', '0.00', '0.00', '123.56', '810.00', '2025-11-04 14:20:52'),
(15, 8, 'GST/2025-26/0002', 3, 'ITEM000003', 'Test 4', '84433290', '750.00', '2.00', '0.00', '150.00', '1054.69', '28.00', 'INTRA', '14.00', '147.66', '14.00', '147.66', '0.00', '0.00', '295.31', '1350.00', '2025-11-04 14:20:52'),
(16, 9, 'GST/2025-26/0003', 1, 'ITEM000001', 'Test 1', '48219090', '150.00', '2.00', '0.00', '0.00', '267.86', '12.00', 'INTRA', '6.00', '16.07', '6.00', '16.07', '0.00', '0.00', '32.14', '300.00', '2025-11-04 15:12:04'),
(17, 9, 'GST/2025-26/0003', 2, 'ITEM000002', 'Test3', '48219090', '300.00', '3.00', '0.00', '0.00', '762.71', '18.00', 'INTRA', '9.00', '68.64', '9.00', '68.64', '0.00', '0.00', '137.29', '900.00', '2025-11-04 15:12:04'),
(18, 10, 'GST/2025-26/0004', 1, 'ITEM000001', 'Test 1', '48219090', '150.00', '10.00', '0.00', '0.00', '1339.29', '12.00', 'INTRA', '6.00', '80.36', '6.00', '80.36', '0.00', '0.00', '160.71', '1500.00', '2025-11-04 15:12:38'),
(19, 10, 'GST/2025-26/0004', 3, 'ITEM000003', 'Test 4', '84433290', '750.00', '3.00', '0.00', '0.00', '1757.81', '28.00', 'INTRA', '14.00', '246.09', '14.00', '246.09', '0.00', '0.00', '492.19', '2250.00', '2025-11-04 15:12:38'),
(20, 11, 'GST/2025-26/0005', 1, 'ITEM000001', 'Test 1', '48219090', '150.00', '10.00', '0.00', '0.00', '1339.29', '12.00', 'INTRA', '6.00', '80.36', '6.00', '80.36', '0.00', '0.00', '160.71', '1500.00', '2025-11-05 07:26:10'),
(21, 11, 'GST/2025-26/0005', 2, 'ITEM000002', 'Test3', '48219090', '300.00', '10.00', '0.00', '0.00', '2542.37', '18.00', 'INTRA', '9.00', '228.81', '9.00', '228.81', '0.00', '0.00', '457.63', '3000.00', '2025-11-05 07:26:10'),
(22, 11, 'GST/2025-26/0005', 3, 'ITEM000003', 'Test 4', '84433290', '750.00', '10.00', '0.00', '0.00', '5859.38', '28.00', 'INTRA', '14.00', '820.31', '14.00', '820.31', '0.00', '0.00', '1640.63', '7500.00', '2025-11-05 07:26:10'),
(23, 12, 'GST/2025-26/0006', 1, 'ITEM000001', 'Test 1', '48219090', '150.00', '1.00', '0.00', '0.00', '133.93', '12.00', 'INTRA', '6.00', '8.04', '6.00', '8.04', '0.00', '0.00', '16.07', '150.00', '2025-11-05 10:01:11'),
(24, 12, 'GST/2025-26/0006', 2, 'ITEM000002', 'Test3', '48219090', '300.00', '3.00', '0.00', '0.00', '762.71', '18.00', 'INTRA', '9.00', '68.64', '9.00', '68.64', '0.00', '0.00', '137.29', '900.00', '2025-11-05 10:01:11'),
(26, 13, 'GST/2025-26/0007', 1, 'ITEM000001', 'Test 1', NULL, '150.00', '10.00', '0.00', '0.00', '1500.00', '0.00', 'INTRA', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '0.00', '1500.00', '2025-11-06 10:41:41'),
(27, 14, 'GST/2025-26/0008', 1, 'ITEM000001', 'Test 1', '48219090', '150.00', '10.00', '0.00', '0.00', '1339.29', '12.00', 'INTRA', '6.00', '80.36', '6.00', '80.36', '0.00', '0.00', '160.71', '1500.00', '2025-11-06 16:02:35'),
(28, 14, 'GST/2025-26/0008', 2, 'ITEM000002', 'Test3', '48219090', '300.00', '2.00', '0.00', '0.00', '508.47', '18.00', 'INTRA', '9.00', '45.76', '9.00', '45.76', '0.00', '0.00', '91.53', '600.00', '2025-11-06 16:02:35'),
(29, 15, 'GST/2025-26/0009', 2, 'ITEM000002', 'Test3', '48219090', '300.00', '10.00', '0.00', '0.00', '2542.37', '18.00', 'INTRA', '9.00', '228.81', '9.00', '228.81', '0.00', '0.00', '457.63', '3000.00', '2025-11-06 16:12:53'),
(30, 16, 'GST/2025-26/0010', 1, 'ITEM000001', 'Test 1', '48219090', '150.00', '1.00', '0.00', '0.00', '133.93', '12.00', 'INTRA', '6.00', '8.04', '6.00', '8.04', '0.00', '0.00', '16.07', '150.00', '2025-11-06 16:17:23'),
(35, 17, 'QTN/2025-26/0001', 1, 'ITEM000001', 'Test 1', '48219090', '150.00', '10.00', '0.00', '0.00', '1339.29', '12.00', 'INTRA', '6.00', '80.36', '6.00', '80.36', '0.00', '0.00', '160.71', '1500.00', '2025-11-08 03:41:33');

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

--
-- Table structure for table `tblsales_type_config`
--

DROP TABLE IF EXISTS `tblsales_type_config`;
CREATE TABLE IF NOT EXISTS `tblsales_type_config` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `SalesType` enum('GST','NON-GST','ONLINE','GST-RETURN','NON-GST-RETURN','ONLINE-RETURN','QUOTATION') COLLATE utf8mb4_general_ci DEFAULT NULL,
  `InvoicePrefix` varchar(10) COLLATE utf8mb4_general_ci NOT NULL,
  `Description` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `IsActive` tinyint(1) DEFAULT '1',
  `CreatedAt` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `UpdatedAt` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `SalesType` (`SalesType`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tblsales_type_config`
--

INSERT INTO `tblsales_type_config` (`ID`, `SalesType`, `InvoicePrefix`, `Description`, `IsActive`, `CreatedAt`) VALUES
(1, 'GST', 'GST', 'GST Sales', 1, '2025-11-03 05:49:46'),
(2, 'NON-GST', 'NGST', 'Sales', 1, '2025-11-03 05:49:46'),
(3, 'ONLINE', 'ONL', 'Online Sales', 1, '2025-11-03 05:49:46'),
(7, 'GST-RETURN', 'GSTR', 'GST Sales Return', 1, '2025-11-04 13:08:59'),
(8, 'NON-GST-RETURN', 'NGSTR', 'Sales Return', 1, '2025-11-04 13:08:59'),
(9, 'ONLINE-RETURN', 'ONLR', 'Online Sales Return', 1, '2025-11-04 13:08:59'),
(10, 'QUOTATION', 'QTN', 'Quotation', 1, '2025-11-08 02:54:25');

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

--
-- Table structure for table `tblsettings_loyalty`
--

DROP TABLE IF EXISTS `tblsettings_loyalty`;
CREATE TABLE IF NOT EXISTS `tblsettings_loyalty` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `SettingKey` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `SettingValue` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `Description` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `UpdatedAt` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `SettingKey` (`SettingKey`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tblsettings_loyalty`
--

INSERT INTO `tblsettings_loyalty` (`ID`, `SettingKey`, `SettingValue`, `Description`) VALUES
(1, 'points_per_100', '1', 'Points earned per ???100 spent'),
(2, 'redemption_rate', '1', 'Value of 1 point in ???'),
(3, 'min_redemption_points', '100', 'Minimum points required for redemption'),
(4, 'points_expiry_days', '365', 'Days after which points expire (0 = never)');

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

--
-- Table structure for table `tblstore_settings`
--

DROP TABLE IF EXISTS `tblstore_settings`;
CREATE TABLE IF NOT EXISTS `tblstore_settings` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `StoreCode` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `StoreName` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `Mobile` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Email` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Phone` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `GSTNumber` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `TaxNumber` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `PANNumber` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `StoreWebsite` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `ShowSignatureOnInvoice` tinyint(1) DEFAULT '0',
  `SignatureImage` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `BankDetails` text COLLATE utf8mb4_general_ci,
  `Country` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `State` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `City` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Postcode` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Address` text COLLATE utf8mb4_general_ci,
  `StoreLogo1` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `StoreLogo2` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `InvoiceTemplate` varchar(100) COLLATE utf8mb4_general_ci DEFAULT 'sale-invoice-template1.php',
  `DateAdded` datetime DEFAULT CURRENT_TIMESTAMP,
  `LastUpdated` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `StoreCode` (`StoreCode`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tblstore_settings`
--

INSERT INTO `tblstore_settings` (`ID`, `StoreCode`, `StoreName`, `Mobile`, `Email`, `Phone`, `GSTNumber`, `TaxNumber`, `PANNumber`, `StoreWebsite`, `ShowSignatureOnInvoice`, `SignatureImage`, `BankDetails`, `Country`, `State`, `City`, `Postcode`, `Address`, `StoreLogo1`, `StoreLogo2`, `InvoiceTemplate`, `DateAdded`) VALUES
(1, 'ST0002', 'ஸ்ரீ அண்ணாமலை பேட்டரி சர்விஸ்', '9444 223794', 'sriannamalaibattery7337@gmail.com', '', '33AXLPR6163M1ZU', '', '', '', 0, 'uploads/store/signature_1762425685_Screenshot 2025-11-06 143640.png', 'Bank : Tamilnadu Mercantile Bank\r\nAc.No : 30415 0050 800 228\r\nIFSCode : TMBL0000304\r\nBranch :SOOLAKKARAI\r\n', 'India', 'Tamil Nadu', 'விருதுநகர்', '626001', '3/1239/21, எம்.ஜி.ஆர். சாலை, புதிய பேருந்து நிலையம், பை பாஸ் ரோடு, ', 'uploads/store/logo1_1762328152_UZRS Logo jpg.jpg', 'uploads/store/logo2_1762328152_amman logo only1.png', 'sale-invoice-template1.php', '2025-11-05 12:41:11');

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

--
-- Table structure for table `tblsuppliers`
--

DROP TABLE IF EXISTS `tblsuppliers`;
CREATE TABLE IF NOT EXISTS `tblsuppliers` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `SupplierName` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `SupplierCode` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `ContactPerson` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `PhoneNumber` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Email` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Address` text COLLATE utf8mb4_general_ci,
  `GSTNumber` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `PanNumber` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `OutstandingBalance` decimal(15,2) DEFAULT '0.00',
  `Status` enum('Active','Inactive') COLLATE utf8mb4_general_ci DEFAULT 'Active',
  `CreatedAt` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `LastUpdated` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `SupplierCode` (`SupplierCode`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tblsuppliers`
--

INSERT INTO `tblsuppliers` (`ID`, `SupplierName`, `SupplierCode`, `ContactPerson`, `PhoneNumber`, `Email`, `Address`, `GSTNumber`, `PanNumber`, `OutstandingBalance`, `Status`, `CreatedAt`) VALUES
(1, 'Test Supplier', 'S0001', '', '49456546546', '', '', '', '', '10500.00', 'Active', '2025-11-04 11:41:46');

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

--
-- Table structure for table `tblsupplier_accounts`
--

DROP TABLE IF EXISTS `tblsupplier_accounts`;
CREATE TABLE IF NOT EXISTS `tblsupplier_accounts` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `SupplierID` int NOT NULL,
  `InvoiceNumber` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `TransactionType` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Debit or Credit',
  `TransactionDate` date NOT NULL,
  `Amount` decimal(15,2) NOT NULL,
  `BalanceAmount` decimal(15,2) NOT NULL,
  `Description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `PaymentMode` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `ReferenceNumber` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `AddedBy` int NOT NULL,
  `CreatedDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `idx_supplier` (`SupplierID`),
  KEY `idx_invoice` (`InvoiceNumber`),
  KEY `idx_date` (`TransactionDate`),
  KEY `idx_type` (`TransactionType`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tblsupplier_accounts`
--

INSERT INTO `tblsupplier_accounts` (`ID`, `SupplierID`, `InvoiceNumber`, `TransactionType`, `TransactionDate`, `Amount`, `BalanceAmount`, `Description`, `PaymentMode`, `ReferenceNumber`, `AddedBy`, `CreatedDate`) VALUES
(6, 1, 'PGST/2025-26/0001', 'Credit', '2025-11-04', '7500.00', '7500.00', 'Purchase Bill', 'Cash:₹2000.00 + Credit:₹5500.00', NULL, 1, '2025-11-04 12:54:26'),
(9, 1, 'PAY-PGST/2025-26/0001', 'Debit', '2025-11-04', '2000.00', '2000.00', 'Payment for Purchase PGST/2025-26/0001', 'Cash:₹2000.00 + Credit:₹5500.00', 'PGST/2025-26/0001', 1, '2025-11-04 12:54:50'),
(10, 1, 'PGST/2025-26/0002', 'Credit', '2025-11-05', '10500.00', '10500.00', 'Purchase Bill', 'Cash', NULL, 1, '2025-11-05 06:27:34'),
(11, 1, 'PAY-PGST/2025-26/0002', 'Debit', '2025-11-05', '10500.00', '10500.00', 'Payment for Purchase PGST/2025-26/0002', 'Cash', 'PGST/2025-26/0002', 1, '2025-11-05 06:27:34');

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

--
-- Table structure for table `tbltaxtypes`
--

DROP TABLE IF EXISTS `tbltaxtypes`;
CREATE TABLE IF NOT EXISTS `tbltaxtypes` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `TaxName` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `TaxType` enum('CGST','SGST','IGST','CESS','VAT','Other') COLLATE utf8mb4_general_ci NOT NULL,
  `TaxRate` decimal(5,2) NOT NULL DEFAULT '0.00',
  `Description` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Status` enum('Active','Inactive') COLLATE utf8mb4_general_ci DEFAULT 'Active',
  `DateAdded` datetime DEFAULT CURRENT_TIMESTAMP,
  `LastUpdated` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=56 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tbltaxtypes`
--

INSERT INTO `tbltaxtypes` (`ID`, `TaxName`, `TaxType`, `TaxRate`, `Description`, `Status`, `DateAdded`, `LastUpdated`) VALUES
(52, 'GST 5%', 'Other', '5.00', 'GST 5% (2.5% CGST + 2.5% SGST)', 'Active', '2025-11-03 14:20:12', '2025-11-03 22:40:19'),
(53, 'GST 12%', 'Other', '12.00', 'GST 12% (6% CGST + 6% SGST)', 'Active', '2025-11-03 14:20:12', '2025-11-03 22:39:55'),
(54, 'GST 18%', 'Other', '18.00', 'GST 18% (9% CGST + 9% SGST)', 'Active', '2025-11-03 14:20:12', '2025-11-03 22:40:02'),
(55, 'GST 28%', 'Other', '28.00', 'GST 28% (14% CGST + 14% SGST)', 'Active', '2025-11-03 14:20:12', '2025-11-03 22:40:11');

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

--
-- Table structure for table `tbltypes`
--

DROP TABLE IF EXISTS `tbltypes`;
CREATE TABLE IF NOT EXISTS `tbltypes` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `TypeName` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `TypeCode` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `TypeCategory` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Description` text COLLATE utf8mb4_general_ci,
  `Status` enum('Active','Inactive') COLLATE utf8mb4_general_ci DEFAULT 'Active',
  `CreatedDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `LastUpdated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `TypeName` (`TypeName`),
  KEY `idx_status` (`Status`),
  KEY `idx_category` (`TypeCategory`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `tblunits`
--

DROP TABLE IF EXISTS `tblunits`;
CREATE TABLE IF NOT EXISTS `tblunits` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `UnitName` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `UnitSymbol` varchar(10) COLLATE utf8mb4_general_ci NOT NULL,
  `Description` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Status` enum('Active','Inactive') COLLATE utf8mb4_general_ci DEFAULT 'Active',
  `DateAdded` datetime DEFAULT CURRENT_TIMESTAMP,
  `LastUpdated` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UnitName` (`UnitName`)
) ENGINE=MyISAM AUTO_INCREMENT=51 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tblunits`
--

INSERT INTO `tblunits` (`ID`, `UnitName`, `UnitSymbol`, `Description`, `Status`, `DateAdded`, `LastUpdated`) VALUES
(41, 'Piece', 'Pcs', 'Individual piece', 'Active', '2025-11-03 14:20:12', NULL),
(42, 'Kilogram', 'Kg', 'Weight in kilograms', 'Active', '2025-11-03 14:20:12', NULL),
(43, 'Gram', 'g', 'Weight in grams', 'Active', '2025-11-03 14:20:12', NULL),
(44, 'Liter', 'L', 'Volume in liters', 'Active', '2025-11-03 14:20:12', NULL),
(45, 'Milliliter', 'ml', 'Volume in milliliters', 'Active', '2025-11-03 14:20:12', NULL),
(46, 'Packet', 'Pkt', 'Packet', 'Active', '2025-11-03 14:20:12', NULL),
(47, 'Box', 'Box', 'Box', 'Active', '2025-11-03 14:20:12', NULL),
(48, 'Dozen', 'Dzn', '12 pieces', 'Active', '2025-11-03 14:20:12', NULL),
(49, 'Bundle', 'Bdl', 'Bundle of items', 'Active', '2025-11-03 14:20:12', NULL),
(50, 'Meter', 'm', 'Length in meters', 'Active', '2025-11-03 14:20:12', NULL);

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

--
-- Table structure for table `tblusers`
--

DROP TABLE IF EXISTS `tblusers`;
CREATE TABLE IF NOT EXISTS `tblusers` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `UserName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `FullName` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `UserType` enum('admin','staff') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'staff',
  `RoleID` int DEFAULT NULL,
  `MobileNumber` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Email` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Password` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `RegDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `unique_username` (`UserName`),
  KEY `idx_usertype` (`UserType`),
  KEY `idx_role_id` (`RoleID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tblusers`
--

INSERT INTO `tblusers` (`ID`, `UserName`, `FullName`, `UserType`, `RoleID`, `MobileNumber`, `Email`, `Password`, `RegDate`) VALUES
(1, 'admin', 'Admin User', 'admin', NULL, '1234567890', 'admin@ihita.com', '0192023a7bbd73250516f069df18b500', '2025-06-09 13:56:57'),
(2, 'staff1', 'Staff User', 'staff', NULL, '9876543210', 'staff1@ihita.com', '4297f44b13955235245b2497399d7a93', '2025-06-09 13:56:57');

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

--
-- Stand-in structure for view `vw_purchases_with_discounts`
-- (See below for the actual view)
--
DROP VIEW IF EXISTS `vw_purchases_with_discounts`;
CREATE TABLE IF NOT EXISTS `vw_purchases_with_discounts` (
`ID` int
,`InvoiceNumber` varchar(50)
,`PurchaseType` enum('GST','NON-GST','IMPORT','GST-PURCHASE-RETURN','NON-GST-PURCHASE-RETURN','IMPORT-PURCHASE-RETURN')
,`SupplierID` int
,`SupplierName` varchar(255)
,`SupplierCode` varchar(50)
,`PurchaseDate` date
,`SubTotal` decimal(12,2)
,`FreightAmount` decimal(12,2)
,`TotalAmount` decimal(12,2)
,`BillDiscount` decimal(10,2)
,`BillDiscountType` enum('PERCENTAGE','AMOUNT')
,`DiscountAmount` decimal(10,2)
,`FinalAmount` decimal(10,2)
,`PaymentAmount` decimal(12,2)
,`PaymentMode` varchar(50)
,`Notes` text
,`AddedBy` int
,`CreatedDate` timestamp
,`RemainingAmount` decimal(14,2)
);

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

--
-- Stand-in structure for view `vw_sales_with_discounts`
-- (See below for the actual view)
--
DROP VIEW IF EXISTS `vw_sales_with_discounts`;
CREATE TABLE IF NOT EXISTS `vw_sales_with_discounts` (
`ID` int
,`InvoiceNumber` varchar(50)
,`SalesType` enum('GST','NON-GST','ONLINE','GST-RETURN','NON-GST-RETURN','ONLINE-RETURN','QUOTATION')
,`CustomerID` int
,`CustomerName` varchar(255)
,`CustomerCode` varchar(50)
,`SaleDate` date
,`SubTotal` decimal(12,2)
,`CooliAmount` decimal(12,2)
,`TotalAmount` decimal(12,2)
,`BillDiscount` decimal(10,2)
,`BillDiscountType` enum('PERCENTAGE','AMOUNT')
,`DiscountAmount` decimal(10,2)
,`FinalAmount` decimal(10,2)
,`PaymentAmount` decimal(12,2)
,`PaymentMode` varchar(50)
,`Notes` text
,`AddedBy` int
,`CreatedDate` timestamp
,`RemainingAmount` decimal(14,2)
);

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

--
-- Structure for view `vw_purchases_with_discounts`
--
DROP TABLE IF EXISTS `vw_purchases_with_discounts`;

DROP VIEW IF EXISTS `vw_purchases_with_discounts`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `vw_purchases_with_discounts`  AS  select `p`.`ID` AS `ID`,`p`.`InvoiceNumber` AS `InvoiceNumber`,`p`.`PurchaseType` AS `PurchaseType`,`p`.`SupplierID` AS `SupplierID`,`s`.`SupplierName` AS `SupplierName`,`s`.`SupplierCode` AS `SupplierCode`,`p`.`PurchaseDate` AS `PurchaseDate`,`p`.`SubTotal` AS `SubTotal`,`p`.`FreightAmount` AS `FreightAmount`,`p`.`TotalAmount` AS `TotalAmount`,`p`.`BillDiscount` AS `BillDiscount`,`p`.`BillDiscountType` AS `BillDiscountType`,`p`.`DiscountAmount` AS `DiscountAmount`,`p`.`FinalAmount` AS `FinalAmount`,`p`.`PaymentAmount` AS `PaymentAmount`,`p`.`PaymentMode` AS `PaymentMode`,`p`.`Notes` AS `Notes`,`p`.`AddedBy` AS `AddedBy`,`p`.`CreatedDate` AS `CreatedDate`,((`p`.`FinalAmount` + `p`.`FreightAmount`) - `p`.`PaymentAmount`) AS `RemainingAmount` from (`tblpurchases` `p` left join `tblsuppliers` `s` on((`p`.`SupplierID` = `s`.`ID`))) ;

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

--
-- Structure for view `vw_sales_with_discounts`
--
DROP TABLE IF EXISTS `vw_sales_with_discounts`;

DROP VIEW IF EXISTS `vw_sales_with_discounts`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `vw_sales_with_discounts`  AS  select `s`.`ID` AS `ID`,`s`.`InvoiceNumber` AS `InvoiceNumber`,`s`.`SalesType` AS `SalesType`,`s`.`CustomerID` AS `CustomerID`,`c`.`CustomerName` AS `CustomerName`,`c`.`CustomerCode` AS `CustomerCode`,`s`.`SaleDate` AS `SaleDate`,`s`.`SubTotal` AS `SubTotal`,`s`.`CooliAmount` AS `CooliAmount`,`s`.`TotalAmount` AS `TotalAmount`,`s`.`BillDiscount` AS `BillDiscount`,`s`.`BillDiscountType` AS `BillDiscountType`,`s`.`DiscountAmount` AS `DiscountAmount`,`s`.`FinalAmount` AS `FinalAmount`,`s`.`PaymentAmount` AS `PaymentAmount`,`s`.`PaymentMode` AS `PaymentMode`,`s`.`Notes` AS `Notes`,`s`.`AddedBy` AS `AddedBy`,`s`.`CreatedDate` AS `CreatedDate`,((`s`.`FinalAmount` + `s`.`CooliAmount`) - `s`.`PaymentAmount`) AS `RemainingAmount` from (`tblsales` `s` left join `tblcustomers` `c` on((`s`.`CustomerID` = `c`.`ID`))) ;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `tblcustomer_loyalty_points`
--
ALTER TABLE `tblcustomer_loyalty_points`
  ADD CONSTRAINT `tblcustomer_loyalty_points_ibfk_1` FOREIGN KEY (`CustomerID`) REFERENCES `tblcustomers` (`ID`) ON DELETE CASCADE;

--
-- Constraints for table `tblpurchases_gst_details`
--
ALTER TABLE `tblpurchases_gst_details`
  ADD CONSTRAINT `fk_gst_purchase` FOREIGN KEY (`PurchaseID`) REFERENCES `tblpurchases` (`ID`) ON DELETE CASCADE;

--
-- Constraints for table `tblrolepermissions`
--
ALTER TABLE `tblrolepermissions`
  ADD CONSTRAINT `fk_permission` FOREIGN KEY (`PermissionID`) REFERENCES `tblpermissions` (`ID`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_role` FOREIGN KEY (`RoleID`) REFERENCES `tblroles` (`ID`) ON DELETE CASCADE;

--
-- Constraints for table `tblsales_gst_details`
--
ALTER TABLE `tblsales_gst_details`
  ADD CONSTRAINT `fk_gst_sale` FOREIGN KEY (`SaleID`) REFERENCES `tblsales` (`ID`) ON DELETE 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 */;
