<?php
  /**
  * Data abstraction layer for Boxee
  */
  class BoxeeDb{
    function BoxeeDb($path=""){
      if( $path != "" ) $this->path = $path;
      else $this->path = "~/.boxee";
      $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 );
      $row = $this->fetchRow();
      if( !$row ) return false;
      return $row;
    }
    function getSeriesIdByName($name){
      if( $this->debug ) echo "  Getting show ID for $name\n";
      $sql = "SELECT idSeries, strBoxeeId FROM series WHERE strTitle LIKE ".$this->quote( $name );
      $row = $this->fetchOne( $sql );
      if( isset( $row["idSeries"] ) ){ 
        if( $this->debug ) echo "  ID: ".$row["idSeries"]."\n";
        return $row;
      }
      return false;
    }
    function insertShow( $name ){
      echo "  Inserting show: $name\n";
      $boxeeid = $this->getNewBoxeeId();
      $sql = "INSERT INTO series ( strTitle, strBoxeeId ) VALUES (".$this->quote($name).", ".$this->quote($boxeeid)." );";
      $this->query( $sql );
      return array( 
        "idSeries" => $this->db->lastInsertId(),
        "strBoxeeId" => $boxeeid
      );
    }
    function getSeasonId( $seriesid, $seasonnum ){
      $sql = "SELECT idSeason FROM seasons WHERE idSeries = ".intval( $seriesid )." AND iSeasonNum = ".intval( $seasonnum ).";";
      $row = $this->fetchOne( $sql );
      if( isset( $row["idSeason"] ) ) return $row["idSeason"];
      return false;
    }
    function insertSeason( $seriesid, $seasonnum ){
      $sql = "INSERT INTO seasons ( idSeries, iSeasonNum ) VALUES ( ".intval( $seriesid ).", ".intval( $seasonnum )." );";
      $this->query( $sql );
      return $this->db->lastInsertId();
    }

    function insertEpisode($path, $name, $season, $episode, $title="" ){
     
      if( $this->debug ) echo "  Inserting $path, $name, $season, $episode...\n";

      // Show ID
      $aSeries = $this->getSeriesIdByName( $name );
      if( !$aSeries ){ 
        $aSeries = $this->insertShow( $name );
      }
      $seriesid = $aSeries["idSeries"];

      // Season ID
      $seasonid = $this->getSeasonId( $seriesid, $season );
      if( !$seasonid ) $seasonid = $this->insertSeason( $seriesid, $season );
      
      $boxeeid = $this->getNewBoxeeId();
      if( $title != "" ){
        $name .= ": ".$title;
      }

      // Check record exists in media_folders
      $folder = dirname( $path )."/";
      $sql = "SELECT * FROM media_folders WHERE strPath = ".$this->quote( $folder );
      if( !$this->fetchOne( $sql ) ){
        $date  = time();
        $sql = "INSERT INTO media_folders ( strPath, strType, iMetadataId, strStatus, iDateStatus, iHasMetadata, iDateAdded, iDateModified ) VALUES ( 
          ".$this->quote( $folder ).",
          'videoFolder',
          -1,
          'resolved',
          $date,
          1,
          $date,
          $date 
          );";
        $this->query( $sql );
      }

      // Insert episode
      $sql = "INSERT INTO video_files ( strTitle, strPath, strSeriesId, iSeason, iEpisode, strDescription, idFile, idFolder, iDuration, iReleaseDate, iYear, iDateAdded, iHasMetadata, iRating, iPopularity, iDateModified, iDropped ) VALUES ( 
        ".$this->quote($name).",
        ".$this->quote($path).",
        ".$this->quote($aSeries["strBoxeeId"]).",
        ".intval( $season ).",
        ".intval( $episode ).",
        ".$this->quote( $title ).",
        1, 0, 0, 0, 0, ".time().", 1, 0, 0, 0, 0
        );";
      $this->query( $sql );
    }
    function getNewBoxeeId(){
      $sql = "select series.strBoxeeId as strBoxeeId FROM series UNION SELECT video_files.strBoxeeId as strBoxeeId FROM video_files ORDER BY strBoxeeId DESC limit 1;";
      $row = $this->fetchOne( $sql );
      if( $this->debug ) print_r( $row );
      if( isset( $row["strBoxeeId"] ) ) return $row["strBoxeeId"];
      return false;
    }
    function getWatchedEpisodePaths($name){
      $sql = "SELECT strPath FROM watched WHERE strPath LIKE ".$this->quote("%/".$name."/%").";";
      $this->query( $sql );
      $aPaths = array();
      while( $row = $this->fetchRow() ){
        $aPaths[] = $row["strPath"];
      }
      return $aPaths;
    }
    function removeEpisode($path){
      $sql = "DELETE FROM video_files WHERE strPath = ".$this->quote($path).";";
      $this->query($sql);
    }
  }
?>