import express from 'express';
import cors from 'cors';
import dotenv from 'dotenv';
import jwt from 'jsonwebtoken';
import bcrypt from 'bcryptjs';
import path from 'path';
import fs from 'fs';
import { fileURLToPath } from 'url';
import mysql from 'mysql2/promise';

dotenv.config();

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

const app = express();
app.use(cors());
app.use(express.json({ limit: '50mb' }));
app.use(express.urlencoded({ extended: true, limit: '50mb' }));

const PORT = process.env.PORT || 5000;
const JWT_SECRET = process.env.JWT_SECRET || 'sportzx_default_secret_key_2026';

// Database State
let dbType = process.env.DB_TYPE || 'sqlite';
let sqliteDb = null;
let mysqlPool = null;

// Initialize Database connection
async function initDatabase() {
  const targetDbType = process.env.DB_TYPE || 'sqlite';

  if (targetDbType === 'mysql') {
    dbType = 'mysql';
    let connected = false;
    let retries = 0;
    const maxRetries = 5;

    while (!connected && retries < maxRetries) {
      try {
        console.log(`Connecting to MySQL database (attempt ${retries + 1}/${maxRetries})...`);
        mysqlPool = mysql.createPool({
          host: process.env.DB_HOST,
          user: process.env.DB_USER,
          password: process.env.DB_PASSWORD,
          database: process.env.DB_NAME,
          waitForConnections: true,
          connectionLimit: 15,
          queueLimit: 0,
          enableKeepAlive: true,
          keepAliveInitialDelay: 10000
        });
        // Test the pool
        await mysqlPool.query('SELECT 1');
        console.log('Successfully connected to MySQL database.');
        connected = true;
      } catch (err) {
        retries++;
        console.error(`Failed to connect to MySQL (attempt ${retries}):`, err.message);
        if (mysqlPool) {
          try {
            await mysqlPool.end();
          } catch (e) {}
          mysqlPool = null;
        }
        if (retries < maxRetries) {
          console.log('Retrying in 3 seconds...');
          await new Promise(resolve => setTimeout(resolve, 3000));
        } else {
          console.error('CRITICAL: Max MySQL connection retries reached. MySQL database is currently unreachable. The server will start but database queries will fail until MySQL is restored.');
        }
      }
    }

    if (connected) {
      // Keep MySQL Pool connection alive by executing a periodic light query
      setInterval(async () => {
        if (dbType === 'mysql' && mysqlPool) {
          try {
            await mysqlPool.query('SELECT 1');
          } catch (e) {
            console.warn('MySQL pool keep-alive ping failed:', e.message);
          }
        }
      }, 30000);

      // MySQL Table Migrations
      try {
        await mysqlPool.query(`
          CREATE TABLE IF NOT EXISTS admins (
            id INT AUTO_INCREMENT PRIMARY KEY,
            username VARCHAR(150) NOT NULL UNIQUE,
            password VARCHAR(255) NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        `);

        await mysqlPool.query(`
          CREATE TABLE IF NOT EXISTS categories (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL UNIQUE,
            logo_url VARCHAR(500) DEFAULT NULL,
            display_order INT DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        `);

        await mysqlPool.query(`
          CREATE TABLE IF NOT EXISTS playlists (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            url VARCHAR(500) DEFAULT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        `);

        await mysqlPool.query(`
          CREATE TABLE IF NOT EXISTS channels (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            stream_url TEXT NOT NULL,
            logo_url VARCHAR(500) DEFAULT NULL,
            category_id INT DEFAULT NULL,
            playlist_id INT DEFAULT NULL,
            servers_json TEXT DEFAULT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
            FOREIGN KEY (playlist_id) REFERENCES playlists(id) ON DELETE SET NULL
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        `);

        await mysqlPool.query(`
          CREATE TABLE IF NOT EXISTS live_events (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255) NOT NULL,
            league VARCHAR(255) DEFAULT NULL,
            team1_name VARCHAR(255) DEFAULT NULL,
            team1_logo VARCHAR(500) DEFAULT NULL,
            team2_name VARCHAR(255) DEFAULT NULL,
            team2_logo VARCHAR(500) DEFAULT NULL,
            start_time VARCHAR(100) DEFAULT NULL,
            status VARCHAR(50) DEFAULT 'Upcoming',
            category VARCHAR(100) DEFAULT 'Sports',
            servers_json TEXT DEFAULT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        `);

        await mysqlPool.query(`
          CREATE TABLE IF NOT EXISTS highlights (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255) NOT NULL,
            league VARCHAR(255) DEFAULT NULL,
            team1_name VARCHAR(255) DEFAULT NULL,
            team1_logo VARCHAR(500) DEFAULT NULL,
            team2_name VARCHAR(255) DEFAULT NULL,
            team2_logo VARCHAR(500) DEFAULT NULL,
            score1 VARCHAR(50) DEFAULT '0',
            score2 VARCHAR(50) DEFAULT '0',
            video_url TEXT NOT NULL,
            category VARCHAR(100) DEFAULT 'All',
            date VARCHAR(100) DEFAULT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        `);

        await mysqlPool.query(`
          CREATE TABLE IF NOT EXISTS settings (
            \`key\` VARCHAR(150) PRIMARY KEY,
            \`value\` TEXT DEFAULT NULL,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        `);

        // Check if playlist_id column exists in channels
        const [columns] = await mysqlPool.query("SHOW COLUMNS FROM channels LIKE 'playlist_id'");
        if (columns.length === 0) {
          console.log("Migrating MySQL database: adding playlist_id to channels...");
          await mysqlPool.query("ALTER TABLE channels ADD COLUMN playlist_id INT DEFAULT NULL");
          await mysqlPool.query("ALTER TABLE channels ADD CONSTRAINT fk_channels_playlist FOREIGN KEY (playlist_id) REFERENCES playlists(id) ON DELETE SET NULL");
        }
      } catch (migrationErr) {
        console.error('Error during MySQL database auto-migration:', migrationErr.message);
      }

      // Seed default credentials and data if database is empty
      await seedDatabase();
    }
  } else {
    dbType = 'sqlite';
    console.log('Initializing SQLite database...');
    const sqlite3 = (await import('sqlite3')).default;
    const dbPath = path.join(__dirname, 'database.sqlite');
    sqliteDb = new sqlite3.Database(dbPath);
    
    // SQLite creation tables script
    const tables = [
      `CREATE TABLE IF NOT EXISTS admins (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT NOT NULL UNIQUE,
        password TEXT NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )`,
      `CREATE TABLE IF NOT EXISTS categories (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL UNIQUE,
        logo_url TEXT DEFAULT NULL,
        display_order INTEGER DEFAULT 0,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )`,
      `CREATE TABLE IF NOT EXISTS playlists (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        url TEXT DEFAULT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )`,
      `CREATE TABLE IF NOT EXISTS channels (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        stream_url TEXT NOT NULL,
        logo_url TEXT DEFAULT NULL,
        category_id INTEGER,
        playlist_id INTEGER,
        servers_json TEXT DEFAULT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
        FOREIGN KEY (playlist_id) REFERENCES playlists(id) ON DELETE SET NULL
      )`,
      `CREATE TABLE IF NOT EXISTS live_events (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        league TEXT DEFAULT NULL,
        team1_name TEXT DEFAULT NULL,
        team1_logo TEXT DEFAULT NULL,
        team2_name TEXT DEFAULT NULL,
        team2_logo TEXT DEFAULT NULL,
        start_time TEXT DEFAULT NULL,
        status TEXT DEFAULT 'Upcoming',
        category TEXT DEFAULT 'Sports',
        servers_json TEXT DEFAULT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )`,
      `CREATE TABLE IF NOT EXISTS highlights (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        league TEXT DEFAULT NULL,
        team1_name TEXT DEFAULT NULL,
        team1_logo TEXT DEFAULT NULL,
        team2_name TEXT DEFAULT NULL,
        team2_logo TEXT DEFAULT NULL,
        score1 TEXT DEFAULT '0',
        score2 TEXT DEFAULT '0',
        video_url TEXT NOT NULL,
        category TEXT DEFAULT 'All',
        date TEXT DEFAULT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )`,
      `CREATE TABLE IF NOT EXISTS settings (
        key TEXT PRIMARY KEY,
        value TEXT
      )`
    ];

    sqliteDb.serialize(() => {
      tables.forEach(table => {
        sqliteDb.run(table, (err) => {
          if (err) console.error('Error creating SQLite table:', err.message);
        });
      });
      
      // SQLite column migration helper
      sqliteDb.all("PRAGMA table_info(channels)", (err, rows) => {
        if (!err && rows) {
          const hasPlaylistId = rows.some(r => r.name === 'playlist_id');
          if (!hasPlaylistId) {
            console.log("Migrating SQLite database: adding playlist_id to channels...");
            sqliteDb.run("ALTER TABLE channels ADD COLUMN playlist_id INTEGER REFERENCES playlists(id) ON DELETE SET NULL");
          }
        }
      });
    });

    // Seed default credentials and data if database is empty
    await seedDatabase();
  }
}

// Promise-based SQL Query Wrapper
async function dbQuery(sql, params = []) {
  // Replace MySQL placeholders (?) with SQLite ones if needed, but both use ? natively!
  if (dbType === 'mysql') {
    if (!mysqlPool) {
      throw new Error('MySQL database pool is not initialized. Connection failed during startup.');
    }
    const [rows] = await mysqlPool.execute(sql, params);
    return rows;
  } else {
    return new Promise((resolve, reject) => {
      sqliteDb.all(sql, params, (err, rows) => {
        if (err) return reject(err);
        resolve(rows);
      });
    });
  }
}

async function dbRun(sql, params = []) {
  if (dbType === 'mysql') {
    if (!mysqlPool) {
      throw new Error('MySQL database pool is not initialized. Connection failed during startup.');
    }
    const [result] = await mysqlPool.execute(sql, params);
    return { insertId: result.insertId, affectedRows: result.affectedRows };
  } else {
    return new Promise((resolve, reject) => {
      sqliteDb.run(sql, params, function (err) {
        if (err) return reject(err);
        resolve({ insertId: this.lastID, affectedRows: this.changes });
      });
    });
  }
}

// Database Seeding Logic
async function seedDatabase() {
  try {
    // 1. Seed Admin User
    const admins = await dbQuery('SELECT * FROM admins LIMIT 1');
    if (admins.length === 0) {
      const username = process.env.ADMIN_USERNAME || 'admin';
      const password = process.env.ADMIN_PASSWORD || 'adminpass';
      const salt = await bcrypt.genSalt(10);
      const hashedPassword = await bcrypt.hash(password, salt);
      await dbRun('INSERT INTO admins (username, password) VALUES (?, ?)', [username, hashedPassword]);
      console.log(`Seeded default admin account: ${username}`);
    }

    // 2. Seed default categories if none exist
    const categories = await dbQuery('SELECT * FROM categories LIMIT 1');
    if (categories.length === 0) {
      const defaultCategories = ['SPORTS', 'BANGLA', 'English News', 'Islamic', 'Kids', 'Documentary', 'Indian-Bangla', 'MUSIC', 'Hindi', 'English Movie', 'FM-Radio', 'Uncategorized'];
      for (const cat of defaultCategories) {
        await dbRun('INSERT OR IGNORE INTO categories (name, display_order) VALUES (?, ?)', [cat, 0])
          .catch(() => dbRun('INSERT INTO categories (name, display_order) VALUES (?, ?)', [cat, 0])); // SQLite vs MySQL fallback
      }
      console.log('Seeded default categories.');
    }

    // 3. Seed channels from local playlist.m3u if empty
    const channels = await dbQuery('SELECT * FROM channels LIMIT 1');
    if (channels.length === 0) {
      const m3uPath = path.join(__dirname, 'playlist.m3u');
      if (fs.existsSync(m3uPath)) {
        console.log('Empty channel list detected. Reading playlist.m3u for automatic seed...');
        const m3uContent = fs.readFileSync(m3uPath, 'utf-8');
        await importM3UContent(m3uContent);
        console.log('Seeded channels from playlist.m3u.');
      }
    }

    // 4. Seed Live Events and Highlights if empty
    const liveEvents = await dbQuery('SELECT * FROM live_events LIMIT 1');
    if (liveEvents.length === 0) {
      const defaultEvents = [
        {
          title: "Argentina VS Honduras",
          league: "National Friendly Games",
          team1_name: "Argentina",
          team1_logo: "https://www.countryflags.com/wp-content/uploads/argentina-flag-png-large.png",
          team2_name: "Honduras",
          team2_logo: "https://www.countryflags.com/wp-content/uploads/honduras-flag-png-large.png",
          start_time: "06:00 AM 07/06/2026",
          status: "Live",
          category: "Football",
          servers: JSON.stringify([
            { name: "SportzX MLB", url: "https://owrcovcrpy.gpcdn.net/bpk-tv/1709/output/index.m3u8" },
            { name: "SportzX Live", url: "http://103.229.254.25:7001/play/a064/index.m3u8" }
          ])
        },
        {
          title: "Brazil VS Egypt",
          league: "National Friendly Games",
          team1_name: "Brazil",
          team1_logo: "https://www.countryflags.com/wp-content/uploads/brazil-flag-png-large.png",
          team2_name: "Egypt",
          team2_logo: "https://www.countryflags.com/wp-content/uploads/egypt-flag-png-large.png",
          start_time: "04:00 AM 07/06/2026",
          status: "Live",
          category: "Football",
          servers: JSON.stringify([
            { name: "SportzX MLB", url: "https://owrcovcrpy.gpcdn.net/bpk-tv/1713/output/index.m3u8" }
          ])
        },
        {
          title: "Royal Challengers Bangalore VS Gujarat Titans",
          league: "IPL | Indian Premier League | Finale",
          team1_name: "Royal Challengers",
          team1_logo: "https://g.sportzcdn.co/Cricket/Team/ipl/rcb_logo.png",
          team2_name: "Gujarat Titans",
          team2_logo: "https://g.sportzcdn.co/Cricket/Team/ipl/gt_logo.png",
          start_time: "08:00 PM 31/05/2026",
          status: "Ended",
          category: "IPL",
          servers: JSON.stringify([
            { name: "Server 1", url: "http://198.195.239.50:8095/Tsports/tracks-v1a1/mono.m3u8" }
          ])
        }
      ];

      for (const ev of defaultEvents) {
        await dbRun(
          'INSERT INTO live_events (title, league, team1_name, team1_logo, team2_name, team2_logo, start_time, status, category, servers_json) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
          [ev.title, ev.league, ev.team1_name, ev.team1_logo, ev.team2_name, ev.team2_logo, ev.start_time, ev.status, ev.category, ev.servers]
        );
      }
      console.log('Seeded default sports live events.');
    }

    const highlights = await dbQuery('SELECT * FROM highlights LIMIT 1');
    if (highlights.length === 0) {
      const defaultHighlights = [
        {
          title: "Argentina VS Honduras Highlights",
          league: "National Friendly Games",
          team1_name: "Argentina",
          team1_logo: "https://www.countryflags.com/wp-content/uploads/argentina-flag-png-large.png",
          team2_name: "Honduras",
          team2_logo: "https://www.countryflags.com/wp-content/uploads/honduras-flag-png-large.png",
          score1: "3",
          score2: "0",
          video_url: "https://test-streams.mux.dev/x36xhzz/x36xhzz.m3u8", // Working HLS demo video
          category: "Football",
          date: "06/06/2026"
        },
        {
          title: "Brazil VS Egypt Highlights",
          league: "National Friendly Games",
          team1_name: "Brazil",
          team1_logo: "https://www.countryflags.com/wp-content/uploads/brazil-flag-png-large.png",
          team2_name: "Egypt",
          team2_logo: "https://www.countryflags.com/wp-content/uploads/egypt-flag-png-large.png",
          score1: "2",
          score2: "1",
          video_url: "https://test-streams.mux.dev/x36xhzz/x36xhzz.m3u8",
          category: "Football",
          date: "05/06/2026"
        }
      ];

      for (const hl of defaultHighlights) {
        await dbRun(
          'INSERT INTO highlights (title, league, team1_name, team1_logo, team2_name, team2_logo, score1, score2, video_url, category, date) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
          [hl.title, hl.league, hl.team1_name, hl.team1_logo, hl.team2_name, hl.team2_logo, hl.score1, hl.score2, hl.video_url, hl.category, hl.date]
        );
      }
      console.log('Seeded default sports highlights.');
    }

    // 5. Seed default settings (announcement)
    const announcement = await dbQuery("SELECT * FROM settings WHERE `key` = 'announcement'");
    if (announcement.length === 0) {
      await dbRun("INSERT INTO settings (`key`, `value`) VALUES ('announcement', 'Welcome to SportzX! Enjoy high-quality streaming of your favorite live TV channels and sports events.')");
      console.log('Seeded default announcement setting.');
    }
  } catch (err) {
    console.error('Error during database seed:', err.message);
  }
}

// M3U Playlist Parser Logic
async function importM3UContent(m3uContent, merge = false, playlistId = null) {
  const lines = m3uContent.split('\n');
  let currentChannel = null;

  for (let i = 0; i < lines.length; i++) {
    const line = lines[i].trim();
    if (line.startsWith('#EXTINF:')) {
      // Parse Channel Metadatas
      currentChannel = {};
      const logoMatch = line.match(/tvg-logo="([^"]+)"/) || line.match(/logo="([^"]+)"/);
      const nameMatch = line.match(/tvg-name="([^"]+)"/);
      const groupMatch = line.match(/group-title="([^"]+)"/);

      currentChannel.logo = logoMatch ? logoMatch[1] : '';
      currentChannel.group = groupMatch ? groupMatch[1] : 'Uncategorized';
      
      // Channel Display Name (usually after the last comma)
      const commaIndex = line.lastIndexOf(',');
      if (commaIndex !== -1) {
        currentChannel.name = line.substring(commaIndex + 1).trim();
      } else {
        currentChannel.name = nameMatch ? nameMatch[1] : 'Unknown Channel';
      }
    } else if (line && !line.startsWith('#') && currentChannel) {
      // This line contains the stream URL
      currentChannel.url = line;

      // Save category
      let categoryId = null;
      try {
        const categories = await dbQuery('SELECT id FROM categories WHERE name = ?', [currentChannel.group]);
        if (categories.length > 0) {
          categoryId = categories[0].id;
        } else {
          const result = await dbRun('INSERT INTO categories (name) VALUES (?)', [currentChannel.group]);
          categoryId = result.insertId;
        }
        
        let existingChannel = null;
        if (merge) {
          const matches = await dbQuery('SELECT * FROM channels WHERE name = ? AND category_id = ?', [currentChannel.name, categoryId]);
          if (matches.length > 0) {
            existingChannel = matches[0];
          }
        }

        if (existingChannel) {
          // Merge: add server to existing servers list
          let servers = [];
          if (existingChannel.servers_json) {
            try {
              servers = JSON.parse(existingChannel.servers_json);
            } catch (e) {
              servers = [{ name: 'Primary Server', url: existingChannel.stream_url }];
            }
          } else {
            servers = [{ name: 'Primary Server', url: existingChannel.stream_url }];
          }

          // Check if server URL already exists in list to avoid duplicates
          const urlExists = servers.some(s => s.url === currentChannel.url);
          if (!urlExists) {
            servers.push({
              name: `Server ${servers.length + 1}`,
              url: currentChannel.url
            });
            await dbRun(
              'UPDATE channels SET servers_json = ?, playlist_id = COALESCE(playlist_id, ?) WHERE id = ?',
              [JSON.stringify(servers), playlistId, existingChannel.id]
            );
          }
        } else {
          // Normal insert
          await dbRun(
            'INSERT INTO channels (name, stream_url, logo_url, category_id, playlist_id, servers_json) VALUES (?, ?, ?, ?, ?, ?)',
            [currentChannel.name, currentChannel.url, currentChannel.logo, categoryId, playlistId, JSON.stringify([{ name: 'Primary Server', url: currentChannel.url }])]
          );
        }
      } catch (err) {
        console.error('Error inserting parsed channel:', err.message);
      }
      currentChannel = null;
    }
  }
}

// --- API AUTH MIDDLEWARE ---
function authenticateToken(req, res, next) {
  const authHeader = req.headers['authorization'];
  const token = authHeader && authHeader.split(' ')[1];

  if (!token) return res.status(401).json({ error: 'Authentication token required' });

  jwt.verify(token, JWT_SECRET, (err, user) => {
    if (err) return res.status(403).json({ error: 'Invalid or expired token' });
    req.user = user;
    next();
  });
}

// --- AUTHENTICATION API ---
app.post('/api/admin/login', async (req, res) => {
  const { username, password } = req.body;
  if (!username || !password) {
    return res.status(400).json({ error: 'Username and password are required' });
  }

  try {
    const admins = await dbQuery('SELECT * FROM admins WHERE username = ?', [username]);
    if (admins.length === 0) {
      return res.status(401).json({ error: 'Invalid admin credentials' });
    }

    const admin = admins[0];
    const passwordMatch = await bcrypt.compare(password, admin.password);
    if (!passwordMatch) {
      return res.status(401).json({ error: 'Invalid admin credentials' });
    }

    const token = jwt.sign({ id: admin.id, username: admin.username }, JWT_SECRET, { expiresIn: '7d' });
    res.json({ token, username: admin.username });
  } catch (err) {
    res.status(500).json({ error: 'Server authentication error: ' + err.message });
  }
});

app.get('/api/admin/verify', authenticateToken, (req, res) => {
  res.json({ valid: true, user: req.user });
});


// --- CATEGORIES API ---
app.get('/api/categories', async (req, res) => {
  try {
    const rows = await dbQuery('SELECT * FROM categories ORDER BY display_order ASC, name ASC');
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

app.post('/api/categories', authenticateToken, async (req, res) => {
  const { name, logo_url, display_order } = req.body;
  if (!name) return res.status(400).json({ error: 'Category name is required' });

  try {
    const result = await dbRun('INSERT INTO categories (name, logo_url, display_order) VALUES (?, ?, ?)', [name, logo_url || null, display_order || 0]);
    res.json({ id: result.insertId, name, logo_url, display_order });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

app.put('/api/categories/:id', authenticateToken, async (req, res) => {
  const { id } = req.params;
  const { name, logo_url, display_order } = req.body;
  if (!name) return res.status(400).json({ error: 'Category name is required' });

  try {
    await dbRun('UPDATE categories SET name = ?, logo_url = ?, display_order = ? WHERE id = ?', [name, logo_url, display_order, id]);
    res.json({ id, name, logo_url, display_order });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

app.delete('/api/categories/:id', authenticateToken, async (req, res) => {
  try {
    await dbRun('DELETE FROM categories WHERE id = ?', [req.params.id]);
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});


// --- CHANNELS API ---
app.get('/api/channels', async (req, res) => {
  try {
    const rows = await dbQuery('SELECT c.*, cat.name as category_name FROM channels c LEFT JOIN categories cat ON c.category_id = cat.id ORDER BY c.name ASC');
    // Parse JSON string field
    const parsedRows = rows.map(row => ({
      ...row,
      servers: row.servers_json ? JSON.parse(row.servers_json) : [{ name: 'Server 1', url: row.stream_url }]
    }));
    res.json(parsedRows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

app.post('/api/channels', authenticateToken, async (req, res) => {
  const { name, stream_url, logo_url, category_id, servers, merge } = req.body;
  if (!name || !stream_url) return res.status(400).json({ error: 'Name and stream_url are required' });

  try {
    if (merge) {
      const matches = await dbQuery('SELECT * FROM channels WHERE name = ?', [name]);
      if (matches.length > 0) {
        const existingChannel = matches[0];
        let existingServers = [];
        if (existingChannel.servers_json) {
          try {
            existingServers = JSON.parse(existingChannel.servers_json);
          } catch (e) {
            existingServers = [{ name: 'Primary Server', url: existingChannel.stream_url }];
          }
        } else {
          existingServers = [{ name: 'Primary Server', url: existingChannel.stream_url }];
        }

        const urlExists = existingServers.some(s => s.url === stream_url);
        if (!urlExists) {
          existingServers.push({
            name: `Server ${existingServers.length + 1}`,
            url: stream_url
          });
          await dbRun(
            'UPDATE channels SET servers_json = ? WHERE id = ?',
            [JSON.stringify(existingServers), existingChannel.id]
          );
        }
        return res.json({ id: existingChannel.id, name, stream_url, logo_url, category_id, servers: existingServers, merged: true });
      }
    }

    const serversJson = servers ? JSON.stringify(servers) : JSON.stringify([{ name: 'Primary Server', url: stream_url }]);
    const result = await dbRun(
      'INSERT INTO channels (name, stream_url, logo_url, category_id, servers_json) VALUES (?, ?, ?, ?, ?)',
      [name, stream_url, logo_url || null, category_id || null, serversJson]
    );
    res.json({ id: result.insertId, name, stream_url, logo_url, category_id, servers });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

app.put('/api/channels/:id', authenticateToken, async (req, res) => {
  const { id } = req.params;
  const { name, stream_url, logo_url, category_id, servers } = req.body;
  if (!name || !stream_url) return res.status(400).json({ error: 'Name and stream_url are required' });

  const serversJson = servers ? JSON.stringify(servers) : JSON.stringify([{ name: 'Server 1', url: stream_url }]);

  try {
    await dbRun(
      'UPDATE channels SET name = ?, stream_url = ?, logo_url = ?, category_id = ?, servers_json = ? WHERE id = ?',
      [name, stream_url, logo_url, category_id, serversJson, id]
    );
    res.json({ id, name, stream_url, logo_url, category_id, servers });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

app.delete('/api/channels/:id', authenticateToken, async (req, res) => {
  try {
    await dbRun('DELETE FROM channels WHERE id = ?', [req.params.id]);
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

app.post('/api/channels/import', authenticateToken, async (req, res) => {
  const { m3u, playlist_url, playlist_name, merge } = req.body;
  if (!m3u && !playlist_url) {
    return res.status(400).json({ error: 'M3U content string or remote URL is required' });
  }

  let m3uContent = m3u || '';
  let finalPlaylistName = playlist_name || '';

  try {
    // 1. Fetch remote URL if provided
    if (playlist_url) {
      console.log('Fetching remote playlist from URL:', playlist_url);
      const fetchResponse = await fetch(playlist_url);
      if (!fetchResponse.ok) {
        throw new Error(`Failed to fetch remote M3U. HTTP Status: ${fetchResponse.status}`);
      }
      m3uContent = await fetchResponse.text();

      // Default playlist name to URL filename if empty
      if (!finalPlaylistName) {
        try {
          const urlObj = new URL(playlist_url);
          const pathSegments = urlObj.pathname.split('/');
          const filename = pathSegments[pathSegments.length - 1];
          finalPlaylistName = filename || urlObj.hostname;
        } catch (e) {
          finalPlaylistName = 'Remote Link Playlist';
        }
      }
    }

    if (!finalPlaylistName) {
      finalPlaylistName = 'Imported Playlist ' + new Date().toLocaleDateString();
    }

    // 2. Create playlist entry in database
    const playlistResult = await dbRun(
      'INSERT INTO playlists (name, url) VALUES (?, ?)',
      [finalPlaylistName, playlist_url || null]
    );
    const playlistId = playlistResult.insertId;

    // 3. Parse and import channels
    await importM3UContent(m3uContent, merge === true, playlistId);

    res.json({ success: true, message: 'Playlist imported successfully', playlistId, playlistName: finalPlaylistName });
  } catch (err) {
    res.status(500).json({ error: 'Parsing or importing error: ' + err.message });
  }
});

app.post('/api/channels/clear', authenticateToken, async (req, res) => {
  try {
    await dbRun('DELETE FROM channels');
    await dbRun('DELETE FROM categories');
    await dbRun('DELETE FROM playlists');
    res.json({ success: true, message: 'All channels, categories, and playlists cleared successfully' });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// --- PLAYLISTS API ---
app.get('/api/playlists', async (req, res) => {
  try {
    const rows = await dbQuery('SELECT * FROM playlists ORDER BY name ASC');
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

app.delete('/api/playlists/:id', authenticateToken, async (req, res) => {
  const { id } = req.params;
  try {
    // 1. Delete channels associated with this playlist
    await dbRun('DELETE FROM channels WHERE playlist_id = ?', [id]);
    
    // 2. Delete playlist itself
    await dbRun('DELETE FROM playlists WHERE id = ?', [id]);
    
    // 3. Garbage collect empty categories (categories that no longer have any channels)
    await dbRun('DELETE FROM categories WHERE id NOT IN (SELECT DISTINCT category_id FROM channels WHERE category_id IS NOT NULL)');

    res.json({ success: true, message: 'Playlist and its channels deleted successfully' });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});


// --- LIVE EVENTS API ---
app.get('/api/events', async (req, res) => {
  try {
    const rows = await dbQuery('SELECT * FROM live_events ORDER BY id DESC');
    const parsedRows = rows.map(row => ({
      ...row,
      servers: row.servers_json ? JSON.parse(row.servers_json) : []
    }));
    res.json(parsedRows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

app.post('/api/events', authenticateToken, async (req, res) => {
  const { title, league, team1_name, team1_logo, team2_name, team2_logo, start_time, status, category, servers } = req.body;
  if (!title) return res.status(400).json({ error: 'Event title is required' });

  const serversJson = servers ? JSON.stringify(servers) : JSON.stringify([]);

  try {
    const result = await dbRun(
      'INSERT INTO live_events (title, league, team1_name, team1_logo, team2_name, team2_logo, start_time, status, category, servers_json) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
      [title, league, team1_name, team1_logo, team2_name, team2_logo, start_time, status || 'Upcoming', category || 'Sports', serversJson]
    );
    res.json({ id: result.insertId, title, league, team1_name, team1_logo, team2_name, team2_logo, start_time, status, category, servers });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

app.put('/api/events/:id', authenticateToken, async (req, res) => {
  const { id } = req.params;
  const { title, league, team1_name, team1_logo, team2_name, team2_logo, start_time, status, category, servers } = req.body;
  
  const serversJson = servers ? JSON.stringify(servers) : JSON.stringify([]);

  try {
    await dbRun(
      'UPDATE live_events SET title = ?, league = ?, team1_name = ?, team1_logo = ?, team2_name = ?, team2_logo = ?, start_time = ?, status = ?, category = ?, servers_json = ? WHERE id = ?',
      [title, league, team1_name, team1_logo, team2_name, team2_logo, start_time, status, category, serversJson, id]
    );
    res.json({ id, title, league, team1_name, team1_logo, team2_name, team2_logo, start_time, status, category, servers });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

app.delete('/api/events/:id', authenticateToken, async (req, res) => {
  try {
    await dbRun('DELETE FROM live_events WHERE id = ?', [req.params.id]);
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});


// --- HIGHLIGHTS API ---
app.get('/api/highlights', async (req, res) => {
  try {
    const rows = await dbQuery('SELECT * FROM highlights ORDER BY id DESC');
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

app.post('/api/highlights', authenticateToken, async (req, res) => {
  const { title, league, team1_name, team1_logo, team2_name, team2_logo, score1, score2, video_url, category, date } = req.body;
  if (!title || !video_url) return res.status(400).json({ error: 'Title and video_url are required' });

  try {
    const result = await dbRun(
      'INSERT INTO highlights (title, league, team1_name, team1_logo, team2_name, team2_logo, score1, score2, video_url, category, date) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
      [title, league, team1_name, team1_logo, team2_name, team2_logo, score1 || '0', score2 || '0', video_url, category || 'All', date]
    );
    res.json({ id: result.insertId, title, league, team1_name, team1_logo, team2_name, team2_logo, score1, score2, video_url, category, date });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

app.put('/api/highlights/:id', authenticateToken, async (req, res) => {
  const { id } = req.params;
  const { title, league, team1_name, team1_logo, team2_name, team2_logo, score1, score2, video_url, category, date } = req.body;

  try {
    await dbRun(
      'UPDATE highlights SET title = ?, league = ?, team1_name = ?, team1_logo = ?, team2_name = ?, team2_logo = ?, score1 = ?, score2 = ?, video_url = ?, category = ?, date = ? WHERE id = ?',
      [title, league, team1_name, team1_logo, team2_name, team2_logo, score1, score2, video_url, category, date, id]
    );
    res.json({ id, title, league, team1_name, team1_logo, team2_name, team2_logo, score1, score2, video_url, category, date });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

app.delete('/api/highlights/:id', authenticateToken, async (req, res) => {
  try {
    await dbRun('DELETE FROM highlights WHERE id = ?', [req.params.id]);
    res.json({ success: true });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});


// --- SETTINGS API ---
app.get('/api/settings/:key', async (req, res) => {
  try {
    const rows = await dbQuery('SELECT * FROM settings WHERE `key` = ?', [req.params.key]);
    if (rows.length === 0) {
      return res.json({ key: req.params.key, value: '' });
    }
    res.json(rows[0]);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

app.post('/api/settings', authenticateToken, async (req, res) => {
  const { key, value } = req.body;
  if (!key) return res.status(400).json({ error: 'Key is required' });

  try {
    if (dbType === 'mysql') {
      await dbRun('INSERT INTO settings (`key`, `value`) VALUES (?, ?) ON DUPLICATE KEY UPDATE `value` = ?', [key, value, value]);
    } else {
      await dbRun('INSERT OR REPLACE INTO settings (`key`, `value`) VALUES (?, ?)', [key, value]);
    }
    res.json({ key, value });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// Serve Static Frontend Assets (Vite Production Build)
const distPath = path.join(__dirname, 'dist');
if (fs.existsSync(distPath)) {
  app.use(express.static(distPath));
  
  // Frontend routing fallback for SPA:
  app.get('/admin', (req, res) => {
    res.sendFile(path.join(distPath, 'admin.html'));
  });
  
  app.get('*', (req, res) => {
    res.sendFile(path.join(distPath, 'index.html'));
  });
} else {
  // Mock fallback route for dev environment
  app.get('/', (req, res) => {
    res.send('Vite Dev Server is active. Access client on http://localhost:5173');
  });
}

// Start Server after Database Initialization
initDatabase().then(() => {
  app.listen(PORT, () => {
    console.log(`Server listening on port ${PORT}...`);
  });
});
