-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `bims`
--

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

--
-- Table structure for table `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`
--

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, 'Your Company Name', 'Your Company Address', '+91-9876543210', 'info@company.com', '22AAAAA0000A1Z5', NULL, 'INV', 'PUR', '2025-09-11 15:05:54', '2025-09-11 15:05:54');

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

--
-- Table structure for table `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`
--

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

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

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

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,
  `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=3 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`, `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, 18.00, '1871827', '202509112047011467', 9, 5, 'active', '2025-09-11 15:17:41', '2025-09-12 03:01:21'),
(2, 'Videocon AC', 2, NULL, 'PCS', 0.00, 0.00, 18.00, '123455545', '202509120740468761', 1, 5, 'active', '2025-09-12 02:11:35', '2025-09-12 03:04:08');

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

--
-- Table structure for table `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, 50000.00, 7627.12, 0.00, 57627.12, 30000.00, 'bank_transfer', '', 1, '2025-09-12', '2025-09-12 02:11:55');

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

--
-- Table structure for table `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_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=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `purchase_items` (`id`, `purchase_id`, `product_id`, `quantity`, `unit_price`, `total_price`) VALUES
(1, 1, 1, 1, 20000.00, 20000.00),
(2, 1, 2, 1, 30000.00, 30000.00);

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

--
-- Table structure for table `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,
  `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',
  `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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `invoice_no` (`invoice_no`),
  KEY `customer_id` (`customer_id`),
  KEY `user_id` (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `sales` (`id`, `invoice_no`, `customer_id`, `customer_name`, `customer_phone`, `customer_address`, `customer_gstin`, `subtotal`, `tax_amount`, `discount`, `grand_total`, `paid_amount`, `payment_mode`, `notes`, `user_id`, `sale_date`, `created_at`) VALUES
(1, 'INV2025090001', 1, 'ABC Supplier', '9062060072', 'Kasba', '', 28990.00, 5218.20, 0.00, 34208.20, 0.00, 'card', '', 1, '2025-09-11', '2025-09-11 15:18:39'),
(2, 'INV2025090002', NULL, 'Walk-in Customer', '9062060072', 'Kolkata', '', 28990.00, 5218.20, 0.00, 34208.20, 0.00, 'cash', '', 1, '2025-09-12', '2025-09-12 03:01:21');

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

--
-- Table structure for table `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_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,
  PRIMARY KEY (`id`),
  KEY `sale_id` (`sale_id`),
  KEY `product_id` (`product_id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 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_price`, `tax_percentage`, `tax_amount`, `total_price`) VALUES
(4, 1, 1, 'Godrej Fridge 220 Litre', '', '1871827', 1, 28990.00, 18.00, 5218.20, 34208.20),
(5, 2, 1, 'Godrej Fridge 220 Litre', '', '1871827', 1, 28990.00, 18.00, 5218.20, 34208.20),
(6, 2, 2, 'Videocon AC', '', '123455545', 1, 0.00, 18.00, 0.00, 0.00);

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

--
-- Table structure for table `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=12 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
(6, 1, 'out', 1, 'sale', 1, NULL, 1, '2025-09-12 03:00:24'),
(2, 1, 'in', 1, 'purchase', 1, NULL, 1, '2025-09-12 02:11:55'),
(3, 2, 'in', 1, 'purchase', 1, NULL, 1, '2025-09-12 02:11:55'),
(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');

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

--
-- Table structure for table `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');
COMMIT;

