const db = require('../db');

/**
 * Synchronizes active Stripe subscriptions with local database.
 * Returns an object with results: { success: boolean, message: string, count: number }
 */
exports.syncStripeSubscriptions = async () => {
    try {
        // 1. Get Stripe Secret Key
        const settingsResult = await db.query("SELECT value FROM system_settings WHERE `key` = 'stripe_secret_key' AND category = 'payment'");
        let secretKey = settingsResult.rows[0]?.value;

        if (!secretKey) secretKey = process.env.STRIPE_SECRET_KEY;
        else {
            try {
                if (typeof secretKey === 'string' && secretKey.startsWith('"')) {
                    secretKey = JSON.parse(secretKey);
                }
            } catch (e) { }
        }

        if (!secretKey) {
            console.error('Stripe Sync: Configuration missing');
            return { success: false, message: 'Stripe configuration missing' };
        }

        const stripe = require('stripe')(secretKey);

        // 2. Fetch all active subscriptions from Stripe
        // Expand customer to get email
        const subscriptions = await stripe.subscriptions.list({
            status: 'active',
            limit: 100, // Handle pagination if > 100 users later
            expand: ['data.customer']
        });

        // 3. Group subscriptions by email to handle users with multiple active subscriptions
        const userSubs = {};
        for (const sub of subscriptions.data) {
            const customer = sub.customer;
            const email = (typeof customer === 'object') ? customer.email : null;
            if (email) {
                if (!userSubs[email]) {
                    userSubs[email] = [];
                }
                userSubs[email].push(sub);
            }
        }

        let updatedCount = 0;
        const activeEmails = [];

        // 4. Process the "best" subscription for each user
        for (const [email, subs] of Object.entries(userSubs)) {
            activeEmails.push(email);
            
            // Prioritize: Yearly over Monthly, then farthest period end
            subs.sort((a, b) => {
                const aPlan = a.items.data[0].price.recurring.interval;
                const bPlan = b.items.data[0].price.recurring.interval;
                if (aPlan === 'year' && bPlan === 'month') return -1;
                if (aPlan === 'month' && bPlan === 'year') return 1;
                return b.current_period_end - a.current_period_end;
            });

            const sub = subs[0];

            const status = sub.status;
            const subscriptionId = sub.id;
            const currentPeriodEnd = new Date(sub.current_period_end * 1000);
            const customerId = (typeof sub.customer === 'object') ? sub.customer.id : sub.customer;

            // Determine local status robustly for UI
            let subStatus = status;
            if (sub.cancel_at_period_end && status === 'active') {
                subStatus = 'cancelling'; 
            }

            // Determine plan
            const interval = sub.items.data[0].price.recurring.interval; // 'month' or 'year'
            const newPlan = interval === 'year' ? 'yearly' : 'monthly';

            // Check user in DB
            const userRes = await db.query("SELECT id, subscription_plan, subscription_id, role, organization_id FROM users WHERE email = ?", [email]);
            if (userRes.rows.length > 0) {
                const user = userRes.rows[0];

                // Determine new role: if normal, upgrade to pro. If admin/alert-admin, keep as is.
                let newRole = user.role;
                if (newRole === 'normal') {
                    newRole = 'pro';
                }

                // Format date for MySQL
                let formattedDate = null;
                if (currentPeriodEnd && !isNaN(currentPeriodEnd.getTime())) {
                    formattedDate = currentPeriodEnd.toISOString().slice(0, 19).replace('T', ' ');
                }
                
                const cancelAtPeriodEnd = sub.cancel_at_period_end ? 1 : 0;

                // 1. Update User Record
                await db.query(
                    "UPDATE users SET subscription_plan = ?, subscription_end_date = ?, renewal_date = ?, subscription_status = ?, customer_id = ?, subscription_id = ?, role = ?, cancel_at_period_end = ? WHERE id = ?",
                    [
                        newPlan || null, 
                        formattedDate,
                        formattedDate,
                        subStatus || null, 
                        customerId || null, 
                        subscriptionId || null, 
                        newRole || null,
                        cancelAtPeriodEnd,
                        user.id
                    ]
                );

                // 2. Check if user owns an organization and update it
                const orgRes = await db.query("SELECT id FROM organizations WHERE owner_id = ?", [user.id]);
                if (orgRes.rows.length > 0) {
                    const org = orgRes.rows[0];
                    const quantity = sub.items.data[0].quantity || 5; // Default to 5 if not set
                    
                    // Map Stripe Interval to Billing Interval
                    const billingInterval = interval === 'year' ? 'yearly' : 'monthly';

                    await db.query(
                        "UPDATE organizations SET plan_type = ?, license_seats = ?, billing_interval = ?, trial_status = 'active' WHERE id = ?",
                        ['business', quantity, billingInterval, org.id]
                    );
                    console.log(`Updated Organization ${org.id} for user ${email}`);
                }

                updatedCount++;
            }
        }

        console.log(`Stripe Sync Job: Updated ${updatedCount} users.`);
        return { success: true, message: `Sync complete. ${updatedCount} users updated.`, count: updatedCount, total_active: subscriptions.data.length };

    } catch (error) {
        console.error('Stripe Sync Logic Error:', error);
        return { success: false, message: 'Sync failed: ' + error.message };
    }
};
