-- SQL-only fix for migration dependency issue and missing customer_requests table
-- Run this directly in your MySQL database

-- Step 1: Fix the migration dependency issue
-- Insert the missing users.0001_initial migration record
INSERT IGNORE INTO django_migrations (app, name, applied) 
VALUES ('users', '0001_initial', NOW());

-- Step 2: Create the missing 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_assigned_to_id_idx` (`assigned_to_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 3: Create other missing enhanced model 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`),
    KEY `registration_fees_product_type_idx` (`product_type`),
    KEY `registration_fees_is_active_idx` (`is_active`)
) 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`),
    KEY `registration_fee_payments_customer_id_idx` (`customer_id`),
    KEY `registration_fee_payments_payment_date_idx` (`payment_date`)
) 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`),
    KEY `report_schedules_is_active_idx` (`is_active`),
    KEY `report_schedules_next_run_idx` (`next_run`)
) 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`),
    KEY `report_executions_started_at_idx` (`started_at`),
    KEY `report_executions_status_idx` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

-- Step 5: 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(), '-', ''), 'mwamba', 'Mwamba Registration Fee', 1000.00, 'Registration fee for Mwamba loan product', 1, NOW(), NOW(), NOW()),
(REPLACE(UUID(), '-', ''), 'imara', 'Imara Registration Fee', 1500.00, 'Registration fee for Imara 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 6: Verify the fix
SELECT 'Migration Status' as check_type, 
       COUNT(*) as count 
FROM django_migrations 
WHERE (app = 'users' AND name = '0001_initial') 
   OR (app = 'reports' AND name = '0002_enhanced_reports_models')

UNION ALL

SELECT 'Tables Created' as check_type, 
       COUNT(*) as count 
FROM information_schema.tables 
WHERE table_schema = DATABASE() 
  AND table_name IN ('customer_requests', 'registration_fees', 'registration_fee_payments')

UNION ALL

SELECT 'Sample Data' as check_type, 
       COUNT(*) as count 
FROM registration_fees;

-- Show the results
SELECT 'Fix completed successfully!' as status;
SELECT 'You can now access /reports/customer-requests/ without the table error' as next_step;