-- IMMEDIATE FIX for customer_requests table error
-- Run this SQL directly in your production database to fix the issue immediately

-- Step 1: Create the customer_requests table
CREATE TABLE IF NOT EXISTS `customer_requests` (
    `id` char(32) NOT NULL,
    `request_number` varchar(20) NOT NULL UNIQUE,
    `request_type` varchar(30) NOT NULL,
    `subject` varchar(200) NOT NULL,
    `description` longtext NOT NULL,
    `priority` varchar(20) NOT NULL DEFAULT 'medium',
    `status` varchar(20) NOT NULL DEFAULT 'pending',
    `resolution_notes` longtext,
    `resolved_at` datetime(6),
    `created_at` datetime(6) NOT NULL,
    `updated_at` datetime(6) NOT NULL,
    `assigned_to_id` bigint,
    `customer_id` bigint NOT NULL,
    `related_application_id` char(32),
    `related_loan_id` char(32),
    `resolved_by_id` bigint,
    PRIMARY KEY (`id`),
    KEY `customer_requests_customer_id_idx` (`customer_id`),
    KEY `customer_requests_created_at_idx` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Step 2: Create other required tables
CREATE TABLE IF NOT EXISTS `registration_fees` (
    `id` char(32) NOT NULL,
    `product_type` varchar(30) NOT NULL,
    `fee_name` varchar(200) NOT NULL,
    `amount` decimal(10,2) NOT NULL,
    `description` longtext,
    `is_active` tinyint(1) NOT NULL DEFAULT 1,
    `effective_from` datetime(6) NOT NULL,
    `effective_to` datetime(6),
    `created_at` datetime(6) NOT NULL,
    `updated_at` datetime(6) NOT NULL,
    `created_by_id` bigint,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `registration_fee_payments` (
    `id` char(32) NOT NULL,
    `receipt_number` varchar(20) NOT NULL UNIQUE,
    `amount_paid` decimal(10,2) NOT NULL,
    `payment_method` varchar(20) NOT NULL,
    `payment_date` datetime(6) NOT NULL,
    `transaction_reference` varchar(100),
    `payment_notes` longtext,
    `created_at` datetime(6) NOT NULL,
    `customer_id` bigint NOT NULL,
    `processed_by_id` bigint,
    `registration_fee_id` char(32) NOT NULL,
    `related_application_id` char(32),
    `related_loan_id` char(32),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `report_schedules` (
    `id` char(32) NOT NULL,
    `name` varchar(200) NOT NULL,
    `report_type` varchar(30) NOT NULL,
    `frequency` varchar(20) NOT NULL,
    `is_active` tinyint(1) NOT NULL DEFAULT 1,
    `next_run` datetime(6) NOT NULL,
    `last_run` datetime(6),
    `parameters` json NOT NULL,
    `created_at` datetime(6) NOT NULL,
    `created_by_id` bigint NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `report_executions` (
    `id` char(32) NOT NULL,
    `report_type` varchar(30) NOT NULL,
    `status` varchar(20) NOT NULL DEFAULT 'pending',
    `started_at` datetime(6) NOT NULL,
    `completed_at` datetime(6),
    `error_message` longtext,
    `pdf_file` varchar(100),
    `excel_file` varchar(100),
    `parameters` json NOT NULL,
    `records_processed` int unsigned NOT NULL DEFAULT 0,
    `execution_time` double,
    `schedule_id` char(32),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Step 3: Mark the migration as applied
INSERT IGNORE INTO django_migrations (app, name, applied) 
VALUES ('reports', '0002_enhanced_reports_models', NOW());

-- Step 4: Insert sample registration fees
INSERT IGNORE INTO `registration_fees` (
    `id`, `product_type`, `fee_name`, `amount`, `description`, 
    `is_active`, `effective_from`, `created_at`, `updated_at`
) VALUES 
(REPLACE(UUID(), '-', ''), 'boost', 'Boost Registration Fee', 500.00, 'Registration fee for Boost loan product', 1, NOW(), NOW(), NOW()),
(REPLACE(UUID(), '-', ''), 'boost_plus', 'Boost Plus Registration Fee', 750.00, 'Registration fee for Boost Plus loan product', 1, NOW(), NOW(), NOW()),
(REPLACE(UUID(), '-', ''), 'account_opening', 'Account Opening Fee', 200.00, 'Fee for opening new customer account', 1, NOW(), NOW(), NOW());

-- Step 5: Verify the fix
SELECT 'Tables Created Successfully!' as status;
SELECT table_name, table_rows FROM information_schema.tables 
WHERE table_schema = DATABASE() 
AND table_name IN ('customer_requests', 'registration_fees') 
ORDER BY table_name;

-- This should resolve the error:
-- "Table 'acbptxvs_branch_system.customer_requests' doesn't exist"