// ── categories.js ─────────────────────────────────────────────
const express = require('express');
const categoriesRouter = express.Router();
const { query } = require('../utils/db');
const { requireAdminAuth } = require('../middleware/auth');

categoriesRouter.get('/', async (req, res) => {
  try {
    const result = await query(`
      SELECT c.*, COUNT(m.id)::int as movie_count
      FROM categories c
      LEFT JOIN movies m ON m.category = c.name AND m.status = 'published'
      WHERE c.is_active = true
      GROUP BY c.id
      ORDER BY c.display_order ASC
    `);
    res.json({ success: true, categories: result.rows });
  } catch (err) {
    res.status(500).json({ success: false, message: 'Failed to fetch categories' });
  }
});

categoriesRouter.post('/', requireAdminAuth, async (req, res) => {
  try {
    const { name, slug, display_order } = req.body;
    const result = await query(
      'INSERT INTO categories (name, slug, display_order) VALUES ($1,$2,$3) RETURNING *',
      [name, slug || name.toLowerCase().replace(/\s+/g,'-'), display_order || 0]
    );
    res.status(201).json({ success: true, category: result.rows[0] });
  } catch (err) {
    if (err.code === '23505') return res.status(409).json({ success: false, message: 'Category already exists' });
    res.status(500).json({ success: false, message: 'Failed to create category' });
  }
});

categoriesRouter.patch('/:id', requireAdminAuth, async (req, res) => {
  try {
    const { name, slug, is_active, display_order } = req.body;
    const result = await query(
      `UPDATE categories SET
         name=COALESCE($1,name), slug=COALESCE($2,slug),
         is_active=COALESCE($3,is_active), display_order=COALESCE($4,display_order)
       WHERE id=$5 RETURNING *`,
      [name, slug, is_active, display_order, req.params.id]
    );
    if (!result.rows.length) return res.status(404).json({ success: false, message: 'Category not found' });
    res.json({ success: true, category: result.rows[0] });
  } catch (err) {
    res.status(500).json({ success: false, message: 'Failed to update category' });
  }
});

categoriesRouter.delete('/:id', requireAdminAuth, async (req, res) => {
  try {
    await query('UPDATE categories SET is_active=false WHERE id=$1', [req.params.id]);
    res.json({ success: true, message: 'Category hidden' });
  } catch (err) {
    res.status(500).json({ success: false, message: 'Failed to delete category' });
  }
});

// ── users.js ──────────────────────────────────────────────────
const usersRouter = express.Router();

usersRouter.get('/', requireAdminAuth, async (req, res) => {
  try {
    const { status, search, limit = 50, offset = 0 } = req.query;
    const conditions = [];
    const params = [];
    let p = 1;

    if (status) { conditions.push(`u.status=$${p++}`); params.push(status); }
    if (search) {
      conditions.push(`(u.name ILIKE $${p} OR u.email ILIKE $${p} OR u.phone ILIKE $${p})`);
      params.push(`%${search}%`); p++;
    }

    const where = conditions.length ? `WHERE ${conditions.join(' AND ')}` : '';
    params.push(parseInt(limit), parseInt(offset));

    const result = await query(`
      SELECT u.*,
        COUNT(r.id)::int as rental_count,
        COALESCE(SUM(py.amount) FILTER (WHERE py.status='completed'), 0)::numeric as total_spent
      FROM users u
      LEFT JOIN rentals r ON r.user_firebase_uid = u.firebase_uid
      LEFT JOIN payments py ON py.user_firebase_uid = u.firebase_uid
      ${where}
      GROUP BY u.id
      ORDER BY u.created_at DESC
      LIMIT $${p} OFFSET $${p+1}
    `, params);

    res.json({ success: true, users: result.rows });
  } catch (err) {
    res.status(500).json({ success: false, message: 'Failed to fetch users' });
  }
});

usersRouter.patch('/:id/status', requireAdminAuth, async (req, res) => {
  try {
    const { status } = req.body;
    if (!['active','suspended'].includes(status)) return res.status(400).json({ success: false, message: 'Invalid status' });
    const result = await query('UPDATE users SET status=$1 WHERE id=$2 RETURNING id,name,email,status', [status, req.params.id]);
    if (!result.rows.length) return res.status(404).json({ success: false, message: 'User not found' });
    res.json({ success: true, user: result.rows[0] });
  } catch (err) {
    res.status(500).json({ success: false, message: 'Failed to update user' });
  }
});

// ── downloads.js ──────────────────────────────────────────────
const downloadsRouter = express.Router();
const { requireFirebaseAuth } = require('../middleware/auth');
const { getPresignedDownloadUrl } = require('../services/r2');

downloadsRouter.get('/my', requireFirebaseAuth, async (req, res) => {
  try {
    const result = await query(`
      SELECT d.id, d.quality, d.size_bytes, d.created_at,
             m.id as movie_id, m.title, m.poster_url, m.duration, m.size_mb
      FROM downloads d
      JOIN movies m ON m.id = d.movie_id
      WHERE d.user_firebase_uid = $1
      ORDER BY d.created_at DESC
    `, [req.user.uid]);
    res.json({ success: true, downloads: result.rows });
  } catch (err) {
    res.status(500).json({ success: false, message: 'Failed to fetch downloads' });
  }
});

downloadsRouter.post('/request', requireFirebaseAuth, async (req, res) => {
  try {
    const { movieId, quality = 'HD' } = req.body;
    const { uid } = req.user;

    // Check if movie is free or user has rental
    const movie = await query('SELECT id, is_rental, video_r2_key, status FROM movies WHERE id=$1', [movieId]);
    if (!movie.rows.length || movie.rows[0].status !== 'published') {
      return res.status(404).json({ success: false, message: 'Movie not found' });
    }

    if (movie.rows[0].is_rental) {
      const rental = await query(
        "SELECT id FROM rentals WHERE user_firebase_uid=$1 AND movie_id=$2 AND status='active' AND expires_at>NOW()",
        [uid, movieId]
      );
      if (!rental.rows.length) {
        return res.status(403).json({ success: false, message: 'Rental required to download' });
      }
    }

    const key = movie.rows[0].video_r2_key;
    if (!key) return res.status(404).json({ success: false, message: 'Video file not available yet' });

    const downloadUrl = await getPresignedDownloadUrl(key, 3600); // 1 hour to download

    // Log download
    await query(
      'INSERT INTO downloads (user_firebase_uid, movie_id, quality) VALUES ($1,$2,$3) ON CONFLICT DO NOTHING',
      [uid, movieId, quality]
    );

    res.json({ success: true, downloadUrl, expiresIn: 3600 });
  } catch (err) {
    res.status(500).json({ success: false, message: 'Failed to generate download URL' });
  }
});

downloadsRouter.delete('/:movieId', requireFirebaseAuth, async (req, res) => {
  try {
    await query('DELETE FROM downloads WHERE user_firebase_uid=$1 AND movie_id=$2', [req.user.uid, req.params.movieId]);
    res.json({ success: true, message: 'Download removed' });
  } catch (err) {
    res.status(500).json({ success: false, message: 'Failed to remove download' });
  }
});

// ── admin.js ──────────────────────────────────────────────────
const adminRouter = express.Router();

adminRouter.get('/stats', requireAdminAuth, async (req, res) => {
  try {
    const [movies, users, payments, rentals, recentPayments] = await Promise.all([
      query("SELECT COUNT(*)::int as total, COUNT(*) FILTER (WHERE status='published')::int as published, COUNT(*) FILTER (WHERE status='draft')::int as draft FROM movies"),
      query("SELECT COUNT(*)::int as total, COUNT(*) FILTER (WHERE status='active')::int as active, COUNT(*) FILTER (WHERE created_at > NOW()-INTERVAL '1 day')::int as new_today FROM users"),
      query("SELECT COUNT(*)::int as total, COALESCE(SUM(amount) FILTER (WHERE status='completed'), 0)::numeric as total_revenue, COALESCE(SUM(amount) FILTER (WHERE status='completed' AND created_at > NOW()-INTERVAL '1 day'), 0)::numeric as today_revenue FROM payments"),
      query("SELECT COUNT(*) FILTER (WHERE status='active' AND expires_at>NOW())::int as active FROM rentals"),
      query(`
        SELECT p.reference, p.amount, p.method, p.status, p.created_at,
               u.name as user_name, m.title as movie_title
        FROM payments p
        LEFT JOIN users u ON u.firebase_uid = p.user_firebase_uid
        LEFT JOIN movies m ON m.id = p.movie_id
        ORDER BY p.created_at DESC LIMIT 10
      `),
    ]);

    res.json({
      success: true,
      stats: {
        movies: movies.rows[0],
        users: users.rows[0],
        payments: { ...payments.rows[0], active_rentals: rentals.rows[0].active },
      },
      recentPayments: recentPayments.rows,
    });
  } catch (err) {
    console.error('Stats error:', err);
    res.status(500).json({ success: false, message: 'Failed to fetch stats' });
  }
});

module.exports = { categoriesRouter, usersRouter, downloadsRouter, adminRouter };
