use sqlx::{sqlite::SqlitePool, Pool, Row};
use std::error::Error;
use serde::{Deserialize, Serialize};
use indoc::indoc;

pub struct Connection {
    pool: Pool<sqlx::Sqlite>,
}

pub async fn connect() -> Result<Connection, Box<dyn Error>> {
    let database_url = "sqlite::memory:"; // Use an in-memory SQLite database for testing
    let pool = SqlitePool::connect(database_url).await?;
    Ok(Connection { pool })
}

pub async fn init_db(conn: &Connection) -> Result<(), Box<dyn Error>> {
    sqlx::query(indoc! {"
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL UNIQUE,
            jwt_token TEXT NOT NULL
        );

        CREATE TABLE IF NOT EXISTS elections (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            username TEXT NOT NULL,
            namespace TEXT NOT NULL,
            description TEXT,
            start_date TEXT NOT NULL,
            end_date TEXT NOT NULL,
            FOREIGN KEY (username) REFERENCES users(username)
        );

        CREATE TABLE IF NOT EXISTS authorizations (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            from_date TEXT NOT NULL,
            to_date TEXT NOT NULL,
            from_user TEXT NOT NULL,
            to_user TEXT NOT NULL,
            namespace TEXT NOT NULL,
            FOREIGN KEY (from_user) REFERENCES users(username),
            FOREIGN KEY (to_user) REFERENCES users(username)
        );

        CREATE TABLE IF NOT EXISTS votes (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            value INTEGER NOT NULL,
            option_id INTEGER NOT NULL,
            election_id INTEGER NOT NULL,
            option_name TEXT NOT NULL,
            user TEXT NOT NULL,
            namespace TEXT NOT NULL,
            date TEXT NOT NULL,
            FOREIGN KEY (election_id) REFERENCES elections(id),
            FOREIGN KEY (user) REFERENCES users(username)
        );
    "})
    .execute(&conn.pool)
    .await?;
    Ok(())
}

// Users
#[derive(Serialize, Deserialize)]
pub struct User {
    pub id: i64,
    pub username: String,
    pub jwt_token: String,
}

pub async fn insert_user(conn: &Connection, username: &str, jwt_token: &str) -> Result<(), Box<dyn Error>> {
    sqlx::query("INSERT INTO users (username, jwt_token) VALUES (?, ?)")
        .bind(username)
        .bind(jwt_token)
        .execute(&conn.pool)
        .await?;
    Ok(())
}

pub async fn get_user(conn: &Connection, username: &str) -> Result<Option<User>, Box<dyn Error>> {
    let row = sqlx::query("SELECT id, username, jwt_token FROM users WHERE username = ?")
        .bind(username)
        .fetch_optional(&conn.pool)
        .await?;
    if let Some(row) = row {
        Ok(Some(User {
            id: row.get("id"),
            username: row.get("username"),
            jwt_token: row.get("jwt_token"),
        }))
    } else {
        Ok(None)
    }
}

// Elections
#[derive(Serialize, Deserialize)]
pub struct Election {
    pub id: i64,
    pub name: String,
    pub username: String,
    pub namespace: String,
    pub description: String,
    pub start_date: String,
    pub end_date: String,
}

pub async fn insert_election(conn: &Connection, election: &Election) -> Result<(), Box<dyn Error>> {
    sqlx::query(indoc! {"
        INSERT INTO elections (name, username, namespace, description, start_date, end_date)
        VALUES (?, ?, ?, ?, ?, ?)
    "})
    .bind(&election.name)
    .bind(&election.username)
    .bind(&election.namespace)
    .bind(&election.description)
    .bind(&election.start_date)
    .bind(&election.end_date)
    .execute(&conn.pool)
    .await?;
    Ok(())
}

pub async fn get_election(conn: &Connection, id: i64) -> Result<Option<Election>, Box<dyn Error>> {
    let row = sqlx::query("SELECT id, name, username, namespace, description, start_date, end_date FROM elections WHERE id = ?")
        .bind(id)
        .fetch_optional(&conn.pool)
        .await?;
    if let Some(row) = row {
        Ok(Some(Election {
            id: row.get("id"),
            name: row.get("name"),
            username: row.get("username"),
            namespace: row.get("namespace"),
            description: row.get("description"),
            start_date: row.get("start_date"),
            end_date: row.get("end_date"),
        }))
    } else {
        Ok(None)
    }
}

// Authorizations
#[derive(Serialize, Deserialize)]
pub struct Authorization {
    pub id: i64,
    pub from_date: String,
    pub to_date: String,
    pub from_user: String,
    pub to_user: String,
    pub namespace: String,
}

pub async fn insert_authorization(conn: &Connection, authorization: &Authorization) -> Result<(), Box<dyn Error>> {
    sqlx::query(indoc! {"
        INSERT INTO authorizations (from_date, to_date, from_user, to_user, namespace)
        VALUES (?, ?, ?, ?, ?)
    "})
    .bind(&authorization.from_date)
    .bind(&authorization.to_date)
    .bind(&authorization.from_user)
    .bind(&authorization.to_user)
    .bind(&authorization.namespace)
    .execute(&conn.pool)
    .await?;
    Ok(())
}

pub async fn get_authorization(conn: &Connection, id: i64) -> Result<Option<Authorization>, Box<dyn Error>> {
    let row = sqlx::query("SELECT id, from_date, to_date, from_user, to_user, namespace FROM authorizations WHERE id = ?")
        .bind(id)
        .fetch_optional(&conn.pool)
        .await?;
    if let Some(row) = row {
        Ok(Some(Authorization {
            id: row.get("id"),
            from_date: row.get("from_date"),
            to_date: row.get("to_date"),
            from_user: row.get("from_user"),
            to_user: row.get("to_user"),
            namespace: row.get("namespace"),
        }))
    } else {
        Ok(None)
    }
}

// Votes
#[derive(Serialize, Deserialize)]
pub struct Vote {
    pub id: i64,
    pub value: i32,
    pub option_id: i64,
    pub election_id: i64,
    pub option_name: String,
    pub user: String,
    pub namespace: String,
    pub date: String,
}

pub async fn insert_vote(conn: &Connection, vote: &Vote) -> Result<(), Box<dyn Error>> {
    sqlx::query(indoc! {"
        INSERT INTO votes (value, option_id, election_id, option_name, user, namespace, date)
        VALUES (?, ?, ?, ?, ?, ?, ?)
    "})
    .bind(vote.value)
    .bind(vote.option_id)
    .bind(vote.election_id)
    .bind(&vote.option_name)
    .bind(&vote.user)
    .bind(&vote.namespace)
    .bind(&vote.date)
    .execute(&conn.pool)
    .await?;
    Ok(())
}

pub async fn get_vote(conn: &Connection, id: i64) -> Result<Option<Vote>, Box<dyn Error>> {
    let row = sqlx::query("SELECT id, value, option_id, election_id, option_name, user, namespace, date FROM votes WHERE id = ?")
        .bind(id)
        .fetch_optional(&conn.pool)
        .await?;
    if let Some(row) = row {
        Ok(Some(Vote {
            id: row.get("id"),
            value: row.get("value"),
            option_id: row.get("option_id"),
            election_id: row.get("election_id"),
            option_name: row.get("option_name"),
            user: row.get("user"),
            namespace: row.get("namespace"),
            date: row.get("date"),
        }))
    } else {
        Ok(None)
    }
}