-- ============================================================================
-- EXPENSES TABLE CREATION - COPY THIS ENTIRE FILE TO PHPMYADMIN
-- ============================================================================
-- Instructions:
-- 1. Login to cPanel → phpMyAdmin
-- 2. Select your database
-- 3. Click "SQL" tab
-- 4. Copy and paste this ENTIRE file
-- 5. Click "Go"
-- 6. Restart your Python app
-- 7. Visit /expenses/ on your website
-- ============================================================================

-- Create expenses table (using varchar instead of char for compatibility)
CREATE TABLE IF NOT EXISTS `expenses` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `title` varchar(200) NOT NULL,
    `description` longtext,
    `category` varchar(50) NOT NULL,
    `amount` decimal(15,2) NOT NULL,
    `expense_date` date NOT NULL,
    `payment_method` varchar(50) NOT NULL,
    `paid_to` varchar(200) NOT NULL,
    `reference_number` varchar(100),
    `receipt_path` varchar(100),
    `status` varchar(20) NOT NULL DEFAULT 'pending',
    `notes` longtext,
    `created_at` datetime(6) NOT NULL,
    `updated_at` datetime(6) NOT NULL,
    `approved_at` datetime(6),
    `rejection_reason` longtext,
    `branch_id` varchar(32) NOT NULL,
    `loan_id` varchar(32),
    `staff_id` varchar(32) NOT NULL,
    `approved_by_id` varchar(32),
    PRIMARY KEY (`id`),
    KEY `expenses_branch__f377ac_idx` (`branch_id`, `expense_date`),
    KEY `expenses_status_0e583e_idx` (`status`, `expense_date`),
    KEY `expenses_categor_a8c11b_idx` (`category`, `expense_date`),
    KEY `expenses_staff_i_a58db8_idx` (`staff_id`, `expense_date`),
    KEY `expenses_expense_loan_id` (`loan_id`),
    KEY `expenses_expense_staff_id` (`staff_id`),
    KEY `expenses_expense_approved_by_id` (`approved_by_id`),
    KEY `expenses_expense_branch_id` (`branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add migration record so Django knows the table exists
INSERT INTO django_migrations (app, name, applied) 
VALUES ('expenses', '0001_initial', NOW())
ON DUPLICATE KEY UPDATE applied = NOW();

-- Verification queries
SELECT 'SUCCESS: Table created!' AS status;
SELECT COUNT(*) as total_columns FROM information_schema.columns 
WHERE table_name = 'expenses_expense' AND table_schema = DATABASE();
SELECT 'Next step: Restart your Python app in cPanel' AS next_step;

-- ============================================================================
-- DONE! Now restart your app and visit /expenses/
-- ============================================================================