using SQLite4Unity3d;
using UnityEngine;

if !UNITY_EDITOR

using System.Collections;
using System.IO;

endif

using System.Collections.Generic;

public class DataService {

private SQLiteConnection _connection;

public DataService(string DatabaseName){

if UNITY_EDITOR

        var dbPath = string.Format(@"Assets/StreamingAssets/{0}", DatabaseName);

else

    // check if file exists in Application.persistentDataPath
    var filepath = string.Format("{0}/{1}", Application.persistentDataPath, DatabaseName);

    if (!File.Exists(filepath))
    {
        Debug.Log("Database not in Persistent path");
        // if it doesn't ->
        // open StreamingAssets directory and load the db ->

if UNITY_ANDROID

        var loadDb = new WWW("jar:file://" + Application.dataPath + "!/assets/" + DatabaseName);  // this is the path to your StreamingAssets in android
        while (!loadDb.isDone) { }  // CAREFUL here, for safety reasons you shouldn't let this while loop unattended, place a timer and error check
        // then save to Application.persistentDataPath
        File.WriteAllBytes(filepath, loadDb.bytes);

elif UNITY_IOS

             var loadDb = Application.dataPath + "/Raw/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
            // then save to Application.persistentDataPath
            File.Copy(loadDb, filepath);

elif UNITY_WP8

            var loadDb = Application.dataPath + "/StreamingAssets/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
            // then save to Application.persistentDataPath
            File.Copy(loadDb, filepath);

elif UNITY_WINRT

    var loadDb = Application.dataPath + "/StreamingAssets/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
    // then save to Application.persistentDataPath
    File.Copy(loadDb, filepath);

elif UNITY_STANDALONE_OSX

    var loadDb = Application.dataPath + "/Resources/Data/StreamingAssets/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
    // then save to Application.persistentDataPath
    File.Copy(loadDb, filepath);

else

var loadDb = Application.dataPath + "/StreamingAssets/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
// then save to Application.persistentDataPath
File.Copy(loadDb, filepath);

endif

        Debug.Log("Database written");
    }

    var dbPath = filepath;

endif

        _connection = new SQLiteConnection(dbPath, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create);
    Debug.Log("Final PATH: " + dbPath);     

}

public void CreateDB(){
    _connection.DropTable<Person> ();
    _connection.CreateTable<Person> ();

    _connection.InsertAll (new[]{
        new Person{
            Id = 1,
            Name = "Tom",
            Surname = "Perez",
            Age = 56
        },
        new Person{
            Id = 2,
            Name = "Fred",
            Surname = "Arthurson",
            Age = 16
        },
        new Person{
            Id = 3,
            Name = "John",
            Surname = "Doe",
            Age = 25
        },
        new Person{
            Id = 4,
            Name = "Roberto",
            Surname = "Huertas",
            Age = 37
        }
    });
}

public IEnumerable<Person> GetPersons(){
    return _connection.Table<Person>();
}

public IEnumerable<Person> GetPersonsNamedRoberto(){
    return _connection.Table<Person>().Where(x => x.Name == "Roberto");
}

public Person GetJohnny(){

    //_connection.Delete();
    Person p= _connection.Table<Person>().Where(x => x.Name == "Johnny").FirstOrDefault();

    _connection.Delete(p);
    _connection.Update(p);
    return p;
}

public void GetJohnny_01()
{
    Person x=new Person() ;
    x.Name = "Johnny";
    _connection.Delete(x);
    TableMapping t= _connection.GetMapping<Person>();
    Debug.Log(t.PK.ToString());
    //return _connection.Table<Person>().Where(x=> x.Name == "Johnny").FirstOrDefault();
    //return true;
}


public Person CreatePerson(){
    var p = new Person{
            Name = "Johnny",
            Surname = "Mnemonic",
            Age = 21
    };
    _connection.Insert (p);
    return p;
}
/// <summary>
/// 此插件的使用介绍
/// 主要方法->创建链接、创建表、删除表\增、删、改、查
/// </summary>
public void UseDB(string dbPath)
{
    //创建链接数据库SQLiteOpenFlags.ReadWrite（读写标签） | SQLiteOpenFlags.Create（创建标签）
    SQLiteConnection _connection = new SQLiteConnection(dbPath, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create);
    //创建一张表，一个类一张表
    _connection.CreateTable<Person>();


    //删除一张表
    _connection.DropTable<Person>();


    //在表中增加一行，此函数在person表中插入一行数据，person中设置了主键和自动增量
    //_connection.InsertAll();此函数在表中批量插入
    var p = new Person
    {
        Name = "Johnny",
        Surname = "Mnemonic",
        Age = 21
    };
    _connection.Insert(p);


    //要删除数据，需要先读取数据，此函数读取到属性Name为Johnny的一条数据，返回一个person对象，通过对象删除此条数据
    Person person = _connection.Table<Person>().Where(x => x.Name == "Johnny").FirstOrDefault();
    _connection.Delete(p);
    //通过主键删除,删除主键为1的一条数据
    _connection.Delete<Person>(1);
    //删除所有数据
    _connection.DeleteAll<Person>();


    //修改。要想修改数据，需要先拿到要修改的对象，通过对象拿到对应的一条数据进行修改
    //_connection.UpdateAll();修改所有
    p = _connection.Table<Person>().Where(x => x.Name == "Johnny").FirstOrDefault();
    _connection.Update(p);

    //查。读取数据。在person表内读取属性Name为Johnny的一条数据，返回person对象
    p = _connection.Table<Person>().Where(x => x.Name == "Johnny").FirstOrDefault();
   
}

}