-- SQL script to manually create the customer_requests table
-- Use this if Django migrations fail to create the table

-- Create 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_related_application_id_idx` (`related_application_id`),
    KEY `customer_requests_related_loan_id_idx` (`related_loan_id`),
    KEY `customer_requests_resolved_by_id_idx` (`resolved_by_id`),
    KEY `customer_requests_created_at_idx` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create registration_fees table
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_created_by_id_idx` (`created_by_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 registration_fee_payments table
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_processed_by_id_idx` (`processed_by_id`),
    KEY `registration_fee_payments_registration_fee_id_idx` (`registration_fee_id`),
    KEY `registration_fee_payments_payment_date_idx` (`payment_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create report_schedules table
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_created_by_id_idx` (`created_by_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 report_executions table
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_schedule_id_idx` (`schedule_id`),
    KEY `report_executions_started_at_idx` (`started_at`),
    KEY `report_executions_status_idx` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create the many-to-many table for report schedule recipients
CREATE TABLE IF NOT EXISTS `report_schedules_recipients` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `reportschedule_id` char(32) NOT NULL,
    `customuser_id` bigint NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `report_schedules_recipients_unique` (`reportschedule_id`, `customuser_id`),
    KEY `report_schedules_recipients_reportschedule_id_idx` (`reportschedule_id`),
    KEY `report_schedules_recipients_customuser_id_idx` (`customuser_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 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 
(UUID(), 'boost', 'Boost Registration Fee', 500.00, 'Registration fee for Boost loan product', 1, NOW(), NOW(), NOW()),
(UUID(), 'boost_plus', 'Boost Plus Registration Fee', 750.00, 'Registration fee for Boost Plus loan product', 1, NOW(), NOW(), NOW()),
(UUID(), 'mwamba', 'Mwamba Registration Fee', 1000.00, 'Registration fee for Mwamba loan product', 1, NOW(), NOW(), NOW()),
(UUID(), 'imara', 'Imara Registration Fee', 1500.00, 'Registration fee for Imara loan product', 1, NOW(), NOW(), NOW()),
(UUID(), 'account_opening', 'Account Opening Fee', 200.00, 'Fee for opening new customer account', 1, NOW(), NOW(), NOW()),
(UUID(), 'document_processing', 'Document Processing Fee', 100.00, 'Fee for processing customer documents', 1, NOW(), NOW(), NOW()),
(UUID(), 'statement_request', 'Statement Request Fee', 50.00, 'Fee for requesting account statements', 1, NOW(), NOW(), NOW());

-- Verify tables were created
SELECT 'customer_requests' as table_name, COUNT(*) as record_count FROM customer_requests
UNION ALL
SELECT 'registration_fees' as table_name, COUNT(*) as record_count FROM registration_fees
UNION ALL
SELECT 'registration_fee_payments' as table_name, COUNT(*) as record_count FROM registration_fee_payments
UNION ALL
SELECT 'report_schedules' as table_name, COUNT(*) as record_count FROM report_schedules
UNION ALL
SELECT 'report_executions' as table_name, COUNT(*) as record_count FROM report_executions;