import { versionMdbTools } from "@el3um4s/mdbtools";

// in Windows
const windowsPath = "./mdbtools-win";
const versionW = await versionMdbTools(windowsPath);

console.log(versionW);
// mdbtools v1.0.0

// in Linux (Debian)
const versionL = await versionMdbTools();

console.log(versionL);
// mdbtools v0.7.1

const windowsPath = "./mdbtools-win";
const database = "./src/__tests__/fruit.mdb";
const v = await version({ database, windowsPath });
console.log(v);
// JET4

const windowsPath = "./mdbtools-win";
const database = "./src/__tests__/fruit.mdb";

const list = await tables({ database, windowsPath });
console.log(list);
// [ "Fruit", "Fruit Salad", "Veggie Salad", "Muffin/Bread", "Dried"]

const listSystem = await tablesSystem({ database });
console.log(listSystem);
// [ "MSysObjects", "MSysACEs", "MSysQueries", "MSysRelationships", "MSysAccessObjects", "MSysNavPaneGroupCategories", "MSysNavPaneGroups", "MSysNavPaneGroupToObjects", "MSysNavPaneObjectIDs", "MSysAccessXML", "MSysNameMap" ]

const file = "./src/__tests__/__to_file__/tables-fruit.txt";
const t = await tablesToFile({ database, windowsPath, file });
console.log(t);
// true

const fileWithSystem =
  "./src/__tests__/__to_file__/tables-fruit-with-system-tables.txt";
const ts = await tablesAllToFile({
  database,
  windowsPath,
  file: fileWithSystem,
});
console.log(ts);
// true

const windowsPath = "./mdbtools-win";
const database = "./src/__tests__/test.mdb";

const listQueries = await queries({ database, windowsPath });
console.log(listQueries);
// [ "UserA", "MainColors", "ChangeValueDogTo40", "ChangeValueDotTo4", "AddApple", "DeleteApple", "aàeèéiìoòuù"]

const s = await queriesSQL({ database, windowsPath, query: "UserA" });
console.log(s);
// SELECT Users.* FROM [Users] WHERE (((Users.UserCategory)="A"))

const file = "./src/__tests__/__to_file__/test-queries.txt";
const t = await queriesToFile({ database, windowsPath, file });
console.log(t);
// true

const query = "UserA";
const fileQuery = "./src/__tests__/__to_file__/test-queries-usera.txt";
const tq = await queriesSQLToFile({
  database,
  windowsPath,
  query,
  file: fileQuery,
});
console.log(tq);
// true

const windowsPath = "./mdbtools-win";
const database = "./src/__tests__/test.mdb";
const s = "SELECT * FROM Colors WHERE Value > 10;";

const result = await sql({ database, windowsPath, sql: s });
console.log(result);
// [
//     {
//       Colors: "Blue",
//       Value: "16",
//     },
//     {
//       Colors: "Yellow",
//       Value: "12",
//     },
// ]

const resultAsString = await sqlAsString({ database, windowsPath, sql: s });
console.log(resultAsString);
// Colors      Value
// Blue        16
// Yellow      12

const file = "./src/__tests__/__to_file__/sql result to file.csv";
const q = await sqlToFile({ database, windowsPath, sql: s, file });
console.log(q);
// true

const inputFile = "./src/__tests__/__to_file__/select colors.sql";
const f = await sqlFromFile({ database, windowsPath, inputFile });
console.log(f);
// [
//     {
//       Colors: "Blue",
//       Value: "16",
//     },
//     {
//       Colors: "Yellow",
//       Value: "12",
//     },
// ]

const fileResult = "./src/__tests__/__to_file__/sql from file to file.csv";
const rf = await sqlFromFileToFile({
  database,
  windowsPath,
  inputFile,
  file: fileResult,
});
console.log(rf);
// true

const windowsPath = "./mdbtools-win";
const database = "./src/__tests__/test.mdb";
const table = "Colors";

const c = await count({ database, windowsPath, table });
console.log(c);
// 7

const windowsPath = "./mdbtools-win";
const database = "./src/__tests__/test.mdb";
const table = "Colors";

const result = await tableToJson({ database, windowsPath, table });
console.log(result);
// [
//     { Colors: "Red", Value: 10 },
//     { Colors: "Green", Value: 5 },
//     { Colors: "Blue", Value: 16 },
//     { Colors: "Black", Value: 1 },
//     { Colors: "Yellow", Value: 12 },
//     { Colors: "White", Value: 10 },
//     { Colors: "Others", Value: 0 },
// ]

const windowsPath = "./mdbtools-win";
const database = "./src/__tests__/test.mdb";
const table = "Colors";

const result = await tableToCSV({ database, windowsPath, table });
console.log(result);
// Colors,Value
// "Red",10
// "Green",5
// "Blue",16
// "Black",1
// "Yellow",12
// "White",10
// "Others",0

const file = "./src/__tests__/__to_file__/test-export-colors.csv";
const options = "-d; -Q";
const t = await tableToCSVFile({
  database,
  windowsPath,
  table,
  file,
  options,
});
console.log(t);
// true

const windowsPath = "./mdbtools-win";
const database = "./src/__tests__/test.mdb";
const s = await schema({ database, windowsPath });

const table = "Colors";
const schemaT = await schemaTable({ database, windowsPath, table });
console.log(schemaT);
// CREATE TABLE [Colors]
//  (
//     [Colors]           Text (50),
//     [Value]            Long Integer
// );

const windowsPath = "./mdbtools-win";
const database = "./src/__tests__/test.mdb";
const table = "Colors";

const columns = await columnsName({ database, windowsPath, table });
console.log(columns);
// [ "Colors", "Value" ]

const allColumns = await columnsNameTables({ database, windowsPath });
console.log(allColumns);
// {
//   Users: [
//     "UserID",
//     "UserName",
//     "UserSex",
//     "UserBirthday",
//     "UserTelephone",
//     "UserValue",
//     "UserCategory",
//   ],
//   Colors: ["Colors", "Value"],
//   Dictionary: ["Number", "Word"],
//   "Colors Table Two": ["Colors", "Value"],
// }