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

class Video {
  static async getAll() {
    const [rows] = await db.query('SELECT * FROM videos ORDER BY publishDate DESC, created_at DESC');
    return rows;
  }

  static async getByIdOrVideoId(idOrVideoId) {
    // If it's a number, try ID first
    if (/^\d+$/.test(idOrVideoId)) {
      const [rows] = await db.query('SELECT * FROM videos WHERE id = ?', [idOrVideoId]);
      if (rows.length > 0) return rows[0];
    }
    // Otherwise or if not found, try videoId / youtubeId
    const [rows] = await db.query('SELECT * FROM videos WHERE videoId = ? OR youtubeId = ?', [idOrVideoId, idOrVideoId]);
    return rows.length > 0 ? rows[0] : null;
  }

  static async getLatest(limit = 5) {
    const [rows] = await db.query('SELECT * FROM videos ORDER BY publishDate DESC, created_at DESC LIMIT ?', [Number(limit)]);
    return rows;
  }

  static async create({ title, speaker, category, youtubeId, videoId, duration, description, isShort, thumbnail, publishDate, videoUrl }) {
    const finalVideoId = videoId || youtubeId;
    const finalYoutubeId = youtubeId || videoId;
    const finalVideoUrl = videoUrl || (finalVideoId ? `https://www.youtube.com/watch?v=${finalVideoId}` : null);
    const finalThumbnail = thumbnail || (finalVideoId ? `https://img.youtube.com/vi/${finalVideoId}/hqdefault.jpg` : null);

    const [result] = await db.query(
      'INSERT INTO videos (title, speaker, category, youtubeId, videoId, duration, description, isShort, thumbnail, publishDate, videoUrl) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
      [
        title,
        speaker || 'Admin Added',
        category || 'Meditation',
        finalYoutubeId,
        finalVideoId,
        duration || null,
        description || null,
        isShort ? 1 : 0,
        finalThumbnail,
        publishDate || new Date(),
        finalVideoUrl
      ]
    );
    return {
      id: result.insertId,
      title,
      speaker: speaker || 'Admin Added',
      category: category || 'Meditation',
      youtubeId: finalYoutubeId,
      videoId: finalVideoId,
      duration,
      description,
      isShort: !!isShort,
      thumbnail: finalThumbnail,
      publishDate: publishDate || new Date(),
      videoUrl: finalVideoUrl
    };
  }

  static async upsert({ videoId, title, description, thumbnail, publishDate, videoUrl, category, speaker, duration, isShort }) {
    const finalYoutubeId = videoId;
    const finalVideoUrl = videoUrl || `https://www.youtube.com/watch?v=${videoId}`;
    const finalThumbnail = thumbnail || `https://img.youtube.com/vi/${videoId}/hqdefault.jpg`;
    
    const [result] = await db.query(
      `INSERT INTO videos 
        (videoId, youtubeId, title, description, thumbnail, publishDate, videoUrl, category, speaker, duration, isShort) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
       ON DUPLICATE KEY UPDATE 
        title = VALUES(title),
        description = VALUES(description),
        thumbnail = VALUES(thumbnail),
        publishDate = VALUES(publishDate),
        videoUrl = VALUES(videoUrl),
        category = COALESCE(VALUES(category), category),
        speaker = COALESCE(VALUES(speaker), speaker),
        duration = COALESCE(VALUES(duration), duration),
        isShort = VALUES(isShort)`,
      [
        videoId,
        finalYoutubeId,
        title,
        description || null,
        finalThumbnail,
        publishDate || null,
        finalVideoUrl,
        category || 'Meditation',
        speaker || 'Vedant Devotions',
        duration || null,
        isShort ? 1 : 0
      ]
    );
    return result.affectedRows > 0;
  }

  static async delete(id) {
    const [result] = await db.query('DELETE FROM videos WHERE id = ? OR videoId = ?', [id, id]);
    return result.affectedRows > 0;
  }
}

module.exports = Video;
