<?php 

require_once 'config.php';
require_once 'connect.php';
require_once 'utils.php';

class Tag 
{
    public $uid; 
    public $name;
}

class Album 
{
    public $uid; 
    public $title;
    public $cover;
    public $description; 
    public $array_music_ids; // 包含的音乐id（考虑到效率，这里不存为music结构）
}

class Music 
{
    public $uid;   // unique id
    public $path;  // 路径
    public $title;  // 标题名
    public $time;  // 创建时间
    public $cover; // 封面
    public $project; // 封面
    public $array_tags;  // 标签名
    public $array_albums; // 从属的专辑
}

function fetch_tag_by_id($uid) 
{
    $result =  execute_sql("SELECT * FROM " . Config::$tb_tags . " where id=" . $uid);
    while($row = mysqli_fetch_assoc($result)){
        $tag = new Tag; 
        $tag->uid = $uid;
        $tag->name = $row['name'];
        return $tag;
    }
    return null;
}

function fetch_tag_by_name($name)
{
    $result =  execute_sql("SELECT * FROM " . Config::$tb_tags . " where name='" . $name . "'");
    if($result == NULL || $result->num_rows == 0)
        return null;
    $arr = array();
    while($row = mysqli_fetch_assoc($result)){
        $tag = new Tag; 
        $tag->uid = $row['id'];
        $tag->name = $row['name'];
        array_push($arr, $tag);
    }
    return $arr;
}

function fetch_all_tags() {
    $sql = "SELECT * FROM ". Config::$tb_tags;
    $result = execute_sql($sql);
    $arr = array();
    while($row = mysqli_fetch_assoc($result)){
        $tag = new Tag; 
        $tag->uid = $row['id'];
        $tag->name = $row['name'];
        array_push($arr, $tag);
    }
    return $arr;
}

function fetch_album_by_name($name)
{
    $result =  execute_sql("SELECT * FROM " . Config::$tb_album . " where name='" . $name . "'");
    if($result == NULL || $result->num_rows == 0)
        return null;
    $arr = array();
    while($row = mysqli_fetch_assoc($result)){
        $album = new Album; 
        $album->uid = $row['id'];
        $album->name = $row['name'];
        array_push($arr, $album);
    }
    return $arr;
}

function insert_tag($name) 
{
    execute_sql("INSERT INTO " . Config::$tb_tags . " (name) values('" . $name . "')");
}

function insert_album($name)
{
    execute_sql("INSERT INTO " . Config::$tb_album . " (name) values('" . $name . "')");
}

function add_unique_tag($name) {
    $arr = fetch_tag_by_name($name);
    if($arr == null || count($arr) == 0) {
        insert_tag($name);
        $arr = fetch_tag_by_name($name);
    }
    return $arr[0];
}

function add_unique_album($name) {
    $arr = fetch_album_by_name($name);
    if($arr == null || count($arr) == 0) {
        insert_album($name);
        $arr = fetch_album_by_name($name);
    }
    return $arr[0];
}

function fetch_albums_music_ids($album_id) {
    $sql = "SELECT music_id FROM " . Config::$tb_album_music . ' WHERE album_id=' . $album_id;
    $result = execute_sql($sql);
    if($result == NULL || $result->num_rows == 0)
        return null;   
    $arr = array();
    while($row = mysqli_fetch_assoc($result)){
        $mid = $row['music_id'];
        array_push($arr, $mid);
    }
    return $arr;
}

// 获得某个音乐的tags
function fetch_music_tags($uid) {
    $sql = "SELECT * FROM " . Config::$tb_music_tag . ' WHERE music_id=' . $uid;
    $result = execute_sql($sql);
    if($result == NULL || $result->num_rows == 0)
        return null;   
    $arr = array();
    while($row = mysqli_fetch_assoc($result)){
        $tagId = $row['tag_id'];
        $tag = fetch_tag_by_id($tagId);
        array_push($arr, $tag);
    }
    return $arr;
}

// 获得某个音乐从属的专辑
function fetch_music_albums ($uid){
    $sql = "SELECT * FROM " . Config::$tb_album_music . ' WHERE music_id=' . $uid;
    $result = execute_sql($sql);
    if($result == NULL || $result->num_rows == 0)
        return null;   
    $arr = array();
    while($row = mysqli_fetch_assoc($result)){
        $albumId = $row['album_id'];
        $album = fetch_album_by_id($albumId);
        array_push($arr, $album);
    }
    return $arr;
}

function fetch_music_by_id($uid) {
    $sql = "SELECT * FROM " . Config::$tb_music . " WHERE id=" . $uid;
    $result = execute_sql($sql);
    if($result == NULL || $result->num_rows == 0)
    {
        return null;   
    }
    while($row = mysqli_fetch_assoc($result)){
        $music = new Music(); 
        $music->uid = $row['id'];
        $music->path = $row['path'];
        $music->cover = $row['cover'];
        $music->project = $row['project'];
        $music->title = $row['title'];
        $music->time = $row['time'];
        $music->array_tags = fetch_music_tags($music->uid);
        $music->array_albums = fetch_music_albums($music->uid);
        return $music;
    }
}

function fetch_album_by_id($uid) {
    $sql = "SELECT * FROM " . Config::$tb_album . " WHERE id=" . $uid;
    $result = execute_sql($sql);
    if($result == NULL || $result->num_rows == 0)
    {
        return null;   
    }
    while($row = mysqli_fetch_assoc($result)){
        $album = new Album(); 
        $album->uid = $row['id'];
        $album->cover = $row['cover'];
        $album->title = $row['title'];
        $album->description = $row['description'];
        $album->array_music_ids = fetch_albums_music_ids($album->uid);
        return $album;
    }
}

function fetch_album_music_ranged($album_id, $from=0, $count=100) {
    $sql = "SELECT music_id FROM " . Config::$tb_album_music . " WHERE album_id=" . $album_id . "  ORDER  BY id DESC LIMIT " . $from . ", " . $count ;;
    $result = execute_sql($sql);
    if($result == NULL || $result->num_rows == 0)
    {
        return null;   
    }
    $arr = array();
    while($row = mysqli_fetch_assoc($result)){
        $id = $row['music_id'];
        $music = fetch_music_by_id($id);
        array_push($arr, $music);
    }
    return $arr;
}

function get_all_album_music_count($album_id) {
    $sql = "SELECT COUNT(*) as count FROM " . Config::$tb_album_music . " WHERE album_id=" . $album_id;//. "  ORDER  BY id DESC LIMIT " . $from . ", " . $count ;;
    $result = execute_sql($sql);
    if($result == NULL || $result->num_rows == 0)
        return null;   
    $row = mysqli_fetch_assoc($result);
    if($row != null){
        return $row['count']; 
    }
    return 0;
}

// 从第from个记录开始的count个
function fetch_range_music($from=0, $count=100) {
    $sql = "SELECT id FROM " . Config::$tb_music .  "  ORDER  BY time DESC LIMIT " . $from . ", " . $count ;
    $result = execute_sql($sql);
    if($result == NULL || $result->num_rows == 0)
    {
        return null;   
    }
    $arr = array();
    while($row = mysqli_fetch_assoc($result)){
        $id = $row['id'];
        $music = fetch_music_by_id($id);
        array_push($arr, $music);
    }
    
    return $arr;
}

// 从第from个记录开始的count个
function fetch_range_album($from=0, $count=100) {
    $sql = "SELECT id FROM " . Config::$tb_album .  "  ORDER  BY id DESC LIMIT " . $from . ", " . $count ;
    $result = execute_sql($sql);
    if($result == NULL || $result->num_rows == 0)
    {
        return null;   
    }
    $arr = array();
    while($row = mysqli_fetch_assoc($result)){
        $id = $row['id'];
        $album = fetch_album_by_id($id);
        array_push($arr, $album);
    }
    
    return $arr;
}

//搜索
 
function fetch_musics_by_name($name, $from=0, $count=100) {
    $sql = "SELECT id FROM " . Config::$tb_music . " WHERE title LIKE '%" . $name . "%' ORDER BY time DESC  LIMIT " . $from . ", " . $count;
    $result = execute_sql($sql);
    if($result == null || $result->num_rows == 0)
        return ;
    $arr = array(); 
    while($row = mysqli_fetch_assoc($result)){
        $music_id = $row['id'];
        $music = fetch_music_by_id($music_id);
        array_push($arr, $music);
    }
    return $arr;
}

function get_music_count_by_name($name) {
    $sql = "SELECT COUNT(*) as count FROM " . Config::$tb_music . " WHERE title LIKE '%" . $name . "%'";
    $result = execute_sql($sql);
    if($result == null || $result->num_rows == 0)
        return 0;
    $row = mysqli_fetch_assoc($result);
    if($row != null){
        return $row['count']; 
    }
    return 0;
}

function fetch_musics_by_tag($tag_id, $from=0, $count=100) {
    $sql = "SELECT music_id FROM " . Config::$tb_music_tag . " WHERE tag_id=" . $tag_id . " ORDER  BY music_id DESC LIMIT " . $from . ", " . $count;
    $result = execute_sql($sql);
    if($result == null || $result->num_rows == 0)
        return ;
    $arr = array(); 
    while($row = mysqli_fetch_assoc($result)){
        $music_id = $row['music_id'];
        $music = fetch_music_by_id($music_id);
        array_push($arr, $music);
    }
    return $arr;
}

function get_music_count_by_tag($tag_id) {
    $sql = "SELECT COUNT(*) as count FROM " . Config::$tb_music_tag . " WHERE tag_id=" . $tag_id ;
    $result = execute_sql($sql);
    if($result == null || $result->num_rows == 0)
        return 0;
    $row = mysqli_fetch_assoc($result);
    if($row != null){
        return $row['count']; 
    }
    return 0;
}

function fetch_musics_by_album($album_id, $from=0, $count=100) {
    $sql = "SELECT music_id FROM " . Config::$tb_album_music .  " WHERE album_id=" . $album_id . " ORDER  BY time DESC LIMIT " . $from . ", " . $count;
    $result = execute_sql($sql);
    if($result == null || $result->num_rows == 0)
        return ;
    $arr = array(); 
    while($row = mysqli_fetch_assoc($result)){
        $music_id = $row['music_id'];
        $music = fetch_music_by_id($music_id);
        array_push($arr, $music);
    }
    return $arr;
}

function get_all_music_count() {
    $sql = "SELECT COUNT(*) as count FROM " . Config::$tb_music;
    $result = execute_sql($sql);
    if($result == null || $result->num_rows == 0)
        return 0;
    $row = mysqli_fetch_assoc($result);
    if($row != null){
        return $row['count']; 
    }
    return 0;
}

function get_all_album_count() {
    $sql = "SELECT COUNT(*) as count FROM " . Config::$tb_album;
    $result = execute_sql($sql);
    if($result == null || $result->num_rows == 0)
        return 0;
    $row = mysqli_fetch_assoc($result);
    if($row != null){
        return $row['count']; 
    }
    return 0;
}

function add_unique_music_tag( $music_id, $tag_id )
{
    $result =  execute_sql("SELECT * FROM " . Config::$tb_music_tag . " where music_id=" . $music_id . " and tag_id=" . $tag_id );
    if($result == NULL || $result->num_rows == 0)
    {
        execute_sql("INSERT INTO " . Config::$tb_music_tag . " (music_id, tag_id) values (" . $music_id . ", " . $tag_id . ")");
    }
}

function add_unique_music_album( $music_id, $album_id )
{
    $result =  execute_sql("SELECT * FROM " . Config::$tb_album_music . " where music_id=" . $music_id . " and album_id=" . $album_id );
    if($result == NULL || $result->num_rows == 0)
    {
        execute_sql("INSERT INTO " . Config::$tb_album_music . " (music_id, album_id) values (" . $music_id . ", " . $album_id . ")");
    }
}

function get_last_id() {
    $result = execute_sql("select @@IDENTITY");
    while($row = mysqli_fetch_assoc($result)){
        println($row);
    }
}

function delete_music($music_id) {
    $sql = "DELETE FROM ". Config::$tb_music .  " WHERE id=" . $music_id;
    echo $sql;
    execute_sql($sql);
    $sql = "DELETE FROM " . Config::$tb_music_tag . " WHERE music_id=" . $music_id;
    execute_sql($sql);
    $sql = "DELETE FROM " . Config::$tb_album_music . " WHERE music_id=" . $music_id;
    execute_sql($sql);

    redirect('admin_music.php');
}

function delete_tag($tag_id) {
    $sql = "DELETE FROM " . Config::$tb_tags . " WHERE id=" . $tag_id;
    execute_sql($sql);
    $sql = "DELETE FROM " . Config::$tb_music_tag . " WHERE tag_id=" . $tag_id;
    execute_sql($sql);

    redirect('admin_tags.php');
}

function delete_album($album_id) {
    $sql = "DELETE FROM " . Config::$tb_album . " WHERE id=" . $album_id;
    execute_sql($sql);
    $sql = "DELETE FROM " . Config::$tb_album_music . " WHERE album_id=" . $album_id;
    execute_sql($sql);

    redirect('admin_albums.php');
}

// 返回描述music tag的字符串
function get_music_tag_string($music) {
    if($music->array_tags == null || count($music->array_tags) == 0){
        return ""; 
    }
    $tag_str = $music->array_tags[0]->name; 
    for($i = 1; $i < count($music->array_tags); $i++) {
        $tag_str = $tag_str . ',' . $music->array_tags[$i]->name;
    }
    return $tag_str;
}

// 返回描述music所属专辑的id字符串
function get_music_albums_id_string($music) {
    if($music->array_albums == null || count($music->array_albums) == 0){
        return ""; 
    }
    $album_str = $music->array_albums[0]->uid; 
    for($i = 1; $i < count($music->array_albums); $i++) {
        $album_str = $album_str . ',' . $music->array_albums[$i]->uid;
    }
    return $album_str;
}

// 返回编辑中的music_id字符串
function get_albums_music_id_string($album) {
    if($album->array_music_ids == null || count($album->array_music_ids) == 0){
        return ""; 
    }
    $music_str = $album->array_music_ids[0];
    for($i = 1; $i < count($album->array_music_ids); $i++) {
        $music_str = $music_str . ',' . $album->array_music_ids[$i];
    }
    return $music_str;
}

function delete_music_tag($music) {
    $sql = "DELETE FROM " . Config::$tb_music_tag . " WHERE music_id=" . $music->uid;
    execute_sql($sql);   
}

function delete_music_album($music) {
    $sql = "DELETE FROM " . Config::$tb_album_music . " WHERE music_id=" . $music->uid;
    execute_sql($sql);   
}

function delete_album_music($album_id) {
    $sql = "DELETE FROM " . Config::$tb_album_music . " WHERE album_id=" . $album_id;
    execute_sql($sql);   
}

// 提交上来的表单中构建music
function get_music_from_post() 
{
    $music = new Music; 

    $music_file = $_FILES["musicFile"];
    $cover_image = $_FILES["coverImage"];
    $music_proj = $_FILES["musicProj"];

    if(is_upload_notempty($music_file)) {
        $res_music =  upload_file(Config::$music_upload_dir, $music_file);
        if($res_music[0] == 0)
            return null;
        $music->path = $res_music[1];
    }
    else // 必须有音乐文件
    {
        echo 'Music file is essential.';
        return null; 
    }

    if(is_upload_notempty($cover_image)) {
        $res_cover =  upload_file(Config::$cover_upload_dir, $cover_image);
        if($res_cover[0] == 0)
            return null;
        $music->cover = $res_cover[1];
    }

    if(is_upload_notempty($music_proj)) {
        $res_proj =  upload_file(Config::$project_upload_dir, $music_proj);
        if($res_proj[0] == 0)
            return null;
        $music->project = $res_proj[1];
    }


    if($_POST["tags"] != null && $_POST["tags"] != "")
        $tags_name = explode(',', $_POST["tags"]);

    if($_POST["albums"] != null && $_POST["albums"] != "")
        $albums_id = explode(',', $_POST["albums"]);
    
    $music->array_tags = array();
    if($tags_name != null && count($tags_name) > 0) {
        foreach($tags_name as $tag_name){
            $tag = add_unique_tag($tag_name); 
            array_push($music->array_tags, $tag);
        }
    }

    $music->array_albums = array();
    if($albums_id != null && count($albums_id) > 0) {
        foreach($albums_id as $album_id){
            $album = fetch_album_by_id($album_id);
            if($album == null)
                continue;
            array_push($music->array_albums, $album);
        }
    }

    $music->time = time(); 

    $music->title = html_escape_string($_POST["title"]);

    $music->uid = -1; // 未插入数据库

    if($_POST["mid"] != null) {
        $music->uid = $_POST["mid"];
    }

    //println($music);

    return $music;
}

// 将music写入数据库（插入或更新）
function write_music_to_db($music) {
    if ($music == null )
        return ;

    if($music->uid == -1){// 插入新的

        $sql = "INSERT INTO " . Config::$tb_music . " (path, title, time, cover, project) values (" .
        "'". $music->path . "',".
        "'". $music->title . "',".
        $music->time . ",".
        "'". $music->cover . "',".
        "'". $music->project . "'".
        ")" ;
        //println($sql);
        execute_sql($sql);

        $id = get_last_insert_id();

        foreach($music->array_tags as $tag){
            add_unique_music_tag($id, $tag->uid);
        }

        foreach($music->array_albums as $album){
            add_unique_music_album($id, $album->uid);
        }
    }
    else // 更新 
    {
        $sql = "UPDATE " . Config::$tb_music . " SET " . 
            "path='" . $music->path ."'," .
            "title='" . $music->title ."'," .
            "cover='" . $music->cover ."'," .
            "project='" . $music->project ."'" .
            " WHERE id=" . $music->uid;
        //println($sql);
        execute_sql($sql);
        
        delete_music_tag($music);
        delete_music_album($music);

        $id = $music->uid;

        foreach($music->array_tags as $tag){
            add_unique_music_tag($id, $tag->uid);
        }

        foreach($music->array_albums as $album){
            add_unique_music_album($id, $album->uid);
        }
    }
}

function get_album_from_post() {
    $album = new Album; 

    $cover_image = $_FILES["coverImage"];

    if(is_upload_notempty($cover_image)) {
        $res_cover =  upload_file(Config::$cover_upload_dir, $cover_image);
        if($res_cover[0] == 0)
            return null;
        $album->cover = $res_cover[1];
    }

    if($_POST["music_list"] != null && $_POST["music_list"] != "")
        $music_ids = explode(',', $_POST["music_list"]);
    
    $album->array_music_ids = array();
    if($music_ids != null && count($music_ids) > 0) {
        foreach($music_ids as $music_id){
            array_push($album->array_music_ids, $music_id);
        }
    }

    $album->title = html_escape_string($_POST["title"]);
    $album->description = html_escape_string($_POST["description"]);

    $album->uid = -1; // 未插入数据库

    if($_POST["aid"] != null) {
        $album->uid = $_POST["aid"];
    }

    // println($album);

    return $album;
}


// 将album写入数据库（插入或更新）
function write_album_to_db($album) {
    if ($album == null )
        return ;

    if($album->uid == -1){// 插入新的

        $sql = "INSERT INTO " . Config::$tb_album . " (title, description, cover) values (" .
        "'". $album->title . "',".
        "'". $album->description . "',".
        "'". $album->cover . "'".
        ")" ;
        //println($sql);
        execute_sql($sql);

        $id = get_last_insert_id();

        foreach($album->array_music_ids as $mid){
            add_unique_music_album($mid, $id);
        }
    }
    else // 更新 
    {
        $sql = "UPDATE " . Config::$tb_album . " SET " . 
            "title='" . $album->title ."'," .
            "description='" . $album->description ."'," .
            "cover='" . $album->cover ."'" .
            " WHERE id=" . $album->uid;
        //println($sql);
        execute_sql($sql);
        
        delete_album_music($album->uid);

        $id = $album->uid;

        foreach($album->array_music_ids as $mid){
            add_unique_music_album($mid, $id);
        }

    }
}


?>