Church-Music/new-site/backend/routes/songs.js

const express = require("express");
const router = express.Router();
const { query } = require("../db");
const { v4: uuidv4 } = require("uuid");
const {
  success,
  error,
  notFound,
  badRequest,
} = require("../utils/responseHandler");
const {
  buildWhereClause,
  buildPagination,
  buildSearchCondition,
} = require("../utils/queryBuilder");
const { authenticate } = require("../middleware/auth");

// Common SQL fragment
const SELECT_SONG_FIELDS = "SELECT *, chords as key_chord FROM songs";

// GET search songs (for worship list song picker)
router.get("/search", async (req, res) => {
  try {
    const { q } = req.query;

    if (!q || q.trim() === "") {
      return success(res, { songs: [], total: 0 });
    }

    const searchTerm = `%${q.toLowerCase()}%`;
    const searchCondition = buildSearchCondition(
      searchTerm,
      ["title", "artist", "singer"],
      1,
    );

    const result = await query(
      `${SELECT_SONG_FIELDS} 
       WHERE ${searchCondition}
       ORDER BY title ASC LIMIT 20`,
      [searchTerm],
    );

    success(res, { songs: result.rows, total: result.rowCount });
  } catch (err) {
    error(res, "Failed to search songs");
  }
});

// GET all songs
router.get("/", async (req, res) => {
  try {
    const { search, artist, band, limit = 100, offset = 0 } = req.query;
    const params = [];
    const conditions = [];

    if (search) {
      params.push(`%${search.toLowerCase()}%`);
      conditions.push(
        `(LOWER(title) LIKE $${params.length} OR LOWER(lyrics) LIKE $${params.length})`,
      );
    }

    if (artist) {
      params.push(`%${artist.toLowerCase()}%`);
      conditions.push(`LOWER(artist) LIKE $${params.length}`);
    }

    if (band) {
      params.push(`%${band.toLowerCase()}%`);
      conditions.push(`LOWER(band) LIKE $${params.length}`);
    }

    const whereClause = buildWhereClause(conditions);
    const { clause: paginationClause, params: paginationParams } =
      buildPagination(limit, offset, params.length + 1);

    const result = await query(
      `${SELECT_SONG_FIELDS}${whereClause} ORDER BY title ASC${paginationClause}`,
      [...params, ...paginationParams],
    );

    success(res, { songs: result.rows, total: result.rowCount });
  } catch (err) {
    error(res, "Failed to fetch songs");
  }
});

// GET single song by ID
router.get("/:id", async (req, res) => {
  try {
    const result = await query(`${SELECT_SONG_FIELDS} WHERE id = $1`, [
      req.params.id,
    ]);

    if (result.rows.length === 0) {
      return notFound(res, "Song");
    }

    success(res, { song: result.rows[0] });
  } catch (err) {
    error(res, "Failed to fetch song");
  }
});

// POST create new song
router.post("/", authenticate, async (req, res) => {
  try {
    const { title, artist, band, singer, lyrics, chords, key_chord, memo } =
      req.body;

    if (!title) {
      return badRequest(res, "Title is required");
    }

    const id = uuidv4();
    const now = Math.floor(Date.now() / 1000);
    const chordsValue = chords || key_chord || "";

    const result = await query(
      `INSERT INTO songs (id, title, artist, band, singer, lyrics, chords, memo, created_at, updated_at)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
       RETURNING *, chords as key_chord`,
      [
        id,
        title,
        artist || "",
        band || "",
        singer || "",
        lyrics || "",
        chordsValue,
        memo || "",
        now,
        now,
      ],
    );

    success(res, { song: result.rows[0] }, 201);
  } catch (err) {
    error(res, "Failed to create song");
  }
});

// PUT update song
router.put("/:id", authenticate, async (req, res) => {
  try {
    const { title, artist, band, singer, lyrics, chords, key_chord, memo } =
      req.body;
    const now = Math.floor(Date.now() / 1000);
    const chordsValue = chords || key_chord;

    const result = await query(
      `UPDATE songs 
       SET title = COALESCE($1, title),
           artist = COALESCE($2, artist),
           band = COALESCE($3, band),
           singer = COALESCE($4, singer),
           lyrics = COALESCE($5, lyrics),
           chords = COALESCE($6, chords),
           memo = COALESCE($7, memo),
           updated_at = $8
       WHERE id = $9
       RETURNING *, chords as key_chord`,
      [
        title,
        artist,
        band,
        singer,
        lyrics,
        chordsValue,
        memo,
        now,
        req.params.id,
      ],
    );

    if (result.rows.length === 0) {
      return notFound(res, "Song");
    }

    success(res, { song: result.rows[0] });
  } catch (err) {
    error(res, "Failed to update song");
  }
});

// DELETE song
router.delete("/:id", authenticate, async (req, res) => {
  try {
    const result = await query("DELETE FROM songs WHERE id = $1 RETURNING id", [
      req.params.id,
    ]);

    if (result.rows.length === 0) {
      return notFound(res, "Song");
    }

    success(res, { message: "Song deleted" });
  } catch (err) {
    error(res, "Failed to delete song");
  }
});

// GET song count
router.get("/stats/count", async (req, res) => {
  try {
    const result = await query("SELECT COUNT(*) as count FROM songs");
    success(res, { count: parseInt(result.rows[0].count) });
  } catch (err) {
    error(res, "Failed to count songs");
  }
});

module.exports = router;