-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Oct 13, 2025 at 09:18 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: `bims`
--

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

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

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` (`id`, `name`, `description`, `status`, `created_at`) VALUES
(1, 'General', 'General Products', 'active', '2025-09-11 15:05:54'),
(2, 'Electronics', 'Electronic Items', 'active', '2025-09-11 15:05:54'),
(3, 'Clothing', 'Clothing & Apparel', 'active', '2025-09-11 15:05:54'),
(4, 'Food & Beverages', 'Food Items', 'active', '2025-09-11 15:05:54');

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

--
-- Table structure for table `company_settings`
--

DROP TABLE IF EXISTS `company_settings`;
CREATE TABLE IF NOT EXISTS `company_settings` (
  `id` int NOT NULL AUTO_INCREMENT,
  `company_name` varchar(255) COLLATE utf8mb4_general_ci NOT 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,
  `logo_path` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `invoice_prefix` varchar(10) COLLATE utf8mb4_general_ci DEFAULT 'INV',
  `purchase_prefix` varchar(10) COLLATE utf8mb4_general_ci DEFAULT 'PUR',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `company_settings`
--

INSERT INTO `company_settings` (`id`, `company_name`, `address`, `phone`, `email`, `gstin`, `logo_path`, `invoice_prefix`, `purchase_prefix`, `created_at`, `updated_at`) VALUES
(1, 'NJ Electronics Shop', 'BALGONA STATION ROAD. N. J NEW MARKET, BHATAR . PURBA BARDHAMAN', '6296120245', '', '19CJIPJ2551A1ZI', NULL, 'INV', 'PUR', '2025-09-11 15:05:54', '2025-09-22 12:36:10');

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

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

--
-- Dumping data for table `customers`
--

INSERT INTO `customers` (`id`, `name`, `company`, `phone`, `email`, `address`, `gstin`, `status`, `created_at`, `updated_at`) VALUES
(1, 'ABC Supplier', NULL, '9062060072', NULL, 'Kasba', '', 'active', '2025-09-12 03:00:24', '2025-09-12 03:00:24');

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

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

DROP TABLE IF EXISTS `expenses`;
CREATE TABLE IF NOT EXISTS `expenses` (
  `id` int NOT NULL AUTO_INCREMENT,
  `expense_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `expense_head_id` int DEFAULT NULL,
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `amount` decimal(12,2) NOT NULL,
  `gst_type` enum('inclusive','exclusive','none') CHARACTER SET utf8mb4 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') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'cash',
  `expense_date` date NOT NULL,
  `voucher_no` varchar(50) CHARACTER SET utf8mb4 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=MyISAM 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) 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 DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `expense_heads`
--

INSERT INTO `expense_heads` (`id`, `name`, `description`, `status`, `created_at`, `updated_at`) VALUES
(1, 'Office Rent', 'Monthly office rent expenses', 'active', '2025-09-12 03:17:34', '2025-09-12 03:17:34'),
(2, 'Electricity Bill', 'Electricity and power consumption charges', 'active', '2025-09-12 03:17:34', '2025-09-12 03:17:34'),
(3, 'Internet & Phone', 'Internet and telephone expenses', 'active', '2025-09-12 03:17:34', '2025-09-12 03:17:34'),
(4, 'Stationery', 'Office stationery and supplies', 'active', '2025-09-12 03:17:34', '2025-09-12 03:17:34'),
(5, 'Travel & Conveyance', 'Travel and conveyance expenses', 'active', '2025-09-12 03:17:34', '2025-09-12 03:17:34'),
(6, 'Professional Fees', 'Legal, audit, and professional service fees', 'active', '2025-09-12 03:17:34', '2025-09-12 03:17:34'),
(7, 'Marketing & Advertising', 'Marketing and advertising expenses', 'active', '2025-09-12 03:17:34', '2025-09-12 03:17:34'),
(8, 'Repairs & Maintenance', 'Equipment and facility maintenance', 'active', '2025-09-12 03:17:34', '2025-09-12 03:17:34'),
(9, 'Insurance', 'Insurance premiums', 'active', '2025-09-12 03:17:34', '2025-09-12 03:17:34'),
(10, 'Miscellaneous', 'Other miscellaneous expenses', 'active', '2025-09-12 03:17:34', '2025-09-12 03:17:34');

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

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

DROP TABLE IF EXISTS `payments`;
CREATE TABLE IF NOT EXISTS `payments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `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') COLLATE utf8mb4_general_ci DEFAULT 'cash',
  `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`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `payments`
--

INSERT INTO `payments` (`id`, `reference_type`, `reference_id`, `amount`, `payment_date`, `payment_mode`, `notes`, `user_id`, `created_at`) VALUES
(1, 'purchase', 1, 30000.00, '2025-09-12', 'card', '', 1, '2025-09-12 07:19:29');

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

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

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`id`, `name`, `category_id`, `description`, `unit`, `buying_price`, `selling_price`, `mrp`, `tax_percentage`, `hsn_code`, `barcode`, `stock_quantity`, `min_stock_level`, `status`, `created_at`, `updated_at`) VALUES
(1, 'Godrej Fridge 220 Litre', 2, '', 'PCS', 0.00, 28990.00, 0.00, 18.00, '1871827', '202509112047011467', 7, 5, 'active', '2025-09-11 15:17:41', '2025-10-13 02:50:36'),
(2, 'Videocon AC', 2, '', 'PCS', 0.00, 45000.00, 0.00, 18.00, '123455545', '202509120740468761', 9, 5, 'active', '2025-09-12 02:11:35', '2025-10-13 02:50:36'),
(3, 'polycarbonized ', 1, NULL, 'PCS', 0.00, 600.00, 0.00, 18.00, 'BAGSGAGSA', '202510101648025289', -1, 5, 'active', '2025-10-10 11:18:02', '2025-10-13 03:41:12'),
(4, 'test', 2, NULL, 'PCS', 440.00, 0.00, 0.00, 5.00, '', '202510130808054400', 34, 5, 'active', '2025-10-13 02:38:25', '2025-10-13 02:50:36'),
(5, 'respect baby diaper', 1, NULL, 'PCS', 0.00, 230.00, 0.00, 5.00, '', '202510130910003258', -20, 5, 'active', '2025-10-13 03:40:00', '2025-10-13 03:41:12');

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

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

--
-- Dumping data for table `purchases`
--

INSERT INTO `purchases` (`id`, `purchase_no`, `supplier_id`, `total_amount`, `tax_amount`, `discount`, `grand_total`, `paid_amount`, `payment_mode`, `notes`, `user_id`, `purchase_date`, `created_at`) VALUES
(1, 'PUR2025090001', 1, 98960.00, 748.00, 0.00, 114828.00, 30000.00, 'bank_transfer', '', 1, '2025-09-12', '2025-09-12 02:11:55');

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

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

--
-- Dumping data for table `purchase_items`
--

INSERT INTO `purchase_items` (`id`, `purchase_id`, `product_id`, `quantity`, `unit`, `pieces_per_unit`, `unit_price`, `total_price`) VALUES
(13, 1, 4, 34, 'PCS', 1, 440.00, 15708.00),
(12, 1, 2, 2, 'PCS', 1, 30000.00, 70800.00),
(11, 1, 1, 1, 'PCS', 1, 20000.00, 23600.00),
(10, 1, 3, 1, 'PCS', 1, 4000.00, 4720.00);

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

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

DROP TABLE IF EXISTS `sales`;
CREATE TABLE IF NOT EXISTS `sales` (
  `id` int NOT NULL AUTO_INCREMENT,
  `invoice_no` varchar(50) COLLATE utf8mb4_general_ci NOT 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,
  `subtotal` decimal(12,2) NOT NULL,
  `tax_amount` decimal(12,2) DEFAULT '0.00',
  `discount` decimal(12,2) DEFAULT '0.00',
  `discount_type` enum('amount','percent') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'amount',
  `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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `invoice_no` (`invoice_no`),
  KEY `customer_id` (`customer_id`),
  KEY `user_id` (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `sales`
--

INSERT INTO `sales` (`id`, `invoice_no`, `customer_id`, `invoice_type`, `customer_name`, `customer_phone`, `customer_address`, `customer_gstin`, `subtotal`, `tax_amount`, `discount`, `discount_type`, `grand_total`, `paid_amount`, `payment_mode`, `notes`, `user_id`, `sale_date`, `created_at`, `carrier_name`, `vehicle_no`, `place_of_supply`) VALUES
(1, 'INV2025090001', 1, 'NON GST', 'ABC Supplier', '9062060072', 'Kasba', '', 102980.00, 8046.00, 0.00, 'amount', 121462.40, 0.00, 'card', '', 1, '2025-09-11', '2025-09-11 15:18:39', NULL, NULL, NULL),
(2, 'INV2025090002', NULL, 'NON GST', 'Walk-in Customer', '9062060072', 'Kolkata', '', 28990.00, 5218.20, 0.00, 'amount', 34208.20, 0.00, 'cash', '', 1, '2025-09-12', '2025-09-12 03:01:21', NULL, NULL, NULL),
(6, 'INV-00003', NULL, 'NON GST', 'Walk-in Customer', '', '', '', 5000.00, 0.00, 0.00, 'amount', 5000.00, 0.00, 'cash', '', 1, '2025-09-22', '2025-09-22 12:30:52', NULL, NULL, NULL),
(7, 'INV-00004', 1, 'NON GST', 'ABC Supplier', '9062060072', 'Kasba', '', 73990.00, 5218.20, 10.00, 'percent', 87298.20, 0.00, 'cash', '', 1, '2025-09-22', '2025-09-22 13:07:34', NULL, NULL, NULL),
(8, 'INVG-00001', 1, 'GST', 'ABC Supplier', '9062060072', 'Kasba', '', 600.00, 108.00, 0.00, 'amount', 708.00, 0.00, 'cash', '', 1, '2025-10-10', '2025-10-10 11:18:43', '', '', ''),
(9, 'INVG-00002', 1, 'GST', 'ABC Supplier', '9062060072', 'Kasba', '', 5200.00, 338.00, 0.00, 'amount', 5538.00, 0.00, 'cash', '', 1, '2025-10-13', '2025-10-13 03:41:12', '', '', '');

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

--
-- 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',
  `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=MyISAM AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `sale_items`
--

INSERT INTO `sale_items` (`id`, `sale_id`, `product_id`, `product_name`, `description`, `hsn_code`, `quantity`, `unit`, `pieces_per_unit`, `unit_price`, `tax_percentage`, `tax_amount`, `total_price`, `mrp`, `discount_value`, `discount_type`) VALUES
(13, 1, 1, 'Godrej Fridge 220 Litre', '', '1871827', 2, 'PCS', 1, 28990.00, 18.00, 10436.40, 68416.40, 28990.00, 0.00, 'amount'),
(5, 2, 1, 'Godrej Fridge 220 Litre', '', '1871827', 1, 'PCS', 1, 28990.00, 18.00, 5218.20, 34208.20, 0.00, 0.00, 'amount'),
(6, 2, 2, 'Videocon AC', '', '123455545', 1, 'PCS', 1, 0.00, 18.00, 0.00, 0.00, 0.00, 0.00, 'amount'),
(10, 6, 2, 'Videocon AC', '', '123455545', 1, 'PCS', 1, 5000.00, 18.00, 0.00, 5000.00, 0.00, 0.00, 'amount'),
(15, 7, 2, 'Videocon AC', '', '123455545', 1, 'PCS', 1, 45000.00, 18.00, 8100.00, 53100.00, 45000.00, 0.00, 'amount'),
(12, 8, 3, 'polycarbonized ', '', 'BAGSGAGSA', 1, 'PCS', 1, 600.00, 18.00, 108.00, 708.00, 600.00, 0.00, 'amount'),
(14, 1, 2, 'Videocon AC', '', '123455545', 1, 'PCS', 1, 45000.00, 18.00, 8046.00, 52746.00, 45000.00, 300.00, 'amount'),
(16, 7, 1, 'Godrej Fridge 220 Litre', '', '1871827', 1, 'PCS', 1, 28990.00, 18.00, 5218.20, 34208.20, 28990.00, 0.00, 'amount'),
(17, 9, 3, 'polycarbonized ', '', 'BAGSGAGSA', 1, 'PCS', 1, 600.00, 18.00, 108.00, 708.00, 600.00, 0.00, 'amount'),
(18, 9, 5, 'respect baby diaper', '', '', 2, 'BOX', 10, 2300.00, 5.00, 230.00, 4830.00, 230.00, 0.00, 'amount');

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

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

--
-- Dumping data for table `stock_movements`
--

INSERT INTO `stock_movements` (`id`, `product_id`, `movement_type`, `quantity`, `reference_type`, `reference_id`, `notes`, `user_id`, `created_at`) VALUES
(15, 1, 'out', 2, 'sale', 1, NULL, 1, '2025-10-12 13:26:46'),
(29, 4, 'in', 34, 'purchase', 1, NULL, 1, '2025-10-13 02:50:36'),
(28, 2, 'in', 2, 'purchase', 1, NULL, 1, '2025-10-13 02:50:36'),
(7, 1, 'out', 1, 'sale', 2, NULL, 1, '2025-09-12 03:01:21'),
(8, 2, 'out', 1, 'sale', 2, NULL, 1, '2025-09-12 03:01:21'),
(12, 2, 'out', 1, 'sale', 6, NULL, 1, '2025-09-22 12:30:52'),
(17, 2, 'out', 1, 'sale', 7, NULL, 1, '2025-10-12 13:27:47'),
(14, 3, 'out', 1, 'sale', 8, NULL, 1, '2025-10-10 11:18:43'),
(16, 2, 'out', 1, 'sale', 1, NULL, 1, '2025-10-12 13:26:46'),
(18, 1, 'out', 1, 'sale', 7, NULL, 1, '2025-10-12 13:27:47'),
(27, 1, 'in', 1, 'purchase', 1, NULL, 1, '2025-10-13 02:50:36'),
(26, 3, 'in', 1, 'purchase', 1, NULL, 1, '2025-10-13 02:50:36'),
(30, 3, 'out', 1, 'sale', 9, NULL, 1, '2025-10-13 03:41:12'),
(31, 5, 'out', 2, 'sale', 9, NULL, 1, '2025-10-13 03:41:12');

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

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

--
-- Dumping data for table `suppliers`
--

INSERT INTO `suppliers` (`id`, `name`, `company`, `phone`, `email`, `address`, `gstin`, `status`, `created_at`, `updated_at`) VALUES
(1, 'ABC Supplier', '', '', '', 'Kolkata', '', 'active', '2025-09-12 01:56:20', '2025-09-12 01:56:20');

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

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

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `name`, `username`, `email`, `password`, `role`, `status`, `created_at`, `updated_at`) VALUES
(1, 'Administrator', 'admin', 'admin@company.com', '0192023a7bbd73250516f069df18b500', 'administrator', 'active', '2025-09-11 15:05:54', '2025-09-11 15:05:54'),
(2, 'Sales User', 'sales', 'sales@company.com', '32250170a0dca92d53ec9624f336ca24', 'sales_user', 'active', '2025-09-11 15:05:54', '2025-09-11 15:05:54');
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 */;