use std::ops::DerefMut;

use chrono::NaiveDateTime;
use serde::{Deserialize, Serialize};
use sqlx::{FromRow, Sqlite, SqlitePool, Transaction};

use super::user::User;

#[derive(FromRow, Debug, Serialize, Deserialize)]
pub struct Notification {
    pub id: i64,
    pub user_id: i64,
    pub message: String,
    pub read_at: Option<NaiveDateTime>,
    pub created_at: NaiveDateTime,
    pub category: String,
    pub link: Option<String>,
}

impl Notification {
    pub async fn find_by_id(db: &SqlitePool, id: i64) -> Option<Self> {
        sqlx::query_as!(Self, "SELECT  * FROM notification WHERE id like ?", id)
            .fetch_one(db)
            .await
            .ok()
    }
    pub async fn create_with_tx(
        db: &mut Transaction<'_, Sqlite>,
        user: &User,
        message: &str,
        category: &str,
        link: Option<&str>,
    ) {
        sqlx::query!(
            "INSERT INTO notification(user_id, message, category, link) VALUES (?, ?, ?, ?)",
            user.id,
            message,
            category,
            link
        )
        .execute(db.deref_mut())
        .await
        .unwrap();
    }

    pub async fn create(
        db: &SqlitePool,
        user: &User,
        message: &str,
        category: &str,
        link: Option<&str>,
    ) {
        let mut tx = db.begin().await.unwrap();
        Self::create_with_tx(&mut tx, user, message, category, link).await;
        tx.commit().await.unwrap();
    }

    pub async fn for_user(db: &SqlitePool, user: &User) -> Vec<Self> {
        sqlx::query_as!(
            Self,
            "
SELECT * FROM notification 
WHERE 
  user_id = ? 
  AND (
    read_at IS NULL 
    OR read_at >= datetime('now', '-14 days')
  ) 
ORDER BY read_at DESC, created_at DESC;
            ",
            user.id
        )
        .fetch_all(db)
        .await
        .unwrap()
    }

    pub async fn mark_read(self, db: &SqlitePool) {
        sqlx::query!(
            "UPDATE notification SET read_at=CURRENT_TIMESTAMP WHERE id=?",
            self.id
        )
        .execute(db)
        .await
        .unwrap();
    }
}
