-- Create the complete database schema for Fanaka University Portal
-- This script creates all necessary tables with proper relationships and RLS policies

-- Enable necessary extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Create enum types
CREATE TYPE student_type AS ENUM ('local', 'foreign');
CREATE TYPE voucher_status AS ENUM ('active', 'used', 'expired');
CREATE TYPE payment_status AS ENUM ('pending', 'completed', 'failed', 'refunded');
CREATE TYPE application_status AS ENUM ('draft', 'submitted', 'under_review', 'accepted', 'rejected');

-- Create vouchers table
CREATE TABLE IF NOT EXISTS vouchers (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    serial_number VARCHAR(50) UNIQUE NOT NULL,
    pin VARCHAR(10) NOT NULL,
    student_type student_type NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) NOT NULL DEFAULT 'GHS',
    status voucher_status NOT NULL DEFAULT 'active',
    email VARCHAR(255) NOT NULL,
    purchase_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    expiry_date TIMESTAMP WITH TIME ZONE DEFAULT (NOW() + INTERVAL '1 year'),
    payment_reference VARCHAR(100),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create payments table
CREATE TABLE IF NOT EXISTS payments (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    reference VARCHAR(100) UNIQUE NOT NULL,
    email VARCHAR(255) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) NOT NULL,
    status payment_status NOT NULL DEFAULT 'pending',
    gateway_response JSONB,
    voucher_id UUID REFERENCES vouchers(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create students table (for application data)
CREATE TABLE IF NOT EXISTS students (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    voucher_id UUID REFERENCES vouchers(id) ON DELETE CASCADE,
    email VARCHAR(255) NOT NULL,
    student_type student_type NOT NULL,
    
    -- Personal Information
    first_name VARCHAR(100),
    middle_name VARCHAR(100),
    last_name VARCHAR(100),
    date_of_birth DATE,
    gender VARCHAR(20),
    nationality VARCHAR(100),
    phone VARCHAR(20),
    
    -- Address Information
    address_line1 TEXT,
    address_line2 TEXT,
    city VARCHAR(100),
    state_region VARCHAR(100),
    postal_code VARCHAR(20),
    country VARCHAR(100),
    
    -- Academic Information
    previous_school VARCHAR(200),
    graduation_year INTEGER,
    gpa DECIMAL(3,2),
    intended_major VARCHAR(100),
    
    -- Application Status
    application_status application_status DEFAULT 'draft',
    submitted_at TIMESTAMP WITH TIME ZONE,
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create documents table
CREATE TABLE IF NOT EXISTS documents (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    student_id UUID REFERENCES students(id) ON DELETE CASCADE,
    document_type VARCHAR(100) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_url TEXT NOT NULL,
    file_size INTEGER,
    mime_type VARCHAR(100),
    uploaded_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create admin_users table
CREATE TABLE IF NOT EXISTS admin_users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(200),
    role VARCHAR(50) DEFAULT 'admin',
    is_active BOOLEAN DEFAULT true,
    last_login TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create email_logs table
CREATE TABLE IF NOT EXISTS email_logs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    recipient_email VARCHAR(255) NOT NULL,
    subject VARCHAR(500) NOT NULL,
    email_type VARCHAR(100) NOT NULL,
    status VARCHAR(50) DEFAULT 'sent',
    voucher_id UUID REFERENCES vouchers(id),
    student_id UUID REFERENCES students(id),
    sent_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    error_message TEXT
);

-- Enable Row Level Security on all tables
ALTER TABLE vouchers ENABLE ROW LEVEL SECURITY;
ALTER TABLE payments ENABLE ROW LEVEL SECURITY;
ALTER TABLE students ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE admin_users ENABLE ROW LEVEL SECURITY;
ALTER TABLE email_logs ENABLE ROW LEVEL SECURITY;

-- Create RLS policies for vouchers (public access for purchase, restricted for admin)
CREATE POLICY "Allow public to create vouchers" ON vouchers FOR INSERT WITH CHECK (true);
CREATE POLICY "Allow users to view their own vouchers" ON vouchers FOR SELECT USING (email = current_setting('request.jwt.claims', true)::json->>'email' OR current_setting('app.current_user_email', true) = email);
CREATE POLICY "Allow admin to view all vouchers" ON vouchers FOR ALL USING (
    EXISTS (
        SELECT 1 FROM admin_users 
        WHERE email = current_setting('request.jwt.claims', true)::json->>'email' 
        AND is_active = true
    )
);

-- Create RLS policies for payments
CREATE POLICY "Allow public to create payments" ON payments FOR INSERT WITH CHECK (true);
CREATE POLICY "Allow users to view their own payments" ON payments FOR SELECT USING (email = current_setting('request.jwt.claims', true)::json->>'email' OR current_setting('app.current_user_email', true) = email);
CREATE POLICY "Allow admin to view all payments" ON payments FOR ALL USING (
    EXISTS (
        SELECT 1 FROM admin_users 
        WHERE email = current_setting('request.jwt.claims', true)::json->>'email' 
        AND is_active = true
    )
);

-- Create RLS policies for students
CREATE POLICY "Allow students to manage their own data" ON students FOR ALL USING (
    voucher_id IN (
        SELECT id FROM vouchers 
        WHERE email = current_setting('request.jwt.claims', true)::json->>'email' 
        OR current_setting('app.current_user_email', true) = email
    )
);
CREATE POLICY "Allow admin to view all students" ON students FOR ALL USING (
    EXISTS (
        SELECT 1 FROM admin_users 
        WHERE email = current_setting('request.jwt.claims', true)::json->>'email' 
        AND is_active = true
    )
);

-- Create RLS policies for documents
CREATE POLICY "Allow students to manage their own documents" ON documents FOR ALL USING (
    student_id IN (
        SELECT s.id FROM students s
        JOIN vouchers v ON s.voucher_id = v.id
        WHERE v.email = current_setting('request.jwt.claims', true)::json->>'email'
        OR current_setting('app.current_user_email', true) = v.email
    )
);
CREATE POLICY "Allow admin to view all documents" ON documents FOR ALL USING (
    EXISTS (
        SELECT 1 FROM admin_users 
        WHERE email = current_setting('request.jwt.claims', true)::json->>'email' 
        AND is_active = true
    )
);

-- Create RLS policies for admin_users
CREATE POLICY "Allow admin to manage admin users" ON admin_users FOR ALL USING (
    email = current_setting('request.jwt.claims', true)::json->>'email'
    OR EXISTS (
        SELECT 1 FROM admin_users 
        WHERE email = current_setting('request.jwt.claims', true)::json->>'email' 
        AND is_active = true
    )
);

-- Create RLS policies for email_logs
CREATE POLICY "Allow admin to view email logs" ON email_logs FOR ALL USING (
    EXISTS (
        SELECT 1 FROM admin_users 
        WHERE email = current_setting('request.jwt.claims', true)::json->>'email' 
        AND is_active = true
    )
);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_vouchers_email ON vouchers(email);
CREATE INDEX IF NOT EXISTS idx_vouchers_serial_pin ON vouchers(serial_number, pin);
CREATE INDEX IF NOT EXISTS idx_vouchers_status ON vouchers(status);
CREATE INDEX IF NOT EXISTS idx_payments_reference ON payments(reference);
CREATE INDEX IF NOT EXISTS idx_payments_email ON payments(email);
CREATE INDEX IF NOT EXISTS idx_students_voucher_id ON students(voucher_id);
CREATE INDEX IF NOT EXISTS idx_students_email ON students(email);
CREATE INDEX IF NOT EXISTS idx_documents_student_id ON documents(student_id);
CREATE INDEX IF NOT EXISTS idx_admin_users_email ON admin_users(email);
CREATE INDEX IF NOT EXISTS idx_email_logs_recipient ON email_logs(recipient_email);

-- Create updated_at trigger function
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Create triggers for updated_at
CREATE TRIGGER update_vouchers_updated_at BEFORE UPDATE ON vouchers FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_payments_updated_at BEFORE UPDATE ON payments FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_students_updated_at BEFORE UPDATE ON students FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_admin_users_updated_at BEFORE UPDATE ON admin_users FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Insert default admin user (password: admin123 - should be changed in production)
INSERT INTO admin_users (email, password_hash, full_name, role) 
VALUES (
    'admin@fanaka.edu.gh', 
    '$2b$10$rQZ9QmZ9QmZ9QmZ9QmZ9Qu', -- This should be properly hashed
    'System Administrator',
    'super_admin'
) ON CONFLICT (email) DO NOTHING;