-- ========================================
-- Stock Management Migration for phpMyAdmin
-- ========================================
-- INSTRUCTIONS:
-- 1. Open phpMyAdmin
-- 2. Select your database
-- 3. Click "SQL" tab
-- 4. Copy and paste this entire script
-- 5. Click "Go" to execute
-- 
-- This script assumes your branch code is 'main'
-- If different, replace 'main' with your actual branch code
-- ========================================

-- Step 1: Add stock fields to existing items table
ALTER TABLE `main_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;

-- Step 2: Create stock transactions table
CREATE TABLE `main_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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Step 3: Create stock receipts table
CREATE TABLE `main_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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Step 4: Create stock receipt items table
CREATE TABLE `main_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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Step 5: Add foreign key constraints (run separately if needed)
ALTER TABLE `main_stock_transactions` 
ADD CONSTRAINT `main_stock_transactions_item_fk` FOREIGN KEY (`item_id`) REFERENCES `main_items` (`items_id`);

ALTER TABLE `main_stock_transactions` 
ADD CONSTRAINT `main_stock_transactions_user_fk` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);

ALTER TABLE `main_stock_receipts` 
ADD CONSTRAINT `main_stock_receipts_user_fk` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);

ALTER TABLE `main_stock_receipt_items` 
ADD CONSTRAINT `main_stock_receipt_items_receipt_fk` FOREIGN KEY (`receipt_id`) REFERENCES `main_stock_receipts` (`receipt_id`) ON DELETE CASCADE;

ALTER TABLE `main_stock_receipt_items` 
ADD CONSTRAINT `main_stock_receipt_items_item_fk` FOREIGN KEY (`item_id`) REFERENCES `main_items` (`items_id`);

-- Step 6: Set initial stock levels (OPTIONAL - uncomment if needed)
-- UPDATE main_items SET 
--     current_stock = 50.00,
--     reorder_level = 10.00,
--     last_updated = NOW()
-- WHERE sell_type = 'unit';

-- UPDATE main_items SET 
--     current_stock = 25.00,
--     reorder_level = 5.00,
--     last_updated = NOW()
-- WHERE sell_type = 'fraction';

-- Migration completed!
-- You should see 3 new tables created and 3 new columns added to main_items table 