import { pool } from '../config/db.js';

const SELECT_ALL = `
  SELECT s.id, s.title, s.artist, s.album, s.artwork_url, s.spotify_track_id, s.spotify_url,
         s.apple_music_url, s.apple_match_score, s.audiomack_url, s.boomplay_url,
         s.slug, s.status, s.release_date,
         s.meta_pixel_id, s.tiktok_pixel_id, s.custom_head_script,
         s.created_by, a.name AS published_by, s.published_at, s.created_at, s.updated_at
  FROM songs s
  LEFT JOIN admins a ON a.id = s.created_by
`;

export const Song = {
  async findAll({ status } = {}) {
    let sql = `${SELECT_ALL} `;
    const params = {};
    if (status) {
      sql += 'WHERE s.status = :status ';
      params.status = status;
    }
    sql += 'ORDER BY s.created_at DESC';
    const [rows] = await pool.query(sql, params);
    return rows;
  },

  async findById(id) {
    const [rows] = await pool.query(`${SELECT_ALL} WHERE s.id = :id LIMIT 1`, { id });
    return rows[0] || null;
  },

  async findBySlug(slug) {
    const [rows] = await pool.query(`${SELECT_ALL} WHERE s.slug = :slug LIMIT 1`, { slug });
    return rows[0] || null;
  },

  async findBySpotifyId(spotify_track_id) {
    const [rows] = await pool.query(
      `${SELECT_ALL} WHERE s.spotify_track_id = :spotify_track_id LIMIT 1`,
      { spotify_track_id }
    );
    return rows[0] || null;
  },

  async slugExists(slug, excludeId = null) {
    const params = { slug };
    let sql = 'SELECT id FROM songs WHERE slug = :slug';
    if (excludeId) {
      sql += ' AND id != :excludeId';
      params.excludeId = excludeId;
    }
    const [rows] = await pool.query(sql, params);
    return rows.length > 0;
  },

  async create(data) {
    const [result] = await pool.query(
      `INSERT INTO songs
        (title, artist, album, artwork_url, spotify_track_id, spotify_url,
         apple_music_url, apple_match_score, audiomack_url, boomplay_url,
         slug, status, release_date,
         meta_pixel_id, tiktok_pixel_id, custom_head_script,
         created_by, published_at)
       VALUES
        (:title, :artist, :album, :artwork_url, :spotify_track_id, :spotify_url,
         :apple_music_url, :apple_match_score, :audiomack_url, :boomplay_url,
         :slug, :status, :release_date,
         :meta_pixel_id, :tiktok_pixel_id, :custom_head_script,
         :created_by, :published_at)`,
      {
        title: data.title,
        artist: data.artist,
        album: data.album ?? null,
        artwork_url: data.artwork_url,
        spotify_track_id: data.spotify_track_id,
        spotify_url: data.spotify_url,
        apple_music_url: data.apple_music_url ?? null,
        apple_match_score: data.apple_match_score ?? null,
        audiomack_url: data.audiomack_url ?? null,
        boomplay_url: data.boomplay_url ?? null,
        slug: data.slug,
        status: data.status ?? 'draft',
        release_date: data.release_date ?? null,
        meta_pixel_id: data.meta_pixel_id ?? null,
        tiktok_pixel_id: data.tiktok_pixel_id ?? null,
        custom_head_script: data.custom_head_script ?? null,
        created_by: data.created_by ?? null,
        published_at: data.status === 'published' ? new Date() : null,
      }
    );
    return this.findById(result.insertId);
  },

  async update(id, data) {
    const existing = await this.findById(id);
    if (!existing) return null;

    const nextStatus = data.status ?? existing.status;
    const published_at =
      nextStatus === 'published'
        ? existing.published_at ?? new Date()
        : nextStatus === 'draft'
          ? null
          : existing.published_at;

    await pool.query(
      `UPDATE songs SET
        title = :title,
        artist = :artist,
        album = :album,
        artwork_url = :artwork_url,
        spotify_track_id = :spotify_track_id,
        spotify_url = :spotify_url,
        apple_music_url = :apple_music_url,
        apple_match_score = :apple_match_score,
        audiomack_url = :audiomack_url,
        boomplay_url = :boomplay_url,
        slug = :slug,
        status = :status,
        release_date = :release_date,
        meta_pixel_id = :meta_pixel_id,
        tiktok_pixel_id = :tiktok_pixel_id,
        custom_head_script = :custom_head_script,
        published_at = :published_at
       WHERE id = :id`,
      {
        id,
        title: data.title ?? existing.title,
        artist: data.artist ?? existing.artist,
        album: data.album ?? existing.album,
        artwork_url: data.artwork_url ?? existing.artwork_url,
        spotify_track_id: data.spotify_track_id ?? existing.spotify_track_id,
        spotify_url: data.spotify_url ?? existing.spotify_url,
        apple_music_url:
          data.apple_music_url !== undefined ? data.apple_music_url : existing.apple_music_url,
        apple_match_score:
          data.apple_match_score !== undefined
            ? data.apple_match_score
            : existing.apple_match_score,
        audiomack_url:
          data.audiomack_url !== undefined ? data.audiomack_url : existing.audiomack_url,
        boomplay_url: data.boomplay_url !== undefined ? data.boomplay_url : existing.boomplay_url,
        slug: data.slug ?? existing.slug,
        status: nextStatus,
        release_date: data.release_date !== undefined ? data.release_date : existing.release_date,
        meta_pixel_id:
          data.meta_pixel_id !== undefined ? data.meta_pixel_id : existing.meta_pixel_id,
        tiktok_pixel_id:
          data.tiktok_pixel_id !== undefined ? data.tiktok_pixel_id : existing.tiktok_pixel_id,
        custom_head_script:
          data.custom_head_script !== undefined
            ? data.custom_head_script
            : existing.custom_head_script,
        published_at,
      }
    );
    return this.findById(id);
  },

  async delete(id) {
    const [result] = await pool.query('DELETE FROM songs WHERE id = :id', { id });
    return result.affectedRows > 0;
  },
};
