/**
 * Internal API Routes
 * 
 * These endpoints are for machine-to-machine communication (n8n, external services).
 * Authentication is via Bearer token, not session-based.
 * 
 * Token is set in environment variable: INTERNAL_API_TOKEN
 */

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

// =============================================================================
// MIDDLEWARE: Internal Token Authentication
// =============================================================================

const verifyInternalToken = (req, res, next) => {
    const authHeader = req.headers.authorization;
    const expectedToken = process.env.INTERNAL_API_TOKEN;
    
    if (!expectedToken) {
        console.error('⚠️ INTERNAL_API_TOKEN not configured');
        return res.status(500).json({ error: 'Server misconfiguration' });
    }
    
    if (!authHeader || !authHeader.startsWith('Bearer ')) {
        return res.status(401).json({ error: 'Missing or invalid Authorization header' });
    }
    
    const token = authHeader.substring(7); // Remove 'Bearer ' prefix
    
    if (token !== expectedToken) {
        return res.status(403).json({ error: 'Invalid token' });
    }
    
    next();
};

// Apply token auth to all routes in this router
router.use(verifyInternalToken);

// =============================================================================
// POST /api/internal/social/sync
// Upsert social post metrics from n8n
// =============================================================================

router.post('/social/sync', async (req, res) => {
    const { platform, platform_post_id, post_title, post_url, posted_at, metrics } = req.body;
    
    // Validate required fields
    if (!platform || !platform_post_id) {
        return res.status(400).json({ 
            error: 'Missing required fields', 
            required: ['platform', 'platform_post_id'] 
        });
    }
    
    // Validate platform enum
    const validPlatforms = ['tiktok', 'facebook', 'instagram', 'x', 'bluesky'];
    if (!validPlatforms.includes(platform)) {
        return res.status(400).json({ 
            error: 'Invalid platform', 
            valid: validPlatforms 
        });
    }
    
    try {
        // Check if post exists
        const { rows: existing } = await db.query(
            'SELECT id FROM social_posts WHERE platform = $1 AND platform_post_id = $2',
            [platform, platform_post_id]
        );
        
        if (existing.length > 0) {
            // UPDATE existing post
            const updateFields = [];
            const updateValues = [];
            let paramIndex = 1;
            
            // Update title and URL if provided
            if (post_title) {
                updateFields.push(`post_title = $${paramIndex}`);
                updateValues.push(post_title);
                paramIndex++;
            }
            if (post_url) {
                updateFields.push(`post_url = $${paramIndex}`);
                updateValues.push(post_url);
                paramIndex++;
            }
            
            // Build dynamic update based on provided metrics
            if (metrics) {
                const metricFields = [
                    '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'
                ];
                
                for (const field of metricFields) {
                    if (metrics[field] !== undefined) {
                        updateFields.push(`${field} = $${paramIndex}`);
                        updateValues.push(metrics[field]);
                        paramIndex++;
                    }
                }
            }
            
            // Always update timestamp
            updateFields.push(`updated_at = NOW()`);
            
            if (updateFields.length > 1) { // More than just updated_at
                updateValues.push(platform, platform_post_id);
                
                await db.query(`
                    UPDATE social_posts 
                    SET ${updateFields.join(', ')}
                    WHERE platform = $${paramIndex} AND platform_post_id = $${paramIndex + 1}
                `, updateValues);
            }
            
            console.log(`📊 [Social Sync] Updated ${platform}:${platform_post_id}`);
            return res.json({ 
                success: true, 
                action: 'updated',
                post_id: existing[0].id 
            });
            
        } else {
            // INSERT new post
            const insertResult = await db.query(`
                INSERT INTO social_posts (
                    platform, platform_post_id, post_title, post_url, posted_at,
                    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
                ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17)
                RETURNING id
            `, [
                platform,
                platform_post_id,
                post_title || 'Untitled',
                post_url || null,
                posted_at || new Date().toISOString(),
                metrics?.views || 0,
                metrics?.likes || 0,
                metrics?.comments || 0,
                metrics?.shares || 0,
                metrics?.saves || 0,
                metrics?.total_play_time_seconds || 0,
                metrics?.avg_watch_time_seconds || 0,
                metrics?.watched_full_pct || 0,
                metrics?.drop_off_seconds || null,
                metrics?.new_followers || 0,
                metrics?.top_traffic_source || null,
                metrics?.top_traffic_pct || null
            ]);
            
            console.log(`📊 [Social Sync] Created ${platform}:${platform_post_id} -> id ${insertResult.rows[0].id}`);
            return res.json({ 
                success: true, 
                action: 'created',
                post_id: insertResult.rows[0].id 
            });
        }
        
    } catch (error) {
        console.error('❌ [Social Sync] Error:', error);
        return res.status(500).json({ error: 'Database operation failed', details: error.message });
    }
});

// =============================================================================
// POST /api/internal/social/sync/batch
// Batch upsert multiple posts at once (for efficiency)
// =============================================================================

router.post('/social/sync/batch', async (req, res) => {
    const { posts } = req.body;
    
    if (!posts || !Array.isArray(posts)) {
        return res.status(400).json({ error: 'posts array required' });
    }
    
    if (posts.length > 100) {
        return res.status(400).json({ error: 'Maximum 100 posts per batch' });
    }
    
    const results = {
        success: true,
        processed: 0,
        created: 0,
        updated: 0,
        errors: []
    };
    
    for (const post of posts) {
        try {
            const { platform, platform_post_id, post_title, post_url, posted_at, metrics } = post;
            
            if (!platform || !platform_post_id) {
                results.errors.push({ post, error: 'Missing platform or platform_post_id' });
                continue;
            }
            
            // Check if exists
            const { rows: existing } = await db.query(
                'SELECT id FROM social_posts WHERE platform = $1 AND platform_post_id = $2',
                [platform, platform_post_id]
            );
            
            if (existing.length > 0) {
                // Update
                await db.query(`
                    UPDATE social_posts SET
                        views = COALESCE($1, views),
                        likes = COALESCE($2, likes),
                        comments = COALESCE($3, comments),
                        shares = COALESCE($4, shares),
                        saves = COALESCE($5, saves),
                        updated_at = NOW()
                    WHERE platform = $6 AND platform_post_id = $7
                `, [
                    metrics?.views, metrics?.likes, metrics?.comments,
                    metrics?.shares, metrics?.saves,
                    platform, platform_post_id
                ]);
                results.updated++;
            } else {
                // Insert
                await db.query(`
                    INSERT INTO social_posts (
                        platform, platform_post_id, post_title, post_url, posted_at,
                        views, likes, comments, shares, saves
                    ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
                `, [
                    platform, platform_post_id,
                    post_title || 'Untitled',
                    post_url || null,
                    posted_at || new Date().toISOString(),
                    metrics?.views || 0, metrics?.likes || 0,
                    metrics?.comments || 0, metrics?.shares || 0,
                    metrics?.saves || 0
                ]);
                results.created++;
            }
            results.processed++;
            
        } catch (error) {
            results.errors.push({ post, error: error.message });
        }
    }
    
    console.log(`📊 [Social Sync Batch] Processed ${results.processed}, Created ${results.created}, Updated ${results.updated}`);
    res.json(results);
});

// =============================================================================
// POST /api/internal/social/snapshot
// Upsert account-level snapshot (followers, profile views, etc.)
// =============================================================================

router.post('/social/snapshot', async (req, res) => {
    const { platform, snapshot_date, total_followers, profile_views, search_queries, demographics } = req.body;
    
    if (!platform) {
        return res.status(400).json({ error: 'platform required' });
    }
    
    const validPlatforms = ['tiktok', 'facebook', 'instagram', 'x', 'bluesky'];
    if (!validPlatforms.includes(platform)) {
        return res.status(400).json({ error: 'Invalid platform', valid: validPlatforms });
    }
    
    try {
        const date = snapshot_date || new Date().toISOString().split('T')[0];
        
        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 = COALESCE(EXCLUDED.total_followers, social_account_snapshots.total_followers),
                profile_views = COALESCE(EXCLUDED.profile_views, social_account_snapshots.profile_views),
                search_queries = COALESCE(EXCLUDED.search_queries, social_account_snapshots.search_queries),
                demographics = COALESCE(EXCLUDED.demographics, social_account_snapshots.demographics)
        `, [
            platform,
            date,
            total_followers || 0,
            profile_views || 0,
            search_queries || null,
            demographics || null
        ]);
        
        console.log(`📊 [Social Snapshot] ${platform} @ ${date}`);
        res.json({ success: true, platform, snapshot_date: date });
        
    } catch (error) {
        console.error('❌ [Social Snapshot] Error:', error);
        res.status(500).json({ error: 'Database operation failed', details: error.message });
    }
});

// =============================================================================
// GET /api/internal/social/digest
// Get summary data for Discord digest notification
// =============================================================================

router.get('/social/digest', async (req, res) => {
    const period = req.query.period || '24h';
    
    let interval;
    switch (period) {
        case '7d': interval = '7 days'; break;
        case '30d': interval = '30 days'; break;
        case '24h':
        default: interval = '24 hours';
    }
    
    try {
        // Get per-platform stats
        const { rows: platformStats } = await db.query(`
            SELECT 
                platform,
                COUNT(*) as posts,
                COALESCE(SUM(views), 0) as views,
                COALESCE(SUM(likes), 0) as likes,
                COALESCE(SUM(comments), 0) as comments,
                COALESCE(SUM(shares), 0) as shares,
                COALESCE(SUM(new_followers), 0) as new_followers
            FROM social_posts 
            WHERE updated_at > NOW() - INTERVAL '${interval}'
            GROUP BY platform
        `);
        
        // Get latest follower counts from snapshots
        const { rows: latestSnapshots } = await db.query(`
            SELECT DISTINCT ON (platform) 
                platform, total_followers, profile_views, snapshot_date
            FROM social_account_snapshots
            ORDER BY platform, snapshot_date DESC
        `);
        
        // Calculate totals
        const totals = platformStats.reduce((acc, p) => ({
            views: acc.views + parseInt(p.views || 0),
            likes: acc.likes + parseInt(p.likes || 0),
            comments: acc.comments + parseInt(p.comments || 0),
            shares: acc.shares + parseInt(p.shares || 0),
            new_followers: acc.new_followers + parseInt(p.new_followers || 0)
        }), { views: 0, likes: 0, comments: 0, shares: 0, new_followers: 0 });
        
        res.json({
            period,
            generated_at: new Date().toISOString(),
            platforms: platformStats,
            snapshots: latestSnapshots,
            totals
        });
        
    } catch (error) {
        console.error('❌ [Social Digest] Error:', error);
        res.status(500).json({ error: 'Failed to generate digest' });
    }
});

// =============================================================================
// POST /api/internal/mcp/log
// Log a command execution from Trinity Core
// =============================================================================

router.post('/mcp/log', async (req, res) => {
    try {
        const { server, command, success, stdout, stderr, error, execution_time_ms } = req.body;

        if (!server || !command || success === undefined) {
            return res.status(400).json({ error: 'Missing required fields: server, command, success' });
        }

        const result = await db.query(
            `INSERT INTO mcp_logs (server, command, success, stdout, stderr, error, execution_time_ms)
             VALUES ($1, $2, $3, $4, $5, $6, $7)
             RETURNING id`,
            [server, command, success, stdout || '', stderr || '', error || null, execution_time_ms || null]
        );

        res.json({ success: true, id: result.rows[0].id });
    } catch (err) {
        console.error('❌ [MCP Log] Error:', err);
        res.status(500).json({ error: 'Failed to log command' });
    }
});

// =============================================================================
// TASK MANAGEMENT API
// =============================================================================

// GET /api/internal/tasks — List tasks with optional filters
router.get('/tasks', async (req, res) => {
    try {
        const { status, priority, owner } = req.query;
        let where = 'WHERE 1=1';
        const params = [];
        let p = 0;

        if (status) {
            p++; where += ` AND status = $${p}`; params.push(status);
        } else {
            where += ` AND status NOT IN ('done', 'obsolete')`;
        }
        if (priority) { p++; where += ` AND priority = $${p}`; params.push(priority); }
        if (owner) { p++; where += ` AND owner = $${p}`; params.push(owner); }

        const result = await db.query(
            `SELECT * FROM tasks ${where} ORDER BY
                CASE priority
                    WHEN 'critical' THEN 1 WHEN 'high' THEN 2
                    WHEN 'medium' THEN 3 WHEN 'low' THEN 4 WHEN 'wish' THEN 5
                END, task_number`,
            params
        );
        res.json({ tasks: result.rows, count: result.rows.length });
    } catch (err) {
        console.error('❌ [Tasks] List error:', err);
        res.status(500).json({ error: 'Failed to fetch tasks' });
    }
});

// GET /api/internal/tasks/summary — Quick stats
router.get('/tasks/summary', async (req, res) => {
    try {
        const result = await db.query(`
            SELECT status, COUNT(*) as count FROM tasks GROUP BY status ORDER BY status
        `);
        const byPriority = await db.query(`
            SELECT priority, COUNT(*) as count FROM tasks
            WHERE status NOT IN ('done', 'obsolete')
            GROUP BY priority ORDER BY
                CASE priority
                    WHEN 'critical' THEN 1 WHEN 'high' THEN 2
                    WHEN 'medium' THEN 3 WHEN 'low' THEN 4 WHEN 'wish' THEN 5
                END
        `);
        res.json({ byStatus: result.rows, byPriority: byPriority.rows });
    } catch (err) {
        res.status(500).json({ error: 'Failed to fetch summary' });
    }
});

// POST /api/internal/tasks — Create a new task
router.post('/tasks', async (req, res) => {
    try {
        const { title, description, priority, owner, tags, spec_path } = req.body;
        if (!title) return res.status(400).json({ error: 'Title required' });

        // Auto-assign next task number
        const maxResult = await db.query('SELECT COALESCE(MAX(task_number), 0) + 1 as next FROM tasks');
        const taskNumber = maxResult.rows[0].next;

        const result = await db.query(
            `INSERT INTO tasks (task_number, title, description, priority, owner, tags, spec_path)
             VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING *`,
            [taskNumber, title, description || null, priority || 'medium', owner || 'unassigned', tags || null, spec_path || null]
        );
        console.log(`📋 [Tasks] Created #${taskNumber}: ${title}`);
        res.json({ success: true, task: result.rows[0] });
    } catch (err) {
        console.error('❌ [Tasks] Create error:', err);
        res.status(500).json({ error: 'Failed to create task' });
    }
});

// PATCH /api/internal/tasks/:id — Update a task
router.patch('/tasks/:id', async (req, res) => {
    try {
        const { id } = req.params;
        const { status, priority, owner, title, description, completed_by, spec_path } = req.body;

        const updates = [];
        const params = [];
        let p = 0;

        if (status) { p++; updates.push(`status = $${p}`); params.push(status); }
        if (priority) { p++; updates.push(`priority = $${p}`); params.push(priority); }
        if (owner) { p++; updates.push(`owner = $${p}`); params.push(owner); }
        if (title) { p++; updates.push(`title = $${p}`); params.push(title); }
        if (description !== undefined) { p++; updates.push(`description = $${p}`); params.push(description); }
        if (spec_path !== undefined) { p++; updates.push(`spec_path = $${p}`); params.push(spec_path); }

        // Auto-set completed_at when marking done
        if (status === 'done') {
            updates.push(`completed_at = NOW()`);
            if (completed_by) { p++; updates.push(`completed_by = $${p}`); params.push(completed_by); }
        }

        updates.push(`updated_at = NOW()`);

        if (updates.length <= 1) return res.status(400).json({ error: 'Nothing to update' });

        p++; params.push(id);
        const result = await db.query(
            `UPDATE tasks SET ${updates.join(', ')} WHERE id = $${p} RETURNING *`,
            params
        );

        if (result.rows.length === 0) return res.status(404).json({ error: 'Task not found' });
        console.log(`📋 [Tasks] Updated #${result.rows[0].task_number}: ${updates.join(', ')}`);
        res.json({ success: true, task: result.rows[0] });
    } catch (err) {
        console.error('❌ [Tasks] Update error:', err);
        res.status(500).json({ error: 'Failed to update task' });
    }
});

// =============================================================================
// Issue Tracker — /api/internal/issues*
// Task #166 — REQ-2026-04-14-issue-tracker
// Token-auth'd REST surface that mirrors the admin UI. Use for n8n / external
// submitters. The admin UI under /admin/issues uses the same DB tables.
// =============================================================================

const issueNotifier = require('../services/issueNotifier');

const ISSUE_STATUSES = ['open', 'in_progress', 'blocked', 'resolved', 'closed'];
const ISSUE_PRIORITIES = ['critical', 'high', 'medium', 'low'];
const ISSUE_CATEGORIES = ['bug', 'feature', 'content', 'infrastructure', 'holly', 'general'];

router.get('/issues', async (req, res) => {
    try {
        const { status, priority, category } = req.query;
        const params = [];
        let where = 'WHERE 1=1';
        let p = 0;
        if (status)   { p++; where += ` AND status   = $${p}`; params.push(status); }
        if (priority) { p++; where += ` AND priority = $${p}`; params.push(priority); }
        if (category) { p++; where += ` AND category = $${p}`; params.push(category); }
        const result = await db.query(
            `SELECT * FROM issues ${where} ORDER BY issue_number DESC LIMIT 200`,
            params
        );
        res.json({ issues: result.rows });
    } catch (err) {
        console.error('[API Issues] list error:', err);
        res.status(500).json({ error: 'Failed to list issues' });
    }
});

router.post('/issues', async (req, res) => {
    try {
        const { title, description, priority, category, submitted_by } = req.body;
        if (!title || !submitted_by) {
            return res.status(400).json({ error: 'title and submitted_by required' });
        }
        const next = await db.query('SELECT COALESCE(MAX(issue_number), 0) + 1 AS n FROM issues');
        const issueNumber = next.rows[0].n;
        const result = await db.query(
            `INSERT INTO issues (issue_number, title, description, priority, category, submitted_by)
             VALUES ($1, $2, $3, $4, $5, $6) RETURNING *`,
            [
                issueNumber,
                title,
                description || null,
                ISSUE_PRIORITIES.includes(priority) ? priority : 'medium',
                ISSUE_CATEGORIES.includes(category) ? category : 'general',
                submitted_by
            ]
        );
        const issue = result.rows[0];
        issueNotifier.notifyCreated(issue);
        res.json({ success: true, issue });
    } catch (err) {
        console.error('[API Issues] create error:', err);
        res.status(500).json({ error: 'Failed to create issue' });
    }
});

router.get('/issues/:id', async (req, res) => {
    try {
        const iss = await db.query('SELECT * FROM issues WHERE id = $1', [req.params.id]);
        if (iss.rows.length === 0) return res.status(404).json({ error: 'Not found' });
        const atts = await db.query(
            'SELECT * FROM issue_attachments WHERE issue_id = $1 ORDER BY uploaded_at',
            [req.params.id]
        );
        const cmts = await db.query(
            'SELECT * FROM issue_comments WHERE issue_id = $1 ORDER BY created_at',
            [req.params.id]
        );
        res.json({ issue: iss.rows[0], attachments: atts.rows, comments: cmts.rows });
    } catch (err) {
        console.error('[API Issues] detail error:', err);
        res.status(500).json({ error: 'Failed to load issue' });
    }
});

router.patch('/issues/:id', async (req, res) => {
    try {
        const { status, priority, category, assigned_to } = req.body;
        const updates = [];
        const params = [];
        let p = 0;
        if (status)      { if (!ISSUE_STATUSES.includes(status))   return res.status(400).json({ error: 'bad status' }); p++; updates.push(`status = $${p}`);      params.push(status); }
        if (priority)    { if (!ISSUE_PRIORITIES.includes(priority)) return res.status(400).json({ error: 'bad priority' }); p++; updates.push(`priority = $${p}`);    params.push(priority); }
        if (category)    { if (!ISSUE_CATEGORIES.includes(category)) return res.status(400).json({ error: 'bad category' }); p++; updates.push(`category = $${p}`);    params.push(category); }
        if (assigned_to) { p++; updates.push(`assigned_to = $${p}`); params.push(assigned_to); }
        if (status === 'resolved' || status === 'closed') {
            updates.push('resolved_at = NOW()');
            p++; updates.push(`resolved_by = $${p}`); params.push(req.body.actor || 'api');
        }
        updates.push('updated_at = NOW()');
        if (updates.length <= 1) return res.status(400).json({ error: 'Nothing to update' });
        p++; params.push(req.params.id);
        const r = await db.query(
            `UPDATE issues SET ${updates.join(', ')} WHERE id = $${p} RETURNING *`,
            params
        );
        if (r.rows.length === 0) return res.status(404).json({ error: 'Not found' });
        if (status) issueNotifier.notifyStatusChange(r.rows[0], status, req.body.actor || 'api');
        res.json({ success: true, issue: r.rows[0] });
    } catch (err) {
        console.error('[API Issues] patch error:', err);
        res.status(500).json({ error: 'Failed to update issue' });
    }
});

router.post('/issues/:id/comments', async (req, res) => {
    try {
        const { author, content } = req.body;
        if (!author || !content) return res.status(400).json({ error: 'author and content required' });
        const r = await db.query(
            `INSERT INTO issue_comments (issue_id, author, content) VALUES ($1, $2, $3) RETURNING *`,
            [req.params.id, author, content]
        );
        await db.query(`UPDATE issues SET updated_at = NOW() WHERE id = $1`, [req.params.id]);
        res.json({ success: true, comment: r.rows[0] });
    } catch (err) {
        console.error('[API Issues] comment error:', err);
        res.status(500).json({ error: 'Failed to add comment' });
    }
});

module.exports = router;
