-- Enable required PostgreSQL extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;

-- Create ENUM types for PostgreSQL
DO $$ BEGIN
    CREATE TYPE user_role AS ENUM ('admin', 'loan_officer', 'borrower');
    EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
    CREATE TYPE user_status AS ENUM ('active', 'inactive', 'blacklisted');
    EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
    CREATE TYPE loan_status AS ENUM ('pending', 'active', 'paid', 'defaulted', 'written_off', 'rolled_over');
    EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
    CREATE TYPE application_status AS ENUM ('pending', 'under_review', 'approved', 'rejected');
    EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
    CREATE TYPE payment_method AS ENUM ('mpesa', 'bank_transfer', 'cash', 'cheque');
    EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
    CREATE TYPE document_type AS ENUM (
        'id_document',
        'utility_bill',
        'bank_statement',
        'business_license',
        'tax_certificate',
        'logbook',
        'title_deed',
        'selfie',
        'signature'
    );
    EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

-- Let Django handle table creation through migrations
-- We'll create the indexes after migrations are run

-- Function to create indexes if they don't exist
CREATE OR REPLACE FUNCTION create_indexes_if_not_exist() RETURNS void AS $$
BEGIN
    -- Users indexes
    IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE indexname = 'idx_user_email') THEN
        CREATE INDEX idx_user_email ON users_customuser(email);
    END IF;

    IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE indexname = 'idx_user_phone') THEN
        CREATE INDEX idx_user_phone ON users_customuser(phone_number);
    END IF;

    -- Loan indexes
    IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE indexname = 'idx_loan_status') THEN
        CREATE INDEX idx_loan_status ON loans_loan(status);
    END IF;

    IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE indexname = 'idx_loan_created') THEN
        CREATE INDEX idx_loan_created ON loans_loan(created_at);
    END IF;

    -- Application indexes
    IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE indexname = 'idx_application_status') THEN
        CREATE INDEX idx_application_status ON loans_loanapplication(status);
    END IF;

    -- Repayment indexes
    IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE indexname = 'idx_repayment_date') THEN
        CREATE INDEX idx_repayment_date ON loans_repayment(payment_date);
    END IF;

    -- Text search indexes
    IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE indexname = 'idx_user_name_trgm') THEN
        CREATE INDEX idx_user_name_trgm ON users_customuser USING gin(first_name gin_trgm_ops, last_name gin_trgm_ops);
    END IF;

    IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE indexname = 'idx_business_name_trgm') THEN
        CREATE INDEX idx_business_name_trgm ON users_customuser USING gin(business_name gin_trgm_ops);
    END IF;
END;
$$ LANGUAGE plpgsql;