-- Complete SQL script to fix all supplier and product related issues
-- This script will create missing tables and add missing columns

-- 1. Create supplier_sales_experts table if it doesn't exist
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) DEFAULT 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;

-- 2. Create supplier_phones table if it doesn't exist
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;

-- 3. Create supplier_notes 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,
  `note` text NOT 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;

-- 4. Add missing columns to suppliers table
-- Note: Run these commands one by one if you get errors about existing columns

-- Check current structure first
DESCRIBE suppliers;

-- Add phone and email if they don't exist
ALTER TABLE `suppliers` ADD COLUMN `phone` VARCHAR(255) NULL AFTER `postal_code`;
ALTER TABLE `suppliers` ADD COLUMN `email` VARCHAR(255) NULL AFTER `phone`;

-- Add new columns for categories and rating
ALTER TABLE `suppliers` ADD COLUMN `categories` JSON NULL AFTER `description`;
ALTER TABLE `suppliers` ADD COLUMN `rating_breakdown` JSON NULL AFTER `categories`;
ALTER TABLE `suppliers` ADD COLUMN `rating` DECIMAL(3,2) DEFAULT 0.00 AFTER `rating_breakdown`;

-- 5. Add missing columns to products table
ALTER TABLE `products` ADD COLUMN `default_packaging` VARCHAR(255) NULL AFTER `unit`;
ALTER TABLE `products` ADD COLUMN `description` TEXT NULL AFTER `default_packaging`;
ALTER TABLE `products` ADD COLUMN `cas_no` VARCHAR(255) NULL AFTER `country`;
ALTER TABLE `products` ADD COLUMN `analysis_sheet` VARCHAR(255) NULL AFTER `inventory`;
ALTER TABLE `products` ADD COLUMN `inStock` BOOLEAN DEFAULT TRUE AFTER `analysis_sheet`;

-- 6. Fix product_supplier pivot table
-- First check if table exists
SHOW TABLES LIKE 'product_supplier';

-- If table doesn't exist, create it first
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) DEFAULT 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_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 product_supplier table
ALTER TABLE `product_supplier` ADD COLUMN `currency` VARCHAR(10) NULL AFTER `price`;
ALTER TABLE `product_supplier` ADD COLUMN `brand` VARCHAR(255) NULL AFTER `currency`;
ALTER TABLE `product_supplier` ADD COLUMN `country` VARCHAR(255) NULL AFTER `brand`;
ALTER TABLE `product_supplier` ADD COLUMN `grade` VARCHAR(255) NULL AFTER `country`;
ALTER TABLE `product_supplier` ADD COLUMN `purity` VARCHAR(255) NULL AFTER `grade`;
ALTER TABLE `product_supplier` ADD COLUMN `packaging` VARCHAR(255) NULL AFTER `purity`;
ALTER TABLE `product_supplier` ADD COLUMN `last_quoted_at` TIMESTAMP NULL AFTER `packaging`;

-- 7. Verify all tables were created and columns added
SHOW TABLES LIKE 'supplier_%';

-- 8. Verify suppliers table structure
DESCRIBE suppliers;

-- 9. Verify products table structure  
DESCRIBE products;

-- 10. Verify product_supplier table structure
DESCRIBE product_supplier;