-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Apr 17, 2026 at 07:53 AM
-- Server version: 8.3.0
-- PHP Version: 7.4.33

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

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

--
-- Table structure for table `cash_vouchers`
--

DROP TABLE IF EXISTS `cash_vouchers`;
CREATE TABLE IF NOT EXISTS `cash_vouchers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `voucher_no` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `voucher_date` date NOT NULL,
  `voucher_type` enum('receipt','payment') COLLATE utf8mb4_general_ci NOT NULL COMMENT 'receipt: from customer, payment: to supplier',
  `party_type` enum('customer','supplier') COLLATE utf8mb4_general_ci NOT NULL,
  `party_id` int NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `payment_mode` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'cash',
  `bank_account_id` int DEFAULT NULL,
  `reference_no` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `notes` text COLLATE utf8mb4_general_ci,
  `company_id` int NOT NULL,
  `user_id` int NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `voucher_no` (`voucher_no`),
  KEY `company_id` (`company_id`),
  KEY `party_id` (`party_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `categories`
--

DROP TABLE IF EXISTS `categories`;
CREATE TABLE IF NOT EXISTS `categories` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `description` text COLLATE utf8mb4_general_ci,
  `status` enum('active','inactive') COLLATE utf8mb4_general_ci DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `challans`
--

DROP TABLE IF EXISTS `challans`;
CREATE TABLE IF NOT EXISTS `challans` (
  `id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `challan_no` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `challan_date` date NOT NULL,
  `customer_id` int DEFAULT NULL,
  `customer_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `customer_phone` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `customer_address` text COLLATE utf8mb4_general_ci,
  `customer_email` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `customer_gstin` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `customer_pan` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `customer_tan` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `customer_tin` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `customer_state` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `customer_state_code` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_phone` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_address` text COLLATE utf8mb4_general_ci,
  `consignee_email` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_gstin` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_pan` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_tan` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_tin` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_state` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_state_code` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `carrier_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `vehicle_no` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `place_of_supply` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `order_no` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `order_date` date DEFAULT NULL,
  `notes` text COLLATE utf8mb4_general_ci,
  `user_id` int NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `challan_no` (`challan_no`,`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `challan_items`
--

DROP TABLE IF EXISTS `challan_items`;
CREATE TABLE IF NOT EXISTS `challan_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `challan_id` int NOT NULL,
  `product_id` int DEFAULT NULL,
  `product_name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `description` text COLLATE utf8mb4_general_ci,
  `hsn_code` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `quantity` int NOT NULL,
  `unit` varchar(50) COLLATE utf8mb4_general_ci DEFAULT 'PCS',
  `pieces_per_unit` int DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `challan_id` (`challan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `companies`
--

DROP TABLE IF EXISTS `companies`;
CREATE TABLE IF NOT EXISTS `companies` (
  `id` int NOT NULL AUTO_INCREMENT,
  `company_name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `company_type` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `address` text COLLATE utf8mb4_general_ci,
  `phone` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `email` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `gstin` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `pan` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `tan` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `state` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `state_code` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `bank_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `bank_branch` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `account_number` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `ifsc_code` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `logo_path` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `print_header_image` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '',
  `pdf_header_image` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '',
  `invoice_prefix` varchar(10) COLLATE utf8mb4_general_ci DEFAULT 'INV',
  `purchase_prefix` varchar(10) COLLATE utf8mb4_general_ci DEFAULT 'PUR',
  `challan_prefix` varchar(20) COLLATE utf8mb4_general_ci DEFAULT 'CH',
  `quotation_prefix` varchar(20) COLLATE utf8mb4_general_ci DEFAULT 'QUO',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `signature_image` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `company_bank_accounts`
--

DROP TABLE IF EXISTS `company_bank_accounts`;
CREATE TABLE IF NOT EXISTS `company_bank_accounts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `bank_name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `bank_branch` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `account_number` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `account_holder_name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `ifsc_code` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `account_type` enum('current','savings','cash_credit','overdraft') COLLATE utf8mb4_general_ci DEFAULT 'current',
  `opening_balance` decimal(15,2) DEFAULT '0.00',
  `current_balance` decimal(15,2) DEFAULT '0.00',
  `is_default` tinyint(1) DEFAULT '0',
  `status` enum('active','inactive') COLLATE utf8mb4_general_ci DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `is_default` (`is_default`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `company_stock`
--

DROP TABLE IF EXISTS `company_stock`;
CREATE TABLE IF NOT EXISTS `company_stock` (
  `id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `product_id` int NOT NULL,
  `stock_quantity` int DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `company_product` (`company_id`,`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

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

DROP TABLE IF EXISTS `customers`;
CREATE TABLE IF NOT EXISTS `customers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `company` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `phone` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `email` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `address` text COLLATE utf8mb4_general_ci,
  `gstin` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `tin` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `state_name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `state_code` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_phone` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_email` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_address` text COLLATE utf8mb4_general_ci,
  `consignee_gstin` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_tin` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_pan` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_tan` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_state_name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_state_code` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `pan` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `tan` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `status` enum('active','inactive') COLLATE utf8mb4_general_ci DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` 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 `employees`
--

DROP TABLE IF EXISTS `employees`;
CREATE TABLE IF NOT EXISTS `employees` (
  `id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `designation` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `employee_type` enum('Permanent','Temporary','Contractual','Other') COLLATE utf8mb4_general_ci NOT NULL,
  `salary` decimal(15,2) DEFAULT '0.00',
  `joining_date` date DEFAULT NULL,
  `phone` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
  `address` text COLLATE utf8mb4_general_ci,
  `adhaar_no` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `pan_no` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `status` enum('active','inactive') COLLATE utf8mb4_general_ci DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `expenses`
--

DROP TABLE IF EXISTS `expenses`;
CREATE TABLE IF NOT EXISTS `expenses` (
  `id` int NOT NULL AUTO_INCREMENT,
  `company_id` int DEFAULT NULL,
  `expense_no` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `expense_head_id` int DEFAULT NULL,
  `description` text COLLATE utf8mb4_general_ci,
  `amount` decimal(12,2) NOT NULL,
  `gst_type` enum('inclusive','exclusive','none') COLLATE utf8mb4_general_ci DEFAULT 'none',
  `gst_percentage` decimal(5,2) DEFAULT '0.00',
  `gst_amount` decimal(12,2) DEFAULT '0.00',
  `total_amount` decimal(12,2) NOT NULL,
  `payment_mode` enum('cash','card','upi','bank_transfer','cheque') COLLATE utf8mb4_general_ci DEFAULT 'cash',
  `expense_date` date NOT NULL,
  `voucher_no` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `user_id` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `expense_no` (`expense_no`),
  UNIQUE KEY `voucher_no` (`voucher_no`),
  KEY `expense_head_id` (`expense_head_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `expense_heads`
--

DROP TABLE IF EXISTS `expense_heads`;
CREATE TABLE IF NOT EXISTS `expense_heads` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `description` text COLLATE utf8mb4_general_ci,
  `status` enum('active','inactive') COLLATE utf8mb4_general_ci DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` 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 `lenders`
--

DROP TABLE IF EXISTS `lenders`;
CREATE TABLE IF NOT EXISTS `lenders` (
  `id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `lender_type` enum('Bank','Finance Institute','Person') COLLATE utf8mb4_general_ci NOT NULL,
  `phone` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `email` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `address` text COLLATE utf8mb4_general_ci,
  `status` enum('active','inactive') COLLATE utf8mb4_general_ci DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `loans`
--

DROP TABLE IF EXISTS `loans`;
CREATE TABLE IF NOT EXISTS `loans` (
  `id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `lender_id` int NOT NULL,
  `loan_amount` decimal(15,2) NOT NULL,
  `interest_rate` decimal(5,2) DEFAULT '0.00',
  `loan_date` date NOT NULL,
  `notes` text COLLATE utf8mb4_general_ci,
  `status` enum('active','closed') COLLATE utf8mb4_general_ci DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `lender_id` (`lender_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `loan_repayments`
--

DROP TABLE IF EXISTS `loan_repayments`;
CREATE TABLE IF NOT EXISTS `loan_repayments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `loan_id` int NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `payment_date` date NOT NULL,
  `payment_mode` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `reference_no` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `notes` text COLLATE utf8mb4_general_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `loan_id` (`loan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `payments`
--

DROP TABLE IF EXISTS `payments`;
CREATE TABLE IF NOT EXISTS `payments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `company_id` int DEFAULT NULL,
  `reference_type` enum('sale','purchase') COLLATE utf8mb4_general_ci NOT NULL,
  `reference_id` int NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `payment_date` date NOT NULL,
  `payment_mode` enum('cash','card','upi','bank_transfer','cheque') COLLATE utf8mb4_general_ci DEFAULT 'cash',
  `bank_account_id` int DEFAULT NULL,
  `notes` text COLLATE utf8mb4_general_ci,
  `user_id` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `reference_type` (`reference_type`,`reference_id`),
  KEY `user_id` (`user_id`),
  KEY `bank_account_id` (`bank_account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `products`
--

DROP TABLE IF EXISTS `products`;
CREATE TABLE IF NOT EXISTS `products` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `category_id` int DEFAULT NULL,
  `description` text COLLATE utf8mb4_general_ci,
  `unit` varchar(50) COLLATE utf8mb4_general_ci DEFAULT 'PCS',
  `buying_price` decimal(10,2) DEFAULT '0.00',
  `selling_price` decimal(10,2) NOT NULL,
  `mrp` decimal(10,2) DEFAULT '0.00',
  `tax_percentage` decimal(5,2) DEFAULT '0.00',
  `hsn_code` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `barcode` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `stock_quantity` int DEFAULT '0',
  `min_stock_level` int DEFAULT '5',
  `status` enum('active','inactive') COLLATE utf8mb4_general_ci DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `barcode` (`barcode`),
  KEY `category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `product_units`
--

DROP TABLE IF EXISTS `product_units`;
CREATE TABLE IF NOT EXISTS `product_units` (
  `id` int NOT NULL AUTO_INCREMENT,
  `product_id` int NOT NULL,
  `unit_name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `pieces_per_unit` int NOT NULL DEFAULT '1',
  `status` enum('active','inactive') COLLATE utf8mb4_general_ci DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_unit` (`product_id`,`unit_name`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `purchases`
--

DROP TABLE IF EXISTS `purchases`;
CREATE TABLE IF NOT EXISTS `purchases` (
  `id` int NOT NULL AUTO_INCREMENT,
  `company_id` int DEFAULT NULL,
  `purchase_no` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `supplier_id` int DEFAULT NULL,
  `total_amount` decimal(12,2) NOT NULL,
  `tax_amount` decimal(12,2) DEFAULT '0.00',
  `discount` decimal(12,2) DEFAULT '0.00',
  `grand_total` decimal(12,2) NOT NULL,
  `paid_amount` decimal(12,2) DEFAULT '0.00',
  `payment_mode` enum('cash','card','upi','bank_transfer') COLLATE utf8mb4_general_ci DEFAULT 'cash',
  `notes` text COLLATE utf8mb4_general_ci,
  `user_id` int DEFAULT NULL,
  `purchase_date` date NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `purchase_no` (`purchase_no`),
  KEY `supplier_id` (`supplier_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `purchase_items`
--

DROP TABLE IF EXISTS `purchase_items`;
CREATE TABLE IF NOT EXISTS `purchase_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `purchase_id` int DEFAULT NULL,
  `product_id` int DEFAULT NULL,
  `quantity` int NOT NULL,
  `unit` varchar(50) COLLATE utf8mb4_general_ci DEFAULT 'PCS',
  `pieces_per_unit` int DEFAULT '1',
  `unit_price` decimal(10,2) NOT NULL,
  `total_price` decimal(12,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `purchase_id` (`purchase_id`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `quotations`
--

DROP TABLE IF EXISTS `quotations`;
CREATE TABLE IF NOT EXISTS `quotations` (
  `id` int NOT NULL AUTO_INCREMENT,
  `company_id` int DEFAULT NULL,
  `quotation_no` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `quotation_date` date NOT NULL,
  `validity_date` date DEFAULT NULL,
  `customer_id` int DEFAULT NULL,
  `customer_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `customer_phone` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `customer_address` text COLLATE utf8mb4_general_ci,
  `customer_email` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `customer_gstin` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `customer_pan` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `customer_tan` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `customer_tin` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `customer_state` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `customer_state_code` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_phone` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_address` text COLLATE utf8mb4_general_ci,
  `consignee_email` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_gstin` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_pan` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_tan` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_tin` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_state` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_state_code` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `carrier_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `vehicle_no` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `place_of_supply` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `subtotal` decimal(12,2) NOT NULL,
  `tax_amount` decimal(12,2) DEFAULT '0.00',
  `cgst` decimal(15,2) DEFAULT '0.00',
  `sgst` decimal(15,2) DEFAULT '0.00',
  `igst` decimal(15,2) DEFAULT '0.00',
  `discount` decimal(12,2) DEFAULT '0.00',
  `discount_type` enum('amount','percent') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'amount',
  `round_off` decimal(10,2) DEFAULT '0.00',
  `grand_total` decimal(12,2) NOT NULL,
  `notes` text COLLATE utf8mb4_general_ci,
  `user_id` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `quotation_no` (`quotation_no`),
  KEY `customer_id` (`customer_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `quotation_items`
--

DROP TABLE IF EXISTS `quotation_items`;
CREATE TABLE IF NOT EXISTS `quotation_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `quotation_id` int DEFAULT NULL,
  `product_id` int DEFAULT NULL,
  `product_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `description` text COLLATE utf8mb4_general_ci,
  `hsn_code` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `quantity` int NOT NULL,
  `unit` varchar(50) COLLATE utf8mb4_general_ci DEFAULT 'PCS',
  `pieces_per_unit` int DEFAULT '1',
  `unit_price` decimal(10,2) NOT NULL,
  `tax_percentage` decimal(5,2) DEFAULT '0.00',
  `tax_amount` decimal(10,2) DEFAULT '0.00',
  `cgst_amount` decimal(15,2) DEFAULT '0.00',
  `sgst_amount` decimal(15,2) DEFAULT '0.00',
  `igst_amount` decimal(15,2) DEFAULT '0.00',
  `total_price` decimal(12,2) NOT NULL,
  `mrp` decimal(10,2) DEFAULT '0.00',
  `discount_value` decimal(10,2) DEFAULT '0.00',
  `discount_type` enum('amount','percent') COLLATE utf8mb4_general_ci DEFAULT 'amount',
  PRIMARY KEY (`id`),
  KEY `quotation_id` (`quotation_id`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `salary_payments`
--

DROP TABLE IF EXISTS `salary_payments`;
CREATE TABLE IF NOT EXISTS `salary_payments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `employee_id` int NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `payment_date` date NOT NULL,
  `payment_mode` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `reference_no` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `notes` text COLLATE utf8mb4_general_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `employee_id` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `salary_records`
--

DROP TABLE IF EXISTS `salary_records`;
CREATE TABLE IF NOT EXISTS `salary_records` (
  `id` int NOT NULL AUTO_INCREMENT,
  `company_id` int NOT NULL,
  `employee_id` int NOT NULL,
  `month_year` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `status` enum('unpaid','paid','partially_paid') COLLATE utf8mb4_general_ci DEFAULT 'unpaid',
  `record_date` date NOT NULL,
  `notes` text COLLATE utf8mb4_general_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `employee_id` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `sales`
--

DROP TABLE IF EXISTS `sales`;
CREATE TABLE IF NOT EXISTS `sales` (
  `id` int NOT NULL AUTO_INCREMENT,
  `company_id` int DEFAULT NULL,
  `invoice_no` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `challan_no` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `challan_date` date DEFAULT NULL,
  `order_no` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `order_date` date DEFAULT NULL,
  `customer_id` int DEFAULT NULL,
  `invoice_type` enum('GST','NON GST') COLLATE utf8mb4_general_ci NOT NULL,
  `customer_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `customer_phone` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `customer_address` text COLLATE utf8mb4_general_ci,
  `customer_gstin` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `customer_pan` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `customer_tan` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `subtotal` decimal(12,2) NOT NULL,
  `tax_amount` decimal(12,2) DEFAULT '0.00',
  `cgst` decimal(15,2) DEFAULT '0.00',
  `sgst` decimal(15,2) DEFAULT '0.00',
  `igst` decimal(15,2) DEFAULT '0.00',
  `discount` decimal(12,2) DEFAULT '0.00',
  `discount_type` enum('amount','percent') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'amount',
  `round_off` decimal(10,2) DEFAULT '0.00',
  `grand_total` decimal(12,2) NOT NULL,
  `paid_amount` decimal(12,2) DEFAULT '0.00',
  `payment_mode` enum('cash','card','upi','bank_transfer') COLLATE utf8mb4_general_ci DEFAULT 'cash',
  `notes` text COLLATE utf8mb4_general_ci,
  `user_id` int DEFAULT NULL,
  `sale_date` date NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `carrier_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `vehicle_no` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `place_of_supply` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `customer_email` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `customer_tin` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_phone` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_address` text COLLATE utf8mb4_general_ci,
  `consignee_email` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_gstin` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_pan` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_tan` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_tin` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `customer_state` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `customer_state_code` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_state` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `consignee_state_code` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `invoice_no` (`invoice_no`),
  KEY `customer_id` (`customer_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `sale_items`
--

DROP TABLE IF EXISTS `sale_items`;
CREATE TABLE IF NOT EXISTS `sale_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `sale_id` int DEFAULT NULL,
  `product_id` int DEFAULT NULL,
  `product_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `description` text COLLATE utf8mb4_general_ci,
  `hsn_code` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `quantity` int NOT NULL,
  `unit` varchar(50) COLLATE utf8mb4_general_ci DEFAULT 'PCS',
  `pieces_per_unit` int DEFAULT '1',
  `unit_price` decimal(10,2) NOT NULL,
  `tax_percentage` decimal(5,2) DEFAULT '0.00',
  `tax_amount` decimal(10,2) DEFAULT '0.00',
  `cgst_amount` decimal(15,2) DEFAULT '0.00',
  `sgst_amount` decimal(15,2) DEFAULT '0.00',
  `igst_amount` decimal(15,2) DEFAULT '0.00',
  `total_price` decimal(12,2) NOT NULL,
  `mrp` decimal(10,2) DEFAULT '0.00',
  `discount_value` decimal(10,2) DEFAULT '0.00',
  `discount_type` enum('amount','percent') COLLATE utf8mb4_general_ci DEFAULT 'amount',
  PRIMARY KEY (`id`),
  KEY `sale_id` (`sale_id`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `stock_movements`
--

DROP TABLE IF EXISTS `stock_movements`;
CREATE TABLE IF NOT EXISTS `stock_movements` (
  `id` int NOT NULL AUTO_INCREMENT,
  `company_id` int DEFAULT NULL,
  `product_id` int DEFAULT NULL,
  `movement_type` enum('in','out') COLLATE utf8mb4_general_ci NOT NULL,
  `quantity` int NOT NULL,
  `reference_type` enum('purchase','sale','adjustment') COLLATE utf8mb4_general_ci NOT NULL,
  `reference_id` int DEFAULT NULL,
  `notes` text COLLATE utf8mb4_general_ci,
  `user_id` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `suppliers`
--

DROP TABLE IF EXISTS `suppliers`;
CREATE TABLE IF NOT EXISTS `suppliers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `company` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `phone` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `email` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `address` text COLLATE utf8mb4_general_ci,
  `gstin` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `status` enum('active','inactive') COLLATE utf8mb4_general_ci DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` 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 `users`
--

DROP TABLE IF EXISTS `users`;
CREATE TABLE IF NOT EXISTS `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `username` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `email` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `password` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `role` enum('administrator','sales_user') COLLATE utf8mb4_general_ci DEFAULT 'sales_user',
  `status` enum('active','inactive') COLLATE utf8mb4_general_ci DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
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 */;