<?php

include_once "includes/bootstrap.inc";
include_once 'includes/common.inc';

switch ($GLOBALS['db_type']) {
    case 'mysql':
    case 'mysqli':
      db_query(
<<<MYSQL_UPDATE
        CREATE TABLE {audio} (
          `vid` int(10) unsigned NOT NULL default '0',
          `nid` int(10) unsigned NOT NULL default '0',
          `title_format` varchar(128) default '',
          `play_count` int(10) unsigned NOT NULL default '0',
          `download_count` int(10) unsigned NOT NULL default '0',
          `downloadable` tinyint(1) NOT NULL default '1',
          `fileformat` varchar(10) NOT NULL default '',
          `sample_rate` int(10) unsigned NOT NULL default '0',
          `channel_mode` varchar(10) NOT NULL default '',
          `bitrate` float unsigned NOT NULL default '0',
          `bitrate_mode` varchar(4) NOT NULL default '',
          `playtime` varchar(10) NOT NULL default '',
          PRIMARY KEY  (`vid`)
        ) /*!40100 DEFAULT CHARACTER SET utf8 */;
MYSQL_UPDATE
      ); 
      db_query(
<<<MYSQL_UPDATE
        CREATE TABLE  {audio_file} (
          `vid` int(10) unsigned NOT NULL default '0',
          `origname` varchar(255) NOT NULL default '',
          `filename` varchar(255) NOT NULL default '',
          `filepath` varchar(255) NOT NULL default '',
          `filemime` varchar(255) NOT NULL default '',
          `filesize` int(10) unsigned NOT NULL default '0',
          PRIMARY KEY  (`vid`)
        ) /*!40100 DEFAULT CHARACTER SET utf8 */;
MYSQL_UPDATE
      );
      db_query(
<<<MYSQL_UPDATE
        CREATE TABLE  {audio_metadata} (
          `vid` int(10) unsigned NOT NULL default '0',
          `tag` varchar(45) NOT NULL default '',
          `value` varchar(255) NOT NULL default '',
          `clean` varchar(255) NOT NULL default '',
          PRIMARY KEY  (`vid`,`tag`,`value`),
          KEY `audio_metadata_tags` (`clean`)
        ) /*!40100 DEFAULT CHARACTER SET utf8 */;
MYSQL_UPDATE
      );
      break;
  }

$result = db_query("SELECT nid FROM {node} WHERE type = audio");
while($row = db_fetch_object($result)) {
  $node = node_load(array('nid' => $row->nid));
  $fid = db_result(db_query("SELECT fid FROM {files} WHERE nid=%d limit 1", $node->nid));
  if ($fid){
    $node->audio = db_fetch_array(db_query("SELECT * FROM {audio_node_metadata} WHERE fid=%d", $fid));
    $node->files = db_fetch_array(db_query("SELECT * FROM {files} WHERE nid=%d limit 1", $node->nid));
    $node->getid3 = audio_read_id3tags($node->files['filepath']);

    // audio files table
    db_query("INSERT INTO {audio_file} (vid, origname, filename, filepath, filemime, filesize) VALUES (%d, '%s', '%s', '%s', '%s', '%s')",
             $node->nid, $node->files['filename'], $node->files['filename'], $node->files['filepath'],
             $node->files['filemime'], $node->files['filesize']);
    // audio table
    db_query("INSERT INTO {audio} (nid, vid, title_format, play_count, downloadable, 
            fileformat, sample_rate, channel_mode, bitrate, bitrate_mode, playtime)    
            VALUES (%d, %d, '%s', %d, %d, %d, '%s', %d, '%s', %f, '%s', '%s')",
            $node->nid, $node->vid, $node->title, $node->audio['playcount'], $node->audio['downloadable'], 
            $node->getid3['fileformat'], $node->getid3['samplerate'], 
            $node->getid3['channel_mode'], $node->getid3['bitrate'], $node->getid3['bitrate_mode'], 
            $node->getid3['playtime']);
    
    $tags = array(
                  'artist' => $node->audio['artist'], 
                  'title' => $node->audio['title'], 
                  'album' => $node->audio['album'], 
                  'year' => $node->audio['year'],
                  'track' => $node->audio['track'],
                  'genre' => $node->audio['genre'], 
                  'comment' =>$node->audio['comment']
                  );
    foreach ($tags as $tag => $value) {                  
    db_query("INSERT INTO {audio_metadata} (vid, tag, value, clean) VALUES (%d, '%s', '%s', '%s')",
            $node->nid, $tag, $value, $value );
    }
  }
}

/*
   1. Create new tables. I don't know if it's safe to assume that audio_install() will be called before running the update script
   2. Move each audio file from {files}/{file_revisons} to {audio_file}:

          file_revisions.vid => audio_file.vid
          files.filename => audio_file.origname
          files.filename => audio_file.filename
          files.filepath => audio_file.filepath
          files.filemime => audio_file.filemime
          files.filemime => audio_file.filesize 

   3. For each audio node:
         1. Call getID3 to get the file info for these {audio} fields:
            fileformat, sample_rate, channel_mode, bitrate, bitrate_mode, playtime
         2. Create an {audio} record using

                node.nid => audio.nid
                node.vid => audio.vid
                node.title => audio.title_format
                audio_node_metadata.playcount => audio.play_count
                audio_node_metadata.downloadable => audio.downloadable
                getID3.fileformat => audio.fileformat
                getID3.sample_rate => audio.sample_rate
                getID3.channel_mode => audio.channel_mode
                getID3.bitrate => audio.bitrate
                getID3.bitrate_mode => audio.bitrate_mode
                getID3.playtime => audio.playtime 

         3. Create {audio_metadata} records from these {audio_node_metadata} fields:

                artist, title, album, year, track, genre, comment


 CREATE TABLE `audio_node_metadata` (
  `fid` int(10) NOT NULL default '0',
  `playcount` int(10) NOT NULL default '0',
  `downloadable` tinyint(1) NOT NULL default '1',
  `fileformat` varchar(10) NOT NULL default '',
  `bitrate` float NOT NULL default '0',
  `sample_rate` float NOT NULL default '0',
  `playtime_seconds` int(10) NOT NULL default '0',
  `artist` varchar(255) NOT NULL default '',
  `title` varchar(255) NOT NULL default '',
  `album` varchar(255) NOT NULL default '',
  `year` varchar(255) NOT NULL default '',
  `track` int(7) NOT NULL default '0',
  `genre` varchar(255) NOT NULL default '',
  `comment` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`fid`)
) TYPE=MyISAM;               
                
                
*/