/* dblayer.c - Database layer for ck -----------------------------------*- C -*-
 *
 * This file is part of ck, the config keeper
 * 
 * -----------------------------------------------------------------------------
 *
 * Copyright (C) 2018  Anastasis Grammenos
 * GPLv3 (see LICENCE for the full notice)
 *
 * -------------------------------------------------------------------------- */
#include <libgen.h>

#include "dblayer.h"
#include "dbhelper.h"
#include "ckutil.h"
#include "ckerrlog.h"

ERRLOG(ckdb);

const char * const DB_FILE_NAME = "/ckdb";

/* figure out the database name */
void make_db_name(char *ret, const char *confPath) {
  char db_path[STR_L];
  strcpy(db_path, confPath);
  strcat(db_path, DB_FILE_NAME);

  strcpy(ret, db_path);
}

/* Check if the db file exists*/
int db_exists(const UserOpt *opt) {
  char db_path[STR_L];
  make_db_name(db_path, opt->confDir);
  return util_is_file_rw(db_path);
}

/* check if db has the correct tables */
int check_initialized_DB(sqlite3 *db) {
  char sql[STR_M];
  dbh_form_query_select_all_tables(sql);
  sqlite3_stmt *stmt;

  sqlite3_prepare_v2(db, sql, (int)strlen(sql), &stmt, NULL);

  int program_table_ok, config_table_ok, rel_table_ok = 0;
  while (sqlite3_step(stmt) != SQLITE_DONE) {
    const unsigned char *tmpbuf  = sqlite3_column_text(stmt, 1);
    if (strcmp((char *)tmpbuf, TBL_PROGRAM) == 0) {
      program_table_ok = 1;
    }
    if (strcmp((char *)tmpbuf, TBL_CONFIG) == 0) {
      config_table_ok = 1;
    }
    if (strcmp((char *)tmpbuf, TBL_REL) == 0) {
      rel_table_ok = 1;
    }
  }

  if (!program_table_ok
      || !config_table_ok
      || !rel_table_ok ) {
    return 1;
  }

  sqlite3_finalize(stmt);
  return 0;
}

DB empty_DB(SqlError err) {
  return (DB){ .ptr = NULL, .error = err };
}

DB new_DB(sqlite3 *db) {
  return (DB){ .ptr = db, .error = SQL_NO_ERR };
}

void close_DB(DB *db) {
  sqlite3_close(db->ptr);
}

DB init_make_DB(const UserOpt *opt) {
  sqlite3 *db;
  char db_path[STR_L];
  int rc;

  make_db_name(db_path, opt->confDir);
  rc = sqlite3_open(db_path, &db);
  if (rc != SQLITE_OK) {
    return empty_DB(SQL_ERR_NO_DB_FILE);
  }

  return new_DB(db);
}

DB open_DB(const UserOpt *opt) {
  sqlite3 *db;
  int rc;
  char db_path[STR_L];

  make_db_name(db_path, opt->confDir);  
  rc = sqlite3_open(db_path, &db);

  if (rc) {
    return empty_DB(SQL_ERR_NO_DB_FILE);
  }

  if (check_initialized_DB(db)) {
    return empty_DB(SQL_ERR_NO_TABLES);
  }

  return new_DB(db);
}

void init_make_tables(DB *db) {
  char sql[STR_L];
  dbh_form_query_make_tables(sql);

  int rc = sqlite3_exec(db->ptr, sql, 0, 0, 0);
  if (rc != SQLITE_OK ) {
    PRINT_ERR("Could not create empty db.");
    db->error = SQL_ERR_SQLITE;
    return;
  }
}

int get_next_valid_id_from_table(DB *db, const char* tableName) {
  sqlite3_stmt *stmt;
  int rc;

  char sql[STR_M];
  dbh_form_query_select_id_from(sql, tableName);
  
  rc = sqlite3_prepare_v2(db->ptr, sql, -1, &stmt, 0);
  if (rc != SQLITE_OK) {
    return -1;
  }
  sqlite3_bind_text(stmt, 1, tableName, (int)strlen(tableName), 0);

  int id = 0;
  while (sqlite3_step(stmt) == SQLITE_ROW) {
    int a = sqlite3_column_int(stmt, 0);
    if (a != id) {
      break;
    }
    id++;
  }
  sqlite3_finalize(stmt);
  return id;
}

int insert_to_program_table(DB *db, const char *name) {
  sqlite3_stmt *stmt;
  int rc;

  char sql[STR_L];
  dbh_form_query_insert_program(sql);

  rc = sqlite3_prepare_v2(db->ptr, sql, -1, &stmt, 0);
  if (rc != SQLITE_OK) {
    PRINT_ERR("while preparing insert to program sql.");
    db->error = SQL_ERR_SQLITE;    
    return -1;
  }
  int id = get_next_valid_id_from_table(db, TBL_PROGRAM);
  if (id == -1) {
    db->error = SQL_ERR_SQLITE;
    return -1;
  }
  sqlite3_bind_int(stmt, 1, id);
  sqlite3_bind_text(stmt, 2, name, (int)strlen(name), 0);
  if (sqlite3_step(stmt) != SQLITE_DONE) {
    PRINT_ERR("while excecuting insert to program sql.");
    db->error = SQL_ERR_SQLITE;
    return -1;
  }
  sqlite3_finalize(stmt);
  return id;
}

int insert_to_config_table(DB *db, const char *path, const int secret, const int prime) {
  sqlite3_stmt *stmt;
  int rc;

  char sql[STR_L];
  dbh_form_query_insert_config(sql);
  rc = sqlite3_prepare_v2(db->ptr, sql, -1, &stmt, 0);
  if (rc != SQLITE_OK) {
    PRINT_ERR("Error while preparing insert to config sql.");
    db->error = SQL_ERR_SQLITE;
    return -1;
  }
  int id = get_next_valid_id_from_table(db, TBL_CONFIG);
  if (id == -1) {
    db->error = SQL_ERR_SQLITE;
    return -1;
  }
  sqlite3_bind_int(stmt, 1, id);
  sqlite3_bind_text(stmt, 2, path, (int)strlen(path), 0);
  sqlite3_bind_int(stmt, 3, secret);
  sqlite3_bind_int(stmt, 4, prime);
  if (sqlite3_step(stmt) != SQLITE_DONE) {
    PRINT_ERR("Error while excecuting insert to config sql.");
    db->error = SQL_ERR_SQLITE;
    return-1;
  }
  sqlite3_finalize(stmt);
  return id;
}

int insert_to_rel_table(DB *db, const int pid, const int cid) {
  sqlite3_stmt *stmt;
  int rc;

  char sql[STR_M];
  dhb_form_query_insert_relationship(sql);
  rc = sqlite3_prepare_v2(db->ptr, sql, -1, &stmt, 0);
  if (rc != SQLITE_OK) {
    db->error = SQL_ERR_SQLITE; 
    PRINT_ERR("while preparing insert to rel sql.");
    return -1;
  }
  sqlite3_bind_int(stmt, 1, pid);
  sqlite3_bind_int(stmt, 2, cid);
  if (sqlite3_step(stmt) != SQLITE_DONE) {
    db->error = SQL_ERR_SQLITE; 
    PRINT_ERR("while excecuting insert to rel sql.");
    return-1;
  }
  sqlite3_finalize(stmt);
  return 1;
}

/* Returns -2 or error, -1 if program doesn't exist
 * else the program ID */
int get_program_id(DB *db, const char* name) {
  sqlite3_stmt *stmt;
  int rc;

  char sql[STR_M];
  dhb_form_query_find_program(sql);

  rc = sqlite3_prepare_v2(db->ptr, sql, -1, &stmt, 0);
  if (rc != SQLITE_OK) {
    PRINT_ERR("Error while preparing get_program_id sql.");
    return -2;
  }
  sqlite3_bind_text(stmt, 1, name, (int)strlen(name), 0);
  int id = -1;
  while (sqlite3_step(stmt) == SQLITE_ROW) {
    id = sqlite3_column_int(stmt, 0);
    break;
  }
  sqlite3_finalize(stmt);
  return id;
}

int program_exists(DB *db, const char *pName) {
  if (get_program_id(db, pName) == -1) {
    return 0;
  }
  return 1;
}

int get_config_id(DB *db, const char* path) {
  sqlite3_stmt *stmt;
  int rc;

  char sql[STR_M];
  dhb_form_query_find_config(sql);

  rc = sqlite3_prepare_v2(db->ptr, sql, -1, &stmt, 0);
  if (rc != SQLITE_OK) {
    PRINT_ERR("while preparing get_config_id sql.");
    return -2;
  }
  sqlite3_bind_text(stmt, 1, path, (int)strlen(path), 0);
  int id = -1;
  while (sqlite3_step(stmt) == SQLITE_ROW) {
    id = sqlite3_column_int(stmt, 0);
    break;
  }
  sqlite3_finalize(stmt);
  return id;
}

int add_insert_relationship(DB *db, const int pid, const int cid) {
  // do checks
  return insert_to_rel_table(db, pid, cid);
}

/* Returns the path of the found config via *ret */
int program_has_primary_config(DB *db, const int pid, char *ret, int *sec) {
  sqlite3_stmt *stmt;
  int rc;

  char sql[STR_L];

  char condition[STR_S] = TBL_PROGRAM;
  strcat(condition, ".");
  strcat(condition, COL_PROGRAM_ID);

  char selection[STR_M] = COL_CONFIG_PRIMARY;
  strcat(selection, ", ");
  strcat(selection, COL_CONFIG_PATH);
  strcat(selection, ", ");
  strcat(selection, COL_CONFIG_SECRET);

  dbh_form_query_select_from_joined_eq(sql, selection, condition);

  rc = sqlite3_prepare_v2(db->ptr, sql, -1, &stmt, 0);
  if (rc != SQLITE_OK) {
    PRINT_ERR("whinnle preparing program_has_primary_exists sql.");
    return -2;
  }

  sqlite3_bind_int(stmt, 1, pid);
  while (sqlite3_step(stmt) == SQLITE_ROW) {
    if (sqlite3_column_int(stmt, 0) == 1) {
      if (ret) {
        strcpy(ret,(char *)sqlite3_column_text(stmt, 1));
      }
      if (sec) {
        *sec = sqlite3_column_int(stmt, 2);
      }
      sqlite3_finalize(stmt);
      return 1;
    }
  }
  sqlite3_finalize(stmt);
  return 0;
}

int add_get_or_insert_config_to_db(DB *db, const int pid, const char *path, const int secret, const int prime, const char *home) {
  char tpath[STR_L];
  if (!swap_home_with_tilde(tpath, path, home)) {
    strcpy(tpath, path);
  }
  int cid = get_config_id(db, tpath);
  if (cid == -2) {
    db->error = SQL_ERR_SQLITE; 
    return -1;
  }
  /* If config doesnt exist insert it and return it's cid */
  if (cid == -1) {
    if (program_has_primary_config(db, pid, NULL, NULL) && prime) {
      db->error = SQL_ERR_PRIMARY_REDEFINITION;
      return -1;
    }
    return insert_to_config_table(db, tpath, secret, prime);
  }

  /* If it exist it means the user has inserted the same path twice */
  db->error = SQL_CONFIG_PATH_EXISTS;
  return -1;
}

int add_get_or_insert_program_to_db(DB *db, const char *name) {
  int pid = get_program_id(db, name);
  if (pid == -2) {
    db->error = SQL_ERR_SQLITE; 
    return -1;
  }
  if (pid == -1) {
    return insert_to_program_table(db, name);
  }
  return pid;
}

int add_basename_exists(DB *db, const char *pName, const char *path) {
  cklist *baseNames = list_make_new();
  get_program_paths(db, baseNames, pName, 1 /*basename */, 0, NULL);
  char *tmp = strdup(path);
  int rc = list_exists(baseNames, basename(tmp));
  free(tmp);
  list_free(baseNames);
  return rc;
}

int add_transaction_try(DB *db, const AddOpt * const opt, const char *home) {
  __BEGIN_TRANSACTION__
  int pid = add_get_or_insert_program_to_db(db, opt->progName);
  if (db->error == SQL_ERR_SQLITE) {
    PRINT_ERR("Could not insert program to db.\n");
    return 1;
  }
  if (add_basename_exists(db, opt->progName, opt->confPath)) {
    ERR("Cannot have two configs with the same basename, for the same program.");
    return 1;
  }
  int cid = add_get_or_insert_config_to_db(db, pid, opt->confPath, opt->secret, opt->prime, home);
  if (db->error == SQL_ERR_SQLITE) {
    PRINT_ERR("Could not insert config to db.\n");
    return 1;
  }
  else if (db->error == SQL_CONFIG_PATH_EXISTS) {
    PRINT_ERR("This config already exists in the database.\n");
    return 1;
  }
  else if (db->error == SQL_ERR_PRIMARY_REDEFINITION) {
    PRINT_ERR("This program already has a primary config.\n");
    return 1;
  }
  add_insert_relationship(db, pid, cid);
  if (db->error == SQL_ERR_SQLITE) {
    PRINT_ERR("rel update failed\n");
    return 1;
  }
  __END_TRANSACTION__

  return 0;
}

int edit_get_prime_config_from_program(DB *db, char *pName, char *ret, int *secret) {
  int pid = get_program_id(db, pName);
  /* error */
  if (pid == -2) {
    return -1;
  }

  /* program exists */
  if (pid > -1) {
    char path[STR_M];
    if (program_has_primary_config(db, pid, path, secret) == 1) {
      if (!str_is_empty(path)) {
        if (ret) {
          str_make_ck_config_name(ret, path, pName);
        }
        return 0;
      }
    }
  }

  /* No prime config found */
  return -1;
}

int edit_get_config(DB *db, const char *pName, char *ret, const char *cName, int *sec) {
  int pid = get_program_id(db, pName);
  /* error */
  if (pid == -2) {
    return -1;
  }

  /* program exists */
  if (pid > -1) {
    sqlite3_stmt *stmt;
    int rc;

    char selection[STR_M] = COL_CONFIG_PATH;
    strcat(selection, ", ");
    strcat(selection, COL_CONFIG_SECRET);
    char condition[STR_M] = TBL_PROGRAM;
    strcat(condition, ".");
    strcat(condition, COL_PROGRAM_ID);

    char sql[STR_L];
    dbh_form_query_select_from_joined_eq(sql, selection, condition);

    rc = sqlite3_prepare_v2(db->ptr, sql, -1, &stmt, 0);
    sqlite3_bind_int(stmt, 1, pid);
    if (rc != SQLITE_OK) {
      return -2;
    }

    int flag = -1;
    while (sqlite3_step(stmt) == SQLITE_ROW) {
      char confName[STR_M];
      if (cName) {
        char *tmp = strdup((char *)sqlite3_column_text(stmt, 0));
        if (strcmp(cName, basename(tmp)) == 0) {
          flag = 0;
          str_make_ck_config_name(confName, (char *)sqlite3_column_text(stmt, 0), pName);
          strcpy(ret, confName);
          if (sec) {
            *sec = sqlite3_column_int(stmt, 1);
          }
        }
        free(tmp);
        break;
      }
      else {
        /* Since we are here, it means there is only 1 config for the selected program */
        flag = 0;
        str_make_ck_config_name(confName, (char *)sqlite3_column_text(stmt, 0), pName);
        strcpy(ret, confName);
        if (sec) {
          *sec = sqlite3_column_int(stmt, 1);
        }
        break;
      }
    }
    sqlite3_finalize(stmt);
    return flag;
  }
  return -1;
}

int get_program_paths(DB *db, cklist *ckl, const char* pName, int bname, int attr, const char *home) {
  int pid = get_program_id(db, pName);
  /* error */
  if (pid == -2) {
    return -1;
  }

  /* program exists */
  if (pid > -1) {
    sqlite3_stmt *stmt;
    int rc;

    char selection[STR_M] = COL_CONFIG_PATH;
    strcat(selection, ",");
    strcat(selection, COL_CONFIG_SECRET);
    strcat(selection, ",");
    strcat(selection, COL_CONFIG_PRIMARY);
    char condition[STR_M] = TBL_PROGRAM;
    strcat(condition, ".");
    strcat(condition, COL_PROGRAM_ID);

    char sql[STR_L];
    dbh_form_query_select_from_joined_eq(sql, selection, condition);

    rc = sqlite3_prepare_v2(db->ptr, sql, -1, &stmt, 0);
    sqlite3_bind_int(stmt, 1, pid);
    if (rc != SQLITE_OK) {
      return -2;
    }

    while (sqlite3_step(stmt) == SQLITE_ROW) {
      char *tmp = strdup((char *)sqlite3_column_text(stmt, 0));
      char entry[STR_M] = "";
      if (bname) {
        strcat(entry, basename(tmp));
      }
      else {
        char tpath[STR_L] = "";
        if (swap_tilde_with_home(tpath, tmp, home)) {
          strcat(entry, tpath);
        }
        else {
          strcat(entry, tmp);
        }
      }
      if (attr) {
        /* primary */
        if (sqlite3_column_int(stmt, 2)) {
          strcat(entry, " [p]");
        }
        /* secret */
        if (sqlite3_column_int(stmt, 1)) {
          strcat(entry, " [s]");
        }
        /* root */
        if (util_is_link_owned_by_root((char *)sqlite3_column_text(stmt, 0))) {
          strcat(entry, " [root]");
        }
      }
      list_add(ckl, entry);
      free(tmp);
    }
    sqlite3_finalize(stmt);
    return 0;
  }
  return -1;
}

int list_get_paths(DB *db, cklist *ckl, int bName, int attr, const char *home) {
  sqlite3_stmt *stmt;
  int rc;

  char sql[STR_M];
  dbh_form_query_select_paths_with_attributes(sql);

  rc = sqlite3_prepare_v2(db->ptr, sql, -1, &stmt, 0);
  if (rc != SQLITE_OK) {
    return -2;
  }

  while (sqlite3_step(stmt) == SQLITE_ROW) {
    char *tmp = strdup((char *)sqlite3_column_text(stmt, 0));
    char path[STR_L] = "";
    if (bName) {
      strcat(path, basename(tmp));
    }
    else {
      char tpath[STR_L] = "";
      if (swap_tilde_with_home(tpath, tmp, home)) {
          strcat(path, tpath);
        }
      else {
        strcat(path, tmp);
      }
    }
    free(tmp);
    if (attr) {
      /* primary */
      if (sqlite3_column_int(stmt, 2)) {
        strcat(path, " [p]");
      }
      /* secret */
      if (sqlite3_column_int(stmt, 1)) {
        strcat(path, " [s]");
      }
      /* root */
      if (util_is_link_owned_by_root((char *)sqlite3_column_text(stmt, 0))) {
        strcat(path, " [root]");
        }
    }
    list_add(ckl, path);
  }
  sqlite3_finalize(stmt);

  return 1;
}

int list_get_programs(DB *db, cklist *ckl) {
  sqlite3_stmt *stmt;
  int rc;

  char sql[STR_M];
  dbh_form_query_select_programs(sql);

  rc = sqlite3_prepare_v2(db->ptr, sql, -1, &stmt, 0);
  if (rc != SQLITE_OK) {
    return -2;
  }

  while (sqlite3_step(stmt) == SQLITE_ROW) {
    list_add(ckl, (char *)sqlite3_column_text(stmt, 0));
  }
  sqlite3_finalize(stmt);

  return 1;
}

int list_get_path_program_tree(DB *db, cklist *ckl, int bName, int attr, const char *home) {
  sqlite3_stmt *stmt;
  int rc;

  char sql[STR_M];
  dbh_form_query_select_programs(sql);

  rc = sqlite3_prepare_v2(db->ptr, sql, -1, &stmt, 0);
  if (rc != SQLITE_OK) {
    return -2;
  }

  while (sqlite3_step(stmt) == SQLITE_ROW) {
    char programName[STR_M] = "";
    strcat(programName, (char *)sqlite3_column_text(stmt, 0));
    strcat(programName, ":");
    list_add(ckl, programName);
    sqlite3_stmt *stmt2;
    int rc2;

    char sql2[STR_L];

    char selection[STR_M] = COL_CONFIG_PATH;
    strcat(selection, ",");
    strcat(selection, COL_CONFIG_SECRET);
    strcat(selection, ",");
    strcat(selection, COL_CONFIG_PRIMARY);

    char condition[STR_M] = TBL_PROGRAM;
    strcat(condition, ".");
    strcat(condition, COL_PROGRAM_NAME);

    dbh_form_query_select_from_joined_eq(sql2, selection, condition);

    rc2 = sqlite3_prepare_v2(db->ptr, sql2, -1, &stmt2, 0);
    if (rc2 != SQLITE_OK) {
      return -2;
    }

    sqlite3_bind_text(stmt2, 1, (char *)sqlite3_column_text(stmt, 0), -1, 0);
    while (sqlite3_step(stmt2) == SQLITE_ROW) {
      char treePath[STR_L] = "|- ";
      char *tmp = strdup((char *)sqlite3_column_text(stmt2, 0));
      if (bName) {
        strcat(treePath, basename(tmp));
      }
      else {
        char tpath[STR_L] = "";
        if (swap_tilde_with_home(tpath, tmp, home)) {
          strcat(treePath, tpath);
        }
        else {
          strcat(treePath, tmp);
        }
      }
      free(tmp);
      if (attr) {
        /* primary */
        if (sqlite3_column_int(stmt2, 2)) {
          strcat(treePath, " [p]");
        }
        /* secret */
        if (sqlite3_column_int(stmt2, 1)) {
          strcat(treePath, " [s]");
        }
        /* root */
        if (util_is_link_owned_by_root((char *)sqlite3_column_text(stmt2, 0))) {
          strcat(treePath, " [root]");
        }
      }
      list_add(ckl, treePath);
    }
    sqlite3_finalize(stmt2);
  }
  sqlite3_finalize(stmt);

  return 1;
}

int delete_prog(DB *db, int pid) {
  sqlite3_stmt *stmt;
  int rc;

  char sql[STR_M];
  dbh_form_query_delete_x_from_y(sql, COL_PROGRAM_ID, TBL_PROGRAM);

  rc = sqlite3_prepare_v2(db->ptr, sql, -1, &stmt, 0);
  if (rc != SQLITE_OK) {
    return -1;
  }
  sqlite3_bind_int(stmt, 1, pid);
  sqlite3_step(stmt);
  if (rc != SQLITE_OK) {
    return -1;
  }

  sqlite3_finalize(stmt);
  return 0;
}

int delete_conf(DB *db, int cid) {
  sqlite3_stmt *stmt;
  int rc;

  char sql[STR_M];
  dbh_form_query_delete_x_from_y(sql, COL_CONFIG_ID, TBL_CONFIG);

  rc = sqlite3_prepare_v2(db->ptr, sql, -1, &stmt, 0);
  if (rc != SQLITE_OK) {
    return -1;
  }
  sqlite3_bind_int(stmt, 1, cid);
  sqlite3_step(stmt);
  if (rc != SQLITE_OK) {
    return -1;
  }

  sqlite3_finalize(stmt);
  return 0;
}

int get_pid_from_cid(DB *db, int cid) {
  int pid = -1;
  sqlite3_stmt *stmt;
  int rc;

  char sql[STR_M];
  dbh_form_query_get_pid_from_cid(sql);

  rc = sqlite3_prepare_v2(db->ptr, sql, -1, &stmt, 0);
  if (rc != SQLITE_OK) {
    return -1;
  }

  sqlite3_bind_int(stmt, 1, cid);
  
  while (sqlite3_step(stmt) == SQLITE_ROW) {
    pid = sqlite3_column_int(stmt, 0);
  }
  
  if (rc != SQLITE_OK) {
    return -1;
  }
  sqlite3_finalize(stmt);
  return pid;
}

int get_program_relations(DB *db, int pid) {
  int count = -1;
  sqlite3_stmt *stmt;
  int rc;

  char sql[STR_M];
  dbh_form_query_count_program_relations(sql);

  rc = sqlite3_prepare_v2(db->ptr, sql, -1, &stmt, 0);
  if (rc != SQLITE_OK) {
    return -1;
  }
  
  sqlite3_bind_int(stmt, 1, pid);
  while (sqlite3_step(stmt) == SQLITE_ROW) {
    count = sqlite3_column_int(stmt, 0);
  }
  
  if (rc != SQLITE_OK) {
    return -1;
  }
  sqlite3_finalize(stmt);
  return count;
}

int get_config_number(DB *db, char* pName) {
  int pid = get_program_id(db, pName);
  /* program exists */
  if (pid > -1) {
    return get_program_relations(db, pid);
  }
  return -1;
}

/* Removes the relationship of `cid` with the corresponding program.
 * Returns the program's pid on succes, negative integer otherwise.
 */
int remove_conf_rel(DB *db, int cid) {
  sqlite3_stmt *stmt;
  int rc;

  int pid = get_pid_from_cid(db, cid);

  char sql[STR_M];
  dbh_form_query_delete_x_from_y(sql, COL_REL_CONFIG_ID, TBL_REL);
  rc = sqlite3_prepare_v2(db->ptr, sql, -1, &stmt, 0);
  if (rc != SQLITE_OK) {
    return -1;
  }
  sqlite3_bind_int(stmt, 1, cid);
  sqlite3_step(stmt);
  if (rc != SQLITE_OK) {
    return -1;
  }

  sqlite3_finalize(stmt);

  return pid;
}

int remove_all_configs(DB *db, int pid) {
  sqlite3_stmt *stmt;
  int rc;

  char sql[STR_M];
  dbh_form_query_select_from_joined_eq(sql, COL_REL_CONFIG_ID, COL_REL_PROGRAM_ID);
  rc = sqlite3_prepare_v2(db->ptr, sql, -1, &stmt, 0);
  if (rc != SQLITE_OK) {
    return -2;
  }

  sqlite3_bind_int(stmt, 1, pid);

  while (sqlite3_step(stmt) == SQLITE_ROW) {
    delete_conf(db, sqlite3_column_int(stmt, 0));
    remove_conf_rel(db, sqlite3_column_int(stmt, 0));
  }
  sqlite3_finalize(stmt);

  return 0;
}

int del_transaction_try(DB *db, char *arg, int conf) {
  __BEGIN_TRANSACTION__
  int pid = -1;
  if (conf) {
    // del conf
    int cid = get_config_id(db, arg);
    if (cid < 0) {
      ERR("Config %s doesn't exist in the database.", arg);
      return -1;
    }
    if (delete_conf(db, cid)) {
      ERR("Could not delete config from db.");
      return -1;
    }
    // handle relations
    pid = remove_conf_rel(db, cid);
    HELP("Deleted config: %s", arg);
    if (get_program_relations(db, pid) > 0) {
      goto end;
    }
  }
  else {
    pid = get_program_id(db, arg);
  }
  if (pid < 0) {
    ERR("Program not found in the db.");
    return -1;
  }
  /* If we are deleting a program we should delete everything that
   * refferences it (configs and relationships) */
  if (!conf) {
    remove_all_configs(db, pid);
  }  
  if(delete_prog(db, pid)) {
    ERR("Could not delete program from db.");
    return -1;
  }
  HELP("Deleted program %s", conf ? "" : arg);
 end:
  __END_TRANSACTION__
  return 0;
}

int restore_program(DB *db, Conf *conf, const char *pName) {
  sqlite3_stmt *stmt;
  int rc;

  char sql[STR_L];

  char selection[STR_M] = COL_CONFIG_PATH;
  strcat(selection, ",");
  strcat(selection, COL_CONFIG_SECRET);

  char condition[STR_M] = TBL_PROGRAM;
  strcat(condition, ".");
  strcat(condition, COL_PROGRAM_NAME);

  dbh_form_query_select_from_joined_eq(sql, selection, condition);

  rc = sqlite3_prepare_v2(db->ptr, sql, -1, &stmt, 0);
  if (rc != SQLITE_OK) {
    return 0;
  }

  sqlite3_bind_text(stmt, 1, pName, -1, 0);
  int err_flag = 0;
  while (sqlite3_step(stmt) == SQLITE_ROW) {
    int secret = sqlite3_column_int(stmt, 1);
    char filePath[STR_L];
    strcpy(filePath, secret ? conf->scrt_dir : conf->vc_dir);
    strcat(filePath, "/");
    strcat(filePath, pName);
    strcat(filePath, "/");
    strcat(filePath, basename((char *)sqlite3_column_text(stmt, 0)));
    if (!util_is_file_rw(filePath)) {
      sERR("%s does not exist or is not accessible.", filePath);
      err_flag = 1;
    }
  }
  sqlite3_finalize(stmt);
  return err_flag;
}

int restore_configs_exists(DB *db, Conf *conf, const char *pName, cklist *from, cklist *to) {
  sqlite3_stmt *stmt;
  int rc;

  char sql[STR_L];

  char selection[STR_M] = COL_CONFIG_PATH;
  strcat(selection, ",");
  strcat(selection, COL_CONFIG_SECRET);

  char condition[STR_M] = TBL_PROGRAM;
  strcat(condition, ".");
  strcat(condition, COL_PROGRAM_NAME);

  dbh_form_query_select_from_joined_eq(sql, selection, condition);

  rc = sqlite3_prepare_v2(db->ptr, sql, -1, &stmt, 0);
  if (rc != SQLITE_OK) {
    return 0;
  }

  sqlite3_bind_text(stmt, 1, pName, -1, 0);
  int err_flag = 0;
  while (sqlite3_step(stmt) == SQLITE_ROW) {
    char filePath[STR_L];
    strcpy(filePath, /*secret*/ sqlite3_column_int(stmt, 1) ? conf->scrt_dir : conf->vc_dir);
    strcat(filePath, "/");
    strcat(filePath, pName);
    if (!util_is_dir(filePath)) {
      sERR("%s is not a directory.", filePath);
      err_flag = 1;
      break;
    }
    strcat(filePath, "/");
    strcat(filePath, basename(/*path*/ (char *)sqlite3_column_text(stmt, 0)));
    if (!util_is_file_rw(filePath)) {
      sERR("%s does not exist or is not accessible.", filePath);
      err_flag = 1;
    }
    list_add(from, filePath);
    list_add(to, /*path*/ (char *)sqlite3_column_text(stmt, 0));
  }
  sqlite3_finalize(stmt);
  return !err_flag;
}

int restore_all_exist(DB *db, Conf *conf, cklist *from, cklist *to) {
  cklist *programs = list_make_new();
  if (list_get_programs(db, programs) != 1) {
    ERR("No programs in ckdb");
    list_free(programs);
    return 0;
  }
  int err_flag = 0;
  if (list_size(programs) > 0) {
    do {
      if (!restore_configs_exists(db, conf, list_get(programs), from, to)) {
        err_flag = 1;
      }
    } while(list_next(programs));
  }
  list_free(programs);
  return !err_flag;
}