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

use rusqlite::{OptionalExtension, Row};
use tracing::instrument;

use crate::database;
use crate::types::post::Post;

use super::{comments, likes};

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

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

    let comments = comments::get_comments_page(0, post_id).unwrap_or_else(|_| Vec::new());
    let likes = likes::get_like_count(post_id).unwrap_or(None).unwrap_or(0);

    Ok(Post {
        post_id,
        user_id,
        content,
        date,
        likes,
        liked: false,
        comments,
    })
}

#[instrument()]
pub fn get_post(post_id: u64) -> Result<Option<Post>, rusqlite::Error> {
    tracing::trace!("Retrieving post");
    let conn = database::connect()?;
    let mut stmt = conn.prepare("SELECT * FROM posts WHERE post_id = ?")?;
    let row = stmt
        .query_row([post_id], |row| {
            let row = post_from_row(row)?;
            Ok(row)
        })
        .optional()?;
    Ok(row)
}

#[instrument()]
pub fn get_post_page(page: u64) -> Result<Vec<Post>, rusqlite::Error> {
    tracing::trace!("Retrieving posts page");
    let page_size = 10;
    let conn = database::connect()?;
    let mut stmt = conn.prepare("SELECT * FROM posts ORDER BY post_id DESC LIMIT ? OFFSET ?")?;
    let row = stmt.query_map([page_size, page_size * page], |row| {
        let row = post_from_row(row)?;
        Ok(row)
    })?;
    Ok(row.into_iter().flatten().collect())
}

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

#[instrument()]
pub fn get_users_post_page(user_id: u64, page: u64) -> Result<Vec<Post>, rusqlite::Error> {
    tracing::trace!("Retrieving users posts");
    let page_size = 10;
    let conn = database::connect()?;
    let mut stmt = conn
        .prepare("SELECT * FROM posts WHERE user_id = ? ORDER BY post_id DESC LIMIT ? OFFSET ?")?;
    let row = stmt.query_map([user_id, page_size, page_size * page], |row| {
        let row = post_from_row(row)?;
        Ok(row)
    })?;
    Ok(row.into_iter().flatten().collect())
}

#[instrument()]
pub fn add_post(user_id: u64, content: &str) -> Result<Post, rusqlite::Error> {
    tracing::trace!("Adding post");
    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 posts (user_id, content, date) VALUES(?,?,?) RETURNING *;")?;
    let post = stmt.query_row((user_id, content, date), |row| {
        let row = post_from_row(row)?;
        Ok(row)
    })?;
    Ok(post)
}