<?php
  /**
  * Data abstraction layer for the firefly media server (sqlite generally)
  */
  class FireflyDb{
    function FireflyDb($path=""){
      if( $path != "" ) $this->path = $path;
      else $this->path = "/var/cache/mt-daapd/songs3.db";
      $this->dbtype = "sqlite";
      $this->sql = "";
      $this->pdostatement = false;
      $this->debug = false;
    }
    function setDb(){
      if( !$this->db ) $this->db = new PDO( $this->dbtype.":".$this->path );
    }
    function quote( $str ){
      $this->setDb();
      return $this->db->quote( $str );
    }
    function fetchRow(){
      // $pdos = $this->db->query( $this->sql );
      return $this->pdostatement->fetch();
    }
    function query( $sql ){
      $this->setDb();
      if( $this->debug ) echo $sql."\n";
      $this->pdostatement = $this->db->query( $sql );
      if( $this->debug ){
        if( !$this->pdostatement ) echo "ERROR: Query failed\n";
				print_r( $this->pdostatement->errorInfo() );
      }
			if( !$this->pdostatement ) return false;
			return true;
    }
    function fetchOne( $sql ){
      $this->query( $sql );
      return $this->fetchRow();
    }

    /**
    * Set any track IDs in the array to play_count = 1 if they are 0
    */
    function markTracksPlayed( $aTracks ){
      if( sizeof( $aTracks ) == 0 ) return false;
      $sql = "UPDATE songs SET play_count = 1 WHERE play_count = 0 AND id IN (".join(", ", $aTracks ).")";
      return $this->query( $sql );
    }
    function insertPlaylist( $name ){
      /*
          * 0 - Static playlist, updated via the web interface
          * 1 - Smart playlists (SQL query)
          * 2 - Static playlist, from a m3u file (can't be edited from the web interface)
          * 3 - Static playlist, from iTunes XML file (can't be edited from the web interface) 
      */
      $sql = "INSERT INTO playlists ( title, type, items, db_timestamp, idx ) VALUES ( '".$name."', 0, 0, 0, 0 )";
      $this->query( $sql );
      return $this->db->lastInsertId();
    }
    function getUnplayedAlbums( $limit=10 ){
        $limit = intval( $limit );
        $sql = "
          select time_added, album, artist, path 
        from songs where play_count = 0 
          and path not like '%_podcasts%' 
          and path not like '%_odds_and_sods%'
          and album not like '' 
          and artist not like ''
          and path like '%share/music/%/%/%/%'
        group by album 
        order by time_added desc, artist, album 
        limit $limit;";
      // Output the folder location of each of the last 5 albums - plain text only is needed
      $aAlbums = array();
      $this->query( $sql );
      while( $row = $this->fetchRow() ){
        $row["path"] = dirname( $row["path"] );
        $aAlbums[] = $row;
      }
      return $aAlbums;
    }

    function getTrackByPath( $path ){
      $this->setDb();
      $sql = "SELECT * FROM songs WHERE path LIKE ".$this->db->quote( $path );
      $this->query( $sql );
      return $this->fetchRow();
    }
    
    function getTrackId( $name, $artist, $album=""){
      $a = $this->getTrack( $name, $artist, $album="" );
      if( !$a ) return false;
      if( !$a["id"] ) return false;
      return $a["id"];
    }
    function getTrack( $name, $artist, $album="" ){
      $this->setDb();
      if( $this->debug ) echo "getTrack( $name, $artist, $album )\n";
      $sql = "SELECT * FROM songs WHERE title LIKE ".$this->db->quote( $name )." AND artist LIKE ".$this->db->quote( $artist );
      if( $album != "" ) $sql .= " AND album LIKE ".$this->db->quote( $album );
      $this->query( $sql );
      $a = $this->fetchRow();

      // Try without "the" if it contains "the"
      if( !is_array( $a ) || !isset( $a["id"] ) ){
        if( preg_match( "/^the (.+)/i", $artist, $m ) ){
          return $this->getTrack( $name, $m[1] );
        }
      }
      
      //Check for accented characters
      $hasaccents = false;
      for($pos=strlen($name.$artist.$album)-1; $pos >= 0 ; $pos--){ 
        $char = substr($name.$artist.$album, $pos, 1); 
        if (ord($char) > 127){
          $hasaccents = true;
          break;
        }
      }
      if( $hasaccents ){
        $trname = iconv("utf-8","ascii//TRANSLIT",$name);
        $trartist = iconv("utf-8","ascii//TRANSLIT",$artist);
        $tralbum = iconv("utf-8","ascii//TRANSLIT",$album);
        return $this->getTrack( $trname, $trartist, $tralbum );
      }
      

      // Try without album name
      if( !is_array( $a ) || !isset( $a["id"] ) ){ 
        if( $album == "" ) return false;
        return $this->getTrack( $name, $artist );
      }
      return $a;
    }
    function addTrackToPlaylist( $trackid, $playlistid ){
      $sql = "INSERT INTO playlistitems ( songid, playlistid ) VALUES ( ".intval( $trackid ).", ".intval( $playlistid )." ) ";
      if( $this->debug ) echo $sql."\n";
      return $this->query( $sql );
    }
    function getPlaylistIdByName( $name ){
      $this->setDb();
      $this->query( "SELECT id FROM playlists WHERE title LIKE ".$this->db->quote( $name ) );
      $a = $this->fetchRow();
      if( !is_array( $a ) || !isset( $a["id"] ) ) return false;
      else return $a["id"];
    }
    function getPlaylists($type=0){
      $aReturn = array();
      $sql = "SELECT id, title FROM playlists WHERE type = ".intval($type);
      $this->query( $sql );
      while( $row = $this->fetchRow() ){
        $aReturn[$row["id"]] = $row["title"];
      }
      return $aReturn;
    }
    function getLatestTracksInDir( $dir, $limit=50 ){
      $this->setDb();
      $sql = "
        SELECT id, artist, album, title, song_length, time_added, path
        FROM songs
        WHERE path LIKE ".$this->db->quote($dir."%")."
          AND play_count = 0
        ORDER BY time_added DESC
        LIMIT ".intval($limit)."
      ";
      $this->query( $sql );
      $aReturn = array();
      while( $row = $this->fetchRow() ){
        $aReturn[] = $row;
      }
      return $aReturn;
    }
    function getPlaylistItemsByName( $name ){
      $id = $this->getPlaylistIdByName( $name );
      if( !$id ) return false;
      $sql = "
        SELECT s.id, artist, album, title, song_length, time_added, path
        FROM playlistitems pi
        INNER JOIN songs s ON s.id = pi.songid
        WHERE pi.playlistid = ".intval( $id )."
        ORDER BY pi.id ASC";
      $this->query( $sql );
      $aReturn = array();
      while( $row = $this->fetchRow() ){
        $aReturn[] = $row;
      }
      return $aReturn;
    }
    function clearPlaylist( $id ){
      $sql = "DELETE FROM playlistitems WHERE playlistid = ".intval( $id );
      $this->query( $sql );
    }
    function deletePlaylist( $name ){
      $playlistid = $this->getPlaylistIdByName( $name );
      if( $playlistid === false ) return false;
      $this->clearPlaylist( $playlistid );
      $this->query( "DELETE FROM playlists WHERE id = ".intval( $playlistid ) );
      return true;
    }
    function savePlaylist( $name, $aTrackIds ){
      $playlistid = $this->getPlaylistIdByName( $name );
      if( $playlistid === false ) $playlistid = $this->insertPlaylist( $name );
      else $this->clearPlaylist( $playlistid );
      $sql = "UPDATE playlists SET items = ".intval(sizeof($aTrackIds))." WHERE id = ".intval($playlistid);
      $this->query($sql);
      foreach( $aTrackIds as $id ){
        $this->addTrackToPlaylist( $id, $playlistid );
      }
    }
  }
?>