-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Dec 23, 2025 at 01:30 PM
-- Server version: 10.4.28-MariaDB
-- PHP Version: 8.0.28

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

DELIMITER $$
--
-- Procedures
--
CREATE DEFINER=`root`@`localhost` PROCEDURE `AddPurchase` (IN `p_supplier_id` INT, IN `p_invoice_no` VARCHAR(100), IN `p_purchase_date` DATE, IN `p_created_by` INT, IN `p_shop_id` INT, IN `p_items` JSON)   BEGIN
    DECLARE v_purchase_id INT;
    DECLARE v_total DECIMAL(12,2) DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE v_batch_id INT;
    DECLARE v_qty INT;
    DECLARE v_cost_price DECIMAL(12,2);
    
    -- Start transaction
    START TRANSACTION;
    
    -- Create purchase record
    INSERT INTO `purchases` 
    (`supplier_id`, `invoice_no`, `purchase_date`, `created_by`, `shop_id`)
    VALUES 
    (p_supplier_id, p_invoice_no, p_purchase_date, p_created_by, p_shop_id);
    
    SET v_purchase_id = LAST_INSERT_ID();
    
    -- Parse and process items
    WHILE i < JSON_LENGTH(p_items) DO
        SET v_batch_id = JSON_UNQUOTE(JSON_EXTRACT(p_items, CONCAT('$[', i, '].batch_id')));
        SET v_qty = JSON_UNQUOTE(JSON_EXTRACT(p_items, CONCAT('$[', i, '].qty')));
        SET v_cost_price = JSON_UNQUOTE(JSON_EXTRACT(p_items, CONCAT('$[', i, '].cost_price')));
        
        -- Add purchase line
        INSERT INTO `purchase_lines` (`purchase_id`, `batch_id`, `qty`, `cost_price`)
        VALUES (v_purchase_id, v_batch_id, v_qty, v_cost_price);
        
        -- Add inventory movement
        INSERT INTO `inventory_movements` 
        (`product_id`, `batch_id`, `change_qty`, `movement_type`, `reference_id`, `created_by`, `shop_id`)
        SELECT 
            pb.product_id,
            v_batch_id,
            v_qty,
            'PURCHASE',
            v_purchase_id,
            p_created_by,
            p_shop_id
        FROM `product_batches` pb
        WHERE pb.id = v_batch_id;
        
        SET v_total = v_total + (v_qty * v_cost_price);
        SET i = i + 1;
    END WHILE;
    
    -- Update purchase total
    UPDATE `purchases` 
    SET `total` = v_total 
    WHERE `id` = v_purchase_id;
    
    COMMIT;
    
    SELECT v_purchase_id as purchase_id, v_total as total_amount;
END$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `DeletePurchase` (IN `p_purchase_id` INT, IN `p_user_id` INT)   BEGIN
    DECLARE v_shop_id INT;
    DECLARE v_batch_id INT;
    DECLARE v_qty INT;
    DECLARE v_product_id INT;
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur CURSOR FOR 
        SELECT pl.batch_id, pl.qty, pb.product_id
        FROM purchase_lines pl
        JOIN product_batches pb ON pl.batch_id = pb.id
        WHERE pl.purchase_id = p_purchase_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    START TRANSACTION;
    
    -- Get shop_id from purchase
    SELECT shop_id INTO v_shop_id FROM purchases WHERE id = p_purchase_id;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO v_batch_id, v_qty, v_product_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- Create reverse inventory movement (adjustment)
        INSERT INTO `inventory_movements` 
        (`product_id`, `batch_id`, `change_qty`, `movement_type`, `note`, `created_by`, `shop_id`)
        VALUES 
        (v_product_id, v_batch_id, v_qty, 'ADJUSTMENT', 
         CONCAT('Purchase #', p_purchase_id, ' deletion'), p_user_id, v_shop_id);
    END LOOP;
    
    CLOSE cur;
    
    -- Delete purchase (triggers will handle the rest)
    DELETE FROM `purchases` WHERE id = p_purchase_id;
    
    COMMIT;
    
    SELECT 'Purchase deleted successfully with stock adjustment' as message;
END$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetProductStock` (IN `p_product_id` INT, IN `p_shop_id` INT)   BEGIN
    SELECT 
        p.id,
        p.sku,
        p.name,
        COALESCE(SUM(
            CASE 
                WHEN im.movement_type IN ('PURCHASE', 'RETURN') THEN im.change_qty
                WHEN im.movement_type IN ('SALE', 'ADJUSTMENT') THEN -im.change_qty
                ELSE 0
            END
        ), 0) as current_stock,
        GROUP_CONCAT(
            DISTINCT CONCAT(
                pb.batch_no, ':', 
                COALESCE(SUM(
                    CASE 
                        WHEN im.movement_type IN ('PURCHASE', 'RETURN') THEN im.change_qty
                        WHEN im.movement_type IN ('SALE', 'ADJUSTMENT') THEN -im.change_qty
                        ELSE 0
                    END
                ), 0)
            ) SEPARATOR ', '
        ) as batch_details
    FROM products p
    LEFT JOIN product_batches pb ON p.id = pb.product_id
    LEFT JOIN inventory_movements im ON pb.id = im.batch_id
    WHERE p.id = p_product_id 
    AND (p_shop_id IS NULL OR p.shop_id = p_shop_id)
    GROUP BY p.id, p.sku, p.name;
END$$

DELIMITER ;

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

--
-- Stand-in structure for view `batch_stock_view`
-- (See below for the actual view)
--
CREATE TABLE `batch_stock_view` (
`batch_id` int(11)
,`product_id` int(11)
,`batch_no` varchar(100)
,`expiry_date` date
,`cost_price` decimal(12,2)
,`sell_price` decimal(12,2)
,`shop_id` int(11)
,`is_active` tinyint(1)
,`current_qty` decimal(33,0)
,`created_at` timestamp
,`updated_at` datetime
);

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

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

CREATE TABLE `customers` (
  `id` int(11) NOT NULL,
  `shop_id` int(11) DEFAULT NULL,
  `name` varchar(200) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `customers` (`id`, `shop_id`, `name`, `phone`, `email`, `address`, `created_at`) VALUES
(13, NULL, 'John Doe', NULL, NULL, NULL, '2025-11-14 10:08:26'),
(14, 1, 'ADELARD ATHANASE', NULL, NULL, NULL, '2025-12-23 09:59:16');

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

--
-- Table structure for table `inventory_movements`
--

CREATE TABLE `inventory_movements` (
  `id` int(11) NOT NULL,
  `product_id` int(11) DEFAULT NULL,
  `batch_id` int(11) DEFAULT NULL,
  `change_qty` int(11) DEFAULT NULL,
  `movement_type` enum('PURCHASE','SALE','ADJUSTMENT','RETURN') NOT NULL,
  `reference_id` int(11) DEFAULT NULL,
  `note` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_by` int(11) DEFAULT NULL,
  `shop_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `inventory_movements`
--

INSERT INTO `inventory_movements` (`id`, `product_id`, `batch_id`, `change_qty`, `movement_type`, `reference_id`, `note`, `created_at`, `created_by`, `shop_id`) VALUES
(1, 2, 4, 5000, 'PURCHASE', 2, NULL, '2025-11-14 07:30:23', 1, 1),
(2, 5, 8, 150, 'PURCHASE', 2, NULL, '2025-11-14 07:30:23', 1, 1),
(3, 2, 9, 12, 'PURCHASE', 3, NULL, '2025-12-03 08:05:28', 1, 0),
(4, 5, 10, 15, 'PURCHASE', 3, NULL, '2025-12-03 08:05:28', 1, 0),
(5, 2, 4, 20, 'PURCHASE', 2, NULL, '2025-11-14 07:30:23', 1, 1),
(6, 2, 13, 15, 'PURCHASE', 129, NULL, '2025-12-11 10:33:04', 1, 1),
(8, 2, 5, 14, 'SALE', 2, NULL, '2025-11-14 13:10:11', 1, 1),
(9, 2, 5, 10, 'SALE', 3, NULL, '2025-11-14 13:11:17', 1, 1),
(10, 2, 5, 1, 'SALE', 5, NULL, '2025-12-04 08:23:23', 1, 1),
(11, 5, 8, 13, 'SALE', 5, NULL, '2025-12-04 08:23:23', 1, 1),
(12, 2, 5, 1, 'SALE', 6, NULL, '2025-12-04 08:24:44', 1, 1),
(13, 2, 5, 10, 'SALE', 7, NULL, '2025-12-05 06:54:58', 1, 1),
(14, 2, 5, 100, 'SALE', 8, NULL, '2025-12-09 06:10:13', 1, 1),
(15, 2, 5, 30, 'SALE', 8, NULL, '2025-12-09 06:10:13', 1, 1),
(16, 2, 5, 300, 'SALE', 9, NULL, '2025-12-09 06:34:27', 1, 1),
(17, 2, 5, 5, 'SALE', 10, NULL, '2025-12-09 06:47:30', 1, 1),
(18, 2, 5, 5, 'SALE', 11, NULL, '2025-12-09 06:48:07', 1, 1),
(19, 4, 11, 18, 'SALE', 12, NULL, '2025-12-16 07:13:34', 1, 1),
(24, 2, 13, 50, 'ADJUSTMENT', NULL, 'Purchase #130 deletion', '2025-12-22 07:53:38', 1, 1),
(25, 5, 15, 100, 'PURCHASE', 131, NULL, '2025-12-23 08:50:41', 1, 1),
(26, 5, 15, 2, 'SALE', 13, NULL, '2025-12-23 09:13:47', 1, 1),
(27, 5, 15, 5, 'SALE', 14, NULL, '2025-12-23 09:15:34', 1, 1),
(28, 5, 15, 2, 'SALE', 15, NULL, '2025-12-23 09:18:54', 1, 1),
(29, 5, 15, 3, 'SALE', 16, NULL, '2025-12-23 09:31:13', 1, 1),
(30, 5, 15, 2, 'SALE', 17, NULL, '2025-12-23 09:31:58', 1, 1),
(31, 5, 15, 2, 'SALE', 19, NULL, '2025-12-23 09:43:09', 1, 1),
(32, 5, 15, 2, 'SALE', 20, NULL, '2025-12-23 09:59:16', 1, 1),
(33, 5, 16, 100, 'PURCHASE', 132, NULL, '2025-12-23 10:02:56', 1, 1),
(34, 5, 16, 19, 'SALE', 21, NULL, '2025-12-23 10:08:14', 1, 1),
(35, 5, 16, 10, 'SALE', 22, NULL, '2025-12-23 10:09:28', 1, 1),
(36, 5, 16, 2, 'SALE', 23, NULL, '2025-12-23 12:30:01', 1, 1);

--
-- Triggers `inventory_movements`
--
DELIMITER $$
CREATE TRIGGER `sync_batch_qty` AFTER INSERT ON `inventory_movements` FOR EACH ROW BEGIN
    UPDATE product_batches pb
    SET pb.qty = (
        SELECT COALESCE(SUM(
            CASE 
                WHEN im.movement_type IN ('PURCHASE', 'RETURN') THEN im.change_qty
                WHEN im.movement_type IN ('SALE', 'ADJUSTMENT') THEN -im.change_qty
                ELSE 0
            END
        ), 0)
        FROM inventory_movements im
        WHERE im.batch_id = pb.id
    )
    WHERE pb.id = NEW.batch_id;
END
$$
DELIMITER ;

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

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

CREATE TABLE `payments` (
  `id` int(11) NOT NULL,
  `sale_id` int(11) DEFAULT NULL,
  `amount` decimal(12,2) DEFAULT NULL,
  `method` enum('CASH','CARD','M-PESA','TIGO-PESA','AIRTEL-MONEY') DEFAULT 'CASH',
  `paid_at` datetime DEFAULT current_timestamp(),
  `created_by` int(11) DEFAULT NULL,
  `shop_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `payments` (`id`, `sale_id`, `amount`, `method`, `paid_at`, `created_by`, `shop_id`) VALUES
(1, 13, 816.00, 'CASH', '2025-12-23 12:13:47', 1, 1),
(2, 14, 2000.00, 'CASH', '2025-12-23 12:15:34', 1, 1),
(3, 15, 800.00, 'CASH', '2025-12-23 12:18:54', 1, 1),
(4, 16, 1200.00, 'CASH', '2025-12-23 12:31:13', 1, 1),
(5, 17, 800.00, 'CASH', '2025-12-23 12:31:58', 1, 1),
(6, 19, 800.00, 'CASH', '2025-12-23 12:43:09', 1, 1),
(7, 20, 800.00, 'CASH', '2025-12-23 12:59:17', 1, 1),
(8, 21, 6650.00, 'CASH', '2025-12-23 13:08:14', 1, 1),
(9, 22, 3500.00, 'CASH', '2025-12-23 13:09:28', 1, 1),
(10, 23, 1100.00, 'CASH', '2025-12-23 15:30:01', 1, 1);

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

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

CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `sku` varchar(50) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `generic_name` varchar(255) DEFAULT NULL,
  `unit` varchar(50) DEFAULT 'tablet',
  `category` varchar(100) DEFAULT NULL,
  `vat_percent` decimal(5,2) DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `buying_price` decimal(10,2) DEFAULT 0.00,
  `selling_price` decimal(10,2) DEFAULT 0.00,
  `shop_id` int(11) DEFAULT NULL,
  `is_active` int(11) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `products` (`id`, `sku`, `name`, `generic_name`, `unit`, `category`, `vat_percent`, `created_at`, `buying_price`, `selling_price`, `shop_id`, `is_active`) VALUES
(2, 'PAR-555', 'Paracetamol 500mg', 'Panadol', 'tablet', 'Anti-pain', 0.00, '2025-11-14 07:23:42', 0.00, 0.00, 1, 1),
(3, 'PAR-803', 'Paracetamol 500mg', 'Panadol', 'tablets', 'Anti-pain', 0.00, '2025-12-01 11:30:44', 0.00, 0.00, 1, 1),
(4, 'PAR-110', 'Paracetamol 500mg', 'Panadol', 'tablet', 'Anti-pain', 3.00, '2025-12-01 11:39:19', 120.00, 250.00, 1, 1),
(5, 'DIC-125', 'Diclo', 'Diclofenac', 'tablet', 'Anti-pain', 0.00, '2025-12-01 12:17:52', 300.00, 550.00, 1, 1),
(6, 'ENO-353', 'ENO', 'CAOH', 'tablet', 'Antibiotic', 0.00, '2025-12-09 09:14:26', 200.00, 300.00, 1, 1);

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

--
-- Stand-in structure for view `product_availability`
-- (See below for the actual view)
--
CREATE TABLE `product_availability` (
`product_id` int(11)
,`sku` varchar(50)
,`name` varchar(255)
,`generic_name` varchar(255)
,`unit` varchar(50)
,`category` varchar(100)
,`buying_price` decimal(10,2)
,`selling_price` decimal(10,2)
,`batch_id` int(11)
,`batch_no` varchar(100)
,`batch_qty` decimal(33,0)
,`expiry_date` date
,`cost_price` decimal(12,2)
,`sell_price` decimal(12,2)
,`shop_id` int(11)
,`stock_status` varchar(14)
,`days_to_expiry` int(7)
);

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

--
-- Table structure for table `product_batches`
--

CREATE TABLE `product_batches` (
  `id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `batch_no` varchar(100) DEFAULT NULL,
  `qty` int(11) NOT NULL DEFAULT 0,
  `cost_price` decimal(12,2) NOT NULL,
  `sell_price` decimal(12,2) NOT NULL,
  `expiry_date` date DEFAULT NULL,
  `supplier_id` int(11) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_by` int(11) DEFAULT NULL,
  `updated_at` datetime NOT NULL DEFAULT current_timestamp(),
  `shop_id` int(11) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `product_batches`
--

INSERT INTO `product_batches` (`id`, `product_id`, `batch_no`, `qty`, `cost_price`, `sell_price`, `expiry_date`, `supplier_id`, `created_by`, `created_at`, `updated_by`, `updated_at`, `shop_id`, `is_active`) VALUES
(4, 2, '265656', 0, 300.00, 400.00, '2025-11-30', 1, 1, '2025-11-14 07:27:37', 1, '2025-12-02 14:59:52', 1, 1),
(5, 2, '265656', 1000, 500.00, 1200.00, '2025-11-30', 1, 1, '2025-11-14 07:30:23', 1, '2025-12-02 15:03:01', 1, 1),
(6, 4, '577887', 150, 300.00, 4000.00, '2025-12-31', 2, 1, '2025-12-01 12:10:49', 0, '2025-12-01 15:10:49', 1, 1),
(8, 5, '502', 87, 300.00, 400.00, '2027-06-25', 1, 1, '2025-12-02 12:04:41', 1, '2025-12-09 09:27:13', 1, 1),
(9, 2, '265656', 12, 100.00, 500.00, '2025-12-31', 1, 1, '2025-12-03 08:05:28', 0, '2025-12-03 11:05:28', 1, 1),
(10, 5, '2312', 15, 50.00, 300.00, '2025-12-31', 1, 1, '2025-12-03 08:05:28', 0, '2025-12-03 11:05:28', 1, 0),
(11, 4, '567', 32, 120.00, 250.00, '2026-03-10', 2, 1, '2025-12-09 09:59:13', 1, '2025-12-09 10:21:36', 1, 1),
(13, 2, '265656', 11, 200.00, 300.00, '2025-12-31', 3, 1, '2025-12-11 10:33:04', 1, '2025-12-11 13:33:04', 1, 1),
(14, 6, '502', 10, 200.00, 300.00, '2025-12-31', 3, 1, '2025-12-11 11:46:46', 0, '2025-12-11 14:46:46', 1, 1),
(15, 5, 'BATCH -2025-001', 82, 300.00, 400.00, '2025-12-31', 1, 1, '2025-12-23 08:50:41', NULL, '2025-12-23 11:50:41', 1, 1),
(16, 5, '502-01', 69, 300.00, 350.00, '2025-12-23', 3, 1, '2025-12-23 10:02:56', NULL, '2025-12-23 13:02:56', 1, 1);

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

--
-- Stand-in structure for view `product_stock_view`
-- (See below for the actual view)
--
CREATE TABLE `product_stock_view` (
`product_id` int(11)
,`sku` varchar(50)
,`name` varchar(255)
,`generic_name` varchar(255)
,`unit` varchar(50)
,`category` varchar(100)
,`shop_id` int(11)
,`total_qty` decimal(55,0)
,`batch_count` bigint(21)
,`earliest_expiry` date
,`latest_expiry` date
);

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

--
-- Table structure for table `product_update_log`
--

CREATE TABLE `product_update_log` (
  `id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `updated_by` int(11) NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

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

CREATE TABLE `purchases` (
  `id` int(11) NOT NULL,
  `supplier_id` int(11) DEFAULT NULL,
  `invoice_no` varchar(100) DEFAULT NULL,
  `purchase_date` date DEFAULT NULL,
  `total` decimal(12,2) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `shop_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `purchases` (`id`, `supplier_id`, `invoice_no`, `purchase_date`, `total`, `created_by`, `updated_by`, `created_at`, `shop_id`) VALUES
(2, 1, '566', '2025-11-14', 2036000.00, 1, NULL, '2025-11-14 07:30:23', 1),
(3, 1, '566', '2025-12-03', 1950.00, 1, NULL, '2025-12-03 08:05:28', 1),
(111, 3, '1234', '2025-12-09', NULL, 1, NULL, '2025-12-09 10:18:36', 1),
(112, 3, '566', '2025-12-10', NULL, 1, NULL, '2025-12-10 09:18:24', 1),
(119, 3, 'INV-20251114-0012', '2025-12-11', 4000.00, 1, NULL, '2025-12-11 07:52:56', 1),
(120, 1, '566', '2025-12-11', 2400.00, 1, NULL, '2025-12-11 07:54:02', 1),
(129, 3, '566', '2025-12-11', 4500.00, 1, 1, '2025-12-11 10:33:04', 1),
(131, 1, 'PUR-20251223-502', '2025-12-23', 30000.00, 1, NULL, '2025-12-23 08:50:41', 1),
(132, 3, 'PUR-20251223-815', '2025-12-23', 30000.00, 1, NULL, '2025-12-23 10:02:56', 1);

--
-- Triggers `purchases`
--
DELIMITER $$
CREATE TRIGGER `after_purchase_delete` AFTER DELETE ON `purchases` FOR EACH ROW BEGIN
    -- Delete associated purchase lines (cascade would be better)
    DELETE FROM `purchase_lines` WHERE purchase_id = OLD.id;
    
    -- Remove inventory movements for this purchase
    DELETE FROM `inventory_movements` 
    WHERE movement_type = 'PURCHASE' AND reference_id = OLD.id;
END
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `before_purchase_delete` BEFORE DELETE ON `purchases` FOR EACH ROW BEGIN
    DECLARE batch_used INT;
    
    -- Check if any purchase lines from this purchase have been used in sales
    SELECT COUNT(*) INTO batch_used
    FROM `purchase_lines` pl
    JOIN `sale_lines` sl ON pl.batch_id = sl.batch_id
    WHERE pl.purchase_id = OLD.id;
    
    IF batch_used > 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Cannot delete purchase. Some batches have been sold. Use adjustment instead.';
    END IF;
END
$$
DELIMITER ;

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

--
-- Table structure for table `purchase_lines`
--

CREATE TABLE `purchase_lines` (
  `id` int(11) NOT NULL,
  `purchase_id` int(11) DEFAULT NULL,
  `batch_id` int(11) DEFAULT NULL,
  `qty` int(11) DEFAULT NULL,
  `cost_price` decimal(12,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `purchase_lines`
--

INSERT INTO `purchase_lines` (`id`, `purchase_id`, `batch_id`, `qty`, `cost_price`) VALUES
(3, 2, 4, 5000, 400.00),
(4, 2, 8, 150, 200.00),
(5, 3, 9, 12, 100.00),
(6, 3, 10, 15, 50.00),
(7, 2, 4, 20, 300.00),
(8, 129, 13, 15, 300.00),
(10, 131, 15, 100, 300.00),
(11, 132, 16, 100, 300.00);

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

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

CREATE TABLE `sales` (
  `id` int(11) NOT NULL,
  `invoice_no` varchar(100) DEFAULT NULL,
  `sale_date` datetime DEFAULT current_timestamp(),
  `customer_id` int(11) DEFAULT NULL,
  `total` decimal(12,2) DEFAULT NULL,
  `paid` decimal(12,2) DEFAULT 0.00,
  `status` enum('open','paid','partially_paid','cancelled') DEFAULT 'open',
  `created_by` int(11) DEFAULT NULL,
  `shop_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `sales` (`id`, `invoice_no`, `sale_date`, `customer_id`, `total`, `paid`, `status`, `created_by`, `shop_id`) VALUES
(2, 'INV-20251114-002', '2025-11-14 16:10:11', NULL, 16800.00, 13200.00, 'open', 1, 1),
(3, 'INV-20251114-003', '2025-11-14 16:11:17', NULL, 12000.00, 120000.00, 'open', 1, 1),
(5, 'INV-20251204-001', '2025-12-04 11:23:23', NULL, 6400.00, 6400.00, 'open', 1, 1),
(6, 'INV-20251204-002', '2025-12-04 11:24:44', NULL, 1200.00, 1200.00, 'open', 1, 1),
(7, 'INV-20251205-001', '2025-12-05 09:54:58', NULL, 12000.00, 12000.00, 'open', 1, 1),
(8, 'INV-20251209-001', '2025-12-09 09:10:13', NULL, 155999.00, 0.00, 'open', 1, 1),
(9, 'INV-20251209-002', '2025-12-09 09:34:27', NULL, 359980.00, 0.00, 'open', 1, 1),
(10, 'INV-20251209-003', '2025-12-09 09:47:30', NULL, 6000.00, 0.00, 'open', 1, 1),
(11, 'INV-20251209-004', '2025-12-09 09:48:07', NULL, 6000.00, 0.00, 'open', 1, 1),
(12, 'INV-20251216-001', '2025-12-16 10:13:34', NULL, 4500.00, 4500.00, 'open', 1, 1),
(13, 'SALE-20251223-452', '2025-12-23 10:13:00', NULL, 816.00, 816.00, 'paid', 1, 1),
(14, 'SALE-20251223-095', '2025-12-23 10:14:00', NULL, 2040.00, 2000.00, 'paid', 1, 1),
(15, 'SALE-20251223-868', '2025-12-23 10:18:00', NULL, 816.00, 800.00, 'paid', 1, 1),
(16, 'SALE-20251223-578', '2025-12-23 10:30:00', NULL, 1224.00, 1200.00, 'paid', 1, 1),
(17, 'SALE-20251223-594', '2025-12-23 10:31:00', NULL, 816.00, 800.00, 'paid', 1, 1),
(19, 'SALE-20251223-662', '2025-12-23 10:42:00', NULL, 816.00, 800.00, 'paid', 1, 1),
(20, 'SALE-20251223-995', '2025-12-23 10:58:00', 14, 816.00, 800.00, 'paid', 1, 1),
(21, 'SALE-20251223-713', '2025-12-23 11:07:00', NULL, 6783.00, 6650.00, 'paid', 1, 1),
(22, 'SALE-20251223-948', '2025-12-23 11:08:00', 14, 3570.00, 3500.00, 'paid', 1, 1),
(23, 'SALE-20251223-943', '2025-12-23 13:29:00', 14, 1100.00, 1100.00, 'paid', 1, 1);

--
-- Triggers `sales`
--
DELIMITER $$
CREATE TRIGGER `after_sale_delete` AFTER DELETE ON `sales` FOR EACH ROW BEGIN
    -- Delete associated sale lines
    DELETE FROM `sale_lines` WHERE sale_id = OLD.id;
    
    -- Remove inventory movements for this sale
    DELETE FROM `inventory_movements` 
    WHERE movement_type = 'SALE' AND reference_id = OLD.id;
END
$$
DELIMITER ;

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

--
-- Table structure for table `sale_lines`
--

CREATE TABLE `sale_lines` (
  `id` int(11) NOT NULL,
  `sale_id` int(11) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  `batch_id` int(11) DEFAULT NULL,
  `qty` int(11) DEFAULT NULL,
  `unit_price` decimal(12,2) DEFAULT NULL,
  `discount` decimal(12,2) DEFAULT 0.00,
  `line_total` decimal(12,2) DEFAULT NULL,
  `shop_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `sale_lines`
--

INSERT INTO `sale_lines` (`id`, `sale_id`, `product_id`, `batch_id`, `qty`, `unit_price`, `discount`, `line_total`, `shop_id`) VALUES
(2, 2, 2, 5, 14, 1200.00, 0.00, 16800.00, 1),
(3, 3, 2, 5, 10, 1200.00, 0.00, 12000.00, 1),
(5, 5, 2, 5, 1, 1200.00, 0.00, 1200.00, NULL),
(6, 5, 5, 8, 13, 400.00, 0.00, 5200.00, NULL),
(7, 6, 2, 5, 1, 1200.00, 0.00, 1200.00, NULL),
(8, 7, 2, 5, 10, 1200.00, 0.00, 12000.00, NULL),
(9, 8, 2, 5, 100, 1200.00, 0.00, 120000.00, NULL),
(10, 8, 2, 5, 30, 1200.00, 1.00, 35999.00, NULL),
(11, 9, 2, 5, 300, 1200.00, 20.00, 359980.00, NULL),
(12, 10, 2, 5, 5, 1200.00, 0.00, 6000.00, NULL),
(13, 11, 2, 5, 5, 1200.00, 0.00, 6000.00, NULL),
(14, 12, 4, 11, 18, 250.00, 0.00, 4500.00, NULL),
(15, 13, 5, 15, 2, 400.00, 0.00, 800.00, 1),
(16, 14, 5, 15, 5, 400.00, 0.00, 2000.00, 1),
(17, 15, 5, 15, 2, 400.00, 0.00, 800.00, 1),
(18, 16, 5, 15, 3, 400.00, 0.00, 1200.00, 1),
(19, 17, 5, 15, 2, 400.00, 0.00, 800.00, 1),
(20, 19, 5, 15, 2, 400.00, 0.00, 800.00, 1),
(21, 20, 5, 15, 2, 400.00, 0.00, 800.00, 1),
(22, 21, 5, 16, 19, 350.00, 0.00, 6650.00, 1),
(23, 22, 5, 16, 10, 350.00, 0.00, 3500.00, 1),
(24, 23, 5, 16, 2, 550.00, 0.00, 1100.00, 1);

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

--
-- Table structure for table `shops`
--

CREATE TABLE `shops` (
  `id` int(11) NOT NULL,
  `name` varchar(150) NOT NULL,
  `phone` varchar(100) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `shops`
--

INSERT INTO `shops` (`id`, `name`, `phone`, `address`, `created_at`) VALUES
(1, 'Goodhope Consultation Pharmacy', '', 'Kamachumu', '2025-12-01 09:02:46'),
(2, 'Shop A', '', 'Bukoba', '2025-12-01 09:10:06'),
(3, 'Shop B', '', 'Mwanza', '2025-12-01 09:10:06');

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

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

CREATE TABLE `suppliers` (
  `id` int(11) NOT NULL,
  `name` varchar(200) NOT NULL,
  `contact` varchar(100) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `suppliers` (`id`, `name`, `contact`, `email`, `address`, `created_at`) VALUES
(1, 'BARICK', '074234522', 'dd@gg.com', 'Bukoba', '2025-11-11 10:55:52'),
(2, 'SHEKAINA WORLD PHARMACY', '0716011831', 'shekaina@gmail.com', 'Shekaina Bukoba\r\nP.O.Box 35091 Bukoba', '2025-11-21 10:37:00'),
(3, 'MARAK PHARMACY', '0767795050', 'agustino0511@icloud.com', 'p.o.box 10 K/KKO', '2025-12-09 10:12:35');

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

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

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `fullname` varchar(150) DEFAULT NULL,
  `role` enum('admin','pharmacist','cashier') NOT NULL DEFAULT 'cashier',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `shop_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `users` (`id`, `username`, `password_hash`, `fullname`, `role`, `created_at`, `shop_id`) VALUES
(1, 'admin', '$2y$10$.GPFn6tIdCa0fqzOBUUoi.irH5T2ZnPc69rCwfyHnjR6E06QGXX5a', 'Dr.Augustino', 'admin', '2025-11-11 07:14:00', 1),
(2, 'myuser', 'mypass', 'Some Name', 'admin', '2025-12-19 11:31:28', 1),
(3, 'root', '123', 'Some Random Name', 'admin', '2025-12-19 11:45:00', 1);

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

--
-- Structure for view `batch_stock_view`
--
DROP TABLE IF EXISTS `batch_stock_view`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `batch_stock_view`  AS SELECT `pb`.`id` AS `batch_id`, `pb`.`product_id` AS `product_id`, `pb`.`batch_no` AS `batch_no`, `pb`.`expiry_date` AS `expiry_date`, `pb`.`cost_price` AS `cost_price`, `pb`.`sell_price` AS `sell_price`, `pb`.`shop_id` AS `shop_id`, `pb`.`is_active` AS `is_active`, coalesce(sum(case when `im`.`movement_type` in ('PURCHASE','RETURN') then `im`.`change_qty` when `im`.`movement_type` in ('SALE','ADJUSTMENT') then -`im`.`change_qty` else 0 end),0) AS `current_qty`, `pb`.`created_at` AS `created_at`, `pb`.`updated_at` AS `updated_at` FROM (`product_batches` `pb` left join `inventory_movements` `im` on(`pb`.`id` = `im`.`batch_id`)) WHERE `pb`.`is_active` = 1 GROUP BY `pb`.`id`, `pb`.`product_id`, `pb`.`batch_no`, `pb`.`expiry_date`, `pb`.`cost_price`, `pb`.`sell_price`, `pb`.`shop_id`, `pb`.`is_active`, `pb`.`created_at`, `pb`.`updated_at` ;

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

--
-- Structure for view `product_availability`
--
DROP TABLE IF EXISTS `product_availability`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `product_availability`  AS SELECT `p`.`id` AS `product_id`, `p`.`sku` AS `sku`, `p`.`name` AS `name`, `p`.`generic_name` AS `generic_name`, `p`.`unit` AS `unit`, `p`.`category` AS `category`, `p`.`buying_price` AS `buying_price`, `p`.`selling_price` AS `selling_price`, `bs`.`batch_id` AS `batch_id`, `bs`.`batch_no` AS `batch_no`, `bs`.`current_qty` AS `batch_qty`, `bs`.`expiry_date` AS `expiry_date`, `bs`.`cost_price` AS `cost_price`, `bs`.`sell_price` AS `sell_price`, `bs`.`shop_id` AS `shop_id`, CASE WHEN `bs`.`current_qty` > 0 THEN 'IN_STOCK' WHEN `bs`.`current_qty` = 0 THEN 'OUT_OF_STOCK' ELSE 'NEGATIVE_STOCK' END AS `stock_status`, to_days(`bs`.`expiry_date`) - to_days(curdate()) AS `days_to_expiry` FROM (`products` `p` left join `batch_stock_view` `bs` on(`p`.`id` = `bs`.`product_id`)) WHERE `bs`.`current_qty` > 0 ORDER BY `p`.`name` ASC, `bs`.`expiry_date` ASC ;

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

--
-- Structure for view `product_stock_view`
--
DROP TABLE IF EXISTS `product_stock_view`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `product_stock_view`  AS SELECT `p`.`id` AS `product_id`, `p`.`sku` AS `sku`, `p`.`name` AS `name`, `p`.`generic_name` AS `generic_name`, `p`.`unit` AS `unit`, `p`.`category` AS `category`, `p`.`shop_id` AS `shop_id`, sum(`bs`.`current_qty`) AS `total_qty`, count(distinct `bs`.`batch_id`) AS `batch_count`, min(`bs`.`expiry_date`) AS `earliest_expiry`, max(`bs`.`expiry_date`) AS `latest_expiry` FROM (`products` `p` left join `batch_stock_view` `bs` on(`p`.`id` = `bs`.`product_id`)) WHERE `p`.`shop_id` is not null GROUP BY `p`.`id`, `p`.`sku`, `p`.`name`, `p`.`generic_name`, `p`.`unit`, `p`.`category`, `p`.`shop_id` ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `customers`
--
ALTER TABLE `customers`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_customers_shop` (`shop_id`);

--
-- Indexes for table `inventory_movements`
--
ALTER TABLE `inventory_movements`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_movements_batch` (`batch_id`),
  ADD KEY `fk_movements_created_by_user` (`created_by`),
  ADD KEY `idx_inventory_movements_product_batch` (`product_id`,`batch_id`),
  ADD KEY `idx_inventory_movements_reference` (`movement_type`,`reference_id`),
  ADD KEY `idx_inventory_movements_shop` (`shop_id`);

--
-- Indexes for table `payments`
--
ALTER TABLE `payments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `sale_id` (`sale_id`),
  ADD KEY `created_by` (`created_by`);

--
-- Indexes for table `products`
--
ALTER TABLE `products`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `sku` (`sku`),
  ADD KEY `shop_id` (`shop_id`);

--
-- Indexes for table `product_batches`
--
ALTER TABLE `product_batches`
  ADD PRIMARY KEY (`id`),
  ADD KEY `product_id` (`product_id`),
  ADD KEY `supplier_id` (`supplier_id`),
  ADD KEY `created_by` (`created_by`),
  ADD KEY `fk_product_batches_updated_by` (`updated_by`),
  ADD KEY `idx_product_batches_product` (`product_id`),
  ADD KEY `idx_product_batches_shop` (`shop_id`);

--
-- Indexes for table `product_update_log`
--
ALTER TABLE `product_update_log`
  ADD PRIMARY KEY (`id`),
  ADD KEY `product_id` (`product_id`),
  ADD KEY `updated_by` (`updated_by`);

--
-- Indexes for table `purchases`
--
ALTER TABLE `purchases`
  ADD PRIMARY KEY (`id`),
  ADD KEY `supplier_id` (`supplier_id`),
  ADD KEY `created_by` (`created_by`);

--
-- Indexes for table `purchase_lines`
--
ALTER TABLE `purchase_lines`
  ADD PRIMARY KEY (`id`),
  ADD KEY `purchase_id` (`purchase_id`),
  ADD KEY `batch_id` (`batch_id`),
  ADD KEY `idx_purchase_lines_batch` (`batch_id`);

--
-- Indexes for table `sales`
--
ALTER TABLE `sales`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `invoice_no` (`invoice_no`),
  ADD KEY `customer_id` (`customer_id`),
  ADD KEY `created_by` (`created_by`),
  ADD KEY `fk_sales_shop` (`shop_id`);

--
-- Indexes for table `sale_lines`
--
ALTER TABLE `sale_lines`
  ADD PRIMARY KEY (`id`),
  ADD KEY `sale_id` (`sale_id`),
  ADD KEY `product_id` (`product_id`),
  ADD KEY `batch_id` (`batch_id`),
  ADD KEY `fk_sale_lines_shop` (`shop_id`),
  ADD KEY `idx_sale_lines_batch` (`batch_id`);

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

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

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `customers`
--
ALTER TABLE `customers`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;

--
-- AUTO_INCREMENT for table `inventory_movements`
--
ALTER TABLE `inventory_movements`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=37;

--
-- AUTO_INCREMENT for table `payments`
--
ALTER TABLE `payments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

--
-- AUTO_INCREMENT for table `products`
--
ALTER TABLE `products`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT for table `product_batches`
--
ALTER TABLE `product_batches`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;

--
-- AUTO_INCREMENT for table `product_update_log`
--
ALTER TABLE `product_update_log`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `purchases`
--
ALTER TABLE `purchases`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=133;

--
-- AUTO_INCREMENT for table `purchase_lines`
--
ALTER TABLE `purchase_lines`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;

--
-- AUTO_INCREMENT for table `sales`
--
ALTER TABLE `sales`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=24;

--
-- AUTO_INCREMENT for table `sale_lines`
--
ALTER TABLE `sale_lines`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=25;

--
-- AUTO_INCREMENT for table `shops`
--
ALTER TABLE `shops`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `suppliers`
--
ALTER TABLE `suppliers`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `customers`
--
ALTER TABLE `customers`
  ADD CONSTRAINT `fk_customers_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `inventory_movements`
--
ALTER TABLE `inventory_movements`
  ADD CONSTRAINT `fk_movements_batch` FOREIGN KEY (`batch_id`) REFERENCES `product_batches` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_movements_created_by_user` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_movements_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_movements_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `payments`
--
ALTER TABLE `payments`
  ADD CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`sale_id`) REFERENCES `sales` (`id`),
  ADD CONSTRAINT `payments_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `products`
--
ALTER TABLE `products`
  ADD CONSTRAINT `fk_products_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`);

--
-- Constraints for table `product_batches`
--
ALTER TABLE `product_batches`
  ADD CONSTRAINT `fk_product_batches_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_product_batches_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `product_batches_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
  ADD CONSTRAINT `product_batches_ibfk_2` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`),
  ADD CONSTRAINT `product_batches_ibfk_3` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `product_update_log`
--
ALTER TABLE `product_update_log`
  ADD CONSTRAINT `product_update_log_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `product_update_log_ibfk_2` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `purchases`
--
ALTER TABLE `purchases`
  ADD CONSTRAINT `purchases_ibfk_1` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`),
  ADD CONSTRAINT `purchases_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `purchase_lines`
--
ALTER TABLE `purchase_lines`
  ADD CONSTRAINT `purchase_lines_ibfk_1` FOREIGN KEY (`purchase_id`) REFERENCES `purchases` (`id`),
  ADD CONSTRAINT `purchase_lines_ibfk_2` FOREIGN KEY (`batch_id`) REFERENCES `product_batches` (`id`);

--
-- Constraints for table `sales`
--
ALTER TABLE `sales`
  ADD CONSTRAINT `fk_sales_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`),
  ADD CONSTRAINT `sales_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`),
  ADD CONSTRAINT `sales_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `sale_lines`
--
ALTER TABLE `sale_lines`
  ADD CONSTRAINT `fk_sale_lines_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `sale_lines_ibfk_1` FOREIGN KEY (`sale_id`) REFERENCES `sales` (`id`),
  ADD CONSTRAINT `sale_lines_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
  ADD CONSTRAINT `sale_lines_ibfk_3` FOREIGN KEY (`batch_id`) REFERENCES `product_batches` (`id`);
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 */;
