-- =====================================================
-- SQL Commands to Fix Missing Warehouses Table
-- =====================================================
-- Run these commands in order to create the warehouses
-- table and related tables for warehouse-based inventory
-- =====================================================

-- Step 1: Create warehouses table
-- =====================================================
CREATE TABLE IF NOT EXISTS `warehouses` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(191) NOT NULL,
  `code` varchar(191) DEFAULT NULL,
  `business_id` int(10) UNSIGNED NOT NULL,
  `address` text DEFAULT NULL,
  `city` varchar(191) DEFAULT NULL,
  `state` varchar(191) DEFAULT NULL,
  `country` varchar(191) DEFAULT NULL,
  `zip_code` varchar(191) DEFAULT NULL,
  `phone` varchar(191) DEFAULT NULL,
  `email` varchar(191) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_by` int(10) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `warehouses_code_unique` (`code`),
  KEY `warehouses_business_id_index` (`business_id`),
  KEY `warehouses_code_index` (`code`),
  KEY `warehouses_is_active_index` (`is_active`),
  CONSTRAINT `warehouses_business_id_foreign` FOREIGN KEY (`business_id`) REFERENCES `business` (`id`) ON DELETE CASCADE,
  CONSTRAINT `warehouses_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Step 2: Create product_warehouse pivot table
-- =====================================================
CREATE TABLE IF NOT EXISTS `product_warehouse` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `product_id` int(10) UNSIGNED NOT NULL,
  `variation_id` int(10) UNSIGNED NOT NULL,
  `warehouse_id` int(10) UNSIGNED NOT NULL,
  `qty_available` decimal(22,4) NOT NULL DEFAULT 0.0000,
  `qty_reserved` decimal(22,4) NOT NULL DEFAULT 0.0000 COMMENT 'Reserved for POS carts/orders',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_variation_warehouse_unique` (`product_id`,`variation_id`,`warehouse_id`),
  KEY `product_warehouse_product_id_index` (`product_id`),
  KEY `product_warehouse_variation_id_index` (`variation_id`),
  KEY `product_warehouse_warehouse_id_index` (`warehouse_id`),
  CONSTRAINT `product_warehouse_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  CONSTRAINT `product_warehouse_variation_id_foreign` FOREIGN KEY (`variation_id`) REFERENCES `variations` (`id`) ON DELETE CASCADE,
  CONSTRAINT `product_warehouse_warehouse_id_foreign` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Step 3: Create stock_movements table (if not exists)
-- =====================================================
CREATE TABLE IF NOT EXISTS `stock_movements` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `business_id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `variation_id` int(10) UNSIGNED NOT NULL,
  `warehouse_id` int(10) UNSIGNED NOT NULL,
  `type` enum('purchase','sale','opening_stock','adjustment','transfer_in','transfer_out','return','production','expiry','damage','other') NOT NULL,
  `quantity` decimal(22,4) NOT NULL COMMENT 'Positive for increase, negative for decrease',
  `qty_before` decimal(22,4) NOT NULL DEFAULT 0.0000,
  `qty_after` decimal(22,4) NOT NULL DEFAULT 0.0000,
  `reference_type` varchar(191) DEFAULT NULL COMMENT 'Model class name (Transaction, StockAdjustment, etc.)',
  `reference_id` int(11) DEFAULT NULL COMMENT 'ID of the reference model',
  `reference_no` varchar(191) DEFAULT NULL COMMENT 'Human-readable reference (invoice_no, etc.)',
  `notes` text DEFAULT NULL,
  `created_by` int(10) UNSIGNED NOT NULL,
  `movement_date` timestamp NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `stock_movements_business_id_index` (`business_id`),
  KEY `stock_movements_product_id_index` (`product_id`),
  KEY `stock_movements_variation_id_index` (`variation_id`),
  KEY `stock_movements_warehouse_id_index` (`warehouse_id`),
  KEY `stock_movements_type_index` (`type`),
  KEY `stock_movements_reference_type_index` (`reference_type`),
  KEY `stock_movements_reference_type_reference_id_index` (`reference_type`,`reference_id`),
  KEY `stock_movements_movement_date_index` (`movement_date`),
  CONSTRAINT `stock_movements_business_id_foreign` FOREIGN KEY (`business_id`) REFERENCES `business` (`id`) ON DELETE CASCADE,
  CONSTRAINT `stock_movements_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  CONSTRAINT `stock_movements_variation_id_foreign` FOREIGN KEY (`variation_id`) REFERENCES `variations` (`id`) ON DELETE CASCADE,
  CONSTRAINT `stock_movements_warehouse_id_foreign` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`) ON DELETE CASCADE,
  CONSTRAINT `stock_movements_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Step 4: Add warehouse_id column to transactions table (if not exists)
-- =====================================================
-- Check if column exists before adding
SET @col_exists = (
    SELECT COUNT(*) 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'transactions' 
    AND COLUMN_NAME = 'warehouse_id'
);

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE `transactions` 
     ADD COLUMN `warehouse_id` int(10) UNSIGNED NULL AFTER `location_id`,
     ADD INDEX `transactions_warehouse_id_index` (`warehouse_id`),
     ADD CONSTRAINT `transactions_warehouse_id_foreign` 
         FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`) ON DELETE SET NULL',
    'SELECT "Column warehouse_id already exists in transactions table" AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Step 5: Add default_warehouse_id to business table (if not exists)
-- =====================================================
SET @col_exists = (
    SELECT COUNT(*) 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'business' 
    AND COLUMN_NAME = 'default_warehouse_id'
);

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE `business` 
     ADD COLUMN `default_warehouse_id` int(10) UNSIGNED NULL,
     ADD INDEX `business_default_warehouse_id_index` (`default_warehouse_id`),
     ADD CONSTRAINT `business_default_warehouse_id_foreign` 
         FOREIGN KEY (`default_warehouse_id`) REFERENCES `warehouses` (`id`) ON DELETE SET NULL',
    'SELECT "Column default_warehouse_id already exists in business table" AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- =====================================================
-- Verification Queries
-- =====================================================
-- Run these to verify the tables were created successfully:

-- SELECT 'warehouses table' AS table_name, COUNT(*) AS row_count FROM warehouses;
-- SELECT 'product_warehouse table' AS table_name, COUNT(*) AS row_count FROM product_warehouse;
-- SELECT 'stock_movements table' AS table_name, COUNT(*) AS row_count FROM stock_movements;
-- SHOW COLUMNS FROM transactions LIKE 'warehouse_id';
-- SHOW COLUMNS FROM business LIKE 'default_warehouse_id';