use rusqlite::OptionalExtension;
use tracing::instrument;

use crate::types::like::Like;

use super::Database;

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

    #[instrument(skip(self))]
    pub fn get_like_count(&self, post_id: u64) -> Result<Option<u64>, rusqlite::Error> {
        tracing::trace!("Retrieving like count");
        let mut stmt = self
            .0
            .prepare("SELECT COUNT(post_id) FROM likes WHERE post_id = ?")?;
        let row = stmt
            .query_row([post_id], |row| {
                let row = row.get(0)?;
                Ok(row)
            })
            .optional()?;
        Ok(row)
    }

    #[instrument(skip(self))]
    pub fn get_liked(&self, user_id: u64, post_id: u64) -> Result<bool, rusqlite::Error> {
        tracing::trace!("Retrieving if liked");
        let mut stmt = self
            .0
            .prepare("SELECT * FROM likes WHERE user_id = ? AND post_id = ?")?;
        let liked = stmt.query_row([user_id, post_id], |_| Ok(())).optional()?;
        Ok(liked.is_some())
    }

    #[instrument(skip(self))]
    pub fn add_liked(&self, user_id: u64, post_id: u64) -> Result<bool, rusqlite::Error> {
        tracing::trace!("Adding like");
        let mut stmt = self
            .0
            .prepare("INSERT OR REPLACE INTO likes (user_id, post_id) VALUES (?,?)")?;
        let changes = stmt.execute([user_id, post_id])?;
        Ok(changes == 1)
    }

    #[instrument(skip(self))]
    pub fn remove_liked(&self, user_id: u64, post_id: u64) -> Result<bool, rusqlite::Error> {
        tracing::trace!("Removing like");
        let mut stmt = self
            .0
            .prepare("DELETE FROM likes WHERE user_id = ? AND post_id = ?;")?;
        let changes = stmt.execute((user_id, post_id))?;
        Ok(changes == 1)
    }

    #[instrument(skip(self))]
    pub fn get_all_likes(&self) -> Result<Vec<Like>, rusqlite::Error> {
        tracing::trace!("Retrieving comments page");
        let mut stmt = self.0.prepare("SELECT * FROM likes")?;
        let row = stmt.query_map([], |row| {
            let like = Like {
                user_id: row.get(0)?,
                post_id: row.get(1)?,
            };
            Ok(like)
        })?;
        Ok(row.into_iter().flatten().collect())
    }
}