use std::time::{Duration, SystemTime, UNIX_EPOCH};

use rusqlite::Row;
use tracing::instrument;

use crate::{database, types::comment::Comment};

pub fn init() -> Result<(), rusqlite::Error> {
    let sql = "
        CREATE TABLE IF NOT EXISTS comments (
            comment_id  INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id     INTEGER NOT NULL,
            post_id     INTEGER NOT NULL,
            date        INTEGER NOT NULL,
            content     VARCHAR(255) NOT NULL,
            FOREIGN KEY(user_id) REFERENCES users(user_id),
            FOREIGN KEY(post_id) REFERENCES posts(post_id)
        );
    ";
    let conn = database::connect()?;
    conn.execute(sql, ())?;

    let sql2 = "CREATE INDEX IF NOT EXISTS post_ids on comments (post_id);";
    conn.execute(sql2, ())?;

    Ok(())
}

fn comment_from_row(row: &Row) -> Result<Comment, rusqlite::Error> {
    let comment_id = row.get(0)?;
    let user_id = row.get(1)?;
    let post_id = row.get(2)?;
    let date = row.get(3)?;
    let content = row.get(4)?;

    Ok(Comment {
        comment_id,
        user_id,
        post_id,
        date,
        content,
    })
}

#[instrument()]
pub fn get_comments_page(page: u64, post_id: u64) -> Result<Vec<Comment>, rusqlite::Error> {
    tracing::trace!("Retrieving comments page");
    let page_size = 5;
    let conn = database::connect()?;
    let mut stmt = conn.prepare(
        "SELECT * FROM comments WHERE post_id = ? ORDER BY comment_id ASC LIMIT ? OFFSET ?",
    )?;
    let row = stmt.query_map([post_id, page_size, page_size * page], |row| {
        let row = comment_from_row(row)?;
        Ok(row)
    })?;
    Ok(row.into_iter().flatten().collect())
}

#[instrument()]
pub fn get_all_comments() -> Result<Vec<Comment>, rusqlite::Error> {
    tracing::trace!("Retrieving comments page");
    let conn = database::connect()?;
    let mut stmt = conn.prepare("SELECT * FROM comments ORDER BY comment_id DESC")?;
    let row = stmt.query_map([], |row| {
        let row = comment_from_row(row)?;
        Ok(row)
    })?;
    Ok(row.into_iter().flatten().collect())
}

#[instrument()]
pub fn add_comment(user_id: u64, post_id: u64, content: &str) -> Result<Comment, rusqlite::Error> {
    tracing::trace!("Adding comment");
    let date = u64::try_from(
        SystemTime::now()
            .duration_since(UNIX_EPOCH)
            .unwrap_or(Duration::ZERO)
            .as_millis(),
    )
    .unwrap_or(0);
    let conn = database::connect()?;
    let mut stmt = conn.prepare(
        "INSERT INTO comments (user_id, post_id, date, content) VALUES(?,?,?,?) RETURNING *;",
    )?;
    let post = stmt.query_row((user_id, post_id, date, content), |row| {
        let row = comment_from_row(row)?;
        Ok(row)
    })?;
    Ok(post)
}