comment_app_backend 0.1.3

// db.rs
// database related functions are implemented here

use std::fs::create_dir;
use std::path::Path;
use std::sync::Arc;
use tokio::sync::Mutex;

use rusqlite::{Connection, named_params, Result, Row};
use rusqlite::NO_PARAMS;
use log::*;

use crate::models::comment::Comment;
use crate::models::status::Status;
use crate::models::reply::Reply;
use crate::models::upvote::Upvote;
use crate::models::downvote::Downvote;

pub type DbConn = Arc<Mutex<Connection>>;

// database connectivity
pub fn conn() -> DbConn {
    //debug!("db::conn().................<< called up");
    let folder = super::config("db_folder");
    let filename = super::config("db_filename");
    let db_init_needed = dir_created(&folder) || file_missing(&folder, &filename);
    let conn = Connection::open( file_path_string(&folder, &filename) ).unwrap(); // creates db if it is missing and then, opens a connection
    if db_init_needed {
        debug!("starting db init.......................");
        init_db(&conn); // database does not exist, then initialize it
    }
    Arc::new(Mutex::new(conn))
}

// database setup
fn init_db(conn: &Connection) {
    match create_comment_table(conn) {
        Ok(count) => debug!("{:?} comment table is created", count),
        Err(message) => error!("{:?} occurred in create comment table", message),
    }
    match create_archive_comment_table(conn) {
        Ok(count) => debug!("{:?} archive comment table is created", count),
        Err(message) => error!("{:?} occurred in create archive comment table", message),
    }
    match create_reply_table(conn) {
        Ok(count) => debug!("{:?} reply table is created", count),
        Err(message) => error!("{:?} occurred in create reply table", message),
    }
    match create_archive_reply_table(conn) {
        Ok(count) => debug!("{:?} reply table is created", count),
        Err(message) => error!("{:?} occurred in create reply table", message),
    }
    match create_upvote_table(conn) {
        Ok(count) => debug!("{:?} upvote table is created", count),
        Err(message) => error!("{:?} occurred in create upvote table", message),
    }    
    match create_archive_upvote_table(conn) {
        Ok(count) => debug!("{:?} archive upvote table is created", count),
        Err(message) => error!("{:?} occurred in create archive upvote table", message),
    }
    match create_downvote_table(conn) {
        Ok(count) => debug!("{:?} downvote table is created", count),
        Err(message) => error!("{:?} occurred in create downvote table", message),
    }    
    match create_archive_downvote_table(conn) {
        Ok(count) => debug!("{:?} archive downvote table is created", count),
        Err(message) => error!("{:?} occurred in create archive downvote table", message),
    }
    debug!("Database initialized...");
}

// database folder and file
fn dir_created(folder: &str) -> bool {
    if Path::new(&folder).is_dir() {
        return false;
    }
    debug!("{} folder is CREATED", &folder);
    create_dir(&folder).unwrap();
    true
}
fn file_missing(folder: &str, filename: &str) -> bool {
    let path = file_path_string(folder, filename);
    !Path::new(&path).is_file()
}
fn file_path_string(folder: &str, filename: &str) -> String {
    folder.to_owned() + &"/".to_string() + &filename
}

// Table(s) creation
fn create_comment_table(conn: &Connection) -> Result<usize> {
    let count = conn.execute(
        "create table if not exists comment(
            id INTEGER PRIMARY KEY,
            message TEXT,
            anonymous INTEGER,
            status TEXT,
            remarks TEXT,
            unique_id TEXT UNIQUE,
            user_id TEXT,
            created_on TEXT,
            updated_on TEXT,
            deleted_on TEXT
        )", 
        NO_PARAMS,)?;
    Ok(count)
}
fn create_archive_comment_table(conn: &Connection) -> Result<usize> {
    let count = conn.execute(
        "create table if not exists archive_comment(
            id INTEGER PRIMARY KEY,
            message TEXT,
            anonymous INTEGER,
            status TEXT,
            remarks TEXT,
            unique_id TEXT,
            user_id TEXT,
            created_on TEXT,
            updated_on TEXT,
            deleted_on TEXT
        )", 
        NO_PARAMS,)?;
    Ok(count)
}
fn create_reply_table(conn: &Connection) -> Result<usize> {
    let count = conn.execute(
        "create table if not exists reply(
            id INTEGER PRIMARY KEY,
            message TEXT,
            anonymous INTEGER,
            status TEXT,
            remarks TEXT,
            unique_id TEXT UNIQUE,
            parent_id TEXT,
            comment_id TEXT,
            level INTEGER,
            user_id TEXT,
            created_on TEXT,
            updated_on TEXT,
            deleted_on TEXT
        )", 
        NO_PARAMS,)?;
    Ok(count)
}
fn create_archive_reply_table(conn: &Connection) -> Result<usize> {
    let count = conn.execute(
        "create table if not exists archive_reply(
            id INTEGER PRIMARY KEY,
            message TEXT,
            anonymous INTEGER,
            status TEXT,
            remarks TEXT,
            unique_id TEXT,
            parent_id TEXT,
            comment_id TEXT,
            level INTEGER,
            user_id TEXT,
            created_on TEXT,
            updated_on TEXT,
            deleted_on TEXT
        )", 
        NO_PARAMS,)?;
    Ok(count)
}
fn create_upvote_table(conn: &Connection) -> Result<usize> {
    let count = conn.execute(
        "create table if not exists upvote(
            id INTEGER PRIMARY KEY,
            comment_id TEXT,
            user_id TEXT,
            remarks TEXT,
            created_on TEXT,
            updated_on TEXT,
            deleted_on TEXT,
            UNIQUE(comment_id, user_id)
        )", 
        NO_PARAMS,)?;
    Ok(count)
}
fn create_archive_upvote_table(conn: &Connection) -> Result<usize> {
    let count = conn.execute(
        "create table if not exists archive_upvote(
            id INTEGER PRIMARY KEY,
            comment_id TEXT,
            user_id TEXT,
            remarks TEXT,
            created_on TEXT,
            updated_on TEXT,
            deleted_on TEXT
        )", 
        NO_PARAMS,)?;
    Ok(count)
}
fn create_downvote_table(conn: &Connection) -> Result<usize> {
    let count = conn.execute(
        "create table if not exists downvote(
            id INTEGER PRIMARY KEY,
            comment_id TEXT,
            user_id TEXT,
            remarks TEXT,
            created_on TEXT,
            updated_on TEXT,
            deleted_on TEXT,
            UNIQUE(comment_id, user_id)
        )", 
        NO_PARAMS,)?;
    Ok(count)
}
fn create_archive_downvote_table(conn: &Connection) -> Result<usize> {
    let count = conn.execute(
        "create table if not exists archive_downvote(
            id INTEGER PRIMARY KEY,
            comment_id TEXT,
            user_id TEXT,
            remarks TEXT,
            created_on TEXT,
            updated_on TEXT,
            deleted_on TEXT
        )", 
        NO_PARAMS,)?;
    Ok(count)
}

// Actions on Comment table
fn sql_to_add_comment() -> String {
    "INSERT INTO comment (
        message, 
        anonymous,
        status, 
        remarks, 
        unique_id, 
        user_id,
        created_on
    ) VALUES (
        :message,
        :anonymous,
        :status,
        :remarks,
        :unique_id,
        :user_id,
        :created_on
    )".to_string()
}

pub async fn add_comment(comment: &Comment, db_conn: DbConn) -> Result<usize> {
    debug!("add_comment({:?})........................", comment);
    let conn = db_conn.lock().await;
    let row_count = conn.execute_named(
        &sql_to_add_comment(),
        named_params!{
            ":message": comment.message,
            ":anonymous": comment.anonymous,
            ":status": comment.status.to_string(),
            ":remarks": comment.remarks,
            ":unique_id": comment.unique_id,
            ":user_id": comment.user_id,
            ":created_on": comment.created_on
        } 
    )?;
    Ok(row_count)
}

pub async fn _get_comments(status: &str, db_conn: DbConn) -> Result<Vec<Comment>> {
    debug!("get_comments({:?})..........................<<", &status);
    let conn = db_conn.lock().await;
    let mut stmt = conn.prepare(
        "SELECT 
            message, anonymous, status, remarks, unique_id, 
            user_id, created_on, updated_on, deleted_on 
         FROM comment 
         WHERE status = :status
         ORDER BY unique_id ASC")?;
    let rows = stmt.query_map_named(
        &[ (":status", &status.to_string()) ], 
        |row| extract_comment_data(row) 
    )?;
    let mut results = Vec::new();
    for row in rows {
        results.push(row?);
    }
    debug!("comments are: {:?}", results);
    Ok(results)
}
fn sql_to_get_all_comments() -> String {
    "SELECT 
        message, anonymous, status, remarks, unique_id, 
        user_id, created_on, updated_on, deleted_on 
    FROM comment 
    WHERE deleted_on is null OR deleted_on = ''
    ORDER BY unique_id ASC".to_string()
}
pub async fn get_all_comments(db_conn: DbConn) -> Result<Vec<Comment>> {
    debug!("get_all_comments()..........................<<");
    let conn = db_conn.lock().await;
    let mut stmt = conn.prepare( &sql_to_get_all_comments() )?;
    let rows = stmt.query_map_named(
        &[], 
        |row| extract_comment_data(row) 
    )?;
    let mut results = Vec::new();
    for row in rows {
        results.push(row?);
    }
    debug!("all comments are: {:?}", results);
    Ok(results)
}
fn sql_to_get_more_comments() -> String {
    "SELECT 
        message, anonymous, status, remarks, unique_id, 
        user_id, created_on, updated_on, deleted_on 
    FROM comment 
    WHERE deleted_on is null OR deleted_on = ''
    ORDER BY unique_id ASC
    LIMIT :count".to_string()
}
pub async fn get_more_comments(count: usize, db_conn: DbConn) -> Result<Vec<Comment>> {
    debug!("get_more_comments()..........................<<");
    let conn = db_conn.lock().await;
    let mut stmt = conn.prepare( &sql_to_get_more_comments() )?;
    let rows = stmt.query_map_named(
        &[ ( ":count", &count.to_string() )], 
        |row| extract_comment_data(row) 
    )?;
    let mut results = Vec::new();
    for row in rows {
        results.push(row?);
    }
    debug!("more comments are: {:?}", results);
    Ok(results)
}

fn extract_comment_data(row: &Row) -> Result<Comment> {
    Ok( Comment {
        message: row.get(0).unwrap_or(String::new()), // message
        anonymous: row.get(1).unwrap_or(false), // anonymous
        status: Status::from_string( row.get(2).unwrap_or(String::from("Pending")) ), // status
        remarks: row.get(3).unwrap_or(String::new()), // remarks
        unique_id: row.get(4).unwrap_or(String::new()), // unique_id
        user_id: row.get(5).unwrap_or(String::new()), // unique_id
        created_on: row.get(6).unwrap_or(String::new()), // created_on
        updated_on: row.get(7).unwrap_or(String::new()), // updated_on
        deleted_on: row.get(8).unwrap_or(String::new())  // deleted_on
    })
}
fn sql_to_update_comment() -> String {
    "UPDATE comment
    SET 
        message = :message, 
        status = :status, 
        remarks = :remarks,
        updated_on = :updated_on
    WHERE 
        unique_id = :unique_id".to_string()
}
pub async fn update_comment(comment: &Comment, db_conn: DbConn) -> Result<usize> {
    debug!("update_comment(..)...............<<");
    let conn = db_conn.lock().await;
    // archive the row before carrying out the update
    let _row_count = conn.execute_named(
        &sql_to_archive_comment(),
        &[ (":unique_id", &comment.unique_id) ] )?;
    // carry out the update now    
    let row_count = conn.execute_named(
        &sql_to_update_comment(),
        &[ (":message", &comment.message),
           (":status", &comment.status.to_string()),
           (":remarks", &comment.remarks),
           (":updated_on", &comment.updated_on),
           (":unique_id", &comment.unique_id) ]
    )?;
    Ok(row_count)
}

fn sql_to_update_comment_status() -> String {
    "UPDATE comment
    SET 
        status = :status, 
        remarks = 'status updated',
        updated_on = :updated_on
    WHERE 
        unique_id = :unique_id".to_string()
}

pub async fn update_comment_status(status: Status, uid: String, now: String, db_conn: DbConn) -> Result<usize> {
    debug!("update_comment_status(..)...............<<");
    let conn = db_conn.lock().await;
    // archive the row before carrying out the update
    let _row_count = conn.execute_named(
        &sql_to_archive_comment(),
        &[ (":unique_id", &uid) ] )?;
    // carry out the update now    
    let row_count = conn.execute_named(
        &sql_to_update_comment_status(),
        &[
            (":status", &status.to_string()),
            (":updated_on", &now),
            (":unique_id", &uid),
        ]
    )?;
    Ok(row_count)
}
fn sql_to_update_comment_message() -> String {
    "UPDATE comment
        SET 
            message = :message, 
            remarks = 'message updated',
            updated_on = :updated_on
        WHERE 
            unique_id = :unique_id".to_string()
}
pub async fn update_comment_message(msg: String, uid: String, now: String, db_conn: DbConn) -> Result<usize> {
    debug!("update_comment_message(..)...............<<");
    let conn = db_conn.lock().await;
    // archive the row before carrying out the update
    let _row_count = conn.execute_named(
        &sql_to_archive_comment(),
        &[ (":unique_id", &uid) ] )?;
    // carry out the update now    
    let row_count = conn.execute_named(
        &sql_to_update_comment_message(),
        &[ (":message", &msg),
           (":updated_on", &now),
           (":unique_id", &uid) ]
    )?;
    Ok(row_count)
}

fn comment_deleted_on() -> String {
    "UPDATE comment
    SET remarks = 'deleted', deleted_on = :deleted_on
    WHERE unique_id = :unique_id".to_string()
}
fn replies_deleted_on() -> String { // marks to deleted on all replies to a comment
    "UPDATE reply
    SET remarks = 'deleted', deleted_on = :deleted_on
    WHERE comment_id = :comment_id".to_string()
}

pub async fn delete_comment(uid: &str, now: String, db_conn: DbConn) -> Result<usize> {
    debug!("delete comment(..)................<<");
    let conn = db_conn.lock().await;
    // step 1a: update deleted on field in comment table
    let _row_count = conn.execute_named(
        &comment_deleted_on(),
        &[ (":deleted_on", &now), (":unique_id", &uid) ] )?;
    // step 1b: update deleted on field in reply table
    let _row_count = conn.execute_named(
        &replies_deleted_on(),
        &[ (":deleted_on", &now), (":comment_id", &uid) ] )?;
    // step 2a: archive the row to be deleted into archive_comment table
    let _row_count = conn.execute_named(
        &sql_to_archive_comment(),
        &[ (":unique_id", &uid) ] )?;
    // step 2b: archive the row to be deleted into archive_reply table
    let _row_count = conn.execute_named(
        &sql_to_archive_all_replies_on_comment(),
        &[ (":comment_id", &uid) ] )?;
    // step 3a: actual delete on replies
    let _result = conn.execute_named(
        "DELETE FROM reply WHERE comment_id = :uid",
        &[ (":uid", &uid) ] )?;

    // step 3b: actual delete on comment 
    let result = conn.execute_named(
        "DELETE FROM comment WHERE unique_id = :uid",
        &[ (":uid", &uid) ] )?;

    Ok(result)
}
fn sql_to_get_comment() -> String {
    "SELECT
        message, anonymous, status, remarks, unique_id, 
        user_id, created_on, updated_on, deleted_on         
    FROM comment 
    WHERE unique_id = :uid".to_string()
}
pub async fn get_comment(uid: &str, db_conn: DbConn) -> Result<Comment> {
    debug!("get_comment({}).....................<<", uid);
    let conn = db_conn.lock().await;
    let result = conn.query_row_named(
        &sql_to_get_comment(),
        &[ (":uid", &uid) ],
        |row| extract_comment_data(row)
    )?;
    debug!("Comment is: {:?}", result);
    Ok(result)
}

fn sql_to_get_comments_total() -> String {
    "SELECT count(*)
    FROM comment 
    WHERE deleted_on is null OR deleted_on = ''
    ORDER BY unique_id ASC".to_string()
}
pub async fn get_comments_total(db_conn: DbConn) -> Result<u32> {
    debug!("get_comments_total().....................<<");
    let conn = db_conn.lock().await;
    let result = conn.query_row_named(
        &sql_to_get_comments_total(),
        &[ ],
        |row| row.get(0)
    )?;
    debug!("Total comments are: {:?}", result);
    Ok(result)
}

// Actions on Archive Comment table
pub fn sql_to_archive_comment() -> String {
    "INSERT INTO archive_comment (
            message, 
            anonymous,
            status, 
            remarks, 
            unique_id, 
            user_id,
            created_on,
            updated_on,
            deleted_on
        ) 
        SELECT 
            message,
            anonymous,
            status,
            remarks,
            unique_id,
            user_id,
            created_on,
            updated_on,
            deleted_on
        FROM
            comment
        WHERE 
            unique_id = :unique_id".to_string()
}

// Actions on Reply table
fn sql_to_add_reply() -> String {
    "INSERT INTO reply (
        message, 
        anonymous,
        status, 
        remarks, 
        unique_id, 
        parent_id,
        comment_id,
        level,
        user_id,
        created_on
    ) VALUES (
        :message,
        :anonymous,
        :status,
        :remarks,
        :unique_id,
        :parent_id,
        :comment_id,
        :level,
        :user_id,
        :created_on
    )".to_string()
}
pub async fn add_reply(reply: &Reply, db_conn: DbConn) -> Result<usize> {
    debug!("add_reply({:?})........................", reply);
    let conn = db_conn.lock().await;
    let row_count = conn.execute_named(
        &sql_to_add_reply(),
        named_params!{
            ":message": reply.message,
            ":anonymous": reply.anonymous,
            ":status": reply.status.to_string(),
            ":remarks": reply.remarks,
            ":unique_id": reply.unique_id,
            ":parent_id": reply.parent_id,
            ":comment_id": reply.comment_id,
            ":level": reply.level,
            ":user_id": reply.user_id,
            ":created_on": reply.created_on
        } 
    )?;
    Ok(row_count)
}
fn sql_to_get_replies() -> String {
    "SELECT 
        message, anonymous, status, remarks, unique_id, 
        parent_id, comment_id, level, user_id, created_on, updated_on, deleted_on 
    FROM reply 
    WHERE comment_id = :comment_id 
        AND status = :status 
        AND ( deleted_on is null OR deleted_on = '')
    ORDER BY unique_id ASC".to_string()
}
pub async fn get_replies(comment_id: String, status: &str, db_conn: DbConn) -> Result<Vec<Reply>> {
    debug!("get_replies({:?}, {:?})..........................<<", &comment_id, &status);
    let conn = db_conn.lock().await;
    let mut stmt = conn.prepare( &sql_to_get_replies() )?;
    let rows = stmt.query_map_named(
        &[ (":comment_id", &comment_id),
           (":status", &status.to_string()) ], 
        |row| extract_reply_data(row) 
    )?;
    let mut results = Vec::new();
    for row in rows {
        results.push(row?);
    }
    debug!("replies are: {:?}", results);
    Ok(results)
}

fn extract_reply_data(row: &Row) -> Result<Reply> {
    Ok( Reply {
        message: row.get(0).unwrap_or(String::new()), // message
        anonymous: row.get(1).unwrap_or(false), // anonymous
        status: Status::from_string( row.get(2).unwrap_or(String::from("Pending")) ), // status
        remarks: row.get(3).unwrap_or(String::new()), // remarks
        unique_id: row.get(4).unwrap_or(String::new()), // unique_id
        parent_id: row.get(5).unwrap_or(String::new()), // parent_id
        comment_id: row.get(6).unwrap_or(String::new()), // comment_id
        level: row.get(7).unwrap_or(0), // level
        user_id: row.get(8).unwrap_or(String::new()), // unique_id
        created_on: row.get(9).unwrap_or(String::new()), // created_on
        updated_on: row.get(10).unwrap_or(String::new()), // updated_on
        deleted_on: row.get(11).unwrap_or(String::new())  // deleted_on
    })
}
fn sql_to_update_reply() -> String {
    "UPDATE reply
    SET 
        message = :message, 
        status = :status, 
        remarks = :remarks,
        updated_on = :updated_on
    WHERE 
        unique_id = :unique_id".to_string()
}
pub async fn update_reply(reply: &Reply, db_conn: DbConn) -> Result<usize> {
    debug!("update_reply(..)...............<<");

    let conn = db_conn.lock().await;
    // archive the row before carrying out the update
    let _row_count = conn.execute_named(
        &sql_to_archive_reply(),
        &[ (":unique_id", &reply.unique_id) ] )?;
    // carry out the update now    
    let row_count = conn.execute_named(
        &sql_to_update_reply(),
        &[ (":message", &reply.message),
           (":status", &reply.status.to_string()),
           (":remarks", &reply.remarks),
           (":updated_on", &reply.updated_on),
           (":unique_id", &reply.unique_id) ]
    )?;
    Ok(row_count)
}
fn sql_to_update_reply_message() -> String {
    "UPDATE reply
    SET 
        message = :message, 
        remarks = 'message updated',
        updated_on = :updated_on
    WHERE 
        unique_id = :unique_id".to_string()
}
pub async fn update_reply_message(msg: String, uid: String, now: String, db_conn: DbConn) -> Result<usize> {
    debug!("update_reply_message(..)...............<<");
    let conn = db_conn.lock().await;
    // archive the row before carrying out the update
    let _row_count = conn.execute_named(
        &sql_to_archive_reply(),
        &[ (":unique_id", &uid) ] )?;
    // carry out the update now
    let row_count = conn.execute_named(
        &sql_to_update_reply_message(),
        &[ (":message", &msg),
           (":updated_on", &now),
           (":unique_id", &uid) ]
    )?;
    Ok(row_count)
}

fn reply_deleted_on() -> String {
    "UPDATE reply
     SET remarks = 'deleted', deleted_on = :deleted_on
     WHERE unique_id = :unique_id".to_string()
}

// delete_reply removes permanently
pub async fn delete_reply(uid: &str, now: String, db_conn: DbConn) -> Result<usize> {
    debug!("delete reply(..)................<<");
    let conn = db_conn.lock().await;
    // step 1: update deleted_on field in reply table
    let _row_count = conn.execute_named(
        &reply_deleted_on(),
        &[ (":deleted_on", &now), (":unique_id", &uid) ] )?;
    // step 2: archive the row to be deleted into archive_reply
    let _row_count = conn.execute_named(
        &sql_to_archive_reply(),
        &[ (":unique_id", &uid) ] );
    // step 3: actual delete of reply
    let result = conn.execute_named(
        "DELETE FROM reply WHERE unique_id = :uid",
        &[ (":uid", &uid) ] )?;

    Ok(result)
}
fn sql_to_get_reply() -> String {
    "SELECT
        message, anonymous, status, remarks, unique_id, 
        parent_id, comment_id, level, user_id, created_on, updated_on, deleted_on         
    FROM reply 
    WHERE unique_id = :uid".to_string()
}
pub async fn get_reply(uid: &str, db_conn: DbConn) -> Result<Reply> {
    debug!("get_reply({}).....................<<", uid);
    let conn = db_conn.lock().await;
    let result = conn.query_row_named( 
        &sql_to_get_reply(),
        &[ (":uid", &uid) ],
        |row| extract_reply_data(row)
    )?;
    debug!("Reply is: {:?}", result);
    Ok(result)
}

// Actions on Archive Reply table
pub fn sql_to_archive_all_replies_on_comment() -> String {
    debug!("sql to archive all replies on comment()........................");
    "INSERT INTO archive_reply (
            message, 
            anonymous,
            status, 
            remarks, 
            unique_id, 
            parent_id,
            comment_id,
            level,
            user_id,
            created_on,
            updated_on,
            deleted_on
        ) 
        SELECT 
            message,
            anonymous,
            status,
            remarks,
            unique_id,
            parent_id,
            comment_id,
            level,
            user_id,
            created_on,
            updated_on,
            deleted_on
        FROM
            reply
        WHERE 
            comment_id = :comment_id".to_string()
}
fn sql_to_archive_reply() -> String {
    debug!("sql to archive reply()........................");
    "INSERT INTO archive_reply (
            message, 
            anonymous,
            status, 
            remarks, 
            unique_id, 
            parent_id,
            comment_id,
            level,
            user_id,
            created_on,
            updated_on,
            deleted_on
        ) 
        SELECT 
            message,
            anonymous,
            status,
            remarks,
            unique_id,
            parent_id,
            comment_id,
            level,
            user_id,
            created_on,
            updated_on,
            deleted_on
        FROM
            reply
        WHERE 
            unique_id = :unique_id".to_string()
}

// Actions on Upvote table
fn sql_to_add_upvote() -> String {
    "INSERT INTO upvote (
        comment_id, 
        user_id,
        remarks, 
        created_on
    ) VALUES (
        :comment_id,
        :user_id,
        :remarks,
        :created_on
    )".to_string()
}
pub async fn add_upvote(upvote: &Upvote, db_conn: DbConn) -> Result<usize> {
    debug!("add_upvote({:?})........................", upvote);
    let conn = db_conn.lock().await;
    let row_count = conn.execute_named(
        &sql_to_add_upvote(),
        named_params!{
            ":comment_id": upvote.comment_id,
            ":user_id": upvote.user_id,
            ":remarks": upvote.remarks,
            ":created_on": upvote.created_on
        } 
    )?;
    Ok(row_count)
}

fn upvote_deleted_on() -> String {
    "UPDATE upvote
    SET remarks = 'deleted', deleted_on = :deleted_on
    WHERE comment_id = :comment_id
        AND user_id = :user_id".to_string()
}
pub async fn delete_upvote(comment_id: &str, user_id: &str, now: String, db_conn: DbConn) -> Result<usize> {
    debug!("delete upvote(..)................<<");
    let conn = db_conn.lock().await;
    // step 1: update deleted on field in upvote table
    let _row_count = conn.execute_named(
        &upvote_deleted_on(),
        &[ (":deleted_on", &now), 
           (":comment_id", &comment_id), 
           (":user_id", &user_id) ] )?;
    // step 2: archive the row to be deleted into archive_upvote table
    let _row_count = conn.execute_named(
        &sql_to_archive_upvote(),
        &[ (":comment_id", &comment_id), 
           (":user_id", &user_id) ] )?;
    // step 3: actual delete on upvote 
    let result = conn.execute_named(
        "DELETE FROM 
            upvote 
        WHERE 
            comment_id = :comment_id 
            AND user_id = :user_id",
        &[ (":comment_id", &comment_id), 
           (":user_id", &user_id) ] )?;

    Ok(result)
}
fn sql_to_get_upvotes_total() -> String {
    "SELECT count(*)
    FROM upvote 
    WHERE (deleted_on is null OR deleted_on = '') 
    AND comment_id = :comment_id".to_string()
}
pub async fn get_upvotes_total(comment_id: &str, db_conn: DbConn) -> Result<u32> {
    debug!("get_upvotes_total({}).....................<<", &comment_id);
    let conn = db_conn.lock().await;
    let result = conn.query_row_named(
        &sql_to_get_upvotes_total(),
        &[ (":comment_id", &comment_id) ],
        |row| row.get(0)
    )?;
    debug!("Total upvotes for comment_id: {} are: {:?}", &comment_id, &result);
    Ok(result)
}

// Actions on Archive Upvote table
pub fn sql_to_archive_upvote() -> String {
    "INSERT INTO archive_upvote (
            comment_id, 
            user_id,
            remarks, 
            created_on,
            updated_on,
            deleted_on
        ) 
        SELECT 
            comment_id,
            user_id,
            remarks,
            created_on,
            updated_on,
            deleted_on
        FROM
            upvote
        WHERE 
            comment_id = :comment_id 
            AND user_id = :user_id".to_string()
}

// Actions on Downvote table
fn sql_to_add_downvote() -> String {
    "INSERT INTO downvote (
        comment_id, 
        user_id,
        remarks, 
        created_on
    ) VALUES (
        :comment_id,
        :user_id,
        :remarks,
        :created_on
    )".to_string()
}
pub async fn add_downvote(downvote: &Downvote, db_conn: DbConn) -> Result<usize> {
    debug!("add_downvote({:?})........................", downvote);
    let conn = db_conn.lock().await;
    let row_count = conn.execute_named(
        &sql_to_add_downvote(),
        named_params!{
            ":comment_id": downvote.comment_id,
            ":user_id": downvote.user_id,
            ":remarks": downvote.remarks,
            ":created_on": downvote.created_on
        } 
    )?;
    Ok(row_count)
}

fn downvote_deleted_on() -> String {
    "UPDATE downvote
    SET remarks = 'deleted', deleted_on = :deleted_on
    WHERE comment_id = :comment_id
        AND user_id = :user_id".to_string()
}
pub async fn delete_downvote(comment_id: &str, user_id: &str, now: String, db_conn: DbConn) -> Result<usize> {
    debug!("delete downvote(..)................<<");
    let conn = db_conn.lock().await;
    // step 1: update deleted on field in downvote table
    let _row_count = conn.execute_named(
        &downvote_deleted_on(),
        &[ (":deleted_on", &now), 
           (":comment_id", &comment_id), 
           (":user_id", &user_id) ] )?;
    // step 2: archive the row to be deleted into archive_downvote table
    let _row_count = conn.execute_named(
        &sql_to_archive_downvote(),
        &[ (":comment_id", &comment_id), 
           (":user_id", &user_id) ] )?;
    // step 3: actual delete on downvote 
    let result = conn.execute_named(
        "DELETE FROM 
            downvote 
        WHERE 
            comment_id = :comment_id 
            AND user_id = :user_id",
        &[ (":comment_id", &comment_id), 
           (":user_id", &user_id) ] )?;

    Ok(result)
}
fn sql_to_get_downvotes_total() -> String {
    "SELECT count(*)
    FROM downvote 
    WHERE (deleted_on is null OR deleted_on = '') 
    AND comment_id = :comment_id".to_string()
}
pub async fn get_downvotes_total(comment_id: &str, db_conn: DbConn) -> Result<u32> {
    debug!("get_downvotes_total({}).....................<<", &comment_id);
    let conn = db_conn.lock().await;
    let result = conn.query_row_named(
        &sql_to_get_downvotes_total(),
        &[ (":comment_id", &comment_id) ],
        |row| row.get(0)
    )?;
    debug!("Total downvotes for comment_id: {} are: {:?}", &comment_id, &result);
    Ok(result)
}

// Actions on Archive Downvote table
pub fn sql_to_archive_downvote() -> String {
    "INSERT INTO archive_downvote (
            comment_id, 
            user_id,
            remarks, 
            created_on,
            updated_on,
            deleted_on
        ) 
        SELECT 
            comment_id,
            user_id,
            remarks,
            created_on,
            updated_on,
            deleted_on
        FROM
            downvote
        WHERE 
            comment_id = :comment_id 
            AND user_id = :user_id".to_string()
}