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

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

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

use super::Database;

impl Database {
    pub fn init_posts(&self) -> Result<(), rusqlite::Error> {
        let sql = "
            CREATE TABLE IF NOT EXISTS posts (
                post_id     INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id     INTEGER NOT NULL,
                content     VARCHAR(5000) NOT NULL,
                date        INTEGER NOT NULL,
                FOREIGN KEY(user_id) REFERENCES users(user_id)
            );
        ";
        self.0.execute(sql, ())?;
        Ok(())
    }

    fn post_from_row(&self, 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 = self
            .get_comments_page(0, post_id)
            .unwrap_or_else(|_| Vec::new());
        let likes = self.get_like_count(post_id).unwrap_or(None).unwrap_or(0);

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

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

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

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

    #[instrument(skip(self))]
    pub fn get_users_post_page(
        &self,
        user_id: u64,
        page: u64,
    ) -> Result<Vec<Post>, rusqlite::Error> {
        tracing::trace!("Retrieving users posts");
        let page_size = 10;
        let mut stmt = self.0.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 = self.post_from_row(row)?;
            Ok(row)
        })?;
        Ok(row.into_iter().flatten().collect())
    }

    #[instrument(skip(self))]
    pub fn add_post(&self, 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 mut stmt = self
            .0
            .prepare("INSERT INTO posts (user_id, content, date) VALUES(?,?,?) RETURNING *;")?;
        let post = stmt.query_row((user_id, content, date), |row| {
            let row = self.post_from_row(row)?;
            Ok(row)
        })?;
        Ok(post)
    }
}