src/database.js

const { createClient } = require('@supabase/supabase-js');

const supabase = createClient(
    process.env.SUPABASE_URL,
    process.env.SUPABASE_SERVICE_ROLE_KEY
);

// ── Prepared Statements (Wrappers for Supabase) ─────────────────
const stmts = {
    // Tickets
    createTicket: async (userId, username, channelId) => {
        return await supabase
            .from('tickets')
            .insert({ user_id: userId, username, channel_id: channelId });
    },
    closeTicket: async (status, channelId) => {
        return await supabase
            .from('tickets')
            .update({ status, closed_at: new Date().toISOString() })
            .eq('channel_id', channelId);
    },
    getTicket: async (channelId) => {
        const { data } = await supabase
            .from('tickets')
            .select('*')
            .eq('channel_id', channelId)
            .single();
        return data;
    },
    getOpenTickets: async (status) => {
        const { data } = await supabase
            .from('tickets')
            .select('*')
            .eq('status', status);
        return data || [];
    },
    getUserTicket: async (userId, status) => {
        const { data } = await supabase
            .from('tickets')
            .select('*')
            .eq('user_id', userId)
            .eq('status', status)
            .single();
        return data;
    },
    ticketStats: async () => {
        const { data, error } = await supabase.rpc('get_ticket_stats');
        if (data) return data;
        
        // Manual fallback if RPC not setup
        const { count: total } = await supabase.from('tickets').select('*', { count: 'exact', head: true });
        const { count: open } = await supabase.from('tickets').select('*', { count: 'exact', head: true }).eq('status', 'open');
        const { count: closed } = await supabase.from('tickets').select('*', { count: 'exact', head: true }).eq('status', 'closed');
        const { count: deleted } = await supabase.from('tickets').select('*', { count: 'exact', head: true }).eq('status', 'deleted');
        
        return { total, open_count: open, closed_count: closed, deleted_count: deleted };
    },

    // Verification log
    logVerification: async (userId, username, action) => {
        return await supabase
            .from('verification_log')
            .insert({ user_id: userId, username, action });
    },

    // Bot state (key-value)
    setState: async (key, value) => {
        return await supabase
            .from('bot_state')
            .upsert({ key, value });
    },
    getState: async (key) => {
        const { data } = await supabase
            .from('bot_state')
            .select('value')
            .eq('key', key)
            .single();
        return data ? data.value : null;
    },
    delState: async (key) => {
        return await supabase
            .from('bot_state')
            .delete()
            .eq('key', key);
    },

    // Whitelist
    addWhitelist: async (userId, maxDrops, addedBy) => {
        return await supabase
            .from('whitelist')
            .upsert({ user_id: userId, max_drops: maxDrops, added_by: addedBy });
    },
    removeWhitelist: async (userId) => {
        return await supabase
            .from('whitelist')
            .delete()
            .eq('user_id', userId);
    },
    getWhitelist: async (userId) => {
        const { data } = await supabase
            .from('whitelist')
            .select('*')
            .eq('user_id', userId)
            .single();
        return data;
    },
    getAllWhitelist: async () => {
        const { data } = await supabase
            .from('whitelist')
            .select('*');
        return data || [];
    },

    // Drops array
    logDrop: async (userId, title, channelId) => {
        return await supabase
            .from('drop_log')
            .insert({ user_id: userId, title, channel_id: channelId });
    },
    getDropCount24h: async (userId) => {
        const twentyFourHoursAgo = new Date(Date.now() - 24 * 60 * 60 * 1000).toISOString();
        const { count } = await supabase
            .from('drop_log')
            .select('*', { count: 'exact', head: true })
            .eq('user_id', userId)
            .gt('dropped_at', twentyFourHoursAgo);
        return { count: count || 0 };
    },
    getLastDrop: async (userId) => {
        const { data } = await supabase
            .from('drop_log')
            .select('dropped_at')
            .eq('user_id', userId)
            .order('dropped_at', { ascending: false })
            .limit(1)
            .single();
        return data;
    },
    
    addWebDrop: async (userId, category, title, description, status, isExternal, assetId, fileUrl, imageUrl) => {
        return await supabase
            .from('web_drops')
            .insert({
                user_id: userId,
                category,
                title,
                description,
                status,
                is_external: isExternal,
                asset_id: assetId,
                file_url: fileUrl,
                image_url: imageUrl
            });
    },
    getWebDrop: async (id) => {
        const { data } = await supabase
            .from('web_drops')
            .select('*')
            .eq('id', id)
            .single();
        return data;
    },
    deleteWebDrop: async (id) => {
        return await supabase
            .from('web_drops')
            .delete()
            .eq('id', id);
    },
    getAllWebDrops: async () => {
        const { data } = await supabase
            .from('web_drops')
            .select('*')
            .order('published_at', { ascending: false })
            .limit(50);
        return data || [];
    },
};

module.exports = { db: supabase, stmts };