-- Stock Management Migration
-- Add stock fields to existing items tables and create stock management tables

-- Add stock fields to items table template
-- This will be applied to all branch-specific items tables
-- Usage: sed 's/{branch_code}/m001/g' add_stock_management.sql | mysql -u root -h localhost test

-- Add stock fields to items table
ALTER TABLE `{branch_code}_items` 
ADD COLUMN `current_stock` decimal(10,3) DEFAULT 0.00 COMMENT 'Current available stock quantity',
ADD COLUMN `reorder_level` decimal(10,3) DEFAULT 0.00 COMMENT 'Minimum stock level for reorder alerts',
ADD COLUMN `last_updated` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- Create stock transactions table - tracks all stock movements
CREATE TABLE IF NOT EXISTS `{branch_code}_stock_transactions` (
  `transaction_id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(11) NOT NULL,
  `transaction_type` enum('receipt','delivery','sale','return','adjustment') NOT NULL,
  `quantity_change` decimal(10,3) NOT NULL COMMENT 'Positive for receipts/returns, negative for sales/deliveries',
  `new_balance` decimal(10,3) NOT NULL COMMENT 'Stock balance after this transaction',
  `reference_type` enum('sale','purchase','manual') DEFAULT NULL,
  `reference_id` int(11) DEFAULT NULL COMMENT 'References sale_id, purchase_id, etc.',
  `notes` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`transaction_id`),
  KEY `item_id` (`item_id`),
  KEY `transaction_type` (`transaction_type`),
  KEY `reference_type_id` (`reference_type`, `reference_id`),
  KEY `created_at` (`created_at`),
  CONSTRAINT `{branch_code}_stock_transactions_item_fk` FOREIGN KEY (`item_id`) REFERENCES `{branch_code}_items` (`items_id`),
  CONSTRAINT `{branch_code}_stock_transactions_user_fk` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create stock receipts table - main table for stock receipt/delivery documents
CREATE TABLE IF NOT EXISTS `{branch_code}_stock_receipts` (
  `receipt_id` int(11) NOT NULL AUTO_INCREMENT,
  `receipt_number` varchar(50) NOT NULL,
  `receipt_type` enum('receipt','delivery') NOT NULL COMMENT 'receipt=stock in, delivery=stock out',
  `supplier_name` varchar(255) DEFAULT NULL,
  `reference_number` varchar(100) DEFAULT NULL COMMENT 'PO number, invoice number, etc.',
  `total_items` int(11) DEFAULT 0,
  `status` enum('draft','completed','cancelled') DEFAULT 'draft',
  `notes` text DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `completed_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`receipt_id`),
  UNIQUE KEY `receipt_number` (`receipt_number`),
  KEY `receipt_type` (`receipt_type`),
  KEY `status` (`status`),
  KEY `created_at` (`created_at`),
  CONSTRAINT `{branch_code}_stock_receipts_user_fk` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create stock receipt items table - detail items for each receipt/delivery
CREATE TABLE IF NOT EXISTS `{branch_code}_stock_receipt_items` (
  `receipt_item_id` int(11) NOT NULL AUTO_INCREMENT,
  `receipt_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `quantity` decimal(10,3) NOT NULL,
  `unit_cost` decimal(10,2) DEFAULT 0.00,
  `total_cost` decimal(10,2) DEFAULT 0.00,
  `expiry_date` date DEFAULT NULL,
  `batch_number` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`receipt_item_id`),
  KEY `receipt_id` (`receipt_id`),
  KEY `item_id` (`item_id`),
  CONSTRAINT `{branch_code}_stock_receipt_items_receipt_fk` FOREIGN KEY (`receipt_id`) REFERENCES `{branch_code}_stock_receipts` (`receipt_id`) ON DELETE CASCADE,
  CONSTRAINT `{branch_code}_stock_receipt_items_item_fk` FOREIGN KEY (`item_id`) REFERENCES `{branch_code}_items` (`items_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; 