use std::time::{SystemTime, UNIX_EPOCH};
use rusqlite::{OptionalExtension, Row};
use tracing::instrument;

use crate::{database, types::user::User, api::auth::RegistrationRequet};

pub fn init() -> Result<(), rusqlite::Error> {
    let sql = "
        CREATE TABLE IF NOT EXISTS users (
            user_id     INTEGER PRIMARY KEY AUTOINCREMENT,
            firstname   VARCHAR(20) NOT NULL,
            lastname    VARCHAR(20) NOT NULL,
            email       VARCHAR(50) NOT NULL,
            password    VARCHAR(50) NOT NULL,
            gender      VARCHAR(100) NOT NULL,
            date        BIGINT NOT NULL,
            day         TINYINT NOT NULL,
            month       TINYINT NOT NULL,
            year        INTEGER NOT NULL
        );
    ";
    let conn = database::connect()?;
    conn.execute(sql, ())?;
    Ok(())
}

fn user_from_row(row: &Row, hide_password: bool) -> Result<User, rusqlite::Error> {
    let user_id = row.get(0)?;
    let firstname = row.get(1)?;
    let lastname = row.get(2)?;
    let email = row.get(3)?;
    let password = row.get(4)?;
    let gender = row.get(5)?;
    let date = row.get(6)?;
    let day = row.get(7)?;
    let month = row.get(8)?;
    let year = row.get(9)?;

    let password = if hide_password { "".to_string() } else { password };

    Ok(User{user_id, firstname, lastname, email, password, gender,date, day, month, year})
}

#[instrument()]
pub fn get_user_by_id(user_id: u64, hide_password: bool) -> Result<Option<User>, rusqlite::Error> {
    tracing::trace!("Retrieving user by id");
    let conn = database::connect()?;
    let mut stmt = conn.prepare("SELECT * FROM users WHERE user_id = ?")?;
    let row = stmt.query_row([user_id], |row| {
        let row = user_from_row(row, hide_password)?;
        Ok(row)
    }).optional()?;
    Ok(row)
}

#[instrument()]
pub fn get_user_by_email(email: &str, hide_password: bool) -> Result<Option<User>, rusqlite::Error> {
    tracing::trace!("Retrieving user by email");
    let conn = database::connect()?;
    let mut stmt = conn.prepare("SELECT * FROM users WHERE email = ?")?;
    let row = stmt.query_row([email], |row| {
        let row = user_from_row(row, hide_password)?;
        Ok(row)
    }).optional()?;
    Ok(row)
}

#[instrument()]
pub fn get_user_by_password(password: &str, hide_password: bool) -> Result<Option<User>, rusqlite::Error> {
    tracing::trace!("Retrieving user by password");
    let conn = database::connect()?;
    let mut stmt = conn.prepare("SELECT * FROM users WHERE password = ?")?;
    let row = stmt.query_row([password], |row| {
        let row = user_from_row(row, hide_password)?;
        Ok(row)
    }).optional()?;
    Ok(row)
}

#[instrument()]
pub fn get_user_page(page: u64, hide_password: bool) -> Result<Vec<User>, rusqlite::Error> {
    tracing::trace!("Retrieving user page");
    let page_size = 5;
    let conn = database::connect()?;
    let mut stmt = conn.prepare("SELECT * FROM users ORDER BY user_id DESC LIMIT ? OFFSET ?")?;
    let row = stmt.query_map([page_size, page_size * page], |row| {
        let row = user_from_row(row, hide_password)?;
        Ok(row)
    })?;
    Ok(row.into_iter().flatten().collect())
}

#[instrument()]
pub fn add_user(request: RegistrationRequet) -> Result<User, rusqlite::Error> {
    tracing::trace!("Adding new user");
    let date = SystemTime::now().duration_since(UNIX_EPOCH).unwrap().as_millis() as u64;

    let conn = database::connect()?;
    let mut stmt = conn.prepare("INSERT INTO users (firstname, lastname, email, password, gender, date, day, month, year) VALUES(?,?,?,?,?,?,?,?,?) RETURNING *;")?;
    let user = stmt.query_row((request.firstname, request.lastname, request.email, request.password, request.gender, date, request.day, request.month, request.year), |row| {
        let row = user_from_row(row, false)?;
        Ok(row)
    })?;
    Ok(user)
}