CREATE TABLE invoices (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    lease_id UUID NOT NULL REFERENCES leases(id) ON DELETE CASCADE,
    property_id UUID NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
    unit_id UUID NOT NULL REFERENCES units(id) ON DELETE CASCADE,
    tenant_id UUID NOT NULL REFERENCES tenants_table(id) ON DELETE CASCADE,
    invoice_number VARCHAR(50) UNIQUE NOT NULL,
    period VARCHAR(7) NOT NULL, -- YYYY-MM format
    invoice_type VARCHAR(50) DEFAULT 'rent' CHECK (invoice_type IN (
        'rent', 'utility', 'late_fee', 'damage', 'deposit', 'other'
    )),
    invoice_date DATE DEFAULT CURRENT_DATE,
    due_date DATE NOT NULL,
    paid_date TIMESTAMP WITH TIME ZONE,
    subtotal DECIMAL(10, 2) NOT NULL,
    tax_rate DECIMAL(5, 2) DEFAULT 0,
    tax_amount DECIMAL(10, 2) DEFAULT 0,
    discount_amount DECIMAL(10, 2) DEFAULT 0,
    total_amount DECIMAL(10, 2) NOT NULL,
    paid_amount DECIMAL(10, 2) DEFAULT 0,
    balance_due DECIMAL(10, 2) GENERATED ALWAYS AS (total_amount - COALESCE(paid_amount, 0)) STORED,
    items JSONB DEFAULT '[]',
    status VARCHAR(50) DEFAULT 'pending' CHECK (status IN (
        'draft', 'pending', 'sent', 'partially_paid', 'paid', 
        'overdue', 'cancelled', 'void', 'disputed'
    )),
    payment_terms VARCHAR(255),
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE,
    metadata JSONB DEFAULT '{}',
    created_by UUID REFERENCES users(id),
    updated_by UUID REFERENCES users(id)
);

CREATE TABLE payments (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    invoice_id UUID REFERENCES invoices(id) ON DELETE SET NULL,
    lease_id UUID REFERENCES leases(id) ON DELETE CASCADE,
    property_id UUID REFERENCES properties(id) ON DELETE CASCADE,
    unit_id UUID REFERENCES units(id) ON DELETE CASCADE,
    tenant_id UUID REFERENCES tenants_table(id) ON DELETE CASCADE,
    payment_reference VARCHAR(100) UNIQUE NOT NULL,
    gateway_reference VARCHAR(255), 
    transaction_id VARCHAR(255), 
    amount DECIMAL(10, 2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'ZMW', 
    payment_method VARCHAR(50) NOT NULL CHECK (payment_method IN (
        'cash', 'bank_transfer', 'card', 'mobile_money', 'cheque', 'other'
    )),
    gateway VARCHAR(50), 
    provider VARCHAR(50), 
    phone_number VARCHAR(20), 
    payment_details JSONB DEFAULT '{}',
    gateway_response JSONB DEFAULT '{}',
    description TEXT,
    status VARCHAR(50) DEFAULT 'pending' CHECK (status IN (
        'pending', 'processing', 'completed', 'failed', 
        'refunded', 'partially_refunded', 'cancelled', 'disputed'
    )),
    retry_count INTEGER DEFAULT 0,
    max_retries INTEGER DEFAULT 3,
    last_retry_at TIMESTAMP WITH TIME ZONE,
    retry_reference VARCHAR(100),
    payment_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    completed_at TIMESTAMP WITH TIME ZONE,
    failed_at TIMESTAMP WITH TIME ZONE,
    error_message TEXT,
    error_code VARCHAR(50),
    verified BOOLEAN DEFAULT FALSE,
    verified_by UUID REFERENCES users(id),
    verified_at TIMESTAMP WITH TIME ZONE,
    receipt_sent BOOLEAN DEFAULT FALSE,
    receipt_sent_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE,
    metadata JSONB DEFAULT '{}',
    created_by UUID REFERENCES users(id),
    CONSTRAINT valid_mobile_money CHECK (
        (payment_method = 'mobile_money' AND provider IS NOT NULL AND phone_number IS NOT NULL) OR
        (payment_method != 'mobile_money')
    ),
    CONSTRAINT valid_card_payment CHECK (
        (payment_method = 'card' AND gateway IS NOT NULL) OR
        (payment_method != 'card')
    )
);

CREATE TABLE payment_webhooks (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    payment_id UUID REFERENCES payments(id) ON DELETE CASCADE,
    gateway VARCHAR(50) NOT NULL,
    event_type VARCHAR(100) NOT NULL,
    event_data JSONB NOT NULL DEFAULT '{}',
    signature VARCHAR(500),
    verified BOOLEAN DEFAULT FALSE,
    processed BOOLEAN DEFAULT FALSE,
    processed_at TIMESTAMP WITH TIME ZONE,
    error_message TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    metadata JSONB DEFAULT '{}'
);

CREATE TABLE mobile_money_transactions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    payment_id UUID REFERENCES payments(id) ON DELETE CASCADE,
    provider VARCHAR(50) NOT NULL CHECK (provider IN ('airtel', 'mtn', 'zamtel')),
    phone_number VARCHAR(20) NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'ZMW',
    provider_reference VARCHAR(255),
    provider_status VARCHAR(50),
    provider_message TEXT,
    request_data JSONB DEFAULT '{}',
    response_data JSONB DEFAULT '{}',
    callback_data JSONB DEFAULT '{}',
    status VARCHAR(50) DEFAULT 'pending' CHECK (status IN (
        'pending', 'processing', 'completed', 'failed', 'expired', 'cancelled'
    )),
    initiated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    completed_at TIMESTAMP WITH TIME ZONE,
    expiry_at TIMESTAMP WITH TIME ZONE,
    retry_count INTEGER DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    metadata JSONB DEFAULT '{}'
);

CREATE TABLE bank_accounts (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    bank_name VARCHAR(100) NOT NULL,
    account_number VARCHAR(50) NOT NULL,
    account_name VARCHAR(100) NOT NULL,
    branch VARCHAR(100),
    swift_code VARCHAR(20),
    currency VARCHAR(3) DEFAULT 'ZMW',
    is_active BOOLEAN DEFAULT TRUE,
    is_default BOOLEAN DEFAULT FALSE,
    instructions TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE,
    metadata JSONB DEFAULT '{}'
);

CREATE TABLE payment_gateway_configs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    gateway VARCHAR(50) NOT NULL CHECK (gateway IN ('flutterwave', 'paystack', 'airtel', 'mtn', 'zamtel')),
    is_active BOOLEAN DEFAULT TRUE,
    config JSONB NOT NULL DEFAULT '{}',
    webhook_url VARCHAR(255),
    webhook_secret VARCHAR(255),
    test_mode BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE,
    metadata JSONB DEFAULT '{}',
    UNIQUE(organization_tenant_id, gateway)
);

CREATE TABLE receipts (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    payment_id UUID NOT NULL REFERENCES payments(id) ON DELETE CASCADE,
    invoice_id UUID REFERENCES invoices(id) ON DELETE SET NULL,
    tenant_id UUID NOT NULL REFERENCES tenants_table(id) ON DELETE CASCADE,
    receipt_number VARCHAR(50) UNIQUE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'ZMW',
    payment_method VARCHAR(50) NOT NULL,
    gateway VARCHAR(50),
    provider VARCHAR(50),
    details JSONB DEFAULT '{}',
    sent_via VARCHAR(50) CHECK (sent_via IN ('email', 'sms', 'whatsapp', 'portal', 'manual')),
    sent_at TIMESTAMP WITH TIME ZONE,
    issued_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    metadata JSONB DEFAULT '{}',
    created_by UUID REFERENCES users(id)
);

CREATE TABLE payment_schedules (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    lease_id UUID NOT NULL REFERENCES leases(id) ON DELETE CASCADE,
    tenant_id UUID NOT NULL REFERENCES tenants_table(id) ON DELETE CASCADE,
    amount DECIMAL(10, 2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'ZMW',
    frequency VARCHAR(50) NOT NULL CHECK (frequency IN (
        'monthly', 'quarterly', 'annually', 'one_time'
    )),
    due_day INTEGER NOT NULL,
    preferred_payment_method VARCHAR(50) CHECK (preferred_payment_method IN (
        'mobile_money', 'card', 'bank_transfer', 'cash'
    )),
    preferred_provider VARCHAR(50),
    auto_debit_enabled BOOLEAN DEFAULT FALSE,
    auto_debit_method VARCHAR(50),
    auto_debit_reference VARCHAR(255),
    status VARCHAR(50) DEFAULT 'active' CHECK (status IN (
        'active', 'paused', 'completed', 'cancelled'
    )),
    start_date DATE NOT NULL,
    end_date DATE,
    next_due_date DATE,
    last_generated_date DATE,
    reminder_days_before INTEGER DEFAULT 3,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    metadata JSONB DEFAULT '{}',
    created_by UUID REFERENCES users(id)
);

CREATE TABLE late_fees (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    invoice_id UUID NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,
    lease_id UUID NOT NULL REFERENCES leases(id) ON DELETE CASCADE,
    tenant_id UUID NOT NULL REFERENCES tenants_table(id) ON DELETE CASCADE,
    fee_type VARCHAR(50) CHECK (fee_type IN ('percentage', 'fixed', 'daily')),
    fee_amount DECIMAL(10, 2) NOT NULL,
    fee_percentage DECIMAL(5, 2),
    days_overdue INTEGER NOT NULL,
    calculated_on DATE NOT NULL,
    status VARCHAR(50) DEFAULT 'pending' CHECK (status IN (
        'pending', 'applied', 'waived', 'paid'
    )),
    waived_by UUID REFERENCES users(id),
    waived_at TIMESTAMP WITH TIME ZONE,
    waiver_reason TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    metadata JSONB DEFAULT '{}'
);

CREATE TABLE payment_disputes (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    payment_id UUID REFERENCES payments(id) ON DELETE SET NULL,
    invoice_id UUID REFERENCES invoices(id) ON DELETE SET NULL,
    tenant_id UUID NOT NULL REFERENCES tenants_table(id) ON DELETE CASCADE,
    dispute_type VARCHAR(50) CHECK (dispute_type IN (
        'incorrect_amount', 'duplicate_payment', 'service_not_rendered',
        'unauthorized', 'other'
    )),
    reason TEXT NOT NULL,
    evidence JSONB DEFAULT '[]',
    status VARCHAR(50) DEFAULT 'open' CHECK (status IN (
        'open', 'under_review', 'resolved', 'rejected', 'escalated'
    )),
    resolution TEXT,
    resolved_by UUID REFERENCES users(id),
    resolved_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    metadata JSONB DEFAULT '{}',
    created_by UUID REFERENCES users(id)
);

CREATE TABLE reconciliations (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    total_payments INTEGER DEFAULT 0,
    total_invoices INTEGER DEFAULT 0,
    total_amount DECIMAL(10, 2) DEFAULT 0,
    payment_details JSONB DEFAULT '{}',
    discrepancies JSONB DEFAULT '[]',
    status VARCHAR(50) DEFAULT 'pending' CHECK (status IN (
        'pending', 'in_progress', 'balanced', 'discrepancies_found', 'archived'
    )),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    completed_at TIMESTAMP WITH TIME ZONE,
    metadata JSONB DEFAULT '{}',
    created_by UUID REFERENCES users(id)
);

CREATE TABLE utility_readings (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    unit_id UUID NOT NULL REFERENCES units(id) ON DELETE CASCADE,
    property_id UUID NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
    utility_type VARCHAR(50) NOT NULL CHECK (utility_type IN (
        'water', 'electricity', 'gas', 'trash', 'internet', 'other'
    )),
    reading_value DECIMAL(10, 2) NOT NULL,
    unit_of_measure VARCHAR(50),
    rate_per_unit DECIMAL(10, 4),
    estimated_cost DECIMAL(10, 2),
    reading_date DATE NOT NULL,
    meter_number VARCHAR(100),
    reading_method VARCHAR(50) DEFAULT 'manual' CHECK (reading_method IN ('manual', 'estimated', 'smart_meter')),
    is_billed BOOLEAN DEFAULT FALSE,
    billed_date DATE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    metadata JSONB DEFAULT '{}',
    created_by UUID REFERENCES users(id)
);

CREATE INDEX idx_invoices_org ON invoices(organization_tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_invoices_lease ON invoices(lease_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_invoices_tenant ON invoices(tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_invoices_number ON invoices(invoice_number);
CREATE INDEX idx_invoices_status ON invoices(status) WHERE deleted_at IS NULL;
CREATE INDEX idx_invoices_due_date ON invoices(due_date) WHERE status != 'paid';
CREATE INDEX idx_invoices_period ON invoices(period);
CREATE INDEX idx_payments_org ON payments(organization_tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_payments_invoice ON payments(invoice_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_payments_lease ON payments(lease_id);
CREATE INDEX idx_payments_tenant ON payments(tenant_id);
CREATE INDEX idx_payments_reference ON payments(payment_reference);
CREATE INDEX idx_payments_gateway_reference ON payments(gateway_reference);
CREATE INDEX idx_payments_status ON payments(status);
CREATE INDEX idx_payments_date ON payments(payment_date);
CREATE INDEX idx_payments_method ON payments(payment_method);
CREATE INDEX idx_payments_gateway ON payments(gateway);
CREATE INDEX idx_payments_provider ON payments(provider);
CREATE INDEX idx_payments_phone ON payments(phone_number);
CREATE INDEX idx_payments_currency ON payments(currency);
CREATE INDEX idx_payment_webhooks_org ON payment_webhooks(organization_tenant_id);
CREATE INDEX idx_payment_webhooks_payment ON payment_webhooks(payment_id);
CREATE INDEX idx_payment_webhooks_gateway ON payment_webhooks(gateway);
CREATE INDEX idx_payment_webhooks_processed ON payment_webhooks(processed, created_at);
CREATE INDEX idx_mm_transactions_org ON mobile_money_transactions(organization_tenant_id);
CREATE INDEX idx_mm_transactions_payment ON mobile_money_transactions(payment_id);
CREATE INDEX idx_mm_transactions_provider ON mobile_money_transactions(provider);
CREATE INDEX idx_mm_transactions_phone ON mobile_money_transactions(phone_number);
CREATE INDEX idx_mm_transactions_status ON mobile_money_transactions(status);
CREATE INDEX idx_mm_transactions_reference ON mobile_money_transactions(provider_reference);
CREATE INDEX idx_bank_accounts_org ON bank_accounts(organization_tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_bank_accounts_active ON bank_accounts(is_active) WHERE deleted_at IS NULL;
CREATE INDEX idx_payment_gateway_configs_org ON payment_gateway_configs(organization_tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_payment_gateway_configs_gateway ON payment_gateway_configs(gateway) WHERE deleted_at IS NULL;
CREATE INDEX idx_payment_gateway_configs_active ON payment_gateway_configs(is_active) WHERE deleted_at IS NULL;
CREATE INDEX idx_receipts_org ON receipts(organization_tenant_id);
CREATE INDEX idx_receipts_payment ON receipts(payment_id);
CREATE INDEX idx_receipts_tenant ON receipts(tenant_id);
CREATE INDEX idx_receipts_number ON receipts(receipt_number);
CREATE INDEX idx_receipts_date ON receipts(issued_date);
CREATE INDEX idx_payment_schedules_org ON payment_schedules(organization_tenant_id);
CREATE INDEX idx_payment_schedules_lease ON payment_schedules(lease_id);
CREATE INDEX idx_payment_schedules_tenant ON payment_schedules(tenant_id);
CREATE INDEX idx_payment_schedules_next_due ON payment_schedules(next_due_date);
CREATE INDEX idx_payment_schedules_method ON payment_schedules(preferred_payment_method);
CREATE INDEX idx_late_fees_org ON late_fees(organization_tenant_id);
CREATE INDEX idx_late_fees_invoice ON late_fees(invoice_id);
CREATE INDEX idx_late_fees_tenant ON late_fees(tenant_id);
CREATE INDEX idx_payment_disputes_org ON payment_disputes(organization_tenant_id);
CREATE INDEX idx_payment_disputes_tenant ON payment_disputes(tenant_id);
CREATE INDEX idx_payment_disputes_status ON payment_disputes(status);
CREATE INDEX idx_reconciliations_org ON reconciliations(organization_tenant_id);
CREATE INDEX idx_reconciliations_dates ON reconciliations(start_date, end_date);
CREATE INDEX idx_utility_readings_org ON utility_readings(organization_tenant_id);
CREATE INDEX idx_utility_readings_unit ON utility_readings(unit_id);
CREATE INDEX idx_utility_readings_date ON utility_readings(reading_date);
CREATE INDEX idx_utility_readings_type ON utility_readings(utility_type);
CREATE TRIGGER update_invoices_timestamp BEFORE UPDATE ON invoices
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_payments_timestamp BEFORE UPDATE ON payments
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_payment_webhooks_timestamp BEFORE UPDATE ON payment_webhooks
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_mm_transactions_timestamp BEFORE UPDATE ON mobile_money_transactions
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_bank_accounts_timestamp BEFORE UPDATE ON bank_accounts
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_payment_gateway_configs_timestamp BEFORE UPDATE ON payment_gateway_configs
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_receipts_timestamp BEFORE UPDATE ON receipts
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_payment_schedules_timestamp BEFORE UPDATE ON payment_schedules
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_late_fees_timestamp BEFORE UPDATE ON late_fees
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_payment_disputes_timestamp BEFORE UPDATE ON payment_disputes
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_utility_readings_timestamp BEFORE UPDATE ON utility_readings
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();
CREATE OR REPLACE FUNCTION update_invoice_balance()
RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'INSERT' AND NEW.status = 'completed') OR 
       (TG_OP = 'UPDATE' AND NEW.status = 'completed' AND OLD.status != 'completed') THEN
        
        UPDATE invoices 
        SET 
            paid_amount = (
                SELECT COALESCE(SUM(amount), 0) 
                FROM payments 
                WHERE invoice_id = NEW.invoice_id 
                AND status = 'completed' 
                AND deleted_at IS NULL
            ),
            status = CASE 
                WHEN (
                    SELECT COALESCE(SUM(amount), 0) 
                    FROM payments 
                    WHERE invoice_id = NEW.invoice_id 
                    AND status = 'completed' 
                    AND deleted_at IS NULL
                ) >= total_amount THEN 'paid'
                WHEN (
                    SELECT COALESCE(SUM(amount), 0) 
                    FROM payments 
                    WHERE invoice_id = NEW.invoice_id 
                    AND status = 'completed' 
                    AND deleted_at IS NULL
                ) > 0 THEN 'partially_paid'
                WHEN due_date < CURRENT_DATE THEN 'overdue'
                ELSE 'pending'
            END,
            paid_date = CASE 
                WHEN (
                    SELECT COALESCE(SUM(amount), 0) 
                    FROM payments 
                    WHERE invoice_id = NEW.invoice_id 
                    AND status = 'completed' 
                    AND deleted_at IS NULL
                ) >= total_amount THEN NOW()
                ELSE paid_date
            END,
            updated_at = NOW()
        WHERE id = NEW.invoice_id;
        
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_invoice_on_payment
    AFTER INSERT OR UPDATE OF status ON payments
    FOR EACH ROW
    EXECUTE FUNCTION update_invoice_balance();
CREATE OR REPLACE FUNCTION set_payment_completed_at()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.status = 'completed' AND OLD.status != 'completed' THEN
        NEW.completed_at = NOW();
    END IF;
    
    IF NEW.status = 'failed' AND OLD.status != 'failed' THEN
        NEW.failed_at = NOW();
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_payment_timestamps
    BEFORE UPDATE OF status ON payments
    FOR EACH ROW
    EXECUTE FUNCTION set_payment_completed_at();
CREATE OR REPLACE FUNCTION expire_mm_transactions()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE mobile_money_transactions
    SET status = 'expired',
        updated_at = NOW()
    WHERE status = 'pending'
    AND initiated_at < NOW() - INTERVAL '15 minutes';
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER expire_pending_mm_transactions
    AFTER INSERT ON mobile_money_transactions
    FOR EACH STATEMENT
    EXECUTE FUNCTION expire_mm_transactions();
CREATE OR REPLACE FUNCTION create_default_bank_account()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO bank_accounts (
        organization_tenant_id,
        bank_name,
        account_number,
        account_name,
        branch,
        swift_code,
        currency,
        is_active,
        is_default
    ) VALUES (
        NEW.id,
        'Zambia National Commercial Bank (ZANACO)',
        '0000000000', 
        'BlackArc Property Management',
        'Main Branch',
        'ZANAZMLU',
        'ZMW',
        TRUE,
        TRUE
    );
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;