<?php

/**
 * AlbumTable
 *
 * This class has been auto-generated by the Doctrine ORM Framework
 */
class AlbumTable extends Doctrine_Table
{
  /**
   * Returns an instance of this class.
   *
   * @return object AlbumTable
   */
  public static function getInstance()
  {
      return Doctrine_Core::getTable('Album');
  }
  
  /**
   * Add an Album to the database or get its key if it exists
   * @param name str: the name of the album to add
   * @return     int: the primary key added or found
   */
  public function addAlbum( $name )
  {
    //is this name already in the collection?
    $q = Doctrine_Query::create()
      ->select( 'a.id' )
      ->from( 'Album a' )
      ->where( 'a.name = ?', $name);
    $result = $q->fetchOne();
     
    if ( is_object( $result ) && $result->id > 0 )
    {
      $retId = $result->id;
      unset($q, $result);
      return (int) $retId;
    }
    else
    {
      $item = new Album();
      $item->name = $name;
      if( $name == 'Unknown Album' )
      {
        $item->has_art = 1; //there's no point scanning an unknown album for art
      }
      $item->scan_id = 1;
      $item->save();
      $id = $item->getId();
      $item->free();
      unset($item, $q, $result);
      
      return (int) $id;
    }
  }
  
  /**
   * Fetch the album list
   * @param alpha str: the alphabetical grouping
   * @return      array: of all album entries
   */
  public function getList( $alpha = 'all', $artist_id = 'all')
  {
    $q = Doctrine_Query::create()
      ->select( 'a.id, a.name' )
      ->from( 'Album a, Song s' )
      ->where( 'a.id = s.album_id' );
    if( $alpha !== 'all' )
    {
      $q->andWhere( 'upper( a.name ) LIKE ?', strtoupper( substr( $alpha, 0, 1 ) ) . '%' );
    }
    if( $artist_id !== 'all' )
    {
      $q->andWhere( 's.artist_id = ?', $artist_id );
    }
    $q->distinct();
    $q->orderBy( 'a.name ASC' );
    $tmp = $q->fetchArray();
    foreach($tmp as $key => $value)
    {
      $tmp[$key]['id'] = (int) $value['id'];
    }
    
    return $tmp;
  }
  
  /**
   *  return a pair of artist and album for scanning - skip previously flagged scans
   *  @param source  str: the art source: amazon|meta|folders|service
   *  @return        array: artist and song names
   */
  public function getUnscannedArtList( $source )
  {
    $query  = 'SELECT DISTINCT ';
    $query .= ' album.id as album_id, album.name as album_name, artist.name as artist_name, song.filename as song_filename ';
    $query .= 'FROM ';
    $query .= ' song ';
    $query .= 'LEFT JOIN ';
    $query .= ' album ON song.album_id = album.id ';
    $query .= 'LEFT JOIN ';
    $query .= ' artist ON song.artist_id = artist.id ';
    $query .= 'WHERE ';
    $query .= ' album.id IS NOT NULL ';
    switch ( $source )
    {
      case 'amazon':
        $query .= ' AND album.amazon_flagged != 1 ';
        break;
      
      case 'meta':
        $query .= ' AND album.meta_flagged != 1 ';
        break;
        
      case 'folders':
        $query .= ' AND album.folders_flagged != 1 ';
        break;
        
      case 'service':
        $query .= ' AND album.service_flagged != 1 ';
        break;
    }
    $query .= ' AND album.has_art != 1 ';
    $query .= ' ORDER BY album.id ASC ';
    
    $dbh = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
    $stmt = $dbh->prepare( $query );
    $stmt->execute( array() );
    
    return $stmt->fetchAll(Doctrine::FETCH_ASSOC);
  }
  
  /**
   * Update an album to flag it as scanned but failed to add art for a given source
   * @param album_id int: the album id field to update
   * @param scan_id  int: the scan id
   * @param source   str: the artwork source: amazon|meta|folders|service etc.
   * @return         bool: true if record updated, otherwise false
   */
  public function setAlbumArtSourceScanned( $album_id, $scan_id, $source )
  {
    $id = 0;
    $q = $this->find( $album_id );
    if ( is_object( $q ) )
    {
      switch ( $source )
      {
        case 'amazon':
          $q->amazon_flagged = 1;
          break;
        
        case 'meta':
          $q->meta_flagged = 1;
          break;
          
        case 'folders':
          $q->folders_flagged = 1;
          break;
        
        case 'service':
          $q->service_flagged = 1;
          break;
      }
      $q->scan_id = $scan_id;
      $q->save();
      $id = $q->getId();
      $q->free();
    }

    if ( $id > 0 )
    {
      return true;
    }

    return false;
  }
  
  /**
   * Update an album record to indicate that the scan has successfully retrieved
   * album artwork from a given source
   * @param album_id int: the album id field to updateA
   * @return         bool: true if record updated, otherwise false
   */
  public function setAlbumArtAdded( $album_id, $scan_id, $source )
  {
    $id = 0;
    $q = $this->find( $album_id );
    if ( is_object( $q ) )
    {
      switch ( $source )
      {
        case 'amazon':
          $q->amazon_flagged = 1;
          break;
        
        case 'meta':
          $q->meta_flagged = 1;
          break;
          
        case 'folders':
          $q->folders_flagged = 1;
          break;
        
        case 'service':
          $q->service_flagged = 1;
          break;
      }
      $q->has_art = 1;
      $q->scan_id = $scan_id;
      $q->save();
      $id = $q->getId();
      $q->free();
    }

    if ( $id > 0 )
    {
      return true;
    }

    return false;
  }
  
  /**
   * Get total album count
   * @return       int: total album count in database
   */
  public function getTotalAlbumsCount()
  {
    $q = Doctrine_Query::create()
      ->select( 'a.id' )
      ->from( 'Album a' );
    return $q->count();
  }
  
  /**
   * Get count of albums with album art
   * @return       int: number of albums with art
   */
  public function getAlbumsWithArtCount()
  {
    $q = Doctrine_Query::create()
      ->select( 'a.id' )
      ->from( 'Album a' )
      ->where( 'a.has_art = ?', 1 );
    return $q->count();
  }
  
  /**
   * Remove album records with no song relation
   *
   * @param last_scan_id int: this should be the id of the latest library scan
   * @return             array: number of records removed
   */
  public function finalizeScan()
  {
    $q = Doctrine_Query::create()
      ->delete('Album a')
      ->where('a.id NOT IN (SELECT s.album_id FROM song AS s)')
      ->execute();
    return $q;
  }
}