-- Purchase Module Database Tables
-- Created based on Sales module structure
-- Date: November 4, 2025

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 */;

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

--
-- 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) COLLATE utf8mb4_general_ci NOT NULL,
  `PurchaseType` enum('GST','NON-GST','IMPORT') 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) 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,
  `SupplierInvoiceNumber` varchar(100) 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- 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) COLLATE utf8mb4_general_ci NOT NULL,
  `ProductName` varchar(255) 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') 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- 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) 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_purchase_id` (`PurchaseID`),
  KEY `idx_invoice` (`InvoiceNumber`),
  KEY `idx_product` (`ProductID`),
  KEY `idx_tax_type` (`TaxType`),
  KEY `idx_created_date` (`CreatedDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='GST breakdown details for each product in purchases';

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

--
-- 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') 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 `PurchaseType` (`PurchaseType`)
) ENGINE=InnoDB 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, CURRENT_TIMESTAMP),
(2, 'NON-GST', 'PNGST', 'Non GST Purchase', 1, CURRENT_TIMESTAMP),
(3, 'IMPORT', 'PIMP', 'Import Purchase', 1, CURRENT_TIMESTAMP);

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

--
-- 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) NOT NULL,
  `SupplierID` int DEFAULT NULL,
  `SupplierName` varchar(200) DEFAULT NULL,
  `PurchaseType` 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- 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) 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_supplier` (`SupplierID`),
  KEY `idx_invoice` (`InvoiceNumber`),
  KEY `idx_date` (`TransactionDate`),
  KEY `idx_type` (`TransactionType`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- 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) 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_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;

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

--
-- Stand-in structure for view `vw_purchases_with_discounts`
--
DROP VIEW IF EXISTS `vw_purchases_with_discounts`;
CREATE OR REPLACE VIEW `vw_purchases_with_discounts` AS 
SELECT 
    p.`ID`,
    p.`InvoiceNumber`,
    p.`PurchaseType`,
    p.`SupplierID`,
    s.`SupplierName`,
    s.`SupplierCode`,
    p.`PurchaseDate`,
    p.`SubTotal`,
    p.`FreightAmount`,
    p.`TotalAmount`,
    p.`BillDiscount`,
    p.`BillDiscountType`,
    p.`DiscountAmount`,
    p.`FinalAmount`,
    p.`PaymentAmount`,
    p.`PaymentMode`,
    p.`Notes`,
    p.`AddedBy`,
    p.`CreatedDate`,
    (p.`FinalAmount` + p.`FreightAmount` - p.`PaymentAmount`) AS `RemainingAmount`
FROM `tblpurchases` p
LEFT JOIN `tblsuppliers` s ON p.`SupplierID` = s.`ID`;

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

--
-- Constraints for dumped tables
--

--
-- 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;

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 */;
