-- COMPREHENSIVE SQL FIX FOR ALL MISSING COLUMNS
-- Run this directly in your production database to fix all OperationalError 1054 issues

-- Create missing tables first
CREATE TABLE IF NOT EXISTS `penalty_charges` (
    `id` char(32) NOT NULL PRIMARY KEY,
    `amount` decimal(12,2) NOT NULL,
    `penalty_rate` decimal(5,2) NOT NULL,
    `days_overdue` int unsigned NOT NULL,
    `outstanding_amount` decimal(12,2) NOT NULL,
    `applied_date` datetime(6) NOT NULL,
    `loan_id` char(32) NOT NULL,
    `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    KEY `penalty_charges_loan_id_idx` (`loan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `customer_requests` (
    `id` char(32) NOT NULL PRIMARY KEY,
    `request_number` varchar(20) NOT NULL UNIQUE,
    `subject` varchar(200) NOT NULL,
    `description` longtext NOT NULL,
    `request_type` varchar(50) NOT NULL,
    `priority` varchar(20) NOT NULL DEFAULT 'medium',
    `status` varchar(20) NOT NULL DEFAULT 'pending',
    `customer_id` char(32) NOT NULL,
    `related_loan_id` char(32) NULL,
    `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    KEY `customer_requests_customer_id_idx` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `offer_letters` (
    `id` char(32) NOT NULL PRIMARY KEY,
    `letter_number` varchar(20) NOT NULL UNIQUE,
    `application_id` char(32) NOT NULL,
    `borrower_id` char(32) NOT NULL,
    `loan_amount` decimal(12,2) NOT NULL,
    `generated_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    `status` varchar(20) NOT NULL DEFAULT 'pending',
    KEY `offer_letters_application_id_idx` (`application_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `portfolio_assignments` (
    `id` char(32) NOT NULL PRIMARY KEY,
    `assignment_number` varchar(20) NOT NULL UNIQUE,
    `portfolio_manager_id` char(32) NOT NULL,
    `client_id` char(32) NOT NULL,
    `assigned_date` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    `status` varchar(20) NOT NULL DEFAULT 'active',
    `notes` longtext NULL,
    `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    `updated_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    KEY `portfolio_assignments_portfolio_manager_id_idx` (`portfolio_manager_id`),
    KEY `portfolio_assignments_client_id_idx` (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add missing columns to existing tables
-- Only add if they don't exist (MySQL will error if column exists)

-- Fix notifications table
ALTER TABLE `notifications` ADD COLUMN `related_loan_id` char(32) NULL;
ALTER TABLE `notifications` ADD COLUMN `related_application_id` char(32) NULL;
ALTER TABLE `notifications` ADD KEY `notifications_related_loan_id_idx` (`related_loan_id`);
ALTER TABLE `notifications` ADD KEY `notifications_related_application_id_idx` (`related_application_id`);

-- Fix receipts table
ALTER TABLE `receipts` ADD COLUMN `loan_id` char(32) NOT NULL;
ALTER TABLE `receipts` ADD COLUMN `repayment_id` char(32) NULL;
ALTER TABLE `receipts` ADD KEY `receipts_loan_id_idx` (`loan_id`);

-- Fix loan_statements table
ALTER TABLE `loan_statements` ADD COLUMN `loan_id` char(32) NOT NULL;
ALTER TABLE `loan_statements` ADD KEY `loan_statements_loan_id_idx` (`loan_id`);

-- Fix utils_notification table
ALTER TABLE `utils_notification` ADD COLUMN `related_loan_id` char(32) NULL;
ALTER TABLE `utils_notification` ADD COLUMN `loan_id` char(32) NULL;
ALTER TABLE `utils_notification` ADD KEY `utils_notification_related_loan_id_idx` (`related_loan_id`);

-- Fix loan_applications table
ALTER TABLE `loan_applications` ADD COLUMN `registration_fee_amount` decimal(12,2) NULL DEFAULT 0.00;

-- Fix loans table
ALTER TABLE `loans` ADD COLUMN `registration_fee` decimal(12,2) NOT NULL DEFAULT 0.00;

-- Fix users_groups table
ALTER TABLE `users_groups` ADD COLUMN `customuser_id` char(32) NOT NULL;
ALTER TABLE `users_groups` ADD KEY `users_groups_customuser_id_idx` (`customuser_id`);

-- Fix users_user_permissions table
ALTER TABLE `users_user_permissions` ADD COLUMN `customuser_id` char(32) NOT NULL;
ALTER TABLE `users_user_permissions` ADD KEY `users_user_permissions_customuser_id_idx` (`customuser_id`);

-- Verify tables exist
SELECT 'Tables created/verified:' as status;
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME IN ('penalty_charges', 'customer_requests', 'offer_letters', 'portfolio_assignments', 'notifications', 'receipts', 'loan_statements', 'utils_notification');

-- Verify critical columns exist
SELECT 'Critical columns verified:' as status;
SELECT CONCAT(TABLE_NAME, '.', COLUMN_NAME) as column_exists
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
AND (
    (TABLE_NAME = 'notifications' AND COLUMN_NAME = 'related_loan_id') OR
    (TABLE_NAME = 'notifications' AND COLUMN_NAME = 'related_application_id') OR
    (TABLE_NAME = 'receipts' AND COLUMN_NAME = 'loan_id') OR
    (TABLE_NAME = 'loan_statements' AND COLUMN_NAME = 'loan_id') OR
    (TABLE_NAME = 'offer_letters' AND COLUMN_NAME = 'application_id') OR
    (TABLE_NAME = 'penalty_charges' AND COLUMN_NAME = 'created_at') OR
    (TABLE_NAME = 'utils_notification' AND COLUMN_NAME = 'related_loan_id') OR
    (TABLE_NAME = 'users_groups' AND COLUMN_NAME = 'customuser_id') OR
    (TABLE_NAME = 'users_user_permissions' AND COLUMN_NAME = 'customuser_id')
);