const express = require('express');
const router = express.Router();
const db = require('../../database');

// GET /admin/social - Main dashboard
router.get('/', async (req, res) => {
    try {
        const platform = req.query.platform || 'tiktok';
        
        // Get recent posts (last 30 days)
        const { rows: posts } = await db.query(`
            SELECT * FROM social_posts 
            WHERE platform = $1 
            ORDER BY posted_at DESC 
            LIMIT 20
        `, [platform]);
        
        // Get aggregate stats for this platform
        const { rows: stats } = await db.query(`
            SELECT 
                COUNT(*) as total_posts,
                COALESCE(SUM(views), 0) as total_views,
                COALESCE(SUM(likes), 0) as total_likes,
                COALESCE(SUM(comments), 0) as total_comments,
                COALESCE(SUM(shares), 0) as total_shares,
                COALESCE(SUM(new_followers), 0) as total_new_followers,
                COALESCE(AVG(watched_full_pct), 0) as avg_completion,
                COALESCE(AVG(avg_watch_time_seconds), 0) as avg_watch_time
            FROM social_posts 
            WHERE platform = $1
              AND posted_at > NOW() - INTERVAL '30 days'
        `, [platform]);
        
        // Get latest account snapshot
        const { rows: snapshots } = await db.query(`
            SELECT * FROM social_account_snapshots 
            WHERE platform = $1 
            ORDER BY snapshot_date DESC 
            LIMIT 1
        `, [platform]);
        
        // Calculate engagement rate
        const totalViews = parseInt(stats[0]?.total_views || 0);
        const totalLikes = parseInt(stats[0]?.total_likes || 0);
        const totalComments = parseInt(stats[0]?.total_comments || 0);
        const totalShares = parseInt(stats[0]?.total_shares || 0);
        const engagementRate = totalViews > 0 
            ? (((totalLikes + totalComments + totalShares) / totalViews) * 100).toFixed(2)
            : 0;
        
        res.render('admin/social/index', {
            title: 'Social Analytics',
            platform,
            posts,
            stats: {
                ...stats[0],
                engagementRate
            },
            snapshot: snapshots[0] || null
        });
        
    } catch (error) {
        console.error('Social Analytics Error:', error);
        res.status(500).send('Error loading social analytics');
    }
});

// GET /admin/social/add - Add post form
router.get('/add', (req, res) => {
    const platform = req.query.platform || 'tiktok';
    res.render('admin/social/add', {
        title: 'Add Social Post',
        platform
    });
});

// POST /admin/social/add - Create new post
router.post('/add', async (req, res) => {
    try {
        const {
            platform,
            post_title,
            post_url,
            posted_at,
            video_length_seconds,
            views,
            likes,
            comments,
            shares,
            saves,
            total_play_time_seconds,
            avg_watch_time_seconds,
            watched_full_pct,
            drop_off_seconds,
            new_followers,
            top_traffic_source,
            top_traffic_pct,
            notes
        } = req.body;
        
        await db.query(`
            INSERT INTO social_posts (
                platform, post_title, post_url, posted_at, video_length_seconds,
                views, likes, comments, shares, saves,
                total_play_time_seconds, avg_watch_time_seconds, watched_full_pct,
                drop_off_seconds, new_followers, top_traffic_source, top_traffic_pct, notes
            ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18)
        `, [
            platform || 'tiktok',
            post_title,
            post_url || null,
            posted_at,
            video_length_seconds || null,
            views || 0,
            likes || 0,
            comments || 0,
            shares || 0,
            saves || 0,
            total_play_time_seconds || 0,
            avg_watch_time_seconds || 0,
            watched_full_pct || 0,
            drop_off_seconds || null,
            new_followers || 0,
            top_traffic_source || null,
            top_traffic_pct || null,
            notes || null
        ]);
        
        res.redirect(`/admin/social?platform=${platform || 'tiktok'}`);
        
    } catch (error) {
        console.error('Add Post Error:', error);
        res.status(500).send('Error adding post');
    }
});

// GET /admin/social/post/:id - View post detail
router.get('/post/:id', async (req, res) => {
    try {
        const { rows } = await db.query(
            'SELECT * FROM social_posts WHERE id = $1',
            [req.params.id]
        );
        
        if (rows.length === 0) {
            return res.status(404).send('Post not found');
        }
        
        res.render('admin/social/detail', {
            title: 'Post Analytics',
            post: rows[0]
        });
        
    } catch (error) {
        console.error('Post Detail Error:', error);
        res.status(500).send('Error loading post');
    }
});

// POST /admin/social/post/:id/update - Update post metrics
router.post('/post/:id/update', async (req, res) => {
    try {
        const {
            views, likes, comments, shares, saves,
            total_play_time_seconds, avg_watch_time_seconds,
            watched_full_pct, drop_off_seconds, new_followers,
            top_traffic_source, top_traffic_pct, notes
        } = req.body;
        
        await db.query(`
            UPDATE social_posts SET
                views = $1, likes = $2, comments = $3, shares = $4, saves = $5,
                total_play_time_seconds = $6, avg_watch_time_seconds = $7,
                watched_full_pct = $8, drop_off_seconds = $9, new_followers = $10,
                top_traffic_source = $11, top_traffic_pct = $12, notes = $13,
                updated_at = NOW()
            WHERE id = $14
        `, [
            views || 0, likes || 0, comments || 0, shares || 0, saves || 0,
            total_play_time_seconds || 0, avg_watch_time_seconds || 0,
            watched_full_pct || 0, drop_off_seconds || null, new_followers || 0,
            top_traffic_source || null, top_traffic_pct || null, notes || null,
            req.params.id
        ]);
        
        res.redirect(`/admin/social/post/${req.params.id}`);
        
    } catch (error) {
        console.error('Update Post Error:', error);
        res.status(500).send('Error updating post');
    }
});

// GET /admin/social/snapshot - Add account snapshot form
router.get('/snapshot', (req, res) => {
    const platform = req.query.platform || 'tiktok';
    res.render('admin/social/snapshot', {
        title: 'Account Snapshot',
        platform
    });
});

// POST /admin/social/snapshot - Save account snapshot
router.post('/snapshot', async (req, res) => {
    try {
        const {
            platform,
            snapshot_date,
            total_followers,
            profile_views,
            search_queries,
            demographics
        } = req.body;
        
        // Upsert - update if exists for this date, insert if not
        await db.query(`
            INSERT INTO social_account_snapshots 
                (platform, snapshot_date, total_followers, profile_views, search_queries, demographics)
            VALUES ($1, $2, $3, $4, $5, $6)
            ON CONFLICT (platform, snapshot_date) 
            DO UPDATE SET
                total_followers = EXCLUDED.total_followers,
                profile_views = EXCLUDED.profile_views,
                search_queries = EXCLUDED.search_queries,
                demographics = EXCLUDED.demographics
        `, [
            platform || 'tiktok',
            snapshot_date,
            total_followers || 0,
            profile_views || 0,
            search_queries ? JSON.parse(search_queries) : null,
            demographics ? JSON.parse(demographics) : null
        ]);
        
        res.redirect(`/admin/social?platform=${platform || 'tiktok'}`);
        
    } catch (error) {
        console.error('Snapshot Error:', error);
        res.status(500).send('Error saving snapshot');
    }
});

// DELETE /admin/social/post/:id - Delete post
router.post('/post/:id/delete', async (req, res) => {
    try {
        const { rows } = await db.query(
            'SELECT platform FROM social_posts WHERE id = $1',
            [req.params.id]
        );
        const platform = rows[0]?.platform || 'tiktok';
        
        await db.query('DELETE FROM social_posts WHERE id = $1', [req.params.id]);
        
        res.redirect(`/admin/social?platform=${platform}`);
        
    } catch (error) {
        console.error('Delete Post Error:', error);
        res.status(500).send('Error deleting post');
    }
});

module.exports = router;
