-- phpMyAdmin SQL Dump
-- version 5.0.2
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Nov 04, 2025 at 10:21 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 `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=20 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);

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

--
-- 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=MyISAM 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, '0.00', '99.00', '99.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=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- 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=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- 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) NOT NULL,
  `CustomerID` int DEFAULT NULL,
  `CustomerName` varchar(200) DEFAULT NULL,
  `SalesType` varchar(50) DEFAULT 'GST',
  `InvoiceNumber` varchar(100) DEFAULT NULL,
  `ItemsJSON` longtext 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,
  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_0900_ai_ci;

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

--
-- 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,
  `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=98 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `tblitems` (`ID`, `ItemCode`, `ItemName`, `BrandID`, `CategoryID`, `SupplierID`, `UnitID`, `HSN`, `AlertQuantity`, `Barcode`, `ItemImage`, `TaxabilityType`, `TaxID`, `PurchasePrice`, `ProfitMarginPercent`, `SalesPrice`, `ShopRate`, `WholesaleRate`, `MRP`, `LocationID`, `RackID`, `OpeningStock`, `CurrentStock`, `Status`, `CreatedAt`) VALUES
(75, '1', 'Rice - Raw (White)', 16, 17, NULL, 42, '1006', '10.00', '8901234560011', NULL, 'Taxable', 52, '40.00', NULL, '50.00', NULL, NULL, '55.00', NULL, NULL, '100.00', '100.00', 'Active', '2025-11-03 08:50:12'),
(76, '3', 'Wheat Flour (Atta)', 16, 17, NULL, 42, '1101', '10.00', '8901234560028', NULL, 'Taxable', 52, '35.00', NULL, '45.00', NULL, NULL, '48.00', NULL, NULL, '80.00', '80.00', 'Active', '2025-11-03 08:50:12'),
(77, '4', 'Tea Powder', 15, 17, NULL, 46, '0902', '10.00', '8901234560035', NULL, 'Taxable', 52, '80.00', NULL, '100.00', NULL, NULL, '110.00', NULL, NULL, '50.00', '50.00', 'Active', '2025-11-03 08:50:12'),
(78, '5', 'Coffee Powder', 17, 17, NULL, 46, '0901', '10.00', '8901234560042', NULL, 'Taxable', 52, '120.00', NULL, '150.00', NULL, NULL, '160.00', NULL, NULL, '40.00', '40.00', 'Active', '2025-11-03 08:50:12'),
(79, '6', 'Sugar', 16, 17, NULL, 42, '1701', '20.00', '8901234560049', NULL, 'Taxable', 52, '38.00', NULL, '48.00', NULL, NULL, '50.00', NULL, NULL, '100.00', '100.00', 'Active', '2025-11-03 08:50:12'),
(80, '7', 'Butter', 17, 17, NULL, 46, '0405', '10.00', '8901234560056', NULL, 'Taxable', 53, '45.00', NULL, '60.00', NULL, NULL, '65.00', NULL, NULL, '30.00', '30.00', 'Active', '2025-11-03 08:50:12'),
(81, '8', 'Cooking Oil (Sunflower)', 15, 17, NULL, 44, '1512', '10.00', '8901234560063', NULL, 'Taxable', 53, '120.00', NULL, '150.00', NULL, NULL, '160.00', NULL, NULL, '50.00', '50.00', 'Active', '2025-11-03 08:50:12'),
(82, '9', 'Biscuits (Premium)', 17, 17, NULL, 46, '1905', '20.00', '8901234560070', NULL, 'Taxable', 54, '30.00', NULL, '40.00', NULL, NULL, '45.00', NULL, NULL, '100.00', '100.00', 'Active', '2025-11-03 08:50:12'),
(83, '10', 'Noodles (Instant)', 15, 17, NULL, 46, '1902', '30.00', '8901234560087', NULL, 'Taxable', 54, '12.00', NULL, '20.00', NULL, NULL, '22.00', NULL, NULL, '200.00', '200.00', 'Active', '2025-11-03 08:50:12'),
(84, '11', 'Chocolates', 17, 17, NULL, 46, '1806', '20.00', '8901234560094', NULL, 'Taxable', 54, '25.00', NULL, '35.00', NULL, NULL, '40.00', NULL, NULL, '150.00', '150.00', 'Active', '2025-11-03 08:50:12'),
(85, '12', 'Cotton T-Shirt', 15, 18, NULL, 41, '6109', '5.00', '8901234560100', NULL, 'Taxable', 52, '150.00', NULL, '250.00', NULL, NULL, '280.00', NULL, NULL, '50.00', '50.00', 'Active', '2025-11-03 08:50:12'),
(86, '13', 'Cotton Saree', 16, 18, NULL, 41, '5208', '5.00', '8901234560117', NULL, 'Taxable', 52, '400.00', NULL, '600.00', NULL, NULL, '650.00', NULL, NULL, '30.00', '30.00', 'Active', '2025-11-03 08:50:12'),
(87, '14', 'Synthetic Shirt', 17, 18, NULL, 41, '6205', '5.00', '8901234560124', NULL, 'Taxable', 53, '300.00', NULL, '500.00', NULL, NULL, '550.00', NULL, NULL, '40.00', '40.00', 'Active', '2025-11-03 08:50:12'),
(88, '15', 'Polyester Fabric', 15, 18, NULL, 50, '5407', '10.00', '8901234560131', NULL, 'Taxable', 53, '80.00', '0.00', '120.00', '0.00', '0.00', '130.00', NULL, NULL, '100.00', '100.00', 'Active', '2025-11-03 08:50:12'),
(89, '16', 'Jeans Pant', 17, 18, NULL, 41, '6203', '5.00', '8901234560148', NULL, 'Taxable', 54, '600.00', NULL, '1000.00', NULL, NULL, '1100.00', NULL, NULL, '30.00', '30.00', 'Active', '2025-11-03 08:50:12'),
(90, '17', 'Greeting Cards (Pack)', 15, 19, NULL, 41, '4909', '10.00', '8901234560155', NULL, 'Taxable', 53, '40.00', NULL, '60.00', NULL, NULL, '70.00', NULL, NULL, '100.00', '100.00', 'Active', '2025-11-03 08:50:12'),
(91, '18', 'Gift Box (Decorative)', 17, 19, NULL, 41, '4819', '10.00', '8901234560162', NULL, 'Taxable', 54, '50.00', NULL, '80.00', NULL, NULL, '90.00', NULL, NULL, '80.00', '80.00', 'Active', '2025-11-03 08:50:12'),
(92, '19', 'Photo Frame', 15, 19, NULL, 41, '4414', '5.00', '8901234560179', NULL, 'Taxable', 54, '100.00', NULL, '150.00', NULL, NULL, '170.00', NULL, NULL, '50.00', '50.00', 'Active', '2025-11-03 08:50:12'),
(93, '20', 'Premium Watch', 17, 19, NULL, 41, '9102', '3.00', '8901234560186', NULL, 'Taxable', 55, '1500.00', NULL, '2500.00', NULL, NULL, '2800.00', NULL, NULL, '20.00', '20.00', 'Active', '2025-11-03 08:50:12'),
(94, '2', 'Luxury Perfume', 17, 19, NULL, 41, '3303', '3.00', '8901234560193', NULL, 'Taxable', 55, '800.00', '0.00', '1200.00', '0.00', '0.00', '1350.00', NULL, NULL, '25.00', '25.00', 'Active', '2025-11-03 08:50:12'),
(95, '21', 'Test1', 15, 18, 1, 47, '1234', '0.00', '123456789', '', 'Taxable', 54, '100.00', '50.00', '150.00', '120.00', '130.00', '200.00', 23, 41, '100.00', '100.00', 'Active', '2025-11-03 17:07:09'),
(96, '22', 'Test2', 15, 18, 1, 47, '54321', '0.00', '654321', '', 'Taxable', 53, '200.00', '50.00', '300.00', '280.00', '250.00', '400.00', NULL, NULL, '200.00', '200.00', 'Active', '2025-11-03 17:08:44'),
(97, '23', 'CHAIN', 16, 18, 1, 47, '48219090', '10.00', '654321', '', 'Taxable', 54, '110.00', '50.00', '165.00', '130.00', '120.00', '200.00', 23, 41, '100.00', '100.00', 'Active', '2025-11-04 05:46:00');

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

--
-- 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) NOT NULL,
  `SettingValue` text NOT NULL,
  `Description` varchar(255) 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=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_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 `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') COLLATE utf8mb4_general_ci DEFAULT 'GST',
  `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=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- 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=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- 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) NOT NULL,
  `ProductID` int NOT NULL,
  `ProductCode` varchar(50) NOT NULL,
  `ProductName` varchar(255) NOT NULL,
  `HSNCode` varchar(50) 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') 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=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='GST breakdown details for each product in sales';

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

--
-- 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') COLLATE utf8mb4_general_ci NOT 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=7 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');

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

--
-- 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 `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,
  `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`, `Status`, `CreatedAt`) VALUES
(1, 'Test', '1', 'Jegan Sekar', '08883888012', 'uzrssoft2016@gmail.com', 'Ramuni Nagar\r\nV.Karisalkulam', '', '', 'Active', '2025-11-03 17:04:58');

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

--
-- 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 `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_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')
,`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_sales_with_discounts`
--
DROP TABLE IF EXISTS `vw_sales_with_discounts`;

DROP VIEW IF EXISTS `vw_sales_with_discounts`;
CREATE 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 `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 */;
