-- =====================================================
-- FINAL DATABASE STRUCTURE FIX SCRIPT
-- This script fixes all identified database issues
-- =====================================================

-- 1. FIX SUPPLIERS TABLE
-- Add missing columns to suppliers table
ALTER TABLE `suppliers` ADD COLUMN IF NOT EXISTS `phone` varchar(255) NULL AFTER `postal_code`;
ALTER TABLE `suppliers` ADD COLUMN IF NOT EXISTS `email` varchar(255) NULL AFTER `phone`;
ALTER TABLE `suppliers` ADD COLUMN IF NOT EXISTS `categories` JSON NULL AFTER `description`;
ALTER TABLE `suppliers` ADD COLUMN IF NOT EXISTS `rating_breakdown` JSON NULL AFTER `categories`;
ALTER TABLE `suppliers` ADD COLUMN IF NOT EXISTS `rating` DECIMAL(3,2) DEFAULT 0.00 AFTER `rating_breakdown`;

-- 2. FIX PRODUCTS TABLE
-- Add missing columns to products table
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `default_packaging` varchar(255) NULL AFTER `unit`;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `description` text NULL AFTER `default_packaging`;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `cas_no` varchar(255) NULL AFTER `country`;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `analysis_sheet` varchar(255) NULL AFTER `cas_no`;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `inStock` boolean DEFAULT true AFTER `analysis_sheet`;

-- 3. CREATE SUPPLIER_SALES_EXPERTS TABLE
CREATE TABLE IF NOT EXISTS `supplier_sales_experts` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `supplier_id` bigint(20) unsigned NOT NULL,
    `name` varchar(255) NOT NULL,
    `phone` varchar(255) NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `supplier_sales_experts_supplier_id_foreign` (`supplier_id`),
    CONSTRAINT `supplier_sales_experts_supplier_id_foreign` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. CREATE SUPPLIER_PHONES TABLE
CREATE TABLE IF NOT EXISTS `supplier_phones` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `supplier_id` bigint(20) unsigned NOT NULL,
    `phone` varchar(255) NOT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `supplier_phones_supplier_id_foreign` (`supplier_id`),
    CONSTRAINT `supplier_phones_supplier_id_foreign` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. FIX SUPPLIER_NOTES TABLE - CRITICAL FIX
-- Create table if it doesn't exist
CREATE TABLE IF NOT EXISTS `supplier_notes` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `supplier_id` bigint(20) unsigned NOT NULL,
    `body` text NOT NULL,
    `created_by` varchar(255) NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `supplier_notes_supplier_id_foreign` (`supplier_id`),
    CONSTRAINT `supplier_notes_supplier_id_foreign` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- If table exists but has 'note' column instead of 'body', fix it
-- First add 'body' column if it doesn't exist
ALTER TABLE `supplier_notes` ADD COLUMN IF NOT EXISTS `body` text AFTER `supplier_id`;

-- Copy data from 'note' to 'body' if 'note' column exists
-- Note: This will only work if both columns exist temporarily
-- UPDATE `supplier_notes` SET `body` = `note` WHERE `note` IS NOT NULL AND `body` IS NULL;

-- Ensure 'created_by' column exists
ALTER TABLE `supplier_notes` ADD COLUMN IF NOT EXISTS `created_by` varchar(255) NULL AFTER `body`;

-- 6. FIX PRODUCT_SUPPLIER PIVOT TABLE
-- Create table if it doesn't exist
CREATE TABLE IF NOT EXISTS `product_supplier` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `product_id` bigint(20) unsigned NOT NULL,
    `supplier_id` bigint(20) unsigned NOT NULL,
    `price` decimal(10,2) NULL,
    `currency` varchar(255) NULL,
    `brand` varchar(255) NULL,
    `country` varchar(255) NULL,
    `grade` varchar(255) NULL,
    `purity` varchar(255) NULL,
    `packaging` varchar(255) NULL,
    `last_quoted_at` timestamp NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `product_supplier_product_id_supplier_id_unique` (`product_id`, `supplier_id`),
    KEY `product_supplier_product_id_foreign` (`product_id`),
    KEY `product_supplier_supplier_id_foreign` (`supplier_id`),
    CONSTRAINT `product_supplier_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
    CONSTRAINT `product_supplier_supplier_id_foreign` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add missing columns to existing product_supplier table
ALTER TABLE `product_supplier` ADD COLUMN IF NOT EXISTS `currency` varchar(255) NULL AFTER `price`;
ALTER TABLE `product_supplier` ADD COLUMN IF NOT EXISTS `brand` varchar(255) NULL AFTER `currency`;
ALTER TABLE `product_supplier` ADD COLUMN IF NOT EXISTS `country` varchar(255) NULL AFTER `brand`;
ALTER TABLE `product_supplier` ADD COLUMN IF NOT EXISTS `grade` varchar(255) NULL AFTER `country`;
ALTER TABLE `product_supplier` ADD COLUMN IF NOT EXISTS `purity` varchar(255) NULL AFTER `grade`;
ALTER TABLE `product_supplier` ADD COLUMN IF NOT EXISTS `packaging` varchar(255) NULL AFTER `purity`;
ALTER TABLE `product_supplier` ADD COLUMN IF NOT EXISTS `last_quoted_at` timestamp NULL AFTER `packaging`;

-- =====================================================
-- VERIFICATION QUERIES
-- Run these to verify the structure is correct
-- =====================================================

-- Check suppliers table structure
-- DESCRIBE suppliers;

-- Check products table structure  
-- DESCRIBE products;

-- Check supplier_notes table structure (should have 'body' column)
-- DESCRIBE supplier_notes;

-- Check if all required tables exist
-- SHOW TABLES LIKE 'supplier_%';
-- SHOW TABLES LIKE 'product_%';