using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;
using Bt.FileLib;

namespace SCAN.lib
{
    class SqliteDb
    {

        public static int dbIdx = 0;

        /// <summary>This method open stringBuilderite db file.
        /// <example>For example:
        /// <code>
        ///    if (dbIdx = openDB(DB_FILE))
        ///      // do with dbIdx...
        /// </code>
        /// return result <c>0</c> open db failed.
        /// <c>Otherwise</c> db index value.
        /// </example>
        /// </summary>
        public static int openDB(string file)
        {
            if (dbIdx > 0) return dbIdx;
            dbIdx = Bt.FileLib.SQLite.btSQLiteOpen(new StringBuilder(file));
            if (dbIdx > 0)
            {
                return dbIdx;
            }
            else
            {
                return 0;
            }
        }

        /// <summary>This method close stringBuilderite db file.
        /// <example>For example:
        /// <code>
        ///    ret = closeDB();
        /// </code>
        /// return result <c>True</c> if db closed.
        /// <c>Otherwise</c> db close failed.
        /// </example>
        /// </summary>
        public static bool closeDB()
        {
            if (dbIdx <= 0) return true;
            int ret = Bt.FileLib.SQLite.btSQLiteClose(dbIdx);
            dbIdx = 0;
            return ret == 0;
        }

        /// <summary>This method query data from db file.
        /// <example>For example:
        /// <code>
        ///    ret = query(sql, ref items);
        /// </code>
        /// return no return
        /// </example>
        /// </summary>
        public static int execute(string sql)
        {
            int ret = 0;
            openDB(Form_Login._Form_LoginInstance.DB_FILE);

            try
            {
                StringBuilder stringBuilder = new StringBuilder(sql);
                ret = Bt.FileLib.SQLite.btSQLiteExecute(dbIdx, stringBuilder);
            }
            finally
            {
                closeDB();
            }

            return ret;
        }

        /// <summary>This method query data from db file.
        /// <example>For example:
        /// <code>
        ///    ret = query(sql, ref items);
        /// </code>
        /// return no return
        /// </example>
        /// </summary>
        public static void query(string sql, ref List<string[]> items)
        {
            int ret = 0, cIdx = 0;
            StringBuilder stringBuilder = new StringBuilder(sql);
            IntPtr data = Marshal.AllocCoTaskMem(Marshal.SizeOf(typeof(Char)) * (8192 + 1));

            openDB(Form_Login._Form_LoginInstance.DB_FILE);

            if (dbIdx <= 0) goto FUNCEND;

            cIdx = Bt.FileLib.SQLite.btSQLiteCmdCreate(dbIdx);
            if (cIdx <= 0) goto FUNCEND;
            ret = Bt.FileLib.SQLite.btSQLiteCmdSetCommandText(cIdx, stringBuilder);
            if (ret < 0) goto FUNCEND;
            ret = Bt.FileLib.SQLite.btSQLiteCmdExecuteReader(cIdx);
            if (ret < 0) goto FUNCEND;
            int read_ret = 0;
            int j = 0;
            do
            {
                read_ret = Bt.FileLib.SQLite.btSQLiteCmdRead(cIdx);
                if (read_ret < 0) goto FUNCEND;
                else if (read_ret == 1)
                {
                    int cnt = Bt.FileLib.SQLite.btSQLiteCmdGetValueCount(cIdx);
                    string[] token = new string[cnt];
                    for (int i = 0; i < cnt; i++)
                    {
                        Bt.FileLib.SQLite.btSQLiteCmdGetValue(cIdx, i, data, 8192);
                        token[i] = Marshal.PtrToStringUni(data);
                    }

                    items.Add(token);
                }
            } while (read_ret == 1);

        FUNCEND:
            if (cIdx > 0) Bt.FileLib.SQLite.btSQLiteCmdDelete(dbIdx);
            Marshal.FreeCoTaskMem(data);
            if (dbIdx > 0) Bt.FileLib.SQLite.btSQLiteClose(dbIdx);

            closeDB();
        }
    }
}
