-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Nov 12, 2026 at 05:27 AM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.0.30

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

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

--
-- Table structure for table `brands`
--

CREATE TABLE `brands` (
  `id` int(10) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED NOT NULL,
  `brand_name` varchar(150) NOT NULL,
  `status` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `brands`
--

INSERT INTO `brands` (`id`, `company_id`, `brand_name`, `status`, `created_at`) VALUES
(1, 1, 'lemon', 'ACTIVE', '2026-09-04 06:00:19');

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

--
-- Table structure for table `business_days`
--

CREATE TABLE `business_days` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED NOT NULL,
  `store_id` int(10) UNSIGNED NOT NULL,
  `business_day_no` int(10) UNSIGNED NOT NULL,
  `open_at` datetime NOT NULL,
  `opened_by` int(10) UNSIGNED NOT NULL,
  `status` enum('OPEN','CLOSED') NOT NULL DEFAULT 'OPEN',
  `opening_cash` decimal(14,2) NOT NULL DEFAULT 0.00,
  `closing_cash` decimal(14,2) NOT NULL DEFAULT 0.00,
  `expected_cash` decimal(14,2) NOT NULL DEFAULT 0.00,
  `cash_difference` decimal(14,2) NOT NULL DEFAULT 0.00,
  `closed_by` int(10) UNSIGNED DEFAULT NULL,
  `closed_at` datetime DEFAULT NULL,
  `remarks` varchar(500) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `business_days`
--

INSERT INTO `business_days` (`id`, `company_id`, `store_id`, `business_day_no`, `open_at`, `opened_by`, `status`, `opening_cash`, `closing_cash`, `expected_cash`, `cash_difference`, `closed_by`, `closed_at`, `remarks`, `created_at`, `updated_at`) VALUES
(1, 1, 1, 1, '2026-09-02 11:32:22', 1, 'CLOSED', 10.00, 1000.00, 1030.00, -30.00, 1, '2026-09-07 09:12:03', '30 کیش نہیں ہے', '2026-09-02 06:32:22', '2026-09-07 04:12:03'),
(2, 1, 1, 2, '2026-09-07 09:51:12', 1, 'CLOSED', 10.00, 1000.00, 695.00, 305.00, 1, '2026-09-10 10:31:03', NULL, '2026-09-07 04:51:12', '2026-09-10 05:31:03'),
(3, 1, 1, 3, '2026-09-10 10:31:42', 1, 'OPEN', 0.00, 0.00, 0.00, 0.00, NULL, NULL, NULL, '2026-09-10 05:31:42', '2026-09-10 05:31:42');

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

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

CREATE TABLE `categories` (
  `id` int(10) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED NOT NULL,
  `category_name` varchar(150) NOT NULL,
  `parent_id` int(10) UNSIGNED DEFAULT NULL,
  `status` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `categories` (`id`, `company_id`, `category_name`, `parent_id`, `status`, `created_at`) VALUES
(1, 1, 'bakery', NULL, 'ACTIVE', '2026-09-04 05:59:39');

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

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

CREATE TABLE `companies` (
  `id` int(10) UNSIGNED NOT NULL,
  `company_name` varchar(150) NOT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `country` varchar(100) DEFAULT 'Pakistan',
  `currency` varchar(10) DEFAULT 'PKR',
  `tax_number` varchar(100) DEFAULT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `status` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `companies`
--

INSERT INTO `companies` (`id`, `company_name`, `phone`, `email`, `address`, `city`, `country`, `currency`, `tax_number`, `logo`, `status`, `created_at`, `updated_at`) VALUES
(1, 'My Mart', '', '', '', '', 'Pakistan', 'PKR', NULL, NULL, 'ACTIVE', '2026-09-02 03:56:11', '2026-09-02 03:56:11');

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

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

CREATE TABLE `customers` (
  `id` int(10) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED NOT NULL,
  `customer_code` varchar(50) DEFAULT NULL,
  `customer_name` varchar(200) NOT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `opening_balance` decimal(14,2) NOT NULL DEFAULT 0.00,
  `credit_limit` decimal(14,2) NOT NULL DEFAULT 0.00,
  `status` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `customers` (`id`, `company_id`, `customer_code`, `customer_name`, `phone`, `email`, `address`, `opening_balance`, `credit_limit`, `status`, `created_at`, `updated_at`) VALUES
(1, 1, NULL, 'Muhammad Tayyab Younas', '03467437921', 'c.ghs4gd@gmail.com', '4/GD Renala Khurd okara\r\n4gd', 100.00, 0.00, 'ACTIVE', '2026-09-05 08:41:37', '2026-09-05 08:49:22'),
(2, 1, '', 'Muhammad Abdullah', '03467437921', 'c.ghs4gd@gmail.com', '4/GD Renala Khurd okara\r\n4gd', 0.00, 0.00, 'ACTIVE', '2026-09-05 09:33:16', '2026-09-05 09:33:16'),
(3, 1, '', 'Abdul Rehman', '03281137921', 'c.ghs4gd@gmail.com', 'XHFR+49M', 50.00, 0.00, 'ACTIVE', '2026-09-05 09:58:49', '2026-09-05 09:58:49');

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

--
-- Table structure for table `customer_ledger`
--

CREATE TABLE `customer_ledger` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED NOT NULL,
  `store_id` int(10) UNSIGNED NOT NULL,
  `customer_id` int(10) UNSIGNED NOT NULL,
  `business_day_no` int(10) UNSIGNED DEFAULT NULL,
  `transaction_type` enum('OPENING','CREDIT_SALE','PAYMENT','SALE_RETURN','ADJUSTMENT_IN','ADJUSTMENT_OUT') NOT NULL,
  `reference_type` varchar(50) DEFAULT NULL,
  `reference_id` bigint(20) UNSIGNED DEFAULT NULL,
  `debit` decimal(14,2) NOT NULL DEFAULT 0.00,
  `credit` decimal(14,2) NOT NULL DEFAULT 0.00,
  `balance` decimal(14,2) NOT NULL DEFAULT 0.00,
  `remarks` varchar(255) DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `customer_ledger`
--

INSERT INTO `customer_ledger` (`id`, `company_id`, `store_id`, `customer_id`, `business_day_no`, `transaction_type`, `reference_type`, `reference_id`, `debit`, `credit`, `balance`, `remarks`, `created_by`, `created_at`) VALUES
(1, 1, 1, 1, 1, '', 'CUSTOMER', 1, 100.00, 0.00, 100.00, 'Opening balance increased from 0.00 to 100.00', 1, '2026-09-05 08:49:22'),
(2, 1, 1, 1, 1, 'PAYMENT', 'CUSTOMER_PAYMENT', 1, 0.00, 20.00, 80.00, 'Customer payment CP-000001', 1, '2026-09-05 08:55:52'),
(3, 1, 1, 3, 1, 'OPENING', 'CUSTOMER', 3, 50.00, 0.00, 50.00, 'Customer opening balance', 1, '2026-09-05 09:58:49'),
(4, 1, 1, 1, 1, 'CREDIT_SALE', 'SALE', 10, 40.00, 0.00, 120.00, 'Credit Sale INV-000010', 1, '2026-09-05 10:21:16');

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

--
-- Table structure for table `customer_payments`
--

CREATE TABLE `customer_payments` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED NOT NULL,
  `store_id` int(10) UNSIGNED NOT NULL,
  `customer_id` int(10) UNSIGNED NOT NULL,
  `business_day_no` int(10) UNSIGNED DEFAULT NULL,
  `payment_no` varchar(50) NOT NULL,
  `payment_date` datetime NOT NULL DEFAULT current_timestamp(),
  `amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `payment_type` enum('CASH','CARD','EASYPAISA','JAZZCASH','BANK') NOT NULL DEFAULT 'CASH',
  `account_id` bigint(20) UNSIGNED DEFAULT NULL,
  `remarks` varchar(255) DEFAULT NULL,
  `status` enum('COMPLETED','CANCELLED') NOT NULL DEFAULT 'COMPLETED',
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `customer_payments`
--

INSERT INTO `customer_payments` (`id`, `company_id`, `store_id`, `customer_id`, `business_day_no`, `payment_no`, `payment_date`, `amount`, `payment_type`, `account_id`, `remarks`, `status`, `created_by`, `created_at`, `updated_at`) VALUES
(1, 1, 1, 1, 1, 'CP-000001', '2026-09-05 13:55:52', 20.00, 'CASH', NULL, '', 'COMPLETED', 1, '2026-09-05 08:55:52', '2026-09-05 08:55:52');

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

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

CREATE TABLE `expenses` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED NOT NULL,
  `store_id` int(10) UNSIGNED NOT NULL,
  `business_day_no` int(10) UNSIGNED NOT NULL,
  `expense_type` enum('SUPPLIER_PAYMENT','SALARY_PAYMENT','PURCHASING','GENERAL_EXPENSE','OTHER_EXPENSE') NOT NULL,
  `supplier_id` int(10) UNSIGNED DEFAULT NULL,
  `purchase_id` bigint(20) UNSIGNED DEFAULT NULL,
  `amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `payment_method` enum('CASH','CARD','EASYPAISA','JAZZCASH') NOT NULL DEFAULT 'CASH',
  `reference_no` varchar(100) DEFAULT NULL,
  `reference_type` varchar(50) DEFAULT NULL,
  `reference_id` bigint(20) UNSIGNED DEFAULT NULL,
  `description` varchar(500) DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `expenses`
--

INSERT INTO `expenses` (`id`, `company_id`, `store_id`, `business_day_no`, `expense_type`, `supplier_id`, `purchase_id`, `amount`, `payment_method`, `reference_no`, `reference_type`, `reference_id`, `description`, `created_by`, `created_at`) VALUES
(1, 1, 1, 1, 'SUPPLIER_PAYMENT', 1, NULL, 10.00, 'CASH', '', 'SUPPLIER_PAYMENT', NULL, '0', 1, '2026-09-05 11:05:42'),
(3, 1, 1, 1, 'SUPPLIER_PAYMENT', 1, NULL, 10.00, 'CASH', '', 'SUPPLIER_PAYMENT', NULL, '0', 1, '2026-09-05 11:10:22'),
(4, 1, 1, 1, 'SUPPLIER_PAYMENT', 1, NULL, 10.00, 'CASH', '', 'SUPPLIER_PAYMENT', NULL, '0', 1, '2026-09-05 11:10:22'),
(5, 1, 1, 1, 'SUPPLIER_PAYMENT', 1, NULL, 10.00, 'CASH', '', 'SUPPLIER_PAYMENT', NULL, '0', 1, '2026-09-05 11:20:31'),
(6, 1, 1, 1, 'SUPPLIER_PAYMENT', 1, NULL, 10.00, 'CASH', '', 'SUPPLIER_PAYMENT', NULL, '0', 1, '2026-09-05 11:20:31'),
(7, 1, 1, 1, 'GENERAL_EXPENSE', 0, NULL, 1.00, 'CASH', '', NULL, NULL, '0', 1, '2026-09-05 11:22:12'),
(8, 1, 1, 1, 'GENERAL_EXPENSE', 0, NULL, 1.00, 'CASH', '', NULL, NULL, '0', 1, '2026-09-05 11:22:12'),
(9, 1, 1, 1, 'GENERAL_EXPENSE', 0, NULL, 1.00, 'CASH', '', NULL, NULL, '0', 1, '2026-09-05 11:25:27'),
(10, 1, 1, 1, 'SALARY_PAYMENT', 0, NULL, 1.00, 'CASH', '', NULL, NULL, '0', 1, '2026-09-05 11:25:46'),
(11, 1, 1, 1, 'GENERAL_EXPENSE', 0, NULL, 1.00, 'CASH', '', NULL, NULL, '0', 1, '2026-09-07 03:53:28');

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

--
-- Table structure for table `inventory_batches`
--

CREATE TABLE `inventory_batches` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED NOT NULL,
  `store_id` int(10) UNSIGNED NOT NULL,
  `warehouse_id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `purchase_id` bigint(20) UNSIGNED DEFAULT NULL,
  `purchase_item_id` bigint(20) UNSIGNED DEFAULT NULL,
  `batch_no` varchar(100) DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `purchase_qty` decimal(14,3) NOT NULL DEFAULT 0.000,
  `remaining_qty` decimal(14,3) NOT NULL DEFAULT 0.000,
  `cost_price` decimal(14,2) NOT NULL DEFAULT 0.00,
  `sale_price` decimal(14,2) NOT NULL DEFAULT 0.00,
  `status` enum('ACTIVE','EXPIRED','DEPLETED','CANCELLED') NOT NULL DEFAULT 'ACTIVE',
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `inventory_batches`
--

INSERT INTO `inventory_batches` (`id`, `company_id`, `store_id`, `warehouse_id`, `product_id`, `purchase_id`, `purchase_item_id`, `batch_no`, `expiry_date`, `purchase_qty`, `remaining_qty`, `cost_price`, `sale_price`, `status`, `created_by`, `created_at`, `updated_at`) VALUES
(1, 1, 1, 1, 6, 8, 9, '23', '2026-12-02', 2.000, 0.000, 120.00, 150.00, 'DEPLETED', 1, '2026-09-08 05:15:12', '2026-09-08 05:19:27'),
(2, 1, 1, 1, 6, 8, 10, '29', '2027-02-04', 1.000, 0.000, 120.00, 150.00, 'DEPLETED', 1, '2026-09-08 05:15:12', '2026-09-09 05:55:58');

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

--
-- Table structure for table `inventory_stock`
--

CREATE TABLE `inventory_stock` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED NOT NULL,
  `store_id` int(10) UNSIGNED NOT NULL,
  `warehouse_id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `quantity` decimal(14,3) NOT NULL DEFAULT 0.000,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `inventory_stock`
--

INSERT INTO `inventory_stock` (`id`, `company_id`, `store_id`, `warehouse_id`, `product_id`, `quantity`, `created_at`, `updated_at`) VALUES
(1, 1, 1, 1, 1, 10.000, '2026-09-03 04:44:33', '2026-09-09 05:37:56'),
(4, 1, 1, 1, 2, 1.000, '2026-09-04 06:05:11', '2026-09-05 10:21:17'),
(6, 1, 1, 1, 4, 2.000, '2026-09-07 04:58:11', '2026-09-07 04:58:11'),
(7, 1, 1, 1, 6, 0.000, '2026-09-08 05:15:12', '2026-09-09 05:55:58');

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

--
-- Table structure for table `permissions`
--

CREATE TABLE `permissions` (
  `id` int(10) UNSIGNED NOT NULL,
  `permission_key` varchar(100) NOT NULL,
  `permission_name` varchar(150) NOT NULL,
  `module_name` varchar(100) NOT NULL,
  `description` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `permissions`
--

INSERT INTO `permissions` (`id`, `permission_key`, `permission_name`, `module_name`, `description`) VALUES
(1, 'dashboard.view', 'View Dashboard', 'Dashboard', NULL),
(2, 'pos.access', 'Access POS', 'POS', NULL),
(3, 'product.view', 'View Products', 'Products', NULL),
(4, 'product.add', 'Add Product', 'Products', NULL),
(5, 'product.edit', 'Edit Product', 'Products', NULL),
(6, 'product.delete', 'Delete Product', 'Products', NULL),
(7, 'category.manage', 'Manage Categories', 'Products', NULL),
(8, 'brand.manage', 'Manage Brands', 'Products', NULL),
(9, 'unit.manage', 'Manage Units', 'Products', NULL),
(10, 'inventory.view', 'View Inventory', 'Inventory', NULL),
(11, 'inventory.adjust', 'Adjust Stock', 'Inventory', NULL),
(12, 'inventory.transfer', 'Transfer Stock', 'Inventory', NULL),
(13, 'purchase.view', 'View Purchases', 'Purchases', NULL),
(14, 'purchase.add', 'Create Purchase', 'Purchases', NULL),
(15, 'purchase.edit', 'Edit Purchase', 'Purchases', NULL),
(16, 'purchase.delete', 'Delete Purchase', 'Purchases', NULL),
(17, 'sale.view', 'View Sales', 'Sales', NULL),
(18, 'sale.return', 'Sale Return', 'Sales', NULL),
(19, 'customer.view', 'View Customers', 'Customers', NULL),
(20, 'customer.manage', 'Manage Customers', 'Customers', NULL),
(21, 'supplier.view', 'View Suppliers', 'Suppliers', NULL),
(22, 'supplier.manage', 'Manage Suppliers', 'Suppliers', NULL),
(23, 'expense.manage', 'Manage Expenses', 'Accounts', NULL),
(24, 'report.view', 'View Reports', 'Reports', NULL),
(25, 'user.manage', 'Manage Users', 'Administration', NULL),
(26, 'role.manage', 'Manage Roles', 'Administration', NULL),
(27, 'settings.manage', 'Manage Settings', 'Administration', NULL);

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

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

CREATE TABLE `products` (
  `id` int(10) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED NOT NULL,
  `sku` varchar(100) DEFAULT NULL,
  `product_name` varchar(200) NOT NULL,
  `category_id` int(10) UNSIGNED DEFAULT NULL,
  `brand_id` int(10) UNSIGNED DEFAULT NULL,
  `unit_id` int(10) UNSIGNED DEFAULT NULL,
  `purchase_price` decimal(14,2) DEFAULT 0.00,
  `sale_price` decimal(14,2) DEFAULT 0.00,
  `wholesale_price` decimal(14,2) DEFAULT 0.00,
  `min_sale_price` decimal(14,2) DEFAULT 0.00,
  `tax_percent` decimal(6,2) DEFAULT 0.00,
  `reorder_level` decimal(14,3) DEFAULT 0.000,
  `allow_negative_stock` tinyint(1) DEFAULT 0,
  `track_batch` tinyint(1) DEFAULT 0,
  `track_expiry` tinyint(1) DEFAULT 0,
  `image` varchar(255) DEFAULT NULL,
  `status` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `products` (`id`, `company_id`, `sku`, `product_name`, `category_id`, `brand_id`, `unit_id`, `purchase_price`, `sale_price`, `wholesale_price`, `min_sale_price`, `tax_percent`, `reorder_level`, `allow_negative_stock`, `track_batch`, `track_expiry`, `image`, `status`, `created_at`, `updated_at`) VALUES
(1, 1, 'CC-1500', 'Coca Cola 1.5 Liter', NULL, NULL, NULL, 100.00, 135.00, 125.00, 110.00, 0.00, 0.000, 1, 0, 0, NULL, 'ACTIVE', '2026-09-02 05:37:46', '2026-09-02 05:51:14'),
(2, 1, '12221', 'lemon biscut', 1, 1, 1, 35.00, 40.00, 37.00, 36.00, 0.00, 0.000, 0, 1, 0, NULL, 'ACTIVE', '2026-09-04 06:04:15', '2026-09-04 06:04:15'),
(3, 1, '213', 'lemon cola', NULL, 1, 1, 120.00, 150.00, 125.00, 124.00, 0.00, 0.000, 0, 0, 0, NULL, 'ACTIVE', '2026-09-05 10:22:48', '2026-09-05 10:22:48'),
(4, 1, '121', 'Super Biscout', 1, NULL, 1, 35.00, 40.00, 38.00, 27.00, 0.00, 0.000, 0, 0, 0, NULL, 'ACTIVE', '2026-09-07 04:57:48', '2026-09-07 04:57:48'),
(5, 1, '1223', 'nimko', 1, NULL, 1, 100.00, 130.00, 120.00, 110.00, 0.00, 10.000, 0, 1, 1, NULL, 'ACTIVE', '2026-09-07 08:06:27', '2026-09-07 08:06:27'),
(6, 1, '322', 'coca colola', NULL, NULL, 1, 120.00, 150.00, 130.00, 125.00, 0.00, 0.000, 0, 1, 1, NULL, 'ACTIVE', '2026-09-08 04:29:05', '2026-09-08 04:29:05');

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

--
-- Table structure for table `product_barcodes`
--

CREATE TABLE `product_barcodes` (
  `id` int(10) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `barcode` varchar(100) NOT NULL,
  `barcode_type` varchar(50) DEFAULT 'Retail',
  `is_primary` tinyint(1) DEFAULT 0,
  `status` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `product_barcodes`
--

INSERT INTO `product_barcodes` (`id`, `company_id`, `product_id`, `barcode`, `barcode_type`, `is_primary`, `status`, `created_at`) VALUES
(1, 1, 1, '211212321', 'Retail', 0, 'ACTIVE', '2026-09-02 06:02:56'),
(2, 1, 2, '2132', 'Retail', 1, 'ACTIVE', '2026-09-04 06:04:15'),
(3, 1, 4, '21211', 'Retail', 1, 'ACTIVE', '2026-09-07 04:57:48'),
(4, 1, 5, '12121', 'Retail', 1, 'ACTIVE', '2026-09-07 08:06:27'),
(5, 1, 6, '23232', 'Retail', 1, 'ACTIVE', '2026-09-08 04:29:05');

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

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

CREATE TABLE `purchases` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED NOT NULL,
  `store_id` int(10) UNSIGNED NOT NULL,
  `business_day_no` int(11) NOT NULL,
  `warehouse_id` int(10) UNSIGNED NOT NULL,
  `supplier_id` int(10) UNSIGNED NOT NULL,
  `invoice_no` varchar(50) NOT NULL,
  `supplier_invoice_no` varchar(100) DEFAULT NULL,
  `purchase_date` datetime NOT NULL,
  `subtotal` decimal(14,2) NOT NULL DEFAULT 0.00,
  `discount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `tax` decimal(14,2) NOT NULL DEFAULT 0.00,
  `grand_total` decimal(14,2) NOT NULL DEFAULT 0.00,
  `paid_amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `due_amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `payment_type` enum('CASH','CARD','EASYPAISA','JAZZCASH','CREDIT') NOT NULL DEFAULT 'CASH',
  `purchase_type` enum('RETAIL','WHOLESALE') NOT NULL DEFAULT 'RETAIL',
  `status` enum('COMPLETED','CANCELLED') NOT NULL DEFAULT 'COMPLETED',
  `remarks` varchar(255) DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `purchases` (`id`, `company_id`, `store_id`, `business_day_no`, `warehouse_id`, `supplier_id`, `invoice_no`, `supplier_invoice_no`, `purchase_date`, `subtotal`, `discount`, `tax`, `grand_total`, `paid_amount`, `due_amount`, `payment_type`, `purchase_type`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`) VALUES
(1, 1, 1, 1, 1, 1, 'PUR-000001', '1042', '2026-09-04 10:51:00', 270.00, 1.05, 0.00, 268.95, 200.00, 68.95, 'CREDIT', 'RETAIL', 'COMPLETED', '', 1, '2026-09-04 06:05:10', '2026-09-04 06:05:10'),
(2, 1, 1, 1, 1, 1, 'PUR-000002', '32', '2026-09-05 12:26:00', 100.00, 0.00, 0.00, 100.00, 0.00, 100.00, 'CREDIT', 'RETAIL', 'COMPLETED', '', 1, '2026-09-05 07:27:55', '2026-09-05 07:27:55'),
(3, 1, 1, 2, 1, 1, 'PUR-000003', '65', '2026-09-07 09:56:00', 70.00, 0.00, 0.00, 70.00, 70.00, 0.00, 'CASH', 'RETAIL', 'COMPLETED', '', 1, '2026-09-07 04:58:10', '2026-09-07 04:58:10'),
(8, 1, 1, 2, 1, 1, 'PUR-002-000004', '45', '2026-09-08 09:58:00', 360.00, 0.00, 0.00, 360.00, 360.00, 0.00, 'CASH', 'RETAIL', 'COMPLETED', '', 1, '2026-09-08 05:15:12', '2026-09-08 05:15:12');

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

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

CREATE TABLE `purchase_items` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `purchase_id` bigint(20) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `barcode` varchar(100) DEFAULT NULL,
  `product_name` varchar(200) NOT NULL,
  `quantity` decimal(14,3) NOT NULL DEFAULT 0.000,
  `unit_price` decimal(14,2) NOT NULL DEFAULT 0.00,
  `discount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `tax_percent` decimal(6,2) NOT NULL DEFAULT 0.00,
  `tax_amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `line_total` decimal(14,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `purchase_items` (`id`, `purchase_id`, `product_id`, `barcode`, `product_name`, `quantity`, `unit_price`, `discount`, `tax_percent`, `tax_amount`, `line_total`, `created_at`) VALUES
(1, 1, 1, '211212321', 'Coca Cola 1.5 Liter', 1.000, 200.00, 1.05, 0.00, 0.00, 198.95, '2026-09-04 06:05:10'),
(2, 1, 2, '2132', 'lemon biscut', 2.000, 35.00, 0.00, 0.00, 0.00, 70.00, '2026-09-04 06:05:11'),
(3, 2, 1, '211212321', 'Coca Cola 1.5 Liter', 1.000, 100.00, 0.00, 0.00, 0.00, 100.00, '2026-09-05 07:27:55'),
(4, 3, 4, '21211', 'Super Biscout', 2.000, 35.00, 0.00, 0.00, 0.00, 70.00, '2026-09-07 04:58:10'),
(9, 8, 6, '23232', 'coca colola', 2.000, 120.00, 0.00, 0.00, 0.00, 240.00, '2026-09-08 05:15:12'),
(10, 8, 6, '23232', 'coca colola', 1.000, 120.00, 0.00, 0.00, 0.00, 120.00, '2026-09-08 05:15:12');

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

--
-- Table structure for table `purchase_returns`
--

CREATE TABLE `purchase_returns` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED NOT NULL,
  `store_id` int(10) UNSIGNED NOT NULL,
  `business_day_no` int(10) UNSIGNED DEFAULT NULL,
  `warehouse_id` int(10) UNSIGNED DEFAULT NULL,
  `purchase_id` bigint(20) UNSIGNED NOT NULL,
  `supplier_id` int(10) UNSIGNED NOT NULL,
  `return_no` varchar(50) NOT NULL,
  `return_date` datetime NOT NULL DEFAULT current_timestamp(),
  `subtotal` decimal(14,2) NOT NULL DEFAULT 0.00,
  `discount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `tax` decimal(14,2) NOT NULL DEFAULT 0.00,
  `return_amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `refund_amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `payment_type` enum('CASH','CARD','EASYPAISA','JAZZCASH','CREDIT','MIXED') NOT NULL DEFAULT 'CREDIT',
  `status` enum('COMPLETED','CANCELLED') NOT NULL DEFAULT 'COMPLETED',
  `remarks` varchar(255) DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `purchase_returns`
--

INSERT INTO `purchase_returns` (`id`, `company_id`, `store_id`, `business_day_no`, `warehouse_id`, `purchase_id`, `supplier_id`, `return_no`, `return_date`, `subtotal`, `discount`, `tax`, `return_amount`, `refund_amount`, `payment_type`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`) VALUES
(1, 1, 1, 1, 1, 1, 1, 'PR-000001', '2026-09-04 12:04:20', 35.00, 0.00, 0.00, 35.00, 0.00, 'CREDIT', 'CANCELLED', '', 1, '2026-09-04 07:04:20', '2026-09-05 07:10:45'),
(2, 1, 1, 1, 1, 2, 1, 'PR-000002', '2026-09-05 12:32:51', 100.00, 0.00, 0.00, 100.00, 0.00, 'CREDIT', 'CANCELLED', '', 1, '2026-09-05 07:32:51', '2026-09-05 08:20:13');

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

--
-- Table structure for table `purchase_return_items`
--

CREATE TABLE `purchase_return_items` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `return_id` bigint(20) UNSIGNED NOT NULL,
  `purchase_id` bigint(20) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `product_name` varchar(200) NOT NULL,
  `barcode` varchar(100) DEFAULT NULL,
  `quantity` decimal(14,3) NOT NULL DEFAULT 0.000,
  `unit_price` decimal(14,2) NOT NULL DEFAULT 0.00,
  `discount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `tax_percent` decimal(6,2) NOT NULL DEFAULT 0.00,
  `tax_amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `line_total` decimal(14,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `purchase_return_items`
--

INSERT INTO `purchase_return_items` (`id`, `return_id`, `purchase_id`, `product_id`, `product_name`, `barcode`, `quantity`, `unit_price`, `discount`, `tax_percent`, `tax_amount`, `line_total`, `created_at`) VALUES
(1, 1, 1, 2, 'lemon biscut', '2132', 1.000, 35.00, 0.00, 0.00, 0.00, 35.00, '2026-09-04 07:04:20'),
(2, 2, 2, 1, 'Coca Cola 1.5 Liter', '211212321', 1.000, 100.00, 0.00, 0.00, 0.00, 100.00, '2026-09-05 07:32:52');

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

--
-- Table structure for table `roles`
--

CREATE TABLE `roles` (
  `id` int(10) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED NOT NULL,
  `role_name` varchar(100) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `status` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `roles`
--

INSERT INTO `roles` (`id`, `company_id`, `role_name`, `description`, `status`, `created_at`) VALUES
(1, 1, 'Super Admin', 'Full access to all Enterprise POS modules', 'ACTIVE', '2026-09-02 04:07:06'),
(2, 1, 'Cashier', 'Sales Counter Cashier', 'ACTIVE', '2026-09-02 05:13:46');

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

--
-- Table structure for table `role_permissions`
--

CREATE TABLE `role_permissions` (
  `id` int(10) UNSIGNED NOT NULL,
  `role_id` int(10) UNSIGNED NOT NULL,
  `permission_id` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `role_permissions`
--

INSERT INTO `role_permissions` (`id`, `role_id`, `permission_id`) VALUES
(5, 1, 1),
(10, 1, 2),
(14, 1, 3),
(11, 1, 4),
(13, 1, 5),
(12, 1, 6),
(2, 1, 7),
(1, 1, 8),
(26, 1, 9),
(9, 1, 10),
(7, 1, 11),
(8, 1, 12),
(18, 1, 13),
(15, 1, 14),
(17, 1, 15),
(16, 1, 16),
(22, 1, 17),
(21, 1, 18),
(4, 1, 19),
(3, 1, 20),
(25, 1, 21),
(24, 1, 22),
(6, 1, 23),
(19, 1, 24),
(27, 1, 25),
(20, 1, 26),
(23, 1, 27),
(40, 2, 2),
(41, 2, 3),
(42, 2, 17),
(39, 2, 19);

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

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

CREATE TABLE `sales` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED NOT NULL,
  `store_id` int(10) UNSIGNED NOT NULL,
  `business_day_no` int(10) UNSIGNED DEFAULT NULL,
  `warehouse_id` int(10) UNSIGNED DEFAULT NULL,
  `invoice_no` varchar(50) NOT NULL,
  `sale_date` datetime NOT NULL DEFAULT current_timestamp(),
  `customer_id` int(10) UNSIGNED DEFAULT NULL,
  `subtotal` decimal(14,2) NOT NULL DEFAULT 0.00,
  `discount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `tax` decimal(14,2) NOT NULL DEFAULT 0.00,
  `grand_total` decimal(14,2) NOT NULL DEFAULT 0.00,
  `paid_amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `change_amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `payment_type` enum('CASH','CARD','EASYPAISA','JAZZCASH','CREDIT','MIXED') NOT NULL DEFAULT 'CASH',
  `sale_type` enum('RETAIL','WHOLESALE') NOT NULL DEFAULT 'RETAIL',
  `status` enum('COMPLETED','CANCELLED','RETURNED') NOT NULL DEFAULT 'COMPLETED',
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `sales` (`id`, `company_id`, `store_id`, `business_day_no`, `warehouse_id`, `invoice_no`, `sale_date`, `customer_id`, `subtotal`, `discount`, `tax`, `grand_total`, `paid_amount`, `change_amount`, `payment_type`, `sale_type`, `status`, `created_by`, `created_at`, `updated_at`) VALUES
(1, 1, 1, 1, 1, 'INV-000001', '2026-09-02 08:51:45', NULL, 135.00, 5.00, 0.00, 130.00, 130.00, 0.00, 'CASH', 'RETAIL', 'COMPLETED', 1, '2026-09-02 06:51:45', '2026-09-02 06:51:45'),
(2, 1, 1, 1, 1, 'INV-000002', '2026-09-02 09:46:54', NULL, 135.00, 0.00, 0.00, 135.00, 135.00, 0.00, 'CASH', 'RETAIL', 'COMPLETED', 1, '2026-09-02 07:46:54', '2026-09-02 07:46:54'),
(3, 1, 1, 1, 1, 'INV-000003', '2026-09-02 09:48:14', NULL, 135.00, 0.00, 0.00, 135.00, 135.00, 0.00, 'CASH', 'RETAIL', 'COMPLETED', 1, '2026-09-02 07:48:14', '2026-09-02 07:48:14'),
(4, 1, 1, 1, 1, 'INV-000004', '2026-09-02 09:49:25', NULL, 135.00, 0.00, 0.00, 135.00, 135.00, 0.00, 'CASH', 'RETAIL', 'COMPLETED', 1, '2026-09-02 07:49:25', '2026-09-02 07:49:25'),
(5, 1, 1, 1, 1, 'INV-000005', '2026-09-03 08:58:55', NULL, 135.00, 0.00, 0.00, 135.00, 135.00, 0.00, 'CASH', 'RETAIL', 'COMPLETED', 1, '2026-09-03 03:58:55', '2026-09-03 03:58:55'),
(6, 1, 1, 1, 1, 'INV-000006', '2026-09-03 09:00:24', NULL, 135.00, 0.00, 0.00, 135.00, 135.00, 0.00, 'CASH', 'RETAIL', 'COMPLETED', 1, '2026-09-03 04:00:24', '2026-09-03 04:00:24'),
(7, 1, 1, 1, 1, 'INV-000007', '2026-09-03 09:15:15', NULL, 135.00, 0.00, 0.00, 135.00, 135.00, 0.00, 'CASH', 'RETAIL', 'COMPLETED', 1, '2026-09-03 04:15:15', '2026-09-03 04:15:15'),
(8, 1, 1, 1, 1, 'INV-000008', '2026-09-03 09:18:27', NULL, 135.00, 0.00, 0.00, 135.00, 135.00, 0.00, 'CASH', 'RETAIL', 'COMPLETED', 1, '2026-09-03 04:18:27', '2026-09-03 04:18:27'),
(9, 1, 1, 1, 1, 'INV-000009', '2026-09-03 10:17:54', NULL, 135.00, 0.00, 0.00, 135.00, 135.00, 0.00, 'CASH', 'RETAIL', 'RETURNED', 1, '2026-09-03 05:17:54', '2026-09-03 05:37:18'),
(10, 1, 1, 1, 1, 'INV-000010', '2026-09-05 15:21:16', 1, 40.00, 0.00, 0.00, 40.00, 0.00, 0.00, 'CREDIT', 'RETAIL', 'COMPLETED', 1, '2026-09-05 10:21:16', '2026-09-05 10:21:16'),
(12, 1, 1, 2, 1, 'INV-002-000001', '2026-09-08 10:19:27', NULL, 300.00, 0.00, 0.00, 300.00, 300.00, 0.00, 'CASH', 'RETAIL', 'COMPLETED', 1, '2026-09-08 05:19:27', '2026-09-08 05:19:27'),
(13, 1, 1, 2, 1, 'INV-002-000002', '2026-09-09 10:37:56', NULL, 270.00, 15.00, 0.00, 255.00, 255.00, 0.00, 'CASH', 'RETAIL', 'COMPLETED', 1, '2026-09-09 05:37:56', '2026-09-09 05:37:56'),
(16, 1, 1, 2, 1, 'INV-002-000003', '2026-09-09 10:55:58', NULL, 150.00, 20.00, 0.00, 130.00, 130.00, 0.00, 'CASH', 'RETAIL', 'COMPLETED', 1, '2026-09-09 05:55:58', '2026-09-09 05:55:58');

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

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

CREATE TABLE `sale_items` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `sale_id` bigint(20) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `barcode` varchar(100) DEFAULT NULL,
  `product_name` varchar(200) NOT NULL,
  `quantity` decimal(14,3) NOT NULL DEFAULT 1.000,
  `unit_price` decimal(14,2) NOT NULL DEFAULT 0.00,
  `discount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `tax_percent` decimal(6,2) NOT NULL DEFAULT 0.00,
  `tax_amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `line_total` decimal(14,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `sale_items` (`id`, `sale_id`, `product_id`, `barcode`, `product_name`, `quantity`, `unit_price`, `discount`, `tax_percent`, `tax_amount`, `line_total`, `created_at`) VALUES
(1, 1, 1, '211212321', 'Coca Cola 1.5 Liter', 1.000, 135.00, 0.00, 0.00, 0.00, 135.00, '2026-09-02 06:51:45'),
(2, 2, 1, '211212321', 'Coca Cola 1.5 Liter', 1.000, 135.00, 0.00, 0.00, 0.00, 135.00, '2026-09-02 07:46:54'),
(3, 3, 1, '211212321', 'Coca Cola 1.5 Liter', 1.000, 135.00, 0.00, 0.00, 0.00, 135.00, '2026-09-02 07:48:14'),
(4, 4, 1, '211212321', 'Coca Cola 1.5 Liter', 1.000, 135.00, 0.00, 0.00, 0.00, 135.00, '2026-09-02 07:49:25'),
(5, 5, 1, '211212321', 'Coca Cola 1.5 Liter', 1.000, 135.00, 0.00, 0.00, 0.00, 135.00, '2026-09-03 03:58:55'),
(6, 6, 1, '211212321', 'Coca Cola 1.5 Liter', 1.000, 135.00, 0.00, 0.00, 0.00, 135.00, '2026-09-03 04:00:24'),
(7, 7, 1, '211212321', 'Coca Cola 1.5 Liter', 1.000, 135.00, 0.00, 0.00, 0.00, 135.00, '2026-09-03 04:15:15'),
(8, 8, 1, '211212321', 'Coca Cola 1.5 Liter', 1.000, 135.00, 0.00, 0.00, 0.00, 135.00, '2026-09-03 04:18:27'),
(9, 9, 1, '211212321', 'Coca Cola 1.5 Liter', 1.000, 135.00, 0.00, 0.00, 0.00, 135.00, '2026-09-03 05:17:54'),
(10, 10, 2, '2132', 'lemon biscut', 1.000, 40.00, 0.00, 0.00, 0.00, 40.00, '2026-09-05 10:21:16'),
(12, 12, 6, '23232', 'coca colola', 2.000, 150.00, 0.00, 0.00, 0.00, 300.00, '2026-09-08 05:19:27'),
(13, 13, 1, '211212321', 'Coca Cola 1.5 Liter', 2.000, 135.00, 0.00, 0.00, 0.00, 270.00, '2026-09-09 05:37:56'),
(16, 16, 6, '23232', 'coca colola', 1.000, 150.00, 10.00, 0.00, 0.00, 140.00, '2026-09-09 05:55:58');

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

--
-- Table structure for table `sale_item_batches`
--

CREATE TABLE `sale_item_batches` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `sale_item_id` bigint(20) UNSIGNED NOT NULL,
  `sale_id` bigint(20) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED NOT NULL,
  `store_id` int(10) UNSIGNED NOT NULL,
  `warehouse_id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `inventory_batch_id` bigint(20) UNSIGNED NOT NULL,
  `batch_no` varchar(100) DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `quantity` decimal(14,3) NOT NULL DEFAULT 0.000,
  `unit_cost` decimal(14,2) NOT NULL DEFAULT 0.00,
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `sale_item_batches`
--

INSERT INTO `sale_item_batches` (`id`, `sale_item_id`, `sale_id`, `company_id`, `store_id`, `warehouse_id`, `product_id`, `inventory_batch_id`, `batch_no`, `expiry_date`, `quantity`, `unit_cost`, `created_by`, `created_at`) VALUES
(3, 12, 12, 1, 1, 1, 6, 1, '23', '2026-12-02', 2.000, 120.00, 1, '2026-09-08 05:19:27'),
(6, 16, 16, 1, 1, 1, 6, 2, '29', '2027-02-04', 1.000, 120.00, 1, '2026-09-09 05:55:58');

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

--
-- Table structure for table `sale_returns`
--

CREATE TABLE `sale_returns` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED NOT NULL,
  `store_id` int(10) UNSIGNED NOT NULL,
  `business_day_no` int(10) UNSIGNED DEFAULT NULL,
  `warehouse_id` int(10) UNSIGNED DEFAULT NULL,
  `sale_id` bigint(20) UNSIGNED NOT NULL,
  `return_no` varchar(50) NOT NULL,
  `return_date` datetime NOT NULL DEFAULT current_timestamp(),
  `subtotal` decimal(14,2) NOT NULL DEFAULT 0.00,
  `discount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `tax` decimal(14,2) NOT NULL DEFAULT 0.00,
  `return_amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `refund_amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `payment_type` enum('CASH','CARD','EASYPAISA','JAZZCASH','CREDIT','MIXED') NOT NULL DEFAULT 'CASH',
  `status` enum('COMPLETED','CANCELLED') NOT NULL DEFAULT 'COMPLETED',
  `remarks` varchar(255) DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `sale_returns`
--

INSERT INTO `sale_returns` (`id`, `company_id`, `store_id`, `business_day_no`, `warehouse_id`, `sale_id`, `return_no`, `return_date`, `subtotal`, `discount`, `tax`, `return_amount`, `refund_amount`, `payment_type`, `status`, `remarks`, `created_by`, `created_at`, `updated_at`) VALUES
(1, 1, 1, 1, 1, 9, 'RET-000001', '2026-09-03 10:37:18', 135.00, 0.00, 0.00, 135.00, 135.00, 'CASH', 'COMPLETED', '', 1, '2026-09-03 05:37:18', '2026-09-03 05:37:18');

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

--
-- Table structure for table `sale_return_items`
--

CREATE TABLE `sale_return_items` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `return_id` bigint(20) UNSIGNED NOT NULL,
  `sale_id` bigint(20) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `product_name` varchar(200) NOT NULL,
  `barcode` varchar(100) DEFAULT NULL,
  `quantity` decimal(14,3) NOT NULL DEFAULT 0.000,
  `unit_price` decimal(14,2) NOT NULL DEFAULT 0.00,
  `discount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `tax_percent` decimal(6,2) NOT NULL DEFAULT 0.00,
  `tax_amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `line_total` decimal(14,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `sale_return_items`
--

INSERT INTO `sale_return_items` (`id`, `return_id`, `sale_id`, `product_id`, `product_name`, `barcode`, `quantity`, `unit_price`, `discount`, `tax_percent`, `tax_amount`, `line_total`, `created_at`) VALUES
(1, 1, 9, 1, 'Coca Cola 1.5 Liter', '211212321', 1.000, 135.00, 0.00, 0.00, 0.00, 135.00, '2026-09-03 05:37:18');

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

--
-- Table structure for table `stock_history`
--

CREATE TABLE `stock_history` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED NOT NULL,
  `store_id` int(10) UNSIGNED NOT NULL,
  `warehouse_id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `transaction_type` enum('OPENING','PURCHASE','PURCHASE_RETURN','SALE','SALE_RETURN','ADJUSTMENT_IN','ADJUSTMENT_OUT','TRANSFER_IN','TRANSFER_OUT') NOT NULL,
  `quantity` decimal(14,3) NOT NULL DEFAULT 0.000,
  `stock_before` decimal(14,3) NOT NULL DEFAULT 0.000,
  `stock_after` decimal(14,3) NOT NULL DEFAULT 0.000,
  `reference_type` varchar(50) DEFAULT NULL,
  `reference_id` bigint(20) UNSIGNED DEFAULT NULL,
  `remarks` varchar(255) DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `stock_history`
--

INSERT INTO `stock_history` (`id`, `company_id`, `store_id`, `warehouse_id`, `product_id`, `transaction_type`, `quantity`, `stock_before`, `stock_after`, `reference_type`, `reference_id`, `remarks`, `created_by`, `created_at`) VALUES
(1, 1, 1, 1, 1, 'OPENING', 10.000, 0.000, 10.000, 'MANUAL', NULL, 'Opening Stock', 1, '2026-09-03 04:44:33'),
(2, 1, 1, 1, 1, 'ADJUSTMENT_IN', 2.000, 10.000, 12.000, 'MANUAL', NULL, 'Stock Adjustment In', 1, '2026-09-03 05:05:43'),
(3, 1, 1, 1, 1, 'ADJUSTMENT_OUT', 2.000, 12.000, 10.000, 'MANUAL', NULL, 'Stock Adjustment Out', 1, '2026-09-03 05:06:11'),
(4, 1, 1, 1, 1, 'SALE', 1.000, 10.000, 9.000, 'SALE', 9, 'Sale INV-000009', 1, '2026-09-03 05:17:54'),
(5, 1, 1, 1, 1, 'SALE_RETURN', 1.000, 9.000, 10.000, 'SALE_RETURN', 1, 'Sale Return RET-000001 against INV-000009', 1, '2026-09-03 05:37:18'),
(6, 1, 1, 1, 1, 'PURCHASE', 1.000, 10.000, 11.000, 'PURCHASE', 1, 'Purchase PUR-000001 - Coca Cola 1.5 Liter', 1, '2026-09-04 06:05:11'),
(7, 1, 1, 1, 2, 'PURCHASE', 2.000, 0.000, 2.000, 'PURCHASE', 1, 'Purchase PUR-000001 - lemon biscut', 1, '2026-09-04 06:05:11'),
(8, 1, 1, 1, 2, 'PURCHASE_RETURN', 1.000, 2.000, 1.000, 'PURCHASE_RETURN', 1, 'Purchase Return PR-000001', 1, '2026-09-04 07:04:20'),
(9, 1, 1, 1, 2, 'ADJUSTMENT_IN', 1.000, 1.000, 2.000, 'PURCHASE_RETURN_CANCEL', 1, 'Purchase Return Cancelled PR-000001', 1, '2026-09-05 07:10:45'),
(10, 1, 1, 1, 1, 'PURCHASE', 1.000, 11.000, 12.000, 'PURCHASE', 2, 'Purchase PUR-000002 - Coca Cola 1.5 Liter', 1, '2026-09-05 07:27:55'),
(11, 1, 1, 1, 1, 'PURCHASE_RETURN', 1.000, 12.000, 11.000, 'PURCHASE_RETURN', 2, 'Purchase Return PR-000002', 1, '2026-09-05 07:32:52'),
(12, 1, 1, 1, 1, 'ADJUSTMENT_IN', 1.000, 11.000, 12.000, 'PURCHASE_RETURN_CANCEL', 2, 'Purchase Return Cancelled PR-000002', 1, '2026-09-05 08:20:13'),
(13, 1, 1, 1, 2, 'SALE', 1.000, 2.000, 1.000, 'SALE', 10, 'Sale INV-000010', 1, '2026-09-05 10:21:17'),
(14, 1, 1, 1, 4, 'PURCHASE', 2.000, 0.000, 2.000, 'PURCHASE', 3, 'Purchase PUR-000003 - Super Biscout', 1, '2026-09-07 04:58:11'),
(15, 1, 1, 1, 6, 'PURCHASE', 2.000, 0.000, 2.000, 'PURCHASE', 8, 'Purchase PUR-002-000004 - coca colola', 1, '2026-09-08 05:15:12'),
(16, 1, 1, 1, 6, 'PURCHASE', 1.000, 2.000, 3.000, 'PURCHASE', 8, 'Purchase PUR-002-000004 - coca colola', 1, '2026-09-08 05:15:12'),
(17, 1, 1, 1, 6, 'SALE', 2.000, 3.000, 1.000, 'SALE', 12, 'Sale INV-002-000001 - FEFO Batch Sale', 1, '2026-09-08 05:19:27'),
(18, 1, 1, 1, 1, 'SALE', 2.000, 12.000, 10.000, 'SALE', 13, 'Sale INV-002-000002', 1, '2026-09-09 05:37:56'),
(19, 1, 1, 1, 6, 'SALE', 1.000, 1.000, 0.000, 'SALE', 16, 'Sale INV-002-000003 - FEFO Batch Sale', 1, '2026-09-09 05:55:58');

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

--
-- Table structure for table `stores`
--

CREATE TABLE `stores` (
  `id` int(10) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED NOT NULL,
  `store_code` varchar(30) NOT NULL,
  `store_name` varchar(150) NOT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `status` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `stores`
--

INSERT INTO `stores` (`id`, `company_id`, `store_code`, `store_name`, `phone`, `address`, `city`, `status`, `created_at`) VALUES
(1, 1, 'STORE-001', 'Main Store', '', '', '', 'ACTIVE', '2026-09-02 03:59:55');

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

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

CREATE TABLE `suppliers` (
  `id` int(10) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED NOT NULL,
  `supplier_code` varchar(50) DEFAULT NULL,
  `supplier_name` varchar(200) NOT NULL,
  `contact_person` varchar(150) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `opening_balance` decimal(14,2) NOT NULL DEFAULT 0.00,
  `credit_limit` decimal(14,2) NOT NULL DEFAULT 0.00,
  `status` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `suppliers` (`id`, `company_id`, `supplier_code`, `supplier_name`, `contact_person`, `phone`, `email`, `address`, `opening_balance`, `credit_limit`, `status`, `created_at`, `updated_at`) VALUES
(1, 1, '1', 'Tayyab Shop', 'tayyab', '03467437921', 'c.ghs4gd@gmail.com', '4/GD Renala Khurd okara', 0.00, 0.00, 'ACTIVE', '2026-09-04 05:58:55', '2026-09-04 05:58:55');

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

--
-- Table structure for table `supplier_ledger`
--

CREATE TABLE `supplier_ledger` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED NOT NULL,
  `store_id` int(10) UNSIGNED NOT NULL,
  `supplier_id` int(10) UNSIGNED NOT NULL,
  `business_day_no` int(10) UNSIGNED DEFAULT NULL,
  `transaction_type` enum('OPENING','PURCHASE','PAYMENT','PURCHASE_RETURN','PURCHASE_RETURN_CANCEL','ADJUSTMENT_IN','ADJUSTMENT_OUT') NOT NULL,
  `reference_type` varchar(50) DEFAULT NULL,
  `reference_id` bigint(20) UNSIGNED DEFAULT NULL,
  `debit` decimal(14,2) NOT NULL DEFAULT 0.00,
  `credit` decimal(14,2) NOT NULL DEFAULT 0.00,
  `balance` decimal(14,2) NOT NULL DEFAULT 0.00,
  `remarks` varchar(255) DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `supplier_ledger`
--

INSERT INTO `supplier_ledger` (`id`, `company_id`, `store_id`, `supplier_id`, `business_day_no`, `transaction_type`, `reference_type`, `reference_id`, `debit`, `credit`, `balance`, `remarks`, `created_by`, `created_at`) VALUES
(1, 1, 1, 1, 1, 'PURCHASE', 'PURCHASE', 2, 100.00, 0.00, 100.00, 'Purchase PUR-000002', 1, '2026-09-05 07:27:55'),
(2, 1, 1, 1, 1, 'PURCHASE_RETURN', 'PURCHASE_RETURN', 2, 0.00, 100.00, 0.00, 'Purchase Return PR-000002 against Purchase PUR-000002', 1, '2026-09-05 07:32:52'),
(3, 1, 1, 1, 1, 'PURCHASE_RETURN_CANCEL', 'PURCHASE_RETURN_CANCEL', 2, 100.00, 0.00, 100.00, 'Cancelled Purchase Return PR-000002', 1, '2026-09-05 08:20:13'),
(4, 1, 1, 1, 1, 'PAYMENT', 'EXPENSE', 1, 0.00, 10.00, 90.00, 'Supplier Payment - Expense #1', 1, '2026-09-05 11:05:43'),
(5, 1, 1, 1, 1, 'PAYMENT', 'EXPENSE', 4, 0.00, 10.00, 80.00, 'Supplier Payment - Expense #4', 1, '2026-09-05 11:10:22'),
(6, 1, 1, 1, 1, 'PAYMENT', 'EXPENSE', 6, 0.00, 10.00, 70.00, 'Supplier Payment - Expense #6', 1, '2026-09-05 11:20:31'),
(7, 1, 1, 1, 2, 'PURCHASE', 'PURCHASE', 3, 70.00, 0.00, 140.00, 'Purchase PUR-000003', 1, '2026-09-07 04:58:11'),
(8, 1, 1, 1, 2, 'PAYMENT', 'PURCHASE', 3, 0.00, 70.00, 70.00, 'Payment against Purchase PUR-000003', 1, '2026-09-07 04:58:11'),
(9, 1, 1, 1, 2, 'PURCHASE', 'PURCHASE', 8, 360.00, 0.00, 430.00, 'Purchase PUR-002-000004', 1, '2026-09-08 05:15:12'),
(10, 1, 1, 1, 2, 'PAYMENT', 'PURCHASE', 8, 0.00, 360.00, 70.00, 'Payment against Purchase PUR-002-000004', 1, '2026-09-08 05:15:12');

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

--
-- Table structure for table `supplier_payments`
--

CREATE TABLE `supplier_payments` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED NOT NULL,
  `store_id` int(10) UNSIGNED NOT NULL,
  `supplier_id` int(10) UNSIGNED NOT NULL,
  `business_day_no` int(10) UNSIGNED DEFAULT NULL,
  `payment_no` varchar(30) NOT NULL,
  `payment_date` datetime NOT NULL,
  `amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `payment_method` enum('CASH','CARD','EASYPAISA','JAZZCASH') NOT NULL DEFAULT 'CASH',
  `reference_no` varchar(100) DEFAULT NULL,
  `remarks` varchar(255) DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `units`
--

CREATE TABLE `units` (
  `id` int(10) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED NOT NULL,
  `unit_name` varchar(100) NOT NULL,
  `short_name` varchar(20) NOT NULL,
  `allow_decimal` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `units`
--

INSERT INTO `units` (`id`, `company_id`, `unit_name`, `short_name`, `allow_decimal`, `created_at`) VALUES
(1, 1, 'Piec', 'pic', 0, '2026-09-04 06:00:07');

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

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(10) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED NOT NULL,
  `store_id` int(10) UNSIGNED DEFAULT NULL,
  `role_id` int(10) UNSIGNED DEFAULT NULL,
  `username` varchar(100) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `full_name` varchar(150) NOT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `status` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE',
  `last_login` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `users` (`id`, `company_id`, `store_id`, `role_id`, `username`, `password_hash`, `full_name`, `phone`, `status`, `last_login`, `created_at`, `updated_at`) VALUES
(1, 1, 1, 1, 'admin', '$2y$10$lyYkKPaFzVZ0a5xu9EQjnOwXbsUB2pjxqeflxyMbxNbiygN1QStFy', 'System Administrator', NULL, 'ACTIVE', '2026-09-10 10:16:58', '2026-09-02 04:40:29', '2026-09-10 05:16:58'),
(2, 1, 1, 1, 'ahmad', '$2y$10$zxCDeqBoXCT7n7EL22uwgedkXrkqbWXPkdOfvODec9nOgP0070MV6', 'Tayyab Younas', NULL, 'ACTIVE', '2026-11-12 09:22:52', '2026-09-02 05:03:45', '2026-11-12 04:22:52'),
(3, 1, 1, 2, 'ali', '$2y$10$bCeczJ/58z5sxEfHWJ6Ot.oxKMatBFhVRoQDXJTr1KxK1r.Hze3ge', 'Ali Ali', NULL, 'ACTIVE', '2026-09-10 10:40:09', '2026-09-08 05:23:36', '2026-09-10 05:40:09');

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

--
-- Table structure for table `warehouses`
--

CREATE TABLE `warehouses` (
  `id` int(10) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED NOT NULL,
  `store_id` int(10) UNSIGNED DEFAULT NULL,
  `warehouse_code` varchar(30) NOT NULL,
  `warehouse_name` varchar(150) NOT NULL,
  `address` text DEFAULT NULL,
  `status` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `warehouses`
--

INSERT INTO `warehouses` (`id`, `company_id`, `store_id`, `warehouse_code`, `warehouse_name`, `address`, `status`, `created_at`) VALUES
(1, 1, 1, 'WH-001', 'Main Warehouse', '', 'ACTIVE', '2026-09-02 04:00:40');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `brands`
--
ALTER TABLE `brands`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_brand` (`company_id`,`brand_name`);

--
-- Indexes for table `business_days`
--
ALTER TABLE `business_days`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_business_day_number` (`company_id`,`store_id`,`business_day_no`),
  ADD KEY `idx_business_day_status` (`company_id`,`store_id`,`status`),
  ADD KEY `idx_business_day_open` (`company_id`,`store_id`,`open_at`),
  ADD KEY `fk_business_days_store` (`store_id`);

--
-- Indexes for table `categories`
--
ALTER TABLE `categories`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_category` (`company_id`,`category_name`),
  ADD KEY `fk_categories_parent` (`parent_id`);

--
-- Indexes for table `companies`
--
ALTER TABLE `companies`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `customers`
--
ALTER TABLE `customers`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_customers_company` (`company_id`),
  ADD KEY `idx_customers_name` (`customer_name`),
  ADD KEY `idx_customers_phone` (`phone`),
  ADD KEY `idx_customers_status` (`status`);

--
-- Indexes for table `customer_ledger`
--
ALTER TABLE `customer_ledger`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_customer_ledger_company` (`company_id`),
  ADD KEY `idx_customer_ledger_store` (`store_id`),
  ADD KEY `idx_customer_ledger_customer` (`customer_id`),
  ADD KEY `idx_customer_ledger_day` (`business_day_no`),
  ADD KEY `idx_customer_ledger_reference` (`reference_type`,`reference_id`),
  ADD KEY `idx_customer_ledger_created` (`created_at`);

--
-- Indexes for table `customer_payments`
--
ALTER TABLE `customer_payments`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_customer_payment_no` (`company_id`,`payment_no`),
  ADD KEY `idx_customer_payments_company` (`company_id`),
  ADD KEY `idx_customer_payments_store` (`store_id`),
  ADD KEY `idx_customer_payments_customer` (`customer_id`),
  ADD KEY `idx_customer_payments_day` (`business_day_no`),
  ADD KEY `idx_customer_payments_date` (`payment_date`),
  ADD KEY `idx_customer_payments_status` (`status`);

--
-- Indexes for table `expenses`
--
ALTER TABLE `expenses`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_expenses_company` (`company_id`),
  ADD KEY `idx_expenses_store` (`store_id`),
  ADD KEY `idx_expenses_business_day` (`business_day_no`),
  ADD KEY `idx_expenses_type` (`expense_type`),
  ADD KEY `idx_expenses_supplier` (`supplier_id`),
  ADD KEY `idx_expenses_purchase` (`purchase_id`),
  ADD KEY `idx_expenses_reference` (`reference_type`,`reference_id`),
  ADD KEY `idx_expenses_created_by` (`created_by`),
  ADD KEY `idx_expenses_day_store` (`company_id`,`store_id`,`business_day_no`);

--
-- Indexes for table `inventory_batches`
--
ALTER TABLE `inventory_batches`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_company_store` (`company_id`,`store_id`),
  ADD KEY `idx_warehouse_product` (`warehouse_id`,`product_id`),
  ADD KEY `idx_product_batch` (`product_id`,`batch_no`),
  ADD KEY `idx_expiry` (`expiry_date`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_purchase` (`purchase_id`),
  ADD KEY `idx_purchase_item` (`purchase_item_id`);

--
-- Indexes for table `inventory_stock`
--
ALTER TABLE `inventory_stock`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_inventory_stock` (`company_id`,`store_id`,`warehouse_id`,`product_id`),
  ADD KEY `idx_inventory_company` (`company_id`),
  ADD KEY `idx_inventory_store` (`store_id`),
  ADD KEY `idx_inventory_warehouse` (`warehouse_id`),
  ADD KEY `idx_inventory_product` (`product_id`);

--
-- Indexes for table `permissions`
--
ALTER TABLE `permissions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_permission_key` (`permission_key`);

--
-- Indexes for table `products`
--
ALTER TABLE `products`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_product_sku` (`company_id`,`sku`),
  ADD KEY `fk_products_category` (`category_id`),
  ADD KEY `fk_products_brand` (`brand_id`),
  ADD KEY `fk_products_unit` (`unit_id`);

--
-- Indexes for table `product_barcodes`
--
ALTER TABLE `product_barcodes`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_company_barcode` (`company_id`,`barcode`),
  ADD KEY `idx_product_id` (`product_id`),
  ADD KEY `idx_company_product` (`company_id`,`product_id`);

--
-- Indexes for table `purchases`
--
ALTER TABLE `purchases`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_purchases_company` (`company_id`),
  ADD KEY `idx_purchases_store` (`store_id`),
  ADD KEY `idx_purchases_business_day` (`business_day_no`),
  ADD KEY `idx_purchases_warehouse` (`warehouse_id`),
  ADD KEY `idx_purchases_supplier` (`supplier_id`),
  ADD KEY `idx_purchases_invoice` (`invoice_no`),
  ADD KEY `idx_purchases_date` (`purchase_date`),
  ADD KEY `idx_purchases_status` (`status`);

--
-- Indexes for table `purchase_items`
--
ALTER TABLE `purchase_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_purchase_items_purchase` (`purchase_id`),
  ADD KEY `idx_purchase_items_product` (`product_id`),
  ADD KEY `idx_purchase_items_barcode` (`barcode`);

--
-- Indexes for table `purchase_returns`
--
ALTER TABLE `purchase_returns`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_company` (`company_id`),
  ADD KEY `idx_store` (`store_id`),
  ADD KEY `idx_business_day` (`business_day_no`),
  ADD KEY `idx_warehouse` (`warehouse_id`),
  ADD KEY `idx_purchase` (`purchase_id`),
  ADD KEY `idx_supplier` (`supplier_id`),
  ADD KEY `idx_return_date` (`return_date`),
  ADD KEY `idx_return_no` (`return_no`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `purchase_return_items`
--
ALTER TABLE `purchase_return_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_return` (`return_id`),
  ADD KEY `idx_purchase` (`purchase_id`),
  ADD KEY `idx_product` (`product_id`),
  ADD KEY `idx_barcode` (`barcode`);

--
-- Indexes for table `roles`
--
ALTER TABLE `roles`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_role` (`company_id`,`role_name`);

--
-- Indexes for table `role_permissions`
--
ALTER TABLE `role_permissions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_role_permission` (`role_id`,`permission_id`),
  ADD KEY `fk_role_permissions_permission` (`permission_id`);

--
-- Indexes for table `sales`
--
ALTER TABLE `sales`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_sales_company_invoice` (`company_id`,`invoice_no`),
  ADD KEY `idx_sales_company_store` (`company_id`,`store_id`),
  ADD KEY `idx_sales_date` (`sale_date`),
  ADD KEY `idx_sales_customer` (`customer_id`),
  ADD KEY `idx_sales_created_by` (`created_by`),
  ADD KEY `fk_sales_store` (`store_id`),
  ADD KEY `fk_sales_warehouse` (`warehouse_id`),
  ADD KEY `idx_sales_business_day` (`company_id`,`store_id`,`business_day_no`);

--
-- Indexes for table `sale_items`
--
ALTER TABLE `sale_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_sale_items_sale` (`sale_id`),
  ADD KEY `idx_sale_items_product` (`product_id`);

--
-- Indexes for table `sale_item_batches`
--
ALTER TABLE `sale_item_batches`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_sale_item` (`sale_item_id`),
  ADD KEY `idx_sale` (`sale_id`),
  ADD KEY `idx_product` (`product_id`),
  ADD KEY `idx_batch` (`inventory_batch_id`),
  ADD KEY `idx_expiry` (`expiry_date`);

--
-- Indexes for table `sale_returns`
--
ALTER TABLE `sale_returns`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_sale_return_no` (`company_id`,`store_id`,`return_no`),
  ADD KEY `idx_sale_returns_company` (`company_id`),
  ADD KEY `idx_sale_returns_store` (`store_id`),
  ADD KEY `idx_sale_returns_sale` (`sale_id`),
  ADD KEY `idx_sale_returns_business_day` (`business_day_no`),
  ADD KEY `idx_sale_returns_date` (`return_date`),
  ADD KEY `fk_sale_returns_warehouse` (`warehouse_id`);

--
-- Indexes for table `sale_return_items`
--
ALTER TABLE `sale_return_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_sale_return_items_return` (`return_id`),
  ADD KEY `idx_sale_return_items_sale` (`sale_id`),
  ADD KEY `idx_sale_return_items_product` (`product_id`);

--
-- Indexes for table `stock_history`
--
ALTER TABLE `stock_history`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_stock_history_company` (`company_id`),
  ADD KEY `idx_stock_history_store` (`store_id`),
  ADD KEY `idx_stock_history_warehouse` (`warehouse_id`),
  ADD KEY `idx_stock_history_product` (`product_id`),
  ADD KEY `idx_stock_history_type` (`transaction_type`),
  ADD KEY `idx_stock_history_reference` (`reference_type`,`reference_id`),
  ADD KEY `idx_stock_history_date` (`created_at`);

--
-- Indexes for table `stores`
--
ALTER TABLE `stores`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_store_code` (`company_id`,`store_code`);

--
-- Indexes for table `suppliers`
--
ALTER TABLE `suppliers`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_suppliers_company` (`company_id`),
  ADD KEY `idx_suppliers_status` (`status`),
  ADD KEY `idx_suppliers_name` (`supplier_name`);

--
-- Indexes for table `supplier_ledger`
--
ALTER TABLE `supplier_ledger`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_supplier_ledger_company_store` (`company_id`,`store_id`),
  ADD KEY `idx_supplier_ledger_supplier` (`supplier_id`),
  ADD KEY `idx_supplier_ledger_business_day` (`business_day_no`),
  ADD KEY `idx_supplier_ledger_reference` (`reference_type`,`reference_id`),
  ADD KEY `idx_supplier_ledger_created_at` (`created_at`);

--
-- Indexes for table `supplier_payments`
--
ALTER TABLE `supplier_payments`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_supplier_payment_no` (`company_id`,`store_id`,`payment_no`),
  ADD KEY `idx_supplier` (`company_id`,`store_id`,`supplier_id`),
  ADD KEY `idx_business_day` (`company_id`,`store_id`,`business_day_no`),
  ADD KEY `idx_payment_date` (`company_id`,`store_id`,`payment_date`),
  ADD KEY `idx_payment_method` (`company_id`,`store_id`,`payment_method`),
  ADD KEY `fk_supplier_payments_supplier` (`supplier_id`);

--
-- Indexes for table `units`
--
ALTER TABLE `units`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_unit` (`company_id`,`unit_name`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_username` (`company_id`,`username`),
  ADD KEY `fk_users_store` (`store_id`),
  ADD KEY `fk_users_role` (`role_id`);

--
-- Indexes for table `warehouses`
--
ALTER TABLE `warehouses`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_warehouse_code` (`company_id`,`warehouse_code`),
  ADD KEY `fk_warehouse_store` (`store_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `brands`
--
ALTER TABLE `brands`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `business_days`
--
ALTER TABLE `business_days`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `categories`
--
ALTER TABLE `categories`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `companies`
--
ALTER TABLE `companies`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `customers`
--
ALTER TABLE `customers`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `customer_ledger`
--
ALTER TABLE `customer_ledger`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `customer_payments`
--
ALTER TABLE `customer_payments`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `expenses`
--
ALTER TABLE `expenses`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;

--
-- AUTO_INCREMENT for table `inventory_batches`
--
ALTER TABLE `inventory_batches`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `inventory_stock`
--
ALTER TABLE `inventory_stock`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=14;

--
-- AUTO_INCREMENT for table `permissions`
--
ALTER TABLE `permissions`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=28;

--
-- AUTO_INCREMENT for table `products`
--
ALTER TABLE `products`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `product_barcodes`
--
ALTER TABLE `product_barcodes`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `purchases`
--
ALTER TABLE `purchases`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT for table `purchase_items`
--
ALTER TABLE `purchase_items`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

--
-- AUTO_INCREMENT for table `purchase_returns`
--
ALTER TABLE `purchase_returns`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `purchase_return_items`
--
ALTER TABLE `purchase_return_items`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `roles`
--
ALTER TABLE `roles`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `role_permissions`
--
ALTER TABLE `role_permissions`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=43;

--
-- AUTO_INCREMENT for table `sales`
--
ALTER TABLE `sales`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;

--
-- AUTO_INCREMENT for table `sale_items`
--
ALTER TABLE `sale_items`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;

--
-- AUTO_INCREMENT for table `sale_item_batches`
--
ALTER TABLE `sale_item_batches`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `sale_returns`
--
ALTER TABLE `sale_returns`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `sale_return_items`
--
ALTER TABLE `sale_return_items`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `stock_history`
--
ALTER TABLE `stock_history`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=20;

--
-- AUTO_INCREMENT for table `stores`
--
ALTER TABLE `stores`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `suppliers`
--
ALTER TABLE `suppliers`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `supplier_ledger`
--
ALTER TABLE `supplier_ledger`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

--
-- AUTO_INCREMENT for table `supplier_payments`
--
ALTER TABLE `supplier_payments`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `units`
--
ALTER TABLE `units`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `warehouses`
--
ALTER TABLE `warehouses`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `brands`
--
ALTER TABLE `brands`
  ADD CONSTRAINT `fk_brands_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `business_days`
--
ALTER TABLE `business_days`
  ADD CONSTRAINT `fk_business_days_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_business_days_store` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`);

--
-- Constraints for table `categories`
--
ALTER TABLE `categories`
  ADD CONSTRAINT `fk_categories_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_categories_parent` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `inventory_stock`
--
ALTER TABLE `inventory_stock`
  ADD CONSTRAINT `fk_inventory_stock_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_inventory_stock_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_inventory_stock_store` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_inventory_stock_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `products`
--
ALTER TABLE `products`
  ADD CONSTRAINT `fk_products_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_products_category` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_products_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_products_unit` FOREIGN KEY (`unit_id`) REFERENCES `units` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `product_barcodes`
--
ALTER TABLE `product_barcodes`
  ADD CONSTRAINT `fk_product_barcodes_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_product_barcodes_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `roles`
--
ALTER TABLE `roles`
  ADD CONSTRAINT `fk_roles_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `role_permissions`
--
ALTER TABLE `role_permissions`
  ADD CONSTRAINT `fk_role_permissions_permission` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_role_permissions_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `sales`
--
ALTER TABLE `sales`
  ADD CONSTRAINT `fk_sales_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_sales_store` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`),
  ADD CONSTRAINT `fk_sales_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `sale_items`
--
ALTER TABLE `sale_items`
  ADD CONSTRAINT `fk_sale_items_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
  ADD CONSTRAINT `fk_sale_items_sale` FOREIGN KEY (`sale_id`) REFERENCES `sales` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `sale_item_batches`
--
ALTER TABLE `sale_item_batches`
  ADD CONSTRAINT `fk_sale_item_batches_batch` FOREIGN KEY (`inventory_batch_id`) REFERENCES `inventory_batches` (`id`),
  ADD CONSTRAINT `fk_sale_item_batches_sale_item` FOREIGN KEY (`sale_item_id`) REFERENCES `sale_items` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `sale_returns`
--
ALTER TABLE `sale_returns`
  ADD CONSTRAINT `fk_sale_returns_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_sale_returns_sale` FOREIGN KEY (`sale_id`) REFERENCES `sales` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_sale_returns_store` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_sale_returns_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `sale_return_items`
--
ALTER TABLE `sale_return_items`
  ADD CONSTRAINT `fk_sale_return_items_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_sale_return_items_return` FOREIGN KEY (`return_id`) REFERENCES `sale_returns` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_sale_return_items_sale` FOREIGN KEY (`sale_id`) REFERENCES `sales` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `stock_history`
--
ALTER TABLE `stock_history`
  ADD CONSTRAINT `fk_stock_history_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_stock_history_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_stock_history_store` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_stock_history_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`) ON UPDATE CASCADE;

--
-- Constraints for table `stores`
--
ALTER TABLE `stores`
  ADD CONSTRAINT `fk_stores_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `supplier_payments`
--
ALTER TABLE `supplier_payments`
  ADD CONSTRAINT `fk_supplier_payments_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `fk_supplier_payments_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`);

--
-- Constraints for table `units`
--
ALTER TABLE `units`
  ADD CONSTRAINT `fk_units_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `users`
--
ALTER TABLE `users`
  ADD CONSTRAINT `fk_users_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_users_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_users_store` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `warehouses`
--
ALTER TABLE `warehouses`
  ADD CONSTRAINT `fk_warehouse_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_warehouse_store` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`) ON DELETE SET NULL;
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 */;