import { supabaseAdmin } from './supabase';
import { query } from './pg';
import fs from 'fs';
import { DEMO_PRICING_PLANS, PricingPlan } from './pricing-defaults';
export type { PricingPlan };



import path from 'path';

const LOG_DIR = path.join(process.cwd(), 'tmp');
const LOG_FILE = path.join(LOG_DIR, 'rag-logs.txt');

function debugLog(msg: string) {
    const timestamp = new Date().toISOString();
    const formattedMsg = `[DB][${timestamp}] ${msg}\n`;
    try {
        if (!fs.existsSync(LOG_DIR)) {
            fs.mkdirSync(LOG_DIR, { recursive: true });
        }
        fs.appendFileSync(LOG_FILE, formattedMsg);
    } catch (e) {
        // Ignore logging errors
    }
}

export interface User {
    id?: string;
    email: string;
    password?: string;
    name?: string;
    role: "user" | "admin";
    createdAt: string;
    status: "active" | "disabled";
    disabledFeatures?: string[];
    tokens?: number;
}

export interface TokenBalance {
    email: string;
    balance: number;
    updatedAt: string;
}

export interface TokenLog {
    email: string;
    amount: number;
    action: "consume" | "add" | "reset";
    feature?: string;
    model?: string;
    timestamp: string;
}

export interface SystemSettings {
    defaultTokens: number;
    aiLimits: Record<string, number>;
    paymentEnabled: boolean;
    paymentGateway?: string;
    stripePublicKey?: string;
    stripeSecretKey?: string;
    paypalClientId?: string;
    paypalClientSecret?: string;
    paypalMode?: string;
    flutterwavePublicKey?: string;
    flutterwaveSecretKey?: string;
    flutterwaveEncryptionKey?: string;
    showAiSettings?: boolean;

    metadata?: Record<string, any>;
}


export interface PaymentRecord {
    id: string;
    userId: string;
    userEmail: string;
    planId: string;
    amount: number;
    status: 'succeeded' | 'failed' | 'pending';
    paymentGateway?: string;
    createdAt: string;
}

export interface SubscriptionRecord {
    id: string;
    userEmail: string;
    planId: string;
    status: 'active' | 'trialing' | 'canceled' | 'past_due' | 'unpaid';
    createdAt: string;
}

export interface WebsiteProject {
    id: string;
    userEmail: string;
    name: string;
    code: string;
    subdomain?: string;
    createdAt: string;
    updatedAt: string;
    previewImage?: string;
    messages?: any[];
}

export interface GameProject {
    id: string;
    userEmail: string;
    name: string;
    code: string;
    prompt: string;
    genre: string;
    visualStyle: string;
    previewImage?: string;
    createdAt: string;
    updatedAt: string;
}

export interface Document {
    id: string;
    userEmail: string;
    name: string;
    status: 'processing' | 'completed' | 'error';
    metadata?: any;
    createdAt: string;
}

export interface DocumentChunk {
    id: string;
    documentId: string;
    content: string;
    similarity?: number;
}

export interface Language {
    id?: string;
    code: string;
    name: string;
    direction: 'ltr' | 'rtl';
    isEnabled: boolean;
    createdAt?: string;
}

export interface Translation {
    id?: string;
    translationKey: string;
    languageCode: string;
    value: string;
    updatedAt?: string;
}

class SystemDB {
    // Users
    async getUser(email: string): Promise<User | null> {
        try {
            const res = await query('SELECT * FROM users WHERE LOWER(email) = LOWER($1)', [email]);
            const data = res.rows[0];

            if (!data) return null;

            return {
                id: data.id,
                email: data.email,
                name: data.name,
                role: data.role,
                createdAt: data.created_at instanceof Date ? data.created_at.toISOString() : (data.created_at || new Date().toISOString()),
                status: data.status,
                disabledFeatures: data.disabled_features || [],
                password: data.password
            };
        } catch (error) {
            console.error("Error in getUser:", error);
            return null;
        }
    }

    async saveUser(user: User): Promise<User> {
        try {
            const res = await query(`
                INSERT INTO users (email, name, role, status, password, created_at, disabled_features)
                VALUES ($1, $2, $3, $4, $5, $6, $7)
                ON CONFLICT (email) DO UPDATE SET
                    name = EXCLUDED.name,
                    role = EXCLUDED.role,
                    status = EXCLUDED.status,
                    password = COALESCE(EXCLUDED.password, users.password),
                    disabled_features = EXCLUDED.disabled_features
                RETURNING *
            `, [
                user.email,
                user.name || null,
                user.role || 'user',
                user.status || 'active',
                user.password || null,
                user.createdAt || new Date().toISOString(),
                user.disabledFeatures || []
            ]);

            const data = res.rows[0];
            return {
                id: data.id,
                email: data.email,
                name: data.name,
                role: data.role,
                createdAt: data.created_at instanceof Date ? data.created_at.toISOString() : (data.created_at || new Date().toISOString()),
                status: data.status,
                disabledFeatures: data.disabled_features || [],
                password: data.password
            };
        } catch (error) {
            console.error("Error in saveUser:", error);
            throw error;
        }
    }

    async listUsers(): Promise<User[]> {
        try {
            const usersRes = await query('SELECT * FROM users');
            const users = usersRes.rows;

            // Fetch settings once for the default tokens fallback
            const settings = await this.getSettings();
            const defaultTokens = settings.defaultTokens;

            // Fetch token balances
            const balancesRes = await query('SELECT email, balance FROM user_balances');
            const balances = balancesRes.rows;

            const balanceMap = new Map<string, number>();
            if (balances) {
                balances.forEach((b: any) => {
                    if (b.email) {
                        balanceMap.set(b.email.toLowerCase(), b.balance);
                    }
                });
            }

            return users.map((u: any) => ({
                id: u.id,
                email: u.email,
                name: u.name,
                role: u.role,
                createdAt: u.created_at instanceof Date ? u.created_at.toISOString() : (u.created_at || new Date().toISOString()),
                status: u.status,
                disabledFeatures: u.disabled_features || [],
                tokens: balanceMap.has(u.email.toLowerCase()) ? (balanceMap.get(u.email.toLowerCase()) ?? defaultTokens) : defaultTokens
            }));
        } catch (error) {
            console.error("Error in listUsers:", error);
            return [];
        }
    }

    async deleteUser(email: string): Promise<void> {
        try {
            await query('DELETE FROM users WHERE LOWER(email) = LOWER($1)', [email]);
        } catch (error) {
            console.error("Error in deleteUser:", error);
            throw error;
        }
    }

    async updateUserPassword(email: string, hashedPassword: string): Promise<void> {
        try {
            await query('UPDATE users SET password = $1 WHERE LOWER(email) = LOWER($2)', [hashedPassword, email]);
        } catch (error) {
            console.error("Error in updateUserPassword:", error);
            throw error;
        }
    }

    // OTP and Password Reset
    async createResetToken(email: string, otp: string, expiresInMinutes: number): Promise<void> {
        // Clear any existing tokens for this user first
        await this.deleteResetTokens(email);

        const expiresAt = new Date();
        expiresAt.setMinutes(expiresAt.getMinutes() + expiresInMinutes);

        const { error } = await supabaseAdmin
            .from('reset_tokens')
            .insert({
                email,
                otp,
                expires_at: expiresAt.toISOString()
            });

        if (error) throw new Error(`Error creating reset token: ${error.message}`);
    }

    async verifyResetToken(email: string, otp: string): Promise<boolean> {
        const { data, error } = await supabaseAdmin
            .from('reset_tokens')
            .select('*')
            .eq('email', email)
            .eq('otp', otp)
            .single();

        if (error || !data) return false;

        const expiresAt = new Date(data.expires_at);
        if (expiresAt < new Date()) {
            return false; // Token expired
        }

        return true;
    }

    async deleteResetTokens(email: string): Promise<void> {
        const { error } = await supabaseAdmin
            .from('reset_tokens')
            .delete()
            .eq('email', email);

        if (error) throw new Error(`Error deleting reset tokens: ${error.message}`);
    }

    // Tokens
    async getTokenBalance(email: string): Promise<TokenBalance> {
        try {
            const res = await query('SELECT * FROM user_balances WHERE LOWER(email) = LOWER($1)', [email]);
            const data = res.rows[0];

            if (!data) {
                const settings = await this.getSettings();
                return { email, balance: settings.defaultTokens, updatedAt: new Date().toISOString() };
            }

            return {
                email: data.email,
                balance: data.balance,
                updatedAt: data.updated_at instanceof Date ? data.updated_at.toISOString() : (data.updated_at || new Date().toISOString())
            };
        } catch (error) {
            console.error("Error in getTokenBalance:", error);
            const settings = await this.getSettings();
            return { email, balance: settings.defaultTokens, updatedAt: new Date().toISOString() };
        }
    }

    async updateTokenBalance(email: string, amount: number, action: TokenLog["action"], feature?: string, model?: string): Promise<void> {
        try {
            const current = await this.getTokenBalance(email);
            const newBalance = action === 'consume' ? current.balance - amount : current.balance + amount;

            // Upsert balance
            await query(`
                INSERT INTO user_balances (email, balance, updated_at)
                VALUES ($1, $2, $3)
                ON CONFLICT (email) DO UPDATE SET
                    balance = EXCLUDED.balance,
                    updated_at = EXCLUDED.updated_at
            `, [email.toLowerCase(), newBalance, new Date().toISOString()]);

            // Insert log
            await query(`
                INSERT INTO token_logs (email, amount, action, feature, model, timestamp)
                VALUES ($1, $2, $3, $4, $5, $6)
            `, [email.toLowerCase(), amount, action, feature || null, model || null, new Date().toISOString()]);
        } catch (error) {
            console.error("Error in updateTokenBalance:", error);
            throw error;
        }
    }

    async getTokenLogs(email: string): Promise<TokenLog[]> {
        try {
            const res = await query(`
                SELECT * FROM token_logs 
                WHERE LOWER(email) = LOWER($1) 
                ORDER BY timestamp DESC 
                LIMIT 50
            `, [email]);

            return res.rows.map((l: any) => ({
                email: l.email,
                amount: l.amount,
                action: l.action,
                feature: l.feature || undefined,
                model: l.model || undefined,
                timestamp: l.timestamp instanceof Date ? l.timestamp.toISOString() : (l.timestamp || new Date().toISOString())
            }));
        } catch (error) {
            console.error("Error in getTokenLogs:", error);
            return [];
        }
    }

    async getTotalDistributedTokens(): Promise<number> {
        try {
            const balancesRes = await query('SELECT balance FROM user_balances');
            const balances = balancesRes.rows;

            const usersCountRes = await query('SELECT COUNT(*) as count FROM users');
            const count = parseInt(usersCountRes.rows[0].count || '0');

            const settings = await this.getSettings();
            const defaultTokens = settings.defaultTokens;

            if (!balances || balances.length === 0) return count * defaultTokens;

            const sumStored = balances.reduce((sum, item) => sum + (item.balance || 0), 0);
            const usersWithoutBalance = Math.max(0, count - balances.length);

            return sumStored + (usersWithoutBalance * defaultTokens);
        } catch (error) {
            console.error("Error in getTotalDistributedTokens:", error);
            return 0;
        }
    }

    async getTokenUsageStats(): Promise<{ date: string; tokens: number }[]> {
        try {
            const res = await query(`
                SELECT amount, timestamp 
                FROM token_logs 
                WHERE action = 'consume' 
                ORDER BY timestamp ASC
            `);
            const data = res.rows;

            const stats: Record<string, number> = {};

            data.forEach((log: any) => {
                const dateVal = log.timestamp instanceof Date ? log.timestamp : new Date(log.timestamp);
                const date = dateVal.toLocaleDateString();
                stats[date] = (stats[date] || 0) + log.amount;
            });

            return Object.entries(stats).map(([date, tokens]) => ({ date, tokens }));
        } catch (error) {
            console.error("Error in getTokenUsageStats:", error);
            return [];
        }
    }

    async getSettings(): Promise<SystemSettings> {
        try {
            const res = await query('SELECT * FROM system_settings WHERE id = 1');
            const data = res.rows[0];

            if (!data) {
                return {
                    defaultTokens: 1000,
                    aiLimits: { 
                        image: 50, 
                        chat: 10,
                        'music-generator': 100,
                        'audio-isolation': 50
                    },
                    paymentEnabled: false,
                    showAiSettings: true
                };
            }

            return {
                defaultTokens: data.default_tokens,
                aiLimits: data.ai_limits,
                paymentEnabled: data.payment_enabled,
                showAiSettings: data.show_ai_settings,
                paymentGateway: data.payment_gateway || 'stripe',
                stripePublicKey: data.stripe_public_key,
                stripeSecretKey: data.stripe_secret_key,
                paypalClientId: data.paypal_client_id,
                paypalClientSecret: data.paypal_client_secret,
                paypalMode: data.paypal_mode || 'sandbox',
                flutterwavePublicKey: data.flutterwave_public_key,
                flutterwaveSecretKey: data.flutterwave_secret_key,
                flutterwaveEncryptionKey: data.flutterwave_encryption_key,

                metadata: {
                    ...(data.metadata || {}),
                    siteName: data.site_name,
                    siteUrl: data.site_url,
                    smtp: data.smtp_config
                }
            };
        } catch (error) {
            console.error("Error fetching settings:", error);
            throw error;
        }
    }

    async saveSettings(settings: SystemSettings): Promise<void> {
        const meta = settings.metadata || {};
        const storedMetadata = { ...meta };
        delete storedMetadata.siteName;
        delete storedMetadata.siteUrl;
        delete storedMetadata.smtp;

        await query(`
            INSERT INTO system_settings (
                id, default_tokens, ai_limits, payment_enabled, payment_gateway, 
                stripe_public_key, stripe_secret_key, paypal_client_id, 
                paypal_client_secret, paypal_mode, 
                flutterwave_public_key, flutterwave_secret_key, 
                flutterwave_encryption_key,
                site_name, site_url, 
                smtp_config, show_ai_settings, metadata, updated_at
            )
            VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19)
            ON CONFLICT (id) DO UPDATE SET
                default_tokens = EXCLUDED.default_tokens,
                ai_limits = EXCLUDED.ai_limits,
                payment_enabled = EXCLUDED.payment_enabled,
                show_ai_settings = EXCLUDED.show_ai_settings,
                payment_gateway = EXCLUDED.payment_gateway,
                stripe_public_key = EXCLUDED.stripe_public_key,
                stripe_secret_key = EXCLUDED.stripe_secret_key,
                paypal_client_id = EXCLUDED.paypal_client_id,
                paypal_client_secret = EXCLUDED.paypal_client_secret,
                paypal_mode = EXCLUDED.paypal_mode,
                flutterwave_public_key = EXCLUDED.flutterwave_public_key,
                flutterwave_secret_key = EXCLUDED.flutterwave_secret_key,
                flutterwave_encryption_key = EXCLUDED.flutterwave_encryption_key,
                site_name = EXCLUDED.site_name,
                site_url = EXCLUDED.site_url,
                smtp_config = EXCLUDED.smtp_config,
                metadata = EXCLUDED.metadata,
                updated_at = EXCLUDED.updated_at
        `, [
            1, settings.defaultTokens, settings.aiLimits, settings.paymentEnabled,
            settings.paymentGateway || 'stripe', settings.stripePublicKey,
            settings.stripeSecretKey, settings.paypalClientId,
            settings.paypalClientSecret, settings.paypalMode || 'sandbox',
            settings.flutterwavePublicKey, settings.flutterwaveSecretKey,
            settings.flutterwaveEncryptionKey,
            meta.siteName, meta.siteUrl, meta.smtp, settings.showAiSettings ?? true, 
            storedMetadata, new Date().toISOString()
        ]);
    }

    // Pricing Plans
    async getPlans(): Promise<PricingPlan[]> {
        try {
            const res = await query('SELECT * FROM pricing_plans');
            if (res.rows.length === 0) return DEMO_PRICING_PLANS;

            return res.rows.map((p: any) => ({
                id: p.id,
                name: p.name,
                price: p.price,
                tokens: p.tokens,
                interval: p.interval,
                features: p.features,
                aiTools: p.ai_tools || [],
                isActive: p.is_active,
                description: p.description,
                popular: p.popular,
                cta: p.cta
            }));
        } catch (error) {
            console.error("Error fetching plans:", error);
            return DEMO_PRICING_PLANS;
        }
    }

    async savePlan(plan: PricingPlan): Promise<void> {
        await query(`
            INSERT INTO pricing_plans (id, name, price, tokens, interval, features, ai_tools, is_active, description, popular, cta)
            VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
            ON CONFLICT (id) DO UPDATE SET
                name = EXCLUDED.name,
                price = EXCLUDED.price,
                tokens = EXCLUDED.tokens,
                interval = EXCLUDED.interval,
                features = EXCLUDED.features,
                ai_tools = EXCLUDED.ai_tools,
                is_active = EXCLUDED.is_active,
                description = EXCLUDED.description,
                popular = EXCLUDED.popular,
                cta = EXCLUDED.cta
        `, [
            plan.id, plan.name, plan.price, plan.tokens, plan.interval, 
            plan.features, plan.aiTools || [], plan.isActive, 
            plan.description, plan.popular, plan.cta
        ]);
    }

    async deletePlan(planId: string): Promise<void> {
        await query('DELETE FROM pricing_plans WHERE id = $1', [planId]);
    }

    // Payments
    async savePayment(payment: PaymentRecord): Promise<void> {
        const { error } = await supabaseAdmin.from('payments').upsert({
            id: payment.id,
            user_id: payment.userId,
            user_email: payment.userEmail,
            plan_id: payment.planId,
            amount: payment.amount,
            status: payment.status,
            payment_gateway: payment.paymentGateway || 'stripe',
            created_at: payment.createdAt
        });
        if (error) throw new Error(`Error saving payment: ${error.message}`);
    }

    async getPayments(limit = 50): Promise<PaymentRecord[]> {
        const { data, error } = await supabaseAdmin
            .from('payments')
            .select('*')
            .order('created_at', { ascending: false })
            .limit(limit);

        if (error || !data) return [];

        return data.map((p: any) => ({
            id: p.id,
            userId: p.user_id,
            userEmail: p.user_email,
            planId: p.plan_id,
            amount: p.amount,
            status: p.status,
            paymentGateway: p.payment_gateway || 'stripe',
            createdAt: p.created_at
        }));
    }

    // Websites
    async getWebsite(id: string): Promise<WebsiteProject | null> {
        const { data, error } = await supabaseAdmin
            .from('websites')
            .select('*')
            .eq('id', id)
            .single();

        if (error || !data) return null;

        return {
            id: data.id,
            userEmail: data.user_email,
            name: data.name,
            code: data.code,
            subdomain: data.subdomain,
            messages: data.messages,
            previewImage: data.preview_image,
            createdAt: data.created_at,
            updatedAt: data.updated_at
        };
    }

    async getWebsiteBySubdomain(subdomain: string): Promise<WebsiteProject | null> {
        const { data, error } = await supabaseAdmin
            .from('websites')
            .select('*')
            .eq('subdomain', subdomain)
            .single();

        if (error || !data) return null;

        return {
            id: data.id,
            userEmail: data.user_email,
            name: data.name,
            code: data.code,
            subdomain: data.subdomain,
            messages: data.messages,
            previewImage: data.preview_image,
            createdAt: data.created_at,
            updatedAt: data.updated_at
        };
    }

    async saveWebsite(project: WebsiteProject): Promise<void> {
        const { error } = await supabaseAdmin.from('websites').upsert({
            id: project.id,
            user_email: project.userEmail,
            name: project.name,
            code: project.code,
            subdomain: project.subdomain,
            messages: project.messages,
            preview_image: project.previewImage,
            created_at: project.createdAt,
            updated_at: project.updatedAt
        });
        if (error) throw new Error(`Error saving website: ${error.message}`);
    }

    async deleteWebsite(id: string): Promise<void> {
        const { error } = await supabaseAdmin.from('websites').delete().eq('id', id);
        if (error) throw new Error(`Error deleting website: ${error.message}`);
    }

    async listWebsites(userEmail: string): Promise<WebsiteProject[]> {
        const { data, error } = await supabaseAdmin
            .from('websites')
            .select('*')
            .eq('user_email', userEmail)
            .order('updated_at', { ascending: false });

        if (error || !data) return [];

        return data.map((w: any) => ({
            id: w.id,
            userEmail: w.user_email,
            name: w.name,
            code: w.code,
            subdomain: w.subdomain,
            messages: w.messages,
            previewImage: w.preview_image,
            createdAt: w.created_at,
            updatedAt: w.updated_at
        }));
    }

    async listAllWebsites(): Promise<WebsiteProject[]> {
        const { data, error } = await supabaseAdmin
            .from('websites')
            .select('*')
            .not('subdomain', 'is', null)
            .neq('subdomain', '')
            .order('created_at', { ascending: false });

        if (error || !data) return [];

        return data.map((w: any) => ({
            id: w.id,
            userEmail: w.user_email,
            name: w.name,
            code: w.code,
            subdomain: w.subdomain,
            messages: w.messages,
            previewImage: w.preview_image,
            createdAt: w.created_at,
            updatedAt: w.updated_at
        }));
    }

    async getTotalWebsites(): Promise<number> {
        const { count, error } = await supabaseAdmin
            .from('websites')
            .select('*', { count: 'exact', head: true });

        if (error) return 0;
        return count || 0;
    }

    async checkSubdomainAvailability(subdomain: string, excludeId?: string): Promise<boolean> {
        const { data, error } = await supabaseAdmin
            .from('websites')
            .select('id')
            .eq('subdomain', subdomain);
            
        if (error) return false;
        
        if (data && data.length > 0) {
            if (excludeId) {
                return !data.some((w: any) => w.id !== excludeId);
            }
            return false;
        }
        return true;
    }

    // Subscriptions
    async saveSubscription(subscription: SubscriptionRecord): Promise<void> {
        const { error } = await supabaseAdmin.from('subscriptions').upsert({
            id: subscription.id,
            user_email: subscription.userEmail,
            plan_id: subscription.planId,
            status: subscription.status,
            created_at: subscription.createdAt
        });
        if (error) throw new Error(`Error saving subscription: ${error.message}`);
    }

    async getUserPlan(email: string): Promise<{ planId: string | null, planName: string | null, aiTools: string[], tokens?: number | null }> {
        try {
            const res = await query(`
                SELECT s.plan_id, p.name as plan_name, p.ai_tools, p.tokens
                FROM subscriptions s
                JOIN pricing_plans p ON s.plan_id = p.id
                WHERE LOWER(s.user_email) = LOWER($1) AND s.status IN ('active', 'trialing')
                ORDER BY s.created_at DESC
                LIMIT 1
            `, [email]);

            const data = res.rows[0];
            if (data) {
                return {
                    planId: data.plan_id,
                    planName: data.plan_name,
                    aiTools: data.ai_tools || [],
                    tokens: data.tokens || null
                };
            }

            return { planId: null, planName: null, aiTools: [], tokens: null };
        } catch (error) {
            console.error("Error in getUserPlan:", error);
            return { planId: null, planName: null, aiTools: [], tokens: null };
        }
    }

    // Documents & RAG
    async saveDocument(doc: Partial<Document> & { userEmail: string; name: string }): Promise<Document> {
        const { data, error } = await supabaseAdmin
            .from('documents')
            .insert({
                user_email: doc.userEmail,
                name: doc.name,
                status: doc.status || 'processing',
                metadata: doc.metadata || {}
            })
            .select()
            .single();

        if (error) throw new Error(`Error saving document: ${error.message}`);
        return {
            id: data.id,
            userEmail: data.user_email,
            name: data.name,
            status: data.status,
            metadata: data.metadata,
            createdAt: data.created_at
        };
    }

    async updateDocumentStatus(id: string, status: Document['status']): Promise<void> {
        const { error } = await supabaseAdmin
            .from('documents')
            .update({ status })
            .eq('id', id);

        if (error) throw new Error(`Error updating document status: ${error.message}`);
    }

    async listDocuments(userEmail: string): Promise<Document[]> {
        const { data, error } = await supabaseAdmin
            .from('documents')
            .select('*')
            .eq('user_email', userEmail)
            .order('created_at', { ascending: false });

        if (error || !data) return [];
        return data.map(d => ({
            id: d.id,
            userEmail: d.user_email,
            name: d.name,
            status: d.status,
            metadata: d.metadata,
            createdAt: d.created_at
        }));
    }

    async deleteDocument(id: string): Promise<void> {
        const { error } = await supabaseAdmin
            .from('documents')
            .delete()
            .eq('id', id);
        if (error) throw new Error(`Error deleting document: ${error.message}`);
    }

    async saveDocumentChunks(chunks: { documentId: string; content: string; embedding: number[] }[]): Promise<void> {
        const { error } = await supabaseAdmin
            .from('document_chunks')
            .insert(chunks.map(c => ({
                document_id: c.documentId,
                content: (c.content || "").replace(/\u0000/g, ""), // Final safety check for null characters
                embedding: c.embedding
            })));

        if (error) throw new Error(`Error saving document chunks: ${error.message}`);
    }

    async matchDocumentChunks(userEmail: string, embedding: number[], limit = 5, threshold = 0.5): Promise<DocumentChunk[]> {
        debugLog(`matchDocumentChunks: email=${userEmail}, limit=${limit}, threshold=${threshold}`);
        const { data, error } = await supabaseAdmin.rpc('match_document_chunks', {
            query_embedding: embedding,
            match_threshold: threshold,
            match_count: limit,
            p_user_email: userEmail
        });

        if (error) {
            debugLog(`matchDocumentChunks ERROR: ${error.message}`);
            throw new Error(`Error matching document chunks: ${error.message}`);
        }
        
        debugLog(`matchDocumentChunks: Found ${data?.length || 0} matches`);
        return (data || []).map((d: any) => ({
            id: d.id,
            documentId: d.document_id,
            content: d.content,
            similarity: d.similarity
        }));
    }

    async keywordSearchChunks(userEmail: string, query: string, limit = 5): Promise<DocumentChunk[]> {
        debugLog(`keywordSearchChunks: email=${userEmail}, query=${query}, limit=${limit}`);
        const { data, error } = await supabaseAdmin.rpc('keyword_search_chunks', {
            query_text: query,
            match_count: limit,
            p_user_email: userEmail
        });

        if (error) {
            debugLog(`keywordSearchChunks ERROR: ${error.message}`);
            return [];
        }

        return (data || []).map((d: any) => ({
            id: d.id,
            documentId: d.document_id,
            content: d.content,
            similarity: d.similarity
        }));
    }

    // Dashboard Analytics & Aggregation
    async getRevenueStats(): Promise<{ totalRevenue: number, revenueData: any[] }> {
        const { data: payments, error } = await supabaseAdmin
            .from('payments')
            .select('amount, created_at')
            .eq('status', 'succeeded');

        if (error || !payments) return { totalRevenue: 0, revenueData: [] };

        let totalRevenue = 0;
        const monthlyData: Record<string, { revenue: number, users: Set<string> }> = {};

        const { data: usersData } = await supabaseAdmin.from('users').select('email, created_at');

        payments.forEach((p: any) => {
            totalRevenue += p.amount;
            const date = new Date(p.created_at);
            const month = date.toLocaleString('default', { month: 'short' });
            if (!monthlyData[month]) monthlyData[month] = { revenue: 0, users: new Set() };
            monthlyData[month].revenue += p.amount;
        });

        if (usersData) {
            usersData.forEach((u: any) => {
                const date = new Date(u.created_at);
                const month = date.toLocaleString('default', { month: 'short' });
                if (!monthlyData[month]) monthlyData[month] = { revenue: 0, users: new Set() };
                monthlyData[month].users.add(u.email);
            });
        }

        const revenueData = Object.keys(monthlyData).map(month => ({
            month,
            revenue: monthlyData[month].revenue,
            users: monthlyData[month].users.size
        }));

        const monthsOrder = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];
        revenueData.sort((a, b) => monthsOrder.indexOf(a.month) - monthsOrder.indexOf(b.month));

        return { totalRevenue, revenueData };
    }

    async getToolUsageDistribution(): Promise<any[]> {
        const { data, error } = await supabaseAdmin
            .from('token_logs')
            .select('amount, feature')
            .eq('action', 'consume');

        if (error || !data) return [];

        const featureMap: Record<string, number> = {};
        let totalTokens = 0;
        data.forEach((log: any) => {
            const feature = log.feature || 'Other';
            featureMap[feature] = (featureMap[feature] || 0) + log.amount;
            totalTokens += log.amount;
        });

        const colors = ["#8b5cf6", "#10b981", "#f59e0b", "#ec4899", "#6b7280", "#3b82f6", "#ef4444"];
        let i = 0;

        const toolUsageData = Object.keys(featureMap).map(name => {
            const percentage = totalTokens > 0 ? Math.round((featureMap[name] / totalTokens) * 100) : 0;
            return {
                name: name.charAt(0).toUpperCase() + name.slice(1),
                value: percentage,
                color: colors[i++ % colors.length]
            };
        });

        return toolUsageData.sort((a, b) => b.value - a.value);
    }

    async getDashboardRecentActivities(): Promise<any[]> {
        const activities: any[] = [];
        const { data: users } = await supabaseAdmin.from('users').select('email, created_at').order('created_at', { ascending: false }).limit(5);
        if (users) {
            users.forEach((u: any) => {
                activities.push({
                    type: "signup",
                    user: u.email,
                    timestamp: new Date(u.created_at).getTime(),
                });
            });
        }
        const { data: payments } = await supabaseAdmin.from('payments').select('user_email, amount, created_at').order('created_at', { ascending: false }).limit(5);
        if (payments) {
            payments.forEach((p: any) => {
                activities.push({
                    type: "payment",
                    user: p.user_email,
                    amount: `$${p.amount}`,
                    timestamp: new Date(p.created_at).getTime(),
                });
            });
        }

        activities.sort((a, b) => b.timestamp - a.timestamp);

        const now = Date.now();
        const formatted = activities.slice(0, 5).map(a => {
            const diffInMins = Math.floor((now - a.timestamp) / 60000);
            let timeStr = `${diffInMins} min ago`;
            if (diffInMins >= 60) {
                const hrs = Math.floor(diffInMins / 60);
                timeStr = `${hrs} hour${hrs > 1 ? 's' : ''} ago`;
            }
            if (diffInMins >= 1440) {
                const days = Math.floor(diffInMins / 1440);
                timeStr = `${days} day${days > 1 ? 's' : ''} ago`;
            }
            return {
                type: a.type,
                user: a.user,
                amount: a.amount,
                time: timeStr
            };
        });

        return formatted;
    }

    // ==================== i18n: Languages ====================

    async getLanguages(): Promise<Language[]> {
        const { data, error } = await supabaseAdmin
            .from('languages')
            .select('*')
            .order('created_at', { ascending: true });

        if (error || !data) return [];

        return data.map((l: any) => ({
            id: l.id,
            code: l.code,
            name: l.name,
            direction: l.direction,
            isEnabled: l.is_enabled,
            createdAt: l.created_at
        }));
    }

    async saveLanguage(language: Language): Promise<Language> {
        const { data, error } = await supabaseAdmin
            .from('languages')
            .upsert({
                ...(language.id ? { id: language.id } : {}),
                code: language.code,
                name: language.name,
                direction: language.direction,
                is_enabled: language.isEnabled
            }, { onConflict: 'code' })
            .select()
            .single();

        if (error) throw new Error(`Error saving language: ${error.message}`);

        return {
            id: data.id,
            code: data.code,
            name: data.name,
            direction: data.direction,
            isEnabled: data.is_enabled,
            createdAt: data.created_at
        };
    }

    async toggleLanguage(id: string, isEnabled: boolean): Promise<void> {
        const { error } = await supabaseAdmin
            .from('languages')
            .update({ is_enabled: isEnabled })
            .eq('id', id);

        if (error) throw new Error(`Error toggling language: ${error.message}`);
    }

    async deleteLanguage(id: string): Promise<void> {
        const { error } = await supabaseAdmin
            .from('languages')
            .delete()
            .eq('id', id);

        if (error) throw new Error(`Error deleting language: ${error.message}`);
    }

    // ==================== i18n: Translations ====================

    async getTranslationKeys(): Promise<string[]> {
        const { data, error } = await supabaseAdmin
            .from('translations')
            .select('translation_key')
            .order('translation_key', { ascending: true });

        if (error || !data) return [];

        // Deduplicate keys
        return [...new Set(data.map((d: any) => d.translation_key))];
    }

    async getTranslationsForLanguage(languageCode: string): Promise<Record<string, string>> {
        const { data, error } = await supabaseAdmin
            .from('translations')
            .select('translation_key, value')
            .eq('language_code', languageCode);

        if (error || !data) return {};

        const result: Record<string, string> = {};
        data.forEach((d: any) => {
            result[d.translation_key] = d.value;
        });
        return result;
    }

    async getAllTranslations(): Promise<Translation[]> {
        const { data, error } = await supabaseAdmin
            .from('translations')
            .select('*')
            .order('translation_key', { ascending: true });

        if (error || !data) return [];

        return data.map((d: any) => ({
            id: d.id,
            translationKey: d.translation_key,
            languageCode: d.language_code,
            value: d.value,
            updatedAt: d.updated_at
        }));
    }

    async saveTranslation(translation: Translation): Promise<void> {
        const { error } = await supabaseAdmin
            .from('translations')
            .upsert({
                translation_key: translation.translationKey,
                language_code: translation.languageCode,
                value: translation.value,
                updated_at: new Date().toISOString()
            }, { onConflict: 'translation_key,language_code' })
            .select();

        if (error) throw new Error(`Error saving translation: ${error.message}`);
    }

    async bulkSaveTranslations(translations: Translation[]): Promise<void> {
        const rows = translations.map(t => ({
            translation_key: t.translationKey,
            language_code: t.languageCode,
            value: t.value,
            updated_at: new Date().toISOString()
        }));

        const { error } = await supabaseAdmin
            .from('translations')
            .upsert(rows, { onConflict: 'translation_key,language_code' });

        if (error) throw new Error(`Error bulk saving translations: ${error.message}`);
    }

    async deleteTranslationKey(key: string): Promise<void> {
        const { error } = await supabaseAdmin
            .from('translations')
            .delete()
            .eq('translation_key', key);

        if (error) throw new Error(`Error deleting translation key: ${error.message}`);
    }

    // ==================== Meetings ====================

    async createMeeting(id: string, title: string, hostEmail: string): Promise<{ id: string; title: string; hostEmail: string; status: string; createdAt: string }> {
        const { data, error } = await supabaseAdmin
            .from('meetings')
            .insert({
                id,
                title,
                host_email: hostEmail,
                status: 'active',
                created_at: new Date().toISOString()
            })
            .select()
            .single();

        if (error) throw new Error(`Error creating meeting: ${error.message}`);

        return {
            id: data.id,
            title: data.title,
            hostEmail: data.host_email,
            status: data.status,
            createdAt: data.created_at
        };
    }

    async getMeeting(id: string): Promise<{ id: string; title: string; hostEmail: string; status: string; maxParticipants: number; createdAt: string; endedAt: string | null } | null> {
        const { data, error } = await supabaseAdmin
            .from('meetings')
            .select('*')
            .eq('id', id)
            .single();

        if (error || !data) return null;

        return {
            id: data.id,
            title: data.title,
            hostEmail: data.host_email,
            status: data.status,
            maxParticipants: data.max_participants,
            createdAt: data.created_at,
            endedAt: data.ended_at
        };
    }

    async endMeeting(id: string): Promise<void> {
        const { error } = await supabaseAdmin
            .from('meetings')
            .update({ status: 'ended', ended_at: new Date().toISOString() })
            .eq('id', id);

        if (error) throw new Error(`Error ending meeting: ${error.message}`);
    }

    async listUserMeetings(email: string, limit = 20): Promise<{ id: string; title: string; status: string; createdAt: string; endedAt: string | null }[]> {
        const { data, error } = await supabaseAdmin
            .from('meetings')
            .select('*')
            .eq('host_email', email)
            .order('created_at', { ascending: false })
            .limit(limit);

        if (error || !data) return [];

        return data.map((m: any) => ({
            id: m.id,
            title: m.title,
            status: m.status,
            createdAt: m.created_at,
            endedAt: m.ended_at
        }));
    }

    // ==================== Games ====================

    async getGame(id: string): Promise<GameProject | null> {
        const res = await query('SELECT * FROM games WHERE id = $1', [id]);
        const data = res.rows[0];

        if (!data) return null;

        return {
            id: data.id,
            userEmail: data.user_email,
            name: data.name,
            code: data.code,
            prompt: data.prompt,
            genre: data.genre,
            visualStyle: data.visual_style,
            previewImage: data.preview_image,
            createdAt: data.created_at,
            updatedAt: data.updated_at
        };
    }

    async saveGame(project: GameProject): Promise<void> {
        await query(`
            INSERT INTO games (
                id, user_email, name, code, prompt, genre, visual_style, 
                preview_image, created_at, updated_at
            )
            VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
            ON CONFLICT (id) DO UPDATE SET
                name = EXCLUDED.name,
                code = EXCLUDED.code,
                prompt = EXCLUDED.prompt,
                genre = EXCLUDED.genre,
                visual_style = EXCLUDED.visual_style,
                preview_image = EXCLUDED.preview_image,
                updated_at = EXCLUDED.updated_at
        `, [
            project.id, project.userEmail, project.name, project.code, 
            project.prompt, project.genre, project.visualStyle, 
            project.previewImage, project.createdAt, project.updatedAt
        ]);
    }

    async deleteGame(id: string): Promise<void> {
        await query('DELETE FROM games WHERE id = $1', [id]);
    }

    async listGames(userEmail: string): Promise<GameProject[]> {
        const res = await query('SELECT * FROM games WHERE user_email = $1 ORDER BY updated_at DESC', [userEmail]);
        
        return res.rows.map((data: any) => ({
            id: data.id,
            userEmail: data.user_email,
            name: data.name,
            code: data.code,
            prompt: data.prompt,
            genre: data.genre,
            visualStyle: data.visual_style,
            previewImage: data.preview_image,
            createdAt: data.created_at,
            updatedAt: data.updated_at
        }));
    }
}

export const db = new SystemDB();