-module(esqlite3Tool).
-compile(export_all).

-include("esqltool.hrl").

createSqlCacheTable() ->
  {ok, DB} = esqlite3:open(database_name:getCacheDatabaseName()),
%%  ok = esqlite3:exec("CREATE TABLE IF NOT EXISTS SqlCache(
%%                            uuid        TEXT PRIMARY KEY,
%%                            func        TEXT DEFAULT NULL,
%%                            sql         TEXT DEFAULT NULL,
%%                            create_time TEXT,
%%                            create_by   TEXT
%%                          );", DB),
  esqlite3:close(DB).

open(DBPath) ->
  TmpDBKey = ?ESQLDBKEY,
  BerPid = mochiglobal:get(TmpDBKey),
  case BerPid of
    undefined ->
      ok;
    _ ->
      mochiglobal:delete(TmpDBKey)
  end,
  %%如果传入值为空则按照默认名称打开数据库
  case DBPath of
    "" ->
      OpenDbRet = esqlite3:open(database_name:getSyncDatabaseName());
%%      mochiglobal:put(TmpDBKey, database_name:getSyncDatabaseName());
    _ ->
      StrDBPath = syncTool:change2Str(DBPath),
      OpenDbRet = esqlite3:open(StrDBPath),
      mochiglobal:put(TmpDBKey, StrDBPath)
  end,
  case OpenDbRet of
    {ok, DB} ->
      io:format("DB is ~p ~n", [DB]),
      esqlite3:close(DB);
    {error, ERROR} ->
      io:format("ERROR is ~p ~n", [ERROR])
  end,
  ok.

-spec close() -> atom().
close() ->
  DelRet = mochiglobal:delete(?ESQLDBKEY),
  case DelRet of
    ture ->
      io:format("key ~p is deleted ~n", [?ESQLDBKEY]),
      Ret = {ok};
    false ->
      io:format("key ~p is not exist ~n", [?ESQLDBKEY])
  end,
  ok.


getAllTableNum() ->
  Conn = getESqlDBRef(),
  case Conn of
    {ok, Db} ->
      {ok, Stmt} = esqlite3:prepare("select count(*) from sqlite_master where type='table' and name = 'sys_o_tables'", Db),
      {'count(*)'} = esqlite3:column_names(Stmt),
      {row, {RecSize}} = esqlite3:step(Stmt),
      esqlite3:close(Db);
    {error, false} ->
      io:format("Conn is ~p ~n", [Conn]),
      RecSize = -1;
    _ ->
      io:format("Conn is ~p ~n", [Conn]),
      RecSize = -2
  end,
  RecSize.

%%initDbTables() ->
%%%%  try
%%  Conn = getESqlDBRef(),
%%  case Conn of
%%    {ok, Db} ->
%%%%  select count(*) from sqlite_master where type='table' and name = 'sys_o_tables';
%%      {ok, Stmt} = esqlite3:prepare("select count(*) from sqlite_master where type='table' and name = 'sys_o_tables'", Db),
%%      {'count(*)'} = esqlite3:column_names(Stmt),
%%      {row, {RecSize}} = esqlite3:step(Stmt),
%%      esqlite3:close(Db),
%%      if
%%        RecSize > 0 ->
%%          io:format("RecSize is ~p ~n", [RecSize]),
%%          RetTmp = {ok, ok};
%%        true ->
%%          io:format("RecSize is ~p ~n", [RecSize]),
%%          RetTmp = initDb()
%%      end;
%%    {error, false} ->
%%      io:format("Conn is ~p ~n", [Conn]),
%%      RetTmp = {error, false};
%%    _ ->
%%      io:format("Conn is ~p ~n", [Conn]),
%%      RetTmp = {error, false}
%%  end,
%%%%  catch
%%%%    Ex:Type ->
%%%%      {Ex, Type, erlang:get_stacktrace()},
%%%%      Ret = {error, ex},
%%%%      io:format("Ex is  ~p,~p ~n", [Ex, Type])
%%%%  after
%%%%    Ret = {error, ex}
%%%%  end,
%%  RetTmp.

createDeviceInfoAndClusterInfoTables() ->
%%  try
  Conn = getESqlDBRef(),
  case Conn of
    {ok, Db} ->
%%      ok = esqlite3:exec("begin;", Db),
      %% sys db table  cte sql ok
      %% cluster_info table  cte sql ok
%%      ok = esqlite3:exec("CREATE TABLE IF NOT EXISTS 'main'.'sys_o_tables'(
%%                              uuid        text PRIMARY KEY,
%%                              ClusterId text,
%%                              tableName   text,
%%                              tableDesc   text,
%%                              tableType   text,
%%                              bwType      text,
%%                              startTime   text,
%%                              endTime     text,
%%                              create_time text default (datetime('now', 'localtime')),
%%                              update_time text,
%%                              create_by   text,
%%                              del_flag    text
%%                          );", Db),
%%      ok = esqlite3:exec("CREATE TABLE IF NOT EXISTS 'main'.'device_info'(
%%                              uuid        text PRIMARY KEY,
%%                              node_id     text,
%%                              father_node text,
%%                              device_id   text,
%%                              cluster_id  text,
%%                              create_time text default (datetime('now', 'localtime')),
%%                              update_time text,
%%                              create_by   text,
%%                              del_flag    text default 0
%%                          );", Db),
%%      ok = esqlite3:exec("CREATE TABLE IF NOT EXISTS 'main'.'cluster_info'(
%%                              cluster_id   text PRIMARY KEY,
%%                              cluster_name text,
%%                              create_time  text default (datetime('now', 'localtime')),
%%                              update_time  text,
%%                              create_by    text,
%%                              del_flag     text default 0
%%                          );", Db),
%%      ok = esqlite3:exec("CREATE TABLE IF NOT EXISTS cache (
%%					id                             TEXT,
%%					sql                            TEXT,
%%					createBy                       TEXT,
%%					createTime                     TEXT,
%%					PRIMARY KEY(id)
%%			);", Db),
%%      ok = esqlite3:exec("commit;", Db),
      esqlite3:close(Db),
      RetTmp = {ok, true};
    {error, false} ->
      io:format("Conn is ~p ~n", [Conn]),
      RetTmp = {error, false};
    _ ->
      io:format("Conn is ~p ~n", [Conn]),
      RetTmp = {error, false}
  end,
  Ret = RetTmp,
%%  catch
%%    Ex:Type ->
%%      {Ex, Type, erlang:get_stacktrace()},
%%      Ret = {error, ex},
%%      io:format("Ex is  ~p,~p ~n", [Ex, Type])
%%  end,
  Ret.

%% 本机执行其他节点获取的sql脚本
getDbSql(Binary) ->
%%  application:load("/data/data/com.termux/files/usr/lib/"),
  Timestamp = syncTool:getTimestamp(),
  BackFile = ["/sdcard/","getDbSql", Timestamp, ".sql"],
  file:write_file(BackFile, Binary),
  SyncDBName = string:sub_word(syncTool:change2Str(node()), 1, $@),
%%  DBName = SyncDBName ++ ".db",
  DBName = database_name:getSyncDatabaseName(),
  CMD = ["cd ",database_name:getSqlBackDir()," && /data/data/com.termux/files/usr/bin/sqlite3 ", DBName, " < ", BackFile],
  Ls = os:cmd(CMD),
  case BackFile of
    "" ->
      ok;
    _ ->
%%      CMD2 = ["rm -f ", BackFile],
%%      os:cmd(CMD2)
      ok
  end,
  Ls.

readAttachment(AttachmentFileName) ->
  {ok, Binary} = file:read_file(AttachmentFileName),
  Binary.

%%//esqlite3Tool:readDbSql().
%% 读取本地生成syncDb备份的sql脚本文件
readDbSql() ->
%%  application:load("/data/data/com.termux/files/usr/lib/"),
  Timestamp = syncTool:getTimestamp(),
  BackFile = ["/sdcard/","readDbSql", Timestamp, ".sql"],
%%  io:format("~p ~n", [BackFile]),
  SyncDBName = string:sub_word(syncTool:change2Str(node()), 1, $@),
%%  DBName = SyncDBName ++ ".db",
  DBName = database_name:getSyncDatabaseName(),
  CMD = ["cd ",database_name:getSqlBackDir()," && /data/data/com.termux/files/usr/bin/sqlite3 ", DBName, " .dump | grep '^INSERT INTO cluster_info'>", BackFile],
  os:cmd(CMD),
  CMD1 = ["cd ",database_name:getSqlBackDir()," && /data/data/com.termux/files/usr/bin/sqlite3 ", DBName, " .dump | grep '^INSERT INTO device_info'>>", BackFile],
  %%ok = file:write("/sdcard/readDBSql1",list_to_binary("aaa1")),
  os:cmd(CMD1),
  %%ok = file:write("/sdcard/readDBSql",LsOut),
  {ok, Binary} = file:read_file(BackFile),
  case BackFile of
    "" ->
      ok;
    _ ->
%%      CMD2 = ["rm -f ", BackFile],
%%      os:cmd(CMD2)
      ok
  end,
  Binary.

testReadDbSql() ->
  Bin = readDbSql(),
  Ls = getDbSql(Bin),
  Ls.