-- Fix supplier_notes table structure
-- This script ensures the table has the correct column names and structure

-- Check if table exists, if not create it
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) DEFAULT 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', rename it
-- Note: This requires ALTER TABLE which may not work in all MySQL versions
-- Alternative approach: Add 'body' column and copy data, then drop 'note'

-- Check if 'note' column exists and 'body' doesn't
-- If so, add 'body' column and copy data
ALTER TABLE `supplier_notes` ADD COLUMN IF NOT EXISTS `body` text AFTER `supplier_id`;

-- Copy data from 'note' to 'body' if 'note' column exists
-- 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) DEFAULT NULL AFTER `body`;

-- Add foreign key constraint if it doesn't exist
-- Note: This may fail if the constraint already exists
-- ALTER TABLE `supplier_notes` ADD CONSTRAINT `supplier_notes_supplier_id_foreign` 
-- FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`) ON DELETE CASCADE;