Supabase 数据库示例

// Supabase Basic Operations
// JavaScript/TypeScript - Using Supabase client library

import { createClient } from '@supabase/supabase-js';

// Initialize Supabase client
const supabaseUrl = 'https://your-project.supabase.co';
const supabaseKey = 'your-anon-key';
const supabase = createClient(supabaseUrl, supabaseKey);

class SupabaseManager {
    constructor(url, key) {
        this.client = createClient(url, key);
    }

    // 1. Database Operations (CRUD)

    // Create operation
    async createRecord(tableName, data) {
        try {
            const { data: insertedData, error } = await this.client
                .from(tableName)
                .insert([data])
                .select();

            if (error) {
                console.error('❌ Error creating record:', error.message);
                return { success: false, error };
            }

            console.log('✅ Record created successfully:', insertedData);
            return { success: true, data: insertedData };
        } catch (error) {
            console.error('❌ Unexpected error:', error);
            return { success: false, error };
        }
    }

    // Read operations
    async getRecords(tableName, options = {}) {
        try {
            let query = this.client.from(tableName).select(options.select || '*');

            // Apply filters
            if (options.filters) {
                options.filters.forEach(filter => {
                    query = query.filter(filter.column, filter.operator, filter.value);
                });
            }

            // Apply ordering
            if (options.order) {
                query = query.order(options.order.column, {
                    ascending: options.order.ascending !== false
                });
            }

            // Apply pagination
            if (options.range) {
                query = query.range(options.range.start, options.range.end);
            }

            // Apply limit
            if (options.limit) {
                query = query.limit(options.limit);
            }

            const { data, error, count } = await query;

            if (error) {
                console.error('❌ Error fetching records:', error.message);
                return { success: false, error };
            }

            console.log(`📊 Fetched ${data.length} records`);
            return { success: true, data, count };
        } catch (error) {
            console.error('❌ Unexpected error:', error);
            return { success: false, error };
        }
    }

    // Get single record by ID
    async getRecordById(tableName, id, options = {}) {
        try {
            const { data, error } = await this.client
                .from(tableName)
                .select(options.select || '*')
                .eq('id', id)
                .single();

            if (error) {
                console.error('❌ Error fetching record:', error.message);
                return { success: false, error };
            }

            console.log('📄 Record fetched successfully:', data);
            return { success: true, data };
        } catch (error) {
            console.error('❌ Unexpected error:', error);
            return { success: false, error };
        }
    }

    // Update operation
    async updateRecord(tableName, id, updates) {
        try {
            const { data: updatedData, error } = await this.client
                .from(tableName)
                .update(updates)
                .eq('id', id)
                .select();

            if (error) {
                console.error('❌ Error updating record:', error.message);
                return { success: false, error };
            }

            console.log('✏️ Record updated successfully:', updatedData);
            return { success: true, data: updatedData };
        } catch (error) {
            console.error('❌ Unexpected error:', error);
            return { success: false, error };
        }
    }

    // Delete operation
    async deleteRecord(tableName, id) {
        try {
            const { data, error } = await this.client
                .from(tableName)
                .delete()
                .eq('id', id)
                .select();

            if (error) {
                console.error('❌ Error deleting record:', error.message);
                return { success: false, error };
            }

            console.log('🗑️ Record deleted successfully:', data);
            return { success: true, data };
        } catch (error) {
            console.error('❌ Unexpected error:', error);
            return { success: false, error };
        }
    }

    // 2. Advanced Queries

    // Join operations (using RPC functions)
    async joinTables(joinQuery) {
        try {
            const { data, error } = await this.client.rpc(joinQuery.functionName, {
                ...joinQuery.params
            });

            if (error) {
                console.error('❌ Error in join query:', error.message);
                return { success: false, error };
            }

            console.log('🔗 Join query successful:', data);
            return { success: true, data };
        } catch (error) {
            console.error('❌ Unexpected error:', error);
            return { success: false, error };
        }
    }

    // Full-text search
    async searchRecords(tableName, searchTerm, columns = ['name', 'description']) {
        try {
            const { data, error } = await this.client
                .from(tableName)
                .select('*')
                .or(`${columns.map(col => `${col}.ilike.%${searchTerm}%`).join(',')}`);

            if (error) {
                console.error('❌ Error searching records:', error.message);
                return { success: false, error };
            }

            console.log(`🔍 Found ${data.length} matching records`);
            return { success: true, data };
        } catch (error) {
            console.error('❌ Unexpected error:', error);
            return { success: false, error };
        }
    }

    // Aggregate functions
    async getAggregates(tableName, aggregations) {
        try {
            const selectClause = aggregations.map(agg => `${agg.alias}: ${agg.function}(${agg.column})`).join(', ');

            const { data, error } = await this.client
                .from(tableName)
                .select(selectClause)
                .single();

            if (error) {
                console.error('❌ Error getting aggregates:', error.message);
                return { success: false, error };
            }

            console.log('📊 Aggregates computed:', data);
            return { success: true, data };
        } catch (error) {
            console.error('❌ Unexpected error:', error);
            return { success: false, error };
        }
    }

    // 3. Transaction Operations
    async performTransaction(operations) {
        try {
            const results = [];

            for (const operation of operations) {
                const { type, table, id, data } = operation;

                let result;
                switch (type) {
                    case 'create':
                        result = await this.createRecord(table, data);
                        break;
                    case 'update':
                        result = await this.updateRecord(table, id, data);
                        break;
                    case 'delete':
                        result = await this.deleteRecord(table, id);
                        break;
                    default:
                        throw new Error(`Unknown operation type: ${type}`);
                }

                if (!result.success) {
                    throw new Error(`Transaction failed at operation: ${type}`);
                }

                results.push(result);
            }

            console.log('✅ Transaction completed successfully');
            return { success: true, results };
        } catch (error) {
            console.error('❌ Transaction failed:', error);
            return { success: false, error };
        }
    }
}

// 4. Sample Data Management
class SampleDataManager extends SupabaseManager {
    // Create sample user data
    async createSampleUsers() {
        const users = [
            {
                email: '[email protected]',
                name: 'John Doe',
                role: 'user',
                is_active: true,
                created_at: new Date().toISOString()
            },
            {
                email: '[email protected]',
                name: 'Jane Smith',
                role: 'admin',
                is_active: true,
                created_at: new Date().toISOString()
            },
            {
                email: '[email protected]',
                name: 'Bob Johnson',
                role: 'user',
                is_active: false,
                created_at: new Date().toISOString()
            }
        ];

        for (const user of users) {
            await this.createRecord('users', user);
        }

        console.log('👥 Sample users created');
    }

    // Create sample product data
    async createSampleProducts() {
        const products = [
            {
                name: 'Laptop Pro',
                description: 'High-performance laptop for professionals',
                price: 1299.99,
                category: 'Electronics',
                stock: 45,
                is_available: true,
                created_at: new Date().toISOString()
            },
            {
                name: 'Wireless Mouse',
                description: 'Ergonomic wireless mouse',
                price: 29.99,
                category: 'Electronics',
                stock: 120,
                is_available: true,
                created_at: new Date().toISOString()
            },
            {
                name: 'Desk Chair',
                description: 'Comfortable office chair',
                price: 199.99,
                category: 'Furniture',
                stock: 15,
                is_available: true,
                created_at: new Date().toISOString()
            }
        ];

        for (const product of products) {
            await this.createRecord('products', product);
        }

        console.log('🛍️ Sample products created');
    }

    // Create sample orders
    async createSampleOrders() {
        const orders = [
            {
                user_id: 1, // Assuming user with ID 1 exists
                total_amount: 1329.98,
                status: 'pending',
                shipping_address: '123 Main St, City, State 12345',
                created_at: new Date().toISOString()
            },
            {
                user_id: 2, // Assuming user with ID 2 exists
                total_amount: 199.99,
                status: 'shipped',
                shipping_address: '456 Oak Ave, Town, State 67890',
                created_at: new Date().toISOString()
            }
        ];

        for (const order of orders) {
            await this.createRecord('orders', order);
        }

        console.log('📦 Sample orders created');
    }
}

// 5. Database Schema Examples
const databaseSchema = {
    users: {
        columns: {
            id: 'serial primary key',
            email: 'text unique not null',
            name: 'text not null',
            role: 'text default \'user\'',
            is_active: 'boolean default true',
            created_at: 'timestamp with time zone default now()',
            updated_at: 'timestamp with time zone default now()'
        },
        indexes: [
            'create index idx_users_email on users(email)',
            'create index idx_users_role on users(role)',
            'create index idx_users_active on users(is_active)'
        ]
    },
    products: {
        columns: {
            id: 'serial primary key',
            name: 'text not null',
            description: 'text',
            price: 'decimal(10,2) not null',
            category: 'text',
            stock: 'integer default 0',
            is_available: 'boolean default true',
            created_at: 'timestamp with time zone default now()',
            updated_at: 'timestamp with time zone default now()'
        },
        indexes: [
            'create index idx_products_category on products(category)',
            'create index idx_products_available on products(is_available)',
            'create index idx_products_price on products(price)'
        ]
    },
    orders: {
        columns: {
            id: 'serial primary key',
            user_id: 'integer references users(id)',
            total_amount: 'decimal(10,2) not null',
            status: 'text default \'pending\'',
            shipping_address: 'text',
            created_at: 'timestamp with time zone default now()',
            updated_at: 'timestamp with time zone default now()'
        },
        indexes: [
            'create index idx_orders_user on orders(user_id)',
            'create index idx_orders_status on orders(status)',
            'create index idx_orders_created on orders(created_at)'
        ]
    },
    order_items: {
        columns: {
            id: 'serial primary key',
            order_id: 'integer references orders(id)',
            product_id: 'integer references products(id)',
            quantity: 'integer not null',
            price_per_item: 'decimal(10,2) not null',
            created_at: 'timestamp with time zone default now()'
        },
        indexes: [
            'create index idx_order_items_order on order_items(order_id)',
            'create index idx_order_items_product on order_items(product_id)'
        ]
    }
};

// 6. Usage Examples
async function demonstrateBasicOperations() {
    const db = new SupabaseManager(supabaseUrl, supabaseKey);

    console.log('🚀 Starting Supabase Basic Operations Demo');

    try {
        // Create sample data
        console.log('\n📝 Creating sample data...');
        await db.createRecord('users', {
            email: '[email protected]',
            name: 'Demo User',
            role: 'user',
            is_active: true
        });

        // Read operations
        console.log('\n🔍 Reading data...');
        const users = await db.getRecords('users', {
            filters: [{ column: 'is_active', operator: 'eq', value: true }],
            order: { column: 'created_at', ascending: false },
            limit: 10
        });

        if (users.success) {
            console.log(`Found ${users.data.length} active users`);
        }

        // Search operations
        console.log('\n🔎 Searching data...');
        const searchResults = await db.searchRecords('users', 'demo', ['name', 'email']);

        if (searchResults.success) {
            console.log(`Search returned ${searchResults.data.length} results`);
        }

        // Aggregate operations
        console.log('\n📊 Getting aggregates...');
        const aggregates = await db.getAggregates('users', [
            { function: 'count', column: '*', alias: 'total_users' },
            { function: 'count', column: 'is_active', alias: 'active_users' }
        ]);

        if (aggregates.success) {
            console.log('User aggregates:', aggregates.data);
        }

    } catch (error) {
        console.error('❌ Demo failed:', error);
    }
}

// 7. Data Validation Examples
class SupabaseValidator extends SupabaseManager {
    // Validate email format
    validateEmail(email) {
        const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
        return emailRegex.test(email);
    }

    // Validate user data before insert
    async validateUser(userData) {
        const errors = [];

        // Email validation
        if (!userData.email) {
            errors.push('Email is required');
        } else if (!this.validateEmail(userData.email)) {
            errors.push('Invalid email format');
        }

        // Name validation
        if (!userData.name || userData.name.trim().length < 2) {
            errors.push('Name must be at least 2 characters long');
        }

        // Role validation
        const validRoles = ['user', 'admin', 'moderator'];
        if (userData.role && !validRoles.includes(userData.role)) {
            errors.push('Invalid role');
        }

        // Check for duplicate email
        if (userData.email) {
            const { data: existingUser } = await this.client
                .from('users')
                .select('email')
                .eq('email', userData.email)
                .single();

            if (existingUser) {
                errors.push('Email already exists');
            }
        }

        return errors;
    }

    // Create user with validation
    async createUserWithValidation(userData) {
        const validationErrors = await this.validateUser(userData);

        if (validationErrors.length > 0) {
            console.error('❌ Validation errors:', validationErrors);
            return { success: false, errors: validationErrors };
        }

        return await this.createRecord('users', userData);
    }
}

// Run the demonstration
if (typeof window === 'undefined') {
    // Node.js environment
    demonstrateBasicOperations().catch(console.error);
}

export { SupabaseManager, SampleDataManager, SupabaseValidator };

// Supabase Realtime and Storage
// TypeScript - Real-time subscriptions, file storage, and edge functions

import { createClient, RealtimeChannel, RealtimePostgresChangesPayload } from '@supabase/supabase-js';
import { useEffect, useState, useCallback } from 'react';
import { RealtimeSubscription } from '@supabase/realtime-js';

// Initialize Supabase client with realtime enabled
const supabase = createClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
        realtime: {
            params: {
                eventsPerSecond: 10
            }
        }
    }
);

// 1. Real-time Subscription Service
class RealtimeService {
    private supabase: ReturnType<typeof createClient>;
    private subscriptions: Map<string, RealtimeChannel> = new Map();

    constructor(supabaseClient: ReturnType<typeof createClient>) {
        this.supabase = supabaseClient;
    }

    // Subscribe to table changes
    subscribeToTable(
        tableName: string,
        event: 'INSERT' | 'UPDATE' | 'DELETE' | '*',
        callback: (payload: RealtimePostgresChangesPayload<any>) => void,
        filter?: string
    ): RealtimeChannel {
        const subscriptionName = `${tableName}_${event}_${Date.now()}`;

        let channel = this.supabase
            .channel(subscriptionName)
            .on(
                'postgres_changes',
                {
                    event,
                    schema: 'public',
                    table: tableName,
                    filter
                },
                callback
            );

        if (filter) {
            channel = (channel as any).filter(filter);
        }

        channel.subscribe((status) => {
            if (status === 'SUBSCRIBED') {
                console.log(`✅ Subscribed to ${tableName} ${event} events`);
            } else if (status === 'CHANNEL_ERROR') {
                console.error(`❌ Error subscribing to ${tableName} ${event} events`);
            }
        });

        this.subscriptions.set(subscriptionName, channel);
        return channel;
    }

    // Subscribe with specific filter
    subscribeWithFilter(
        tableName: string,
        filter: string,
        callback: (payload: RealtimePostgresChangesPayload<any>) => void
    ): RealtimeChannel {
        const subscriptionName = `${tableName}_filtered_${Date.now()}`;

        const channel = this.supabase
            .channel(subscriptionName)
            .on(
                'postgres_changes',
                {
                    event: '*',
                    schema: 'public',
                    table: tableName,
                    filter
                },
                callback
            )
            .subscribe((status) => {
                if (status === 'SUBSCRIBED') {
                    console.log(`✅ Subscribed to ${tableName} with filter: ${filter}`);
                }
            });

        this.subscriptions.set(subscriptionName, channel);
        return channel;
    }

    // Subscribe to broadcast messages
    subscribeToBroadcast(
        channelName: string,
        event: string,
        callback: (payload: any) => void
    ): RealtimeChannel {
        const subscriptionName = `broadcast_${channelName}_${Date.now()}`;

        const channel = this.supabase
            .channel(subscriptionName)
            .on(
                'broadcast',
                { event },
                callback
            )
            .subscribe((status) => {
                if (status === 'SUBSCRIBED') {
                    console.log(`✅ Subscribed to broadcast ${event} on ${channelName}`);
                }
            });

        this.subscriptions.set(subscriptionName, channel);
        return channel;
    }

    // Send broadcast message
    async sendBroadcast(channelName: string, event: string, payload: any): Promise<void> {
        try {
            const channel = this.subscriptions.get(`broadcast_${channelName}`);
            if (channel) {
                await channel.send({
                    type: 'broadcast',
                    event,
                    payload
                });
                console.log(`📤 Broadcast sent: ${event}`);
            }
        } catch (error) {
            console.error('❌ Error sending broadcast:', error);
        }
    }

    // Subscribe to presence
    subscribeToPresence(
        channelName: string,
        callback: (state: 'JOIN' | 'LEAVE' | 'SYNC', presence: any) => void
    ): RealtimeChannel {
        const subscriptionName = `presence_${channelName}_${Date.now()}`;

        const channel = this.supabase
            .channel(subscriptionName)
            .on('presence', { event: 'sync' }, () => {
                const state = channel.presenceState();
                callback('SYNC', state);
            })
            .on('presence', { event: 'join' }, ({ key, newPresences }) => {
                callback('JOIN', { key, newPresences });
            })
            .on('presence', { event: 'leave' }, ({ key, leftPresences }) => {
                callback('LEAVE', { key, leftPresences });
            })
            .subscribe((status) => {
                if (status === 'SUBSCRIBED') {
                    console.log(`✅ Subscribed to presence on ${channelName}`);
                }
            });

        this.subscriptions.set(subscriptionName, channel);
        return channel;
    }

    // Track user presence
    async trackPresence(channel: RealtimeChannel, userState: any): Promise<void> {
        try {
            await channel.track(userState);
            console.log('👤 Presence tracked:', userState);
        } catch (error) {
            console.error('❌ Error tracking presence:', error);
        }
    }

    // Unsubscribe from specific channel
    unsubscribe(subscriptionName: string): void {
        const subscription = this.subscriptions.get(subscriptionName);
        if (subscription) {
            this.supabase.removeChannel(subscription);
            this.subscriptions.delete(subscriptionName);
            console.log(`🔌 Unsubscribed from ${subscriptionName}`);
        }
    }

    // Unsubscribe from all channels
    unsubscribeAll(): void {
        this.subscriptions.forEach((channel, name) => {
            this.supabase.removeChannel(channel);
            console.log(`🔌 Unsubscribed from ${name}`);
        });
        this.subscriptions.clear();
    }
}

// 2. React Hooks for Real-time
export function useRealtimeSubscription<T>(
    tableName: string,
    event: 'INSERT' | 'UPDATE' | 'DELETE' | '*',
    filter?: string,
    callback?: (payload: RealtimePostgresChangesPayload<T>) => void
) {
    const [data, setData] = useState<T[]>([]);
    const [error, setError] = useState<Error | null>(null);
    const [loading, setLoading] = useState(true);

    useEffect(() => {
        const realtimeService = new RealtimeService(supabase);

        const handleRealtimeEvent = (payload: RealtimePostgresChangesPayload<T>) => {
            console.log('📡 Realtime event received:', payload);

            switch (payload.eventType) {
                case 'INSERT':
                    setData(prev => [...prev, payload.new as T]);
                    break;
                case 'UPDATE':
                    setData(prev =>
                        prev.map(item =>
                            (item as any).id === (payload.new as any).id
                                ? payload.new as T
                                : item
                        )
                    );
                    break;
                case 'DELETE':
                    setData(prev =>
                        prev.filter(item => (item as any).id !== (payload.old as any).id)
                    );
                    break;
            }

            if (callback) {
                callback(payload);
            }
        };

        const channel = realtimeService.subscribeToTable(tableName, event, handleRealtimeEvent, filter);

        // Load initial data
        const loadInitialData = async () => {
            try {
                let query = supabase.from(tableName).select('*');

                if (filter) {
                    // Simple filter parsing (you might want to enhance this)
                    const [column, operator, value] = filter.split('.');
                    if (column && operator && value) {
                        query = query.filter(column, operator, value);
                    }
                }

                const { data: initialData, error } = await query;

                if (error) throw error;
                setData(initialData || []);
            } catch (err) {
                setError(err as Error);
            } finally {
                setLoading(false);
            }
        };

        loadInitialData();

        return () => {
            realtimeService.unsubscribeAll();
        };
    }, [tableName, event, filter, callback]);

    return { data, error, loading };
}

// 3. File Storage Service
class StorageService {
    private supabase: ReturnType<typeof createClient>;

    constructor(supabaseClient: ReturnType<typeof createClient>) {
        this.supabase = supabaseClient;
    }

    // Upload file
    async uploadFile(
        bucket: string,
        path: string,
        file: File | ArrayBuffer,
        options: {
            upsert?: boolean;
            contentType?: string;
            metadata?: Record<string, string>;
        } = {}
    ) {
        try {
            const { data, error } = await this.supabase.storage
                .from(bucket)
                .upload(path, file, {
                    upsert: options.upsert || false,
                    contentType: options.contentType,
                    metadata: options.metadata
                });

            if (error) {
                console.error('❌ Error uploading file:', error);
                return { success: false, error };
            }

            console.log(`✅ File uploaded successfully: ${path}`);
            return { success: true, data };
        } catch (error) {
            console.error('❌ Unexpected error uploading file:', error);
            return { success: false, error };
        }
    }

    // Download file
    async downloadFile(bucket: string, path: string) {
        try {
            const { data, error } = await this.supabase.storage
                .from(bucket)
                .download(path);

            if (error) {
                console.error('❌ Error downloading file:', error);
                return { success: false, error };
            }

            console.log(`✅ File downloaded successfully: ${path}`);
            return { success: true, data };
        } catch (error) {
            console.error('❌ Unexpected error downloading file:', error);
            return { success: false, error };
        }
    }

    // Get public URL
    getPublicUrl(bucket: string, path: string): string {
        const { data } = this.supabase.storage
            .from(bucket)
            .getPublicUrl(path);

        return data.publicUrl;
    }

    // Get signed URL for private files
    async getSignedUrl(bucket: string, path: string, expiresIn: number = 3600) {
        try {
            const { data, error } = await this.supabase.storage
                .from(bucket)
                .createSignedUrl(path, expiresIn);

            if (error) {
                console.error('❌ Error creating signed URL:', error);
                return { success: false, error };
            }

            console.log(`✅ Signed URL created for: ${path}`);
            return { success: true, data };
        } catch (error) {
            console.error('❌ Unexpected error creating signed URL:', error);
            return { success: false, error };
        }
    }

    // List files
    async listFiles(bucket: string, path?: string, search?: string) {
        try {
            const { data, error } = await this.supabase.storage
                .from(bucket)
                .list(path, {
                    search,
                    limit: 100
                });

            if (error) {
                console.error('❌ Error listing files:', error);
                return { success: false, error };
            }

            console.log(`📋 Listed ${data.length} files`);
            return { success: true, data };
        } catch (error) {
            console.error('❌ Unexpected error listing files:', error);
            return { success: false, error };
        }
    }

    // Delete file
    async deleteFile(bucket: string, path: string) {
        try {
            const { data, error } = await this.supabase.storage
                .from(bucket)
                .remove([path]);

            if (error) {
                console.error('❌ Error deleting file:', error);
                return { success: false, error };
            }

            console.log(`🗑️ File deleted successfully: ${path}`);
            return { success: true, data };
        } catch (error) {
            console.error('❌ Unexpected error deleting file:', error);
            return { success: false, error };
        }
    }

    // Upload with progress tracking
    async uploadWithProgress(
        bucket: string,
        path: string,
        file: File,
        onProgress: (progress: number) => void
    ) {
        return new Promise((resolve, reject) => {
            const xhr = new XMLHttpRequest();

            xhr.upload.addEventListener('progress', (event) => {
                if (event.lengthComputable) {
                    const progress = (event.loaded / event.total) * 100;
                    onProgress(Math.round(progress));
                }
            });

            xhr.addEventListener('load', () => {
                if (xhr.status === 200) {
                    const data = JSON.parse(xhr.responseText);
                    console.log(`✅ File uploaded with progress: ${path}`);
                    resolve({ success: true, data });
                } else {
                    reject(new Error(`Upload failed with status ${xhr.status}`));
                }
            });

            xhr.addEventListener('error', () => {
                reject(new Error('Upload failed due to network error'));
            });

            // Get signed upload URL
            this.getSignedUrl(bucket, path, 3600).then(({ success, data, error }) => {
                if (!success || error) {
                    reject(error || new Error('Failed to get signed URL'));
                    return;
                }

                xhr.open('PUT', data.signedUrl);
                xhr.setRequestHeader('Content-Type', file.type);
                xhr.send(file);
            });
        });
    }
}

// 4. React Hook for File Upload
export function useFileUpload() {
    const [uploading, setUploading] = useState(false);
    const [progress, setProgress] = useState(0);
    const [error, setError] = useState<Error | null>(null);

    const storageService = new StorageService(supabase);

    const uploadFile = useCallback(async (
        bucket: string,
        path: string,
        file: File,
        options?: {
            upsert?: boolean;
            onProgress?: (progress: number) => void;
        }
    ) => {
        setUploading(true);
        setProgress(0);
        setError(null);

        try {
            const onProgress = (progress: number) => {
                setProgress(progress);
                options?.onProgress?.(progress);
            };

            const result = await storageService.uploadWithProgress(
                bucket,
                path,
                file,
                onProgress
            );

            if (result.success) {
                console.log('✅ File upload completed');
                return result;
            } else {
                throw result.error;
            }
        } catch (err) {
            const error = err as Error;
            setError(error);
            console.error('❌ File upload failed:', error);
            return { success: false, error };
        } finally {
            setUploading(false);
            setProgress(0);
        }
    }, []);

    const reset = useCallback(() => {
        setUploading(false);
        setProgress(0);
        setError(null);
    }, []);

    return { uploadFile, uploading, progress, error, reset };
}

// 5. Edge Functions Examples
class EdgeFunctionService {
    private supabase: ReturnType<typeof createClient>;

    constructor(supabaseClient: ReturnType<typeof createClient>) {
        this.supabase = supabaseClient;
    }

    // Call edge function
    async callEdgeFunction(
        functionName: string,
        payload: any,
        options: {
            method?: 'GET' | 'POST' | 'PUT' | 'DELETE';
            headers?: Record<string, string>;
        } = {}
    ) {
        try {
            const { data, error } = await this.supabase.functions.invoke(
                functionName,
                {
                    body: payload,
                    method: options.method || 'POST',
                    headers: options.headers
                }
            );

            if (error) {
                console.error(`❌ Error calling edge function ${functionName}:`, error);
                return { success: false, error };
            }

            console.log(`✅ Edge function ${functionName} called successfully`);
            return { success: true, data };
        } catch (error) {
            console.error(`❌ Unexpected error calling edge function ${functionName}:`, error);
            return { success: false, error };
        }
    }

    // Generate image thumbnail
    async generateThumbnail(imageUrl: string, width: number = 200, height: number = 200) {
        return this.callEdgeFunction('generate-thumbnail', {
            imageUrl,
            width,
            height
        });
    }

    // Send notification
    async sendNotification(userId: string, title: string, message: string, type: string = 'info') {
        return this.callEdgeFunction('send-notification', {
            userId,
            title,
            message,
            type
        });
    }

    // Process payment
    async processPayment(amount: number, currency: string, paymentMethodId: string) {
        return this.callEdgeFunction('process-payment', {
            amount,
            currency,
            paymentMethodId
        });
    }

    // Generate report
    async generateReport(reportType: string, filters: any) {
        return this.callEdgeFunction('generate-report', {
            reportType,
            filters
        });
    }
}

// 6. Real-time Collaboration Example
export function useCollaborativeEditor(documentId: string) {
    const [content, setContent] = useState('');
    const [collaborators, setCollaborators] = useState<any[]>([]);
    const realtimeService = new RealtimeService(supabase);

    useEffect(() => {
        // Subscribe to document changes
        const documentChannel = realtimeService.subscribeToTable(
            'documents',
            'UPDATE',
            `id=eq.${documentId}`,
            (payload) => {
                if (payload.eventType === 'UPDATE') {
                    setContent(payload.new.content);
                }
            }
        );

        // Subscribe to user presence
        const presenceChannel = realtimeService.subscribeToPresence(
            `document_${documentId}`,
            (state, presence) => {
                if (state === 'SYNC') {
                    const users = Object.values(presence).flat();
                    setCollaborators(users);
                }
            }
        );

        // Track current user presence
        const trackUserPresence = async () => {
            const { data: { user } } = await supabase.auth.getUser();
            if (user) {
                await realtimeService.trackPresence(presenceChannel, {
                    user_id: user.id,
                    user_email: user.email,
                    cursor_position: 0
                });
            }
        };

        trackUserPresence();

        return () => {
            realtimeService.unsubscribeAll();
        };
    }, [documentId]);

    const updateContent = async (newContent: string) => {
        try {
            const { error } = await supabase
                .from('documents')
                .update({ content: newContent })
                .eq('id', documentId);

            if (error) throw error;
        } catch (error) {
            console.error('❌ Error updating document:', error);
        }
    };

    const broadcastCursorUpdate = async (position: number) => {
        const presenceChannels = Array.from(realtimeService['subscriptions'].values())
            .filter(channel => channel.topic?.includes('presence'));

        if (presenceChannels.length > 0) {
            await realtimeService.trackPresence(presenceChannels[0], {
                cursor_position: position
            });
        }
    };

    return {
        content,
        setContent,
        updateContent,
        collaborators,
        broadcastCursorUpdate
    };
}

// 7. Storage Policies Setup (SQL)
const storagePolicies = `
-- Enable Row Level Security for storage
ALTER TABLE storage.buckets ENABLE ROW LEVEL SECURITY;
ALTER TABLE storage.objects ENABLE ROW LEVEL SECURITY;

-- Policy for users to view their own files
CREATE POLICY "Users can view their own files" ON storage.objects
    FOR SELECT USING (auth.uid()::text = (storage.foldername(name))[1]);

-- Policy for users to upload their own files
CREATE POLICY "Users can upload their own files" ON storage.objects
    FOR INSERT WITH CHECK (
        auth.uid()::text = (storage.foldername(name))[1]
    );

-- Policy for users to update their own files
CREATE POLICY "Users can update their own files" ON storage.objects
    FOR UPDATE USING (
        auth.uid()::text = (storage.foldername(name))[1]
    );

-- Policy for users to delete their own files
CREATE POLICY "Users can delete their own files" ON storage.objects
    FOR DELETE USING (
        auth.uid()::text = (storage.foldername(name))[1]
    );

-- Policy for public access to avatars
CREATE POLICY "Public access to avatars" ON storage.objects
    FOR SELECT USING (
        bucket_id = 'avatars' AND
        (storage.foldername(name))[1] = 'public'
    );
`;

export { RealtimeService, StorageService, EdgeFunctionService };

// Supabase Authentication
// TypeScript - Authentication and authorization patterns

import { createClient, User, AuthResponse, AuthError } from '@supabase/supabase-js';
import { useEffect, useState } from 'react';

// Initialize Supabase client with auth persistence
const supabase = createClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
        auth: {
            persistSession: true,
            storage: typeof window !== 'undefined' ? window.localStorage : undefined,
            autoRefreshToken: true,
            detectSessionInUrl: true
        }
    }
);

// 1. User Authentication Service
class AuthService {
    private supabase: ReturnType<typeof createClient>;

    constructor(supabaseClient: ReturnType<typeof createClient>) {
        this.supabase = supabaseClient;
    }

    // Sign up new user
    async signUp(email: string, password: string, metadata?: Record<string, any>): Promise<AuthResponse> {
        try {
            const response = await this.supabase.auth.signUp({
                email,
                password,
                options: {
                    data: {
                        name: metadata?.name,
                        role: metadata?.role || 'user',
                        ...metadata
                    }
                }
            });

            if (response.error) {
                console.error('❌ Sign up error:', response.error.message);
            } else {
                console.log('✅ User signed up successfully');

                // Create user profile in database
                if (response.data.user) {
                    await this.createUserProfile(response.data.user);
                }
            }

            return response;
        } catch (error) {
            console.error('❌ Unexpected error during sign up:', error);
            throw error;
        }
    }

    // Sign in user
    async signIn(email: string, password: string): Promise<AuthResponse> {
        try {
            const response = await this.supabase.auth.signInWithPassword({
                email,
                password
            });

            if (response.error) {
                console.error('❌ Sign in error:', response.error.message);
            } else {
                console.log('✅ User signed in successfully');

                // Update last login
                if (response.data.user) {
                    await this.updateLastLogin(response.data.user.id);
                }
            }

            return response;
        } catch (error) {
            console.error('❌ Unexpected error during sign in:', error);
            throw error;
        }
    }

    // Sign in with OAuth provider
    async signInWithOAuth(provider: 'github' | 'google' | 'facebook'): Promise<void> {
        try {
            const { error } = await this.supabase.auth.signInWithOAuth({
                provider,
                options: {
                    redirectTo: `${window.location.origin}/auth/callback`
                }
            });

            if (error) {
                console.error('❌ OAuth sign in error:', error.message);
            }
        } catch (error) {
            console.error('❌ Unexpected error during OAuth sign in:', error);
            throw error;
        }
    }

    // Sign out user
    async signOut(): Promise<void> {
        try {
            const { error } = await this.supabase.auth.signOut();

            if (error) {
                console.error('❌ Sign out error:', error.message);
            } else {
                console.log('✅ User signed out successfully');
            }
        } catch (error) {
            console.error('❌ Unexpected error during sign out:', error);
            throw error;
        }
    }

    // Reset password
    async resetPassword(email: string): Promise<void> {
        try {
            const { error } = await this.supabase.auth.resetPasswordForEmail(email, {
                redirectTo: `${window.location.origin}/reset-password`
            });

            if (error) {
                console.error('❌ Password reset error:', error.message);
            } else {
                console.log('✅ Password reset email sent');
            }
        } catch (error) {
            console.error('❌ Unexpected error during password reset:', error);
            throw error;
        }
    }

    // Update password
    async updatePassword(newPassword: string): Promise<void> {
        try {
            const { error } = await this.supabase.auth.updateUser({
                password: newPassword
            });

            if (error) {
                console.error('❌ Password update error:', error.message);
            } else {
                console.log('✅ Password updated successfully');
            }
        } catch (error) {
            console.error('❌ Unexpected error during password update:', error);
            throw error;
        }
    }

    // Get current user
    async getCurrentUser(): Promise<User | null> {
        try {
            const { data: { user }, error } = await this.supabase.auth.getUser();

            if (error) {
                console.error('❌ Error getting current user:', error.message);
                return null;
            }

            return user;
        } catch (error) {
            console.error('❌ Unexpected error getting current user:', error);
            return null;
        }
    }

    // Listen for auth state changes
    onAuthStateChange(callback: (event: string, session: any) => void): () => void {
        const { data: { subscription } } = this.supabase.auth.onAuthStateChange(
            (event, session) => {
                console.log(`🔐 Auth event: ${event}`);
                callback(event, session);
            }
        );

        return () => subscription.unsubscribe();
    }

    // Private helper methods
    private async createUserProfile(user: User): Promise<void> {
        try {
            const { error } = await this.supabase
                .from('user_profiles')
                .insert([{
                    id: user.id,
                    email: user.email,
                    name: user.user_metadata?.name || 'Anonymous',
                    role: user.user_metadata?.role || 'user',
                    created_at: new Date().toISOString(),
                    updated_at: new Date().toISOString()
                }]);

            if (error) {
                console.error('❌ Error creating user profile:', error);
            }
        } catch (error) {
            console.error('❌ Unexpected error creating user profile:', error);
        }
    }

    private async updateLastLogin(userId: string): Promise<void> {
        try {
            const { error } = await this.supabase
                .from('user_profiles')
                .update({ last_login_at: new Date().toISOString() })
                .eq('id', userId);

            if (error) {
                console.error('❌ Error updating last login:', error);
            }
        } catch (error) {
            console.error('❌ Unexpected error updating last login:', error);
        }
    }
}

// 2. React Hook for Authentication
export function useAuth() {
    const [user, setUser] = useState<User | null>(null);
    const [loading, setLoading] = useState(true);
    const authService = new AuthService(supabase);

    useEffect(() => {
        // Get initial user session
        const initializeAuth = async () => {
            try {
                const currentUser = await authService.getCurrentUser();
                setUser(currentUser);
            } catch (error) {
                console.error('❌ Error initializing auth:', error);
            } finally {
                setLoading(false);
            }
        };

        initializeAuth();

        // Listen for auth changes
        const unsubscribe = authService.onAuthStateChange((event, session) => {
            setUser(session?.user ?? null);
        });

        return unsubscribe;
    }, []);

    const signUp = async (email: string, password: string, metadata?: Record<string, any>) => {
        return await authService.signUp(email, password, metadata);
    };

    const signIn = async (email: string, password: string) => {
        return await authService.signIn(email, password);
    };

    const signInWithOAuth = async (provider: 'github' | 'google' | 'facebook') => {
        return await authService.signInWithOAuth(provider);
    };

    const signOut = async () => {
        return await authService.signOut();
    };

    const resetPassword = async (email: string) => {
        return await authService.resetPassword(email);
    };

    const updatePassword = async (newPassword: string) => {
        return await authService.updatePassword(newPassword);
    };

    return {
        user,
        loading,
        signUp,
        signIn,
        signInWithOAuth,
        signOut,
        resetPassword,
        updatePassword
    };
}

// 3. Authorization and Role-Based Access Control
class AuthorizationService {
    private supabase: ReturnType<typeof createClient>;

    constructor(supabaseClient: ReturnType<typeof createClient>) {
        this.supabase = supabaseClient;
    }

    // Check if user has specific role
    async hasRole(userId: string, requiredRole: string): Promise<boolean> {
        try {
            const { data: profile, error } = await this.supabase
                .from('user_profiles')
                .select('role')
                .eq('id', userId)
                .single();

            if (error || !profile) {
                console.error('❌ Error checking user role:', error);
                return false;
            }

            return profile.role === requiredRole;
        } catch (error) {
            console.error('❌ Unexpected error checking role:', error);
            return false;
        }
    }

    // Check if user has any of the required roles
    async hasAnyRole(userId: string, requiredRoles: string[]): Promise<boolean> {
        try {
            const { data: profile, error } = await this.supabase
                .from('user_profiles')
                .select('role')
                .eq('id', userId)
                .single();

            if (error || !profile) {
                console.error('❌ Error checking user roles:', error);
                return false;
            }

            return requiredRoles.includes(profile.role);
        } catch (error) {
            console.error('❌ Unexpected error checking roles:', error);
            return false;
        }
    }

    // Check if user can access resource
    async canAccessResource(userId: string, resource: string, action: string): Promise<boolean> {
        try {
            // Get user permissions
            const { data: permissions, error } = await this.supabase
                .from('user_permissions')
                .select('permissions')
                .eq('user_id', userId)
                .single();

            if (error || !permissions) {
                // Fall back to role-based permissions
                return this.checkRoleBasedPermission(userId, resource, action);
            }

            return permissions.permissions.includes(`${resource}:${action}`);
        } catch (error) {
            console.error('❌ Unexpected error checking resource access:', error);
            return false;
        }
    }

    private async checkRoleBasedPermission(userId: string, resource: string, action: string): Promise<boolean> {
        const rolePermissions = {
            admin: ['*'],
            moderator: ['read:*', 'write:posts', 'moderate:comments'],
            user: ['read:*', 'write:posts', 'write:comments']
        };

        const { data: profile } = await this.supabase
            .from('user_profiles')
            .select('role')
            .eq('id', userId)
            .single();

        if (!profile || !rolePermissions[profile.role]) {
            return false;
        }

        const permissions = rolePermissions[profile.role];
        const requiredPermission = `${resource}:${action}`;

        return permissions.some(permission => {
            if (permission === '*') return true;
            if (permission.startsWith('*:')) {
                return action === permission.split(':')[1];
            }
            if (permission.endsWith(':*')) {
                return resource === permission.split(':')[0];
            }
            return permission === requiredPermission;
        });
    }

    // Grant permission to user
    async grantPermission(userId: string, permission: string): Promise<void> {
        try {
            const { data: existing, error: fetchError } = await this.supabase
                .from('user_permissions')
                .select('permissions')
                .eq('user_id', userId)
                .single();

            if (fetchError && fetchError.code !== 'PGRST116') { // Not found error
                throw fetchError;
            }

            let permissions: string[] = [];

            if (existing) {
                permissions = existing.permissions;
                if (!permissions.includes(permission)) {
                    permissions.push(permission);
                }
            } else {
                permissions = [permission];
            }

            const { error } = await this.supabase
                .from('user_permissions')
                .upsert([{
                    user_id: userId,
                    permissions,
                    updated_at: new Date().toISOString()
                }]);

            if (error) {
                console.error('❌ Error granting permission:', error);
            } else {
                console.log(`✅ Permission '${permission}' granted to user ${userId}`);
            }
        } catch (error) {
            console.error('❌ Unexpected error granting permission:', error);
        }
    }

    // Revoke permission from user
    async revokePermission(userId: string, permission: string): Promise<void> {
        try {
            const { data: existing, error } = await this.supabase
                .from('user_permissions')
                .select('permissions')
                .eq('user_id', userId)
                .single();

            if (error || !existing) {
                console.log('ℹ️ User has no permissions to revoke');
                return;
            }

            const updatedPermissions = existing.permissions.filter((p: string) => p !== permission);

            await this.supabase
                .from('user_permissions')
                .update({
                    permissions: updatedPermissions,
                    updated_at: new Date().toISOString()
                })
                .eq('user_id', userId);

            console.log(`✅ Permission '${permission}' revoked from user ${userId}`);
        } catch (error) {
            console.error('❌ Unexpected error revoking permission:', error);
        }
    }
}

// 4. High-Order Components for Protected Routes
import { ReactNode } from 'react';

interface ProtectedRouteProps {
    children: ReactNode;
    requiredRole?: string;
    fallback?: ReactNode;
}

export function ProtectedRoute({ children, requiredRole, fallback }: ProtectedRouteProps) {
    const { user, loading } = useAuth();
    const [hasPermission, setHasPermission] = useState(false);
    const [checkingPermission, setCheckingPermission] = useState(true);

    useEffect(() => {
        const checkPermission = async () => {
            if (!user) {
                setHasPermission(false);
                setCheckingPermission(false);
                return;
            }

            if (!requiredRole) {
                setHasPermission(true);
                setCheckingPermission(false);
                return;
            }

            const authService = new AuthService(supabase);
            const authzService = new AuthorizationService(supabase);

            try {
                const hasRequiredRole = await authzService.hasRole(user.id, requiredRole);
                setHasPermission(hasRequiredRole);
            } catch (error) {
                console.error('❌ Error checking permission:', error);
                setHasPermission(false);
            } finally {
                setCheckingPermission(false);
            }
        };

        checkPermission();
    }, [user, requiredRole]);

    if (loading || checkingPermission) {
        return <div>Loading...</div>;
    }

    if (!user) {
        return fallback || <div>Please sign in to access this page</div>;
    }

    if (requiredRole && !hasPermission) {
        return fallback || <div>You don't have permission to access this page</div>;
    }

    return <>{children}</>;
}

// 5. Database Schema for Authentication and Authorization
const authSchema = {
    user_profiles: {
        columns: {
            id: 'uuid primary key references auth.users(id)',
            email: 'text unique not null',
            name: 'text not null',
            role: 'text default \'user\' check (role in (\'admin\', \'moderator\', \'user\'))',
            avatar_url: 'text',
            bio: 'text',
            is_active: 'boolean default true',
            last_login_at: 'timestamp with time zone',
            created_at: 'timestamp with time zone default now()',
            updated_at: 'timestamp with time zone default now()'
        },
        indexes: [
            'create index idx_user_profiles_email on user_profiles(email)',
            'create index idx_user_profiles_role on user_profiles(role)'
        ],
        triggers: [
            `
            create function public.handle_new_user()
            returns trigger as $$
            begin
                insert into public.user_profiles (id, email, name)
                values (
                    new.id,
                    new.email,
                    coalesce(new.raw_user_meta_data->>'name', 'Anonymous')
                );
                return new;
            end;
            $$ language plpgsql security definer;

            create trigger on_auth_user_created
                after insert on auth.users
                for each row execute procedure public.handle_new_user();
            `
        ]
    },
    user_permissions: {
        columns: {
            id: 'serial primary key',
            user_id: 'uuid references user_profiles(id) on delete cascade',
            permissions: 'text[] not null',
            created_at: 'timestamp with time zone default now()',
            updated_at: 'timestamp with time zone default now()'
        },
        indexes: [
            'create unique index idx_user_permissions_user on user_permissions(user_id)'
        ]
    },
    user_sessions: {
        columns: {
            id: 'serial primary key',
            user_id: 'uuid references user_profiles(id) on delete cascade',
            session_token: 'text unique not null',
            ip_address: 'text',
            user_agent: 'text',
            expires_at: 'timestamp with time zone not null',
            created_at: 'timestamp with time zone default now()'
        },
        indexes: [
            'create unique index idx_user_sessions_token on user_sessions(session_token)',
            'create index idx_user_sessions_user on user_sessions(user_id)',
            'create index idx_user_sessions_expires on user_sessions(expires_at)'
        ]
    }
};

export { AuthService, AuthorizationService };