<?php

   error_reporting(E_ALL);
   ini_set('display_errors', 1);

   include_once "pdo_connection.php";

   if(isset($_REQUEST["JOB"])) {
       if($_REQUEST["JOB"] == "LETTERS") getLetters();
       if($_REQUEST["JOB"] == "ARTISTS") getArtists();
       if($_REQUEST["JOB"] == "ADD-A") addArtist();
       if($_REQUEST["JOB"] == "EDIT-A") updateArtist();
       if($_REQUEST["JOB"] == "DELETE-A") deleteArtist();
       if($_REQUEST["JOB"] == "SONGS") getSongs();
       if($_REQUEST["JOB"] == "SONG") getSong();
       if($_REQUEST["JOB"] == "ADD-S") addSong();
       if($_REQUEST["JOB"] == "EDIT-S") updateSong();
       if($_REQUEST["JOB"] == "DELETE-S") deleteSong();
       if($_REQUEST["JOB"] == "SAVE") saveSong();
   }


// -------------------------------------------------------------------------------------- LETTERS

   function getLetters() {
   
      $sql  = "SELECT Letter AS L, Active AS A FROM letter";
      $stmt = $GLOBALS["pdo"]->prepare($sql);
      $stmt->execute();

      echo json_encode($stmt->fetchAll(), JSON_PRETTY_PRINT);

   }

// -------------------------------------------------------------------------------------- ARTISTS

   function getArtists($status = "OK", $SelectID = 0) {

      $sql  = "SELECT artist.ID, artist.Name, COUNT(song.ID) AS TotalSongs FROM artist LEFT JOIN song ON (artist.ID = song.Artist_ID) WHERE artist.Name LIKE ? GROUP BY artist.ID, artist.Name ORDER BY artist.Name";
      $stmt = $GLOBALS["pdo"]->prepare($sql);
      $stmt->execute(array($_REQUEST["FirstLetter"]."%"));

      $output = array("Status"=>$status, "Rows" => $stmt->rowCount(), "Artists" => $stmt->fetchAll(), "SelectID" => $SelectID);

      echo json_encode($output, JSON_PRETTY_PRINT);

   }

   function deleteArtist() {

      if(haveSongs()) {
          echo json_encode(array("Status"=>"HAS-SONGS"));
          return;
      }

      $sql  = "DELETE FROM artist WHERE ID = ?";
      $stmt = $GLOBALS["pdo"]->prepare($sql);
      $stmt->execute(array($_REQUEST["ID"]));

      if($stmt->rowCount() == 0) echo json_encode(array("Status"=>"ERROR"));
      else getArtists("OK", 0);

   }

   function updateArtist() {

      if(artistExists()) {
          echo json_encode(array("Status"=>"DUPLICATE"));
          return;
      }

      $sql  = "UPDATE artist SET Name = ? WHERE ID = ?";
      $stmt = $GLOBALS["pdo"]->prepare($sql);
      $stmt->execute(array($_REQUEST["ARTIST"], $_REQUEST["ID"]));

      if($stmt->rowCount() == 0) echo json_encode(array("Status"=>"ERROR", "Reason"=>$stmt));
      else getArtists("OK", $_REQUEST["ID"]);

   }

   function addArtist() {

      if(artistExists()) {
          echo json_encode(array("Status"=>"DUPLICATE"));
          return;
      }

      $sql  = "INSERT INTO artist SET Name = ?";
      $stmt = $GLOBALS["pdo"]->prepare($sql);
      $stmt->execute(array($_REQUEST["ARTIST"]));

      if($GLOBALS["pdo"]->lastInsertId() == 0) echo json_encode(array("Status"=>"ERROR"));
      else getArtists("OK", $GLOBALS["pdo"]->lastInsertId());

   }

   function artistExists() {
      $sql  = "SELECT ID FROM artist WHERE Name = ?";
      $stmt = $GLOBALS["pdo"]->prepare($sql);
      $stmt->execute(array($_REQUEST["ARTIST"]));
      if($stmt->rowCount() > 0) return true;
      else return false;
   }

   function haveSongs() {
      $sql  = "SELECT ID FROM song WHERE Artist_ID = ? LIMIT 1";
      $stmt = $GLOBALS["pdo"]->prepare($sql);
      $stmt->execute(array($_REQUEST["ID"]));
      if($stmt->rowCount() > 0) return true;
      else return false;
   }

// ---------------------------------------------------------------------------------------- SONGS

   function saveSong() {

      $sql  = "UPDATE song SET Song = ? WHERE ID = ?";
      $stmt = $GLOBALS["pdo"]->prepare($sql);
      $stmt->execute(array($_REQUEST["Data"], $_REQUEST["ID"]));

      if($stmt->rowCount() == 0) echo json_encode(array("Status"=>"ERROR"));
      else echo json_encode(array("Status" => "OK"));

   }

   function getSong() {

      $sql  = "SELECT song.ID, song.Title, song.Song AS Tune, artist.Name AS Artist, YTID FROM song INNER JOIN artist ON song.Artist_ID = artist.ID WHERE song.ID = ? LIMIT 1";
      $stmt = $GLOBALS["pdo"]->prepare($sql);
      $stmt->execute(array($_REQUEST["ID"]));

      $songData = $stmt->fetchAll();

      if((trim($songData[0]["Tune"])) == "") $songData[0]["Tune"] = "[[0]]";

      if($stmt->rowCount() == 0) echo json_encode(array("Status"=>"ERROR"));
      else echo json_encode(array("Status" => "OK", "Data" => $songData));

   }

   function getSongs($status = "OK") {

      $sql  = "SELECT ID, Title, YTID FROM song WHERE Artist_ID = ? ORDER BY Title";
      $stmt = $GLOBALS["pdo"]->prepare($sql);
      $stmt->execute(array($_REQUEST["ARTISTID"]));

      $output = array("Status"=>$status, "Rows" => $stmt->rowCount(), "Songs" => $stmt->fetchAll());

      echo json_encode($output);
   }

   function deleteSong() {

      $sql  = "DELETE FROM song WHERE ID = ?";
      $stmt = $GLOBALS["pdo"]->prepare($sql);
      $stmt->execute(array($_REQUEST["ID"]));

      if($stmt->rowCount() == 0) echo json_encode(array("Status"=>"ERROR"));
      else getSongs("OK");

   }

   function updateSong() {

      /*
      if(songExists()) {
          echo json_encode(array("Status"=>"DUPLICATE"));
          return;
      }
      */

      $sql  = "UPDATE song SET Title = ?, YTID = ? WHERE ID = ?";
      $stmt = $GLOBALS["pdo"]->prepare($sql);
      $stmt->execute(array($_REQUEST["TITLE"], $_REQUEST["YTID"], $_REQUEST["ID"]));

      if($stmt->rowCount() == 0) echo json_encode(array("Status"=>"ERROR"));
      else getSongs("OK");

   }

   function addSong() {

      if(songExists()) {
          echo json_encode(array("Status"=>"DUPLICATE"));
          return;
      }

      $sql  = "INSERT INTO song SET Artist_ID = ?, Title = ?, YTID = ?";
      $stmt = $GLOBALS["pdo"]->prepare($sql);
      $stmt->execute(array($_REQUEST["ARTISTID"], $_REQUEST["TITLE"], $_REQUEST["YTID"]));

      if($GLOBALS["pdo"]->lastInsertId() == 0) echo json_encode(array("Status"=>"ERROR"));
      else getSongs("OK");

   }

   function songExists() {
      $sql  = "SELECT ID FROM song WHERE Artist_ID = ? AND Title = ? LIMIT 1";
      $stmt = $GLOBALS["pdo"]->prepare($sql);
      $stmt->execute(array($_REQUEST["ARTISTID"], $_REQUEST["TITLE"]));
      if($stmt->rowCount() > 0) return true;
      else return false;
   }

?>