Complete Setup Guide

-- ============================================
-- NEYATECH COMPLETE DATABASE SCHEMA
-- Run this entire script in Supabase SQL Editor
-- ============================================

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Create ENUM types
CREATE TYPE user_type AS ENUM ('owner', 'volunteer', 'admin');
CREATE TYPE intention_status AS ENUM ('pending', 'accepted', 'in_progress', 'completed', 'cancelled');
CREATE TYPE intention_category AS ENUM ('prayer', 'umrah', 'charity', 'holy_sites', 'zamzam', 'quran');
CREATE TYPE proof_type AS ENUM ('photo', 'video', 'document');

-- ============================================
-- USERS TABLE
-- ============================================
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    clerk_user_id VARCHAR(255) UNIQUE,
    email VARCHAR(255) UNIQUE NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    phone VARCHAR(50),
    user_type user_type NOT NULL,
    profile_image VARCHAR(500),
    location VARCHAR(255),
    bio TEXT,
    is_verified BOOLEAN DEFAULT FALSE,
    rating DECIMAL(3,2) DEFAULT 0.00,
    total_reviews INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- ============================================
-- INTENTIONS TABLE
-- ============================================
CREATE TABLE intentions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    owner_id UUID REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    category intention_category NOT NULL,
    location VARCHAR(255) NOT NULL,
    status intention_status DEFAULT 'pending',
    deadline TIMESTAMP,
    cost DECIMAL(10,2) DEFAULT 0.00,
    image_url VARCHAR(500),
    views_count INTEGER DEFAULT 0,
    volunteer_id UUID REFERENCES users(id) ON DELETE SET NULL,
    accepted_at TIMESTAMP,
    completed_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- ============================================
-- PROOF SUBMISSIONS TABLE
-- ============================================
CREATE TABLE proof_submissions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    intention_id UUID REFERENCES intentions(id) ON DELETE CASCADE,
    volunteer_id UUID REFERENCES users(id) ON DELETE CASCADE,
    proof_type proof_type NOT NULL,
    file_url VARCHAR(500) NOT NULL,
    description TEXT,
    is_approved BOOLEAN DEFAULT FALSE,
    submitted_at TIMESTAMP DEFAULT NOW()
);

-- ============================================
-- REVIEWS TABLE
-- ============================================
CREATE TABLE reviews (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    intention_id UUID REFERENCES intentions(id) ON DELETE CASCADE,
    owner_id UUID REFERENCES users(id) ON DELETE CASCADE,
    volunteer_id UUID REFERENCES users(id) ON DELETE CASCADE,
    rating INTEGER CHECK (rating >= 1 AND rating <= 5) NOT NULL,
    comment TEXT,
    is_public BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW()
);

-- ============================================
-- NOTIFICATIONS TABLE
-- ============================================
CREATE TABLE notifications (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    type VARCHAR(50) NOT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    link VARCHAR(500),
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT NOW()
);

-- ============================================
-- CONTACT MESSAGES TABLE
-- ============================================
CREATE TABLE contact_messages (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(50),
    subject VARCHAR(255),
    message TEXT NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT NOW()
);

-- ============================================
-- CREATE INDEXES FOR PERFORMANCE
-- ============================================
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_type ON users(user_type);
CREATE INDEX idx_intentions_owner ON intentions(owner_id);
CREATE INDEX idx_intentions_volunteer ON intentions(volunteer_id);
CREATE INDEX idx_intentions_status ON intentions(status);
CREATE INDEX idx_intentions_category ON intentions(category);
CREATE INDEX idx_intentions_location ON intentions(location);
CREATE INDEX idx_proofs_intention ON proof_submissions(intention_id);
CREATE INDEX idx_proofs_volunteer ON proof_submissions(volunteer_id);
CREATE INDEX idx_reviews_volunteer ON reviews(volunteer_id);
CREATE INDEX idx_reviews_intention ON reviews(intention_id);
CREATE INDEX idx_notifications_user ON notifications(user_id);
CREATE INDEX idx_notifications_read ON notifications(is_read);

-- ============================================
-- ENABLE ROW LEVEL SECURITY
-- ============================================
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE intentions ENABLE ROW LEVEL SECURITY;
ALTER TABLE proof_submissions ENABLE ROW LEVEL SECURITY;
ALTER TABLE reviews ENABLE ROW LEVEL SECURITY;
ALTER TABLE notifications ENABLE ROW LEVEL SECURITY;
ALTER TABLE contact_messages ENABLE ROW LEVEL SECURITY;

-- ============================================
-- RLS POLICIES - USERS
-- ============================================
CREATE POLICY "Anyone can view user profiles" 
ON users FOR SELECT 
USING (true);

CREATE POLICY "Users can insert their own profile" 
ON users FOR INSERT 
WITH CHECK (true);

CREATE POLICY "Users can update own profile" 
ON users FOR UPDATE 
USING (true);

-- ============================================
-- RLS POLICIES - INTENTIONS
-- ============================================
CREATE POLICY "Anyone can view pending intentions" 
ON intentions FOR SELECT 
USING (true);

CREATE POLICY "Users can create intentions" 
ON intentions FOR INSERT 
WITH CHECK (true);

CREATE POLICY "Users can update intentions" 
ON intentions FOR UPDATE 
USING (true);

-- ============================================
-- RLS POLICIES - PROOFS
-- ============================================
CREATE POLICY "Users can view proofs" 
ON proof_submissions FOR SELECT 
USING (true);

CREATE POLICY "Volunteers can upload proofs" 
ON proof_submissions FOR INSERT 
WITH CHECK (true);

-- ============================================
-- RLS POLICIES - REVIEWS
-- ============================================
CREATE POLICY "Anyone can view public reviews" 
ON reviews FOR SELECT 
USING (is_public = true);

CREATE POLICY "Users can create reviews" 
ON reviews FOR INSERT 
WITH CHECK (true);

-- ============================================
-- RLS POLICIES - NOTIFICATIONS
-- ============================================
CREATE POLICY "Users can view notifications" 
ON notifications FOR SELECT 
USING (true);

CREATE POLICY "System can create notifications" 
ON notifications FOR INSERT 
WITH CHECK (true);

CREATE POLICY "Users can update notifications" 
ON notifications FOR UPDATE 
USING (true);

-- ============================================
-- RLS POLICIES - CONTACT MESSAGES
-- ============================================
CREATE POLICY "Anyone can create contact messages" 
ON contact_messages FOR INSERT 
WITH CHECK (true);

CREATE POLICY "Admins can view contact messages" 
ON contact_messages FOR SELECT 
USING (true);

-- ============================================
-- INSERT SAMPLE DATA FOR TESTING
-- ============================================

-- Sample Users
INSERT INTO users (email, full_name, user_type, location, bio, rating, total_reviews) VALUES
('[email protected]', 'Fatima Ahmed', 'volunteer', 'Madinah, Saudi Arabia', 'Certified volunteer helping fulfill intentions in the blessed city of Madinah', 4.9, 38),
('[email protected]', 'Ahmed Mohamed', 'owner', 'Dubai, UAE', 'Seeking righteous people to fulfill my intentions', 0, 0),
('[email protected]', 'Omar Youssef', 'volunteer', 'Makkah, Saudi Arabia', 'Performing Umrah and good deeds for those who cannot', 4.8, 24),
('[email protected]', 'Admin User', 'admin', 'Riyadh, Saudi Arabia', 'Platform administrator', 0, 0);

-- Sample Intentions
INSERT INTO intentions (owner_id, title, description, category, location, status, cost) VALUES
((SELECT id FROM users WHERE email = '[email protected]'), 'Umrah for Deceased Father', 'Please perform a complete Umrah and dedicate the reward to my late father. May Allah have mercy on him.', 'umrah', 'Makkah', 'pending', 0),
((SELECT id FROM users WHERE email = '[email protected]'), 'Prayer at the Prophet''s Grave', 'Visit the Prophet''s grave (peace be upon him) and send greetings on my behalf', 'holy_sites', 'Madinah', 'pending', 0),
((SELECT id FROM users WHERE email = '[email protected]'), 'Distribute Meals to the Poor', 'Feed 20 fasting people during Ramadan in Makkah', 'charity', 'Makkah', 'pending', 200);

-- Sample Reviews
INSERT INTO reviews (intention_id, owner_id, volunteer_id, rating, comment, is_public) VALUES
((SELECT id FROM intentions LIMIT 1), 
 (SELECT id FROM users WHERE email = '[email protected]'), 
 (SELECT id FROM users WHERE email = '[email protected]'), 
 5, 
 'May Allah reward you abundantly for your excellent work. The photos brought peace to my heart.', 
 true);

-- ============================================
-- SUCCESS MESSAGE
-- ============================================
DO $$ 
BEGIN 
    RAISE NOTICE '✅ ========================================';
    RAISE NOTICE '✅ NEYATECH DATABASE SETUP COMPLETE!';
    RAISE NOTICE '✅ ========================================';
    RAISE NOTICE '✅ Tables created: 6';
    RAISE NOTICE '✅ Indexes created: 12';
    RAISE NOTICE '✅ RLS Policies: Enabled';
    RAISE NOTICE '✅ Sample data: Inserted';
    RAISE NOTICE '✅ ========================================';
    RAISE NOTICE '✅ NEXT STEP: Set up Storage Buckets';
    RAISE NOTICE '✅ ========================================';
END $$;