SQLiteKitScript

using UnityEngine;
using UnityEngine.UI;
using System.Collections;
using System;
using System.IO;
using System.Threading;
using System.Diagnostics;
using System.Collections.Generic;
using Debug = UnityEngine.Debug;

public class SQLiteKitScript : MonoBehaviour
{
    //
    // DataUser
    //
    private string DataUser = "DataUser0";

    // Query Create DataUser
    private string tbl_HeroUser =
        "CREATE TABLE IF NOT EXISTS tbl_HeroUser (id_hero INTEGER, id_unlockhero INTEGER, id_skill INTEGER, level_hero INTEGER, attack INTEGER, unlock INTEGER);";
    private string tbl_LevelUser =
            "CREATE TABLE IF NOT EXISTS tbl_LevelUser (id_level INTEGER, play INTEGER, unlock INTEGER, top_dame INTEGER, top_move INTEGER);";

    // Query Select DataUser
    private string tbl_HeroUserSelect = "SELECT * FROM tbl_HeroUser;";
    private string tbl_LevelUserSelect = "SELECT * FROM tbl_LevelUser;";

    // Query Insert DataUser
    private string tbl_HeroUserInsert =
        "INSERT INTO tbl_HeroUser (id_hero,id_unlockhero,id_skill,level_hero,attack,unlock) VALUES(?,?,?,?,?,?);";
    private string tbl_LevelUserInsert =
        "INSERT INTO tbl_LevelUser (id_level,play,unlock,top_dame,top_move) VALUES(?,?,?,?,?);";

    //
    // DataGame
    //
    private string DataGame = "DataGame0";

    // Query Create DataGame
    private string tbl_Boss =
            "CREATE TABLE IF NOT EXISTS tbl_Boss (id_boss INTEGER, type INTEGER, id_level INTEGER, name_boss VARCHAR, blood INTEGER, resist INTEGER, special INTEGER, move INTEGER, blood_special INTEGER, num_special INTEGER, img VARCHAR);";
    private string tbl_Draw =
            "CREATE TABLE IF NOT EXISTS tbl_Draw (id INTEGER, note INTEGER, food_percent VARCHAR, food INTEGER, soul_percent VARCHAR, soul INTEGER, cube_percent VARCHAR, cube INTEGER, cube_buy INTEGER);";
    private string tbl_Energy =
            "CREATE TABLE IF NOT EXISTS tbl_Energy (buy INTEGER, note INTEGER, energy INTEGER, energy_buy INTEGER, buy_cube INTEGER, energy_refill VARCHAR);";
    private string tbl_Hero =
            "CREATE TABLE IF NOT EXISTS tbl_Hero (id_hero INTEGER, map_open INTEGER, map_gift INTEGER, type_hero INTEGER, id_skill INTEGER, name_hero VARCHAR, des VARCHAR, food INTEGER, p_food VARCHAR, soul INTEGER, p_soul VARCHAR, cube INTEGER, p_cube VARCHAR, shop_food INTEGER, shop_soul INTEGER, shop_cube INTEGER);";
    private string tbl_Level =
            "CREATE TABLE IF NOT EXISTS tbl_Level (id_level INTEGER, chapter INTEGER, name_level VARCHAR, energy INTEGER DEFAULT (0), w_crystal INTEGER DEFAULT (0), w_food INTEGER DEFAULT (0), w_soul INTEGER, w_energy INTEGER, w_ticket INTEGER, w_hero INTEGER, q_move INTEGER, q_h1 INTEGER, q_h2 INTEGER, q_h3 INTEGER, q_h4 INTEGER, q_h5 INTEGER, q_box INTEGER, q_boss INTEGER, q_food INTEGER, q_soul INTEGER, q_ticket INTEGER, q_crystal INTEGER, q_hero INTEGER, q_bom INTEGER, log_win VARCHAR, log_lose VARCHAR);";
    private string tbl_Skill =
            "CREATE TABLE IF NOT EXISTS tbl_Skill (id_skill INTEGER, type_skill INTEGER, name_skill INTEGER, level_skill INTEGER, num_gem INTEGER, dame INTEGER, range INTEGER, des VARCHAR);";
    private string tbl_UnlockHero =
            "CREATE TABLE IF NOT EXISTS tbl_UnlockHero (id_unlockhero INTEGER, id_hero INTEGER, level_hero INTEGER, attack INTEGER, soul INTEGER, food INTEGER, crystal INTEGER, img VARCHAR);";

    // Query Select DataGame
    private string tbl_BossSelect = "SELECT * FROM tbl_Boss;";
    private string tbl_DrawSelect = "SELECT * FROM tbl_Draw;";
    private string tbl_EnergySelect = "SELECT * FROM tbl_Energy;";
    private string tbl_HeroSelect = "SELECT * FROM tbl_Hero;";
    private string tbl_LevelSelect = "SELECT * FROM tbl_Level;";
    private string tbl_SkillSelect = "SELECT * FROM tbl_Skill;";
    private string tbl_UnlockHeroSelect = "SELECT * FROM tbl_UnlockHero;";

    // Query Insert DataGame
    private string tbl_BossInsert =
        "INSERT INTO tbl_Boss (id_boss,type,id_level,name_boss,blood,resist,special,special,move,blood_special,num_special,img) VALUES(?,?,?,?,?,?,?,?,?,?,?);";
    private string tbl_DrawInsert =
        "INSERT INTO tbl_Draw (id,note,food_percent,food,soul_percent,soul,cube_percent,cube,cube_buy) VALUES(?,?,?,?,?,?,?,?,?);";
    private string tbl_EnergyInsert =
        "INSERT INTO tbl_Energy (buy,note,energy,energy_buy,buy_cube,energy_refill) VALUES(?,?,?,?,?,?);";
    private string tbl_HeroInsert =
        "INSERT INTO tbl_Hero (id_hero,map_open,map_gift,type_hero,id_skill,name_hero,des,food,p_food,soul,p_soul,cube,p_cube,shop_food,shop_soul,shop_cube) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
    private string tbl_LevelInsert =
        "INSERT INTO tbl_Level (id_level,chapter,name_level,energy,w_crystal,w_food,w_soul,w_energy,w_ticket,w_hero,q_move,q_h1,q_h2,q_h3,q_h4,q_h5,q_box,q_boss,q_food,q_soul,q_ticket,q_crystal,q_hero,q_bom,log_win,log_lose) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
    private string tbl_SkillInsert =
        "INSERT INTO tbl_Skill (id_skill,type_skill,name_skill,level_skill,num_gem,dame,range,des) VALUES(?,?,?,?,?,?,?,?);";
    private string tbl_UnlockHeroInsert =
        "INSERT INTO tbl_UnlockHero (id_unlockhero,id_hero,level_hero,attack,soul,food,crystal,img) VALUES(?,?,?,?,?,?,?,?);";

    //
    // Other
    //
    private string password = "thieumao";
    private SQLiteDB db = null;

    // Use this for initialization
    void Start()
    {
        createDataUser(password);
        createDataGame(password);
        //GetHeroesUserData();
        //tbl_HeroUser,tbl_LevelUser
        //tbl_Boss tbl_Draw tbl_Energy tbl_Hero tbl_Level lbl_Skill tbl_UnlockHero
//        converttbl_HeroUser();
//        converttbl_LevelUser();
//
//        converttbl_Boss();
//        converttbl_Draw();
//        converttbl_Energy();
//        converttbl_Hero();
//        converttbl_Level();
//        converttbl_Skill();
//        converttbl_UnlockHero();

    }

    // Update is called once per frame
    void Update()
    {
    }

    private void createDataUser(string password)
    {
        string heykey = Md5Sum(password);
        string pass = "PRAGMA hexkey=\"0x" + heykey + "\";";
        // tat ma hoa
        pass = "PRAGMA hex";

        try
        {
            SQLiteDB db2 = null;

            db2 = new SQLiteDB();
            string filename = Application.persistentDataPath + "/" + DataUser;
            File.Delete(filename);
            db2.Open(filename);

            //
            // set ENCRYPTION
            //
            SQLiteQuery qr = new SQLiteQuery(db2, pass);
            qr.Step();
            qr.Release();

            //
            // create table
            //
            qr = new SQLiteQuery(db2, tbl_HeroUser);
            qr.Step();
            qr.Release();
            qr = new SQLiteQuery(db2, tbl_LevelUser);
            qr.Step();
            qr.Release();

            //
            // insert table
            //
            converttbl_HeroUser(db2);
            converttbl_LevelUser(db2);

            // close
            db2.Close();
        }
        catch (Exception e)
        {
        }
    }

    private void createDataGame(string password)
    {
        string heykey = Md5Sum(password);
        string pass = "PRAGMA hexkey=\"0x" + heykey + "\";";
        // tat ma hoa
        pass = "PRAGMA hex";

        try
        {
            SQLiteDB db3 = null;

            db3 = new SQLiteDB();
            string filename = Application.persistentDataPath + "/" + DataGame;
            File.Delete(filename);
            db3.Open(filename);

            //
            // set ENCRYPTION
            //
            SQLiteQuery qr = new SQLiteQuery(db3, pass);
            qr.Step();
            qr.Release();

            //
            // create table
            //
            qr = new SQLiteQuery(db3, tbl_Boss);
            qr.Step();
            qr.Release();
            qr = new SQLiteQuery(db3, tbl_Draw);
            qr.Step();
            qr.Release();
            qr = new SQLiteQuery(db3, tbl_Energy);
            qr.Step();
            qr.Release();
            qr = new SQLiteQuery(db3, tbl_Hero);
            qr.Step();
            qr.Release();
            qr = new SQLiteQuery(db3, tbl_Level);
            qr.Step();
            qr.Release();
            qr = new SQLiteQuery(db3, tbl_Skill);
            qr.Step();
            qr.Release();
            qr = new SQLiteQuery(db3, tbl_UnlockHero);
            qr.Step();
            qr.Release();

            //tbl_Boss tbl_Draw tbl_Energy tbl_Hero tbl_Level lbl_Skill tbl_UnlockHero

            //
            // insert table
            //
            converttbl_Boss(db3);
            converttbl_Draw(db3);
            converttbl_Energy(db3);
            converttbl_Hero(db3);
            converttbl_Level(db3);
            converttbl_Skill(db3);
            converttbl_UnlockHero(db3);

            // close
            db3.Close();
        }
        catch (Exception e)
        {
        }
    }

    //tbl_HeroUser,tbl_LevelUser
    //tbl_Boss tbl_Draw tbl_Energy tbl_Hero tbl_Level lbl_Skill tbl_UnlockHero
    private void converttbl_HeroUser(SQLiteDB db2)
    {
        LoadDatabase("DataUser");
        SQLiteQuery qr = new SQLiteQuery(db, tbl_HeroUserSelect);
        while (qr.Step())
        {
            SQLiteQuery qr2 = new SQLiteQuery(db, tbl_HeroUserInsert);
            qr2.Bind(qr.GetInteger("id_hero"));
            qr2.Bind(qr.GetInteger("id_unlockhero"));
            qr2.Bind(qr.GetInteger("id_skill"));
            qr2.Bind(qr.GetInteger("level_hero"));
            qr2.Bind(qr.GetInteger("attack"));
            qr2.Bind(qr.GetInteger("unlock"));
            qr2.Step();
            qr2.Release();
        }
    }

    private void converttbl_LevelUser(SQLiteDB db2)
    {
        LoadDatabase("DataUser");
        SQLiteQuery qr = new SQLiteQuery(db, tbl_LevelUserSelect);
        while (qr.Step())
        {
            SQLiteQuery qr2 = new SQLiteQuery(db, tbl_LevelUserInsert);
            qr2.Bind(qr.GetInteger("id_level"));
            qr2.Bind(qr.GetInteger("play"));
            qr2.Bind(qr.GetInteger("unlock"));
            qr2.Bind(qr.GetInteger("top_dame"));
            qr2.Bind(qr.GetInteger("top_move"));
            qr2.Step();
            qr2.Release();
        }
    }

    private void converttbl_Boss(SQLiteDB db3)
    {
        LoadDatabase("DataGame");

        SQLiteQuery qr = new SQLiteQuery(db, tbl_BossSelect);
        while (qr.Step())
        {
            //heroUserData.ID = qr.IsNULL("id_hero") ? 0 : qr.GetInteger("id_hero");
        }
    }

    private void converttbl_Draw(SQLiteDB db3)
    {
        LoadDatabase("DataGame");

        SQLiteQuery qr = new SQLiteQuery(db, tbl_DrawSelect);
        while (qr.Step())
        {
            //heroUserData.ID = qr.IsNULL("id_hero") ? 0 : qr.GetInteger("id_hero");
        }
    }

    private void converttbl_Energy(SQLiteDB db3)
    {
        LoadDatabase("DataGame");

        SQLiteQuery qr = new SQLiteQuery(db, tbl_EnergySelect);
        while (qr.Step())
        {
            //heroUserData.ID = qr.IsNULL("id_hero") ? 0 : qr.GetInteger("id_hero");
        }
    }

    private void converttbl_Hero(SQLiteDB db3)
    {
        LoadDatabase("DataGame");

        SQLiteQuery qr = new SQLiteQuery(db, tbl_HeroSelect);
        while (qr.Step())
        {
            //heroUserData.ID = qr.IsNULL("id_hero") ? 0 : qr.GetInteger("id_hero");
        }
    }

    private void converttbl_Level(SQLiteDB db3)
    {
        LoadDatabase("DataGame");

        SQLiteQuery qr = new SQLiteQuery(db, tbl_LevelSelect);
        while (qr.Step())
        {
            //heroUserData.ID = qr.IsNULL("id_hero") ? 0 : qr.GetInteger("id_hero");
        }
    }

    private void converttbl_Skill(SQLiteDB db3)
    {
        LoadDatabase("DataGame");

        SQLiteQuery qr = new SQLiteQuery(db, tbl_SkillSelect);
        while (qr.Step())
        {
            //heroUserData.ID = qr.IsNULL("id_hero") ? 0 : qr.GetInteger("id_hero");
        }
    }

    private void converttbl_UnlockHero(SQLiteDB db3)
    {
        LoadDatabase("DataGame");

        SQLiteQuery qr = new SQLiteQuery(db, tbl_UnlockHeroSelect);
        while (qr.Step())
        {
            //heroUserData.ID = qr.IsNULL("id_hero") ? 0 : qr.GetInteger("id_hero");
            print("mao " + qr.GetInteger("id_unlockhero"));
        }
    }

    //Data User
    public List<HeroUser> heroesUserData;

    public List<HeroUser> GetHeroesUserData()
    {
        LoadDatabase("DataUser");

        heroesUserData = new List<HeroUser>();
        SQLiteQuery qr = new SQLiteQuery(db, "Select * FROM tbl_HeroUser");
        print("truoc");
        while (qr.Step())
        {
            HeroUser heroUserData = new HeroUser();
            heroUserData.ID = qr.IsNULL("id_hero") ? 0 : qr.GetInteger("id_hero");
            heroUserData.Level = qr.IsNULL("level_hero") ? 0 : qr.GetInteger("level_hero");
            heroUserData.State = qr.IsNULL("unlock") ? 0 : qr.GetInteger("unlock");
            heroUserData.SkillID = qr.IsNULL("id_skill") ? 0 : qr.GetInteger("id_skill");
            heroUserData.UnlockHeroID = qr.IsNULL("id_unlockhero") ? 0 : qr.GetInteger("id_unlockhero");
            heroUserData.AttackPower = qr.IsNULL("attack") ? 0 : qr.GetInteger("attack");

            print("ID=" + heroUserData.ID + " Level=" + heroUserData.Level + " AttackPower= " + heroUserData.AttackPower);

            heroesUserData.Add(heroUserData);
        }
        print("sau");
        return heroesUserData;

    }

    public void LoadDatabase(String nameDb)
    {
        db = new SQLiteDB();

        String filename = Application.persistentDataPath + "/" + nameDb;

        // check if database already exists.
        if (!File.Exists(filename))
        {
            UnityEngine.Debug.Log("file does not exist");
            // ok , this is first time application start!
            // so lets copy prebuild dtabase from StreamingAssets and load store to persistancePath with Test2
            byte[] bytes = null;
#if UNITY_EDITOR || UNITY_STANDALONE_WIN || UNITY_STANDALONE_OSX
            string dbpath = "file://" + Application.streamingAssetsPath + "/" + nameDb;
            WWW www = new WWW(dbpath);
            StartCoroutine(Download(www));
            bytes = www.bytes;
#elif UNITY_WEBPLAYER
                            string dbpath = "StreamingAssets/" + nameDb;
                            WWW www = new WWW(dbpath);
                            StartCoroutine(Download(www));
                            bytes = www.bytes;
#elif UNITY_IPHONE
                            string dbpath = Application.dataPath + "/Raw/" + nameDb;
                            try{
                                using ( FileStream fs = new FileStream(dbpath, FileMode.Open, FileAccess.Read, FileShare.Read) ){
                                    bytes = new byte[fs.Length];
                                    fs.Read(bytes,0,(int)fs.Length);
                                }
                            } catch (Exception e){

                            }
#elif UNITY_ANDROID

                            UnityEngine.Debug.Log("UNITY_ANDROID");
                            string dbpath = Application.streamingAssetsPath + "/" + nameDb;
                            UnityEngine.Debug.Log(dbpath);
                            WWW www = new WWW(dbpath);
                            StartCoroutine(Download(www));
                            while(!www.isDone){}
                            bytes = www.bytes;
#endif

            if (bytes != null)
            {
                try
                {
                    // copy database to real file into cache folder
                    using (FileStream fs = new FileStream(filename, FileMode.OpenOrCreate, FileAccess.Write))
                    {
                        fs.Write(bytes, 0, bytes.Length);
                    }

                    // initialize database
                    db.Open(filename);
                }
                catch (Exception e)
                {
                    Debug.Log("ERROR: " + e);
                }
            }
        }
        else
        {
            // it mean we already download prebuild data base and store into persistantPath
            // lest update, I will call Test

            try
            {
                // initialize database
                db.Open(filename);
            }
            catch (Exception e)
            {
                Debug.Log("ERROR: " + e);
            }

        }
    }

    IEnumerator Download(WWW www)
    {
        yield return www;
    }

    private string Md5Sum(string strToEncrypt)
    {
        System.Text.UTF8Encoding ue = new System.Text.UTF8Encoding();
        byte[] bytes = ue.GetBytes(strToEncrypt);

        // encrypt bytes
        System.Security.Cryptography.MD5CryptoServiceProvider md5 = new System.Security.Cryptography.MD5CryptoServiceProvider();
        byte[] hashBytes = md5.ComputeHash(bytes);

        // Convert the encrypted bytes back to a string (base 16)
        string hashString = "";

        for (int i = 0; i < hashBytes.Length; i++)
        {
            hashString += System.Convert.ToString(hashBytes[i], 16).PadLeft(2, '0');
        }

        return hashString.PadLeft(32, '0');
    }
}