-- Emergency fix for receipts table missing columns
-- Run this SQL script directly on your MySQL database

-- Check if receipts table exists, if not create it
CREATE TABLE IF NOT EXISTS receipts (
    id CHAR(32) NOT NULL PRIMARY KEY,
    repayment_id CHAR(32) NULL,
    loan_id CHAR(32) NOT NULL,
    borrower_id CHAR(32) NOT NULL,
    receipt_number VARCHAR(20) NOT NULL UNIQUE,
    amount_paid DECIMAL(12,2) NOT NULL,
    payment_method VARCHAR(20) NOT NULL,
    payment_date DATETIME(6) NOT NULL,
    previous_balance DECIMAL(12,2) NOT NULL,
    new_balance DECIMAL(12,2) NOT NULL,
    pdf_file VARCHAR(100) NULL,
    created_at DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6)
);

-- Add missing columns if they don't exist (MySQL will ignore if they exist)
ALTER TABLE receipts ADD COLUMN IF NOT EXISTS repayment_id CHAR(32) NULL;
ALTER TABLE receipts ADD COLUMN IF NOT EXISTS loan_id CHAR(32) NOT NULL;
ALTER TABLE receipts ADD COLUMN IF NOT EXISTS borrower_id CHAR(32) NOT NULL;
ALTER TABLE receipts ADD COLUMN IF NOT EXISTS amount_paid DECIMAL(12,2) NOT NULL;
ALTER TABLE receipts ADD COLUMN IF NOT EXISTS payment_method VARCHAR(20) NOT NULL;
ALTER TABLE receipts ADD COLUMN IF NOT EXISTS payment_date DATETIME(6) NOT NULL;
ALTER TABLE receipts ADD COLUMN IF NOT EXISTS previous_balance DECIMAL(12,2) NOT NULL;
ALTER TABLE receipts ADD COLUMN IF NOT EXISTS new_balance DECIMAL(12,2) NOT NULL;
ALTER TABLE receipts ADD COLUMN IF NOT EXISTS pdf_file VARCHAR(100) NULL;
ALTER TABLE receipts ADD COLUMN IF NOT EXISTS created_at DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_receipts_repayment_id ON receipts (repayment_id);
CREATE INDEX IF NOT EXISTS idx_receipts_loan_id ON receipts (loan_id);
CREATE INDEX IF NOT EXISTS idx_receipts_borrower_id ON receipts (borrower_id);
CREATE INDEX IF NOT EXISTS idx_receipts_receipt_number ON receipts (receipt_number);
CREATE INDEX IF NOT EXISTS idx_receipts_payment_date ON receipts (payment_date);

-- Ensure repayments table exists
CREATE TABLE IF NOT EXISTS repayments (
    id CHAR(32) NOT NULL PRIMARY KEY,
    loan_id CHAR(32) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    payment_method VARCHAR(20) NOT NULL DEFAULT 'mpesa',
    mpesa_transaction_id VARCHAR(50) NULL,
    mpesa_phone_number VARCHAR(17) NULL,
    receipt_number VARCHAR(20) NOT NULL UNIQUE,
    payment_date DATETIME(6) NOT NULL,
    created_at DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6)
);

-- Create indexes for repayments table
CREATE INDEX IF NOT EXISTS idx_repayments_loan_id ON repayments (loan_id);
CREATE INDEX IF NOT EXISTS idx_repayments_receipt_number ON repayments (receipt_number);
CREATE INDEX IF NOT EXISTS idx_repayments_payment_date ON repayments (payment_date);

-- Show completion message
SELECT 'Receipts table fix completed successfully!' as Status;