//! Notifications, watching, subscriptions and the search index (hub spec

//! §10, §11).

use super::{now_ms, Pool};

use sqlx::FromRow;

#[derive(Clone, Debug, FromRow)]

pub struct Notification {

    pub id: i64,

    pub account_id: i64,

    pub at_ms: i64,

    pub kind: String,

    pub title: String,

    pub url: String,

    pub actor: String,

    pub read: bool,

    pub repo_id: Option<i64>,

    pub thread: String,

    pub reason: String,

    pub done: bool,

}

/// Why someone is told: they take part in the thread, or only watch the repository.

#[derive(Clone, Copy, Debug, PartialEq, Eq)]

pub enum Reason {

    Participating,

    Watching,

}

impl Reason {

    pub fn as_str(self) -> &'static str {

        match self {

            Reason::Participating => "participating",

            Reason::Watching => "watching",

        }

    }

}

/// The thread a notification belongs to: its page, without a fragment.

pub fn thread_of(url: &str) -> &str {

    url.split('#').next().unwrap_or(url)

}

/// A notification to record.

pub struct New<'a> {

    pub account_id: i64,

    pub repo_id: Option<i64>,

    pub reason: Reason,

    pub kind: &'a str,

    pub title: &'a str,

    pub url: &'a str,

    pub actor: &'a str,

}

pub async fn add(pool: &Pool, n: New<'_>) -> sqlx::Result<()> {

    let New { account_id, repo_id, reason, kind, title, url, actor } = n;

    // A new event brings a thread that was set aside back to the inbox.

    sqlx::query("UPDATE notifications SET done = FALSE WHERE account_id = $1 AND thread = $2 AND done = TRUE")

        .bind(account_id)

        .bind(thread_of(url))

        .execute(pool)

        .await?;

    sqlx::query("INSERT INTO notifications (account_id, at_ms, kind, title, url, actor, repo_id, thread, reason) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)")

        .bind(account_id)

        .bind(now_ms())

        .bind(kind)

        .bind(title)

        .bind(url)

        .bind(actor)

        .bind(repo_id)

        .bind(thread_of(url))

        .bind(reason.as_str())

        .execute(pool)

        .await?;

    Ok(())

}

/// One inbox row: a thread, shown by its newest notification.

#[derive(Clone, Debug, FromRow)]

pub struct Thread {

    /// The newest notification's id.

    pub id: i64,

    pub at_ms: i64,

    pub kind: String,

    pub title: String,

    pub url: String,

    pub actor: String,

    pub thread: String,

    pub repo_id: Option<i64>,

    pub repo: Option<String>,

    pub count: i64,

    pub unread: i64,

    pub participating: bool,

    pub done: bool,

}

/// What the inbox shows.

#[derive(Clone, Debug, Default)]

pub struct Filter {

    pub done: bool,

    pub unread_only: bool,

    /// `participating` or `watching`.

    pub reason: Option<String>,

    /// A kind or its family (`issue` matches `issue.closed`).

    pub kind: Option<String>,

    pub repo_id: Option<i64>,

}

pub async fn threads(pool: &Pool, account_id: i64, f: &Filter, limit: i64) -> sqlx::Result<Vec<Thread>> {

    // Each thread is shown as its newest notification (row 1 by time) with

    // counts over the whole thread: window functions, so the same SQL runs on

    // SQLite and Postgres.

    let mut sql = String::from(

        "SELECT id, at_ms, kind, title, url, actor, thread, repo_id, \

         (SELECT r.owner || '/' || r.name FROM repositories r WHERE r.id = t.repo_id) AS repo, \

         count, unread, participating > 0 AS participating, done FROM (\

         SELECT n.id, n.at_ms, n.kind, n.title, n.url, n.actor, n.thread, n.repo_id, n.done, \

         ROW_NUMBER() OVER (PARTITION BY n.thread ORDER BY n.at_ms DESC, n.id DESC) AS newest, \

         COUNT(*) OVER (PARTITION BY n.thread) AS count, \

         SUM(CASE WHEN n.read THEN 0 ELSE 1 END) OVER (PARTITION BY n.thread) AS unread, \

         SUM(CASE WHEN n.reason = 'participating' THEN 1 ELSE 0 END) OVER (PARTITION BY n.thread) AS participating \

         FROM notifications n WHERE n.account_id = $1 AND n.done = $2",

    );

    // Placeholders numbered as they are added: $1 and $2 above.

    let mut next = 3;

    if f.kind.is_some() {

        sql.push_str(&format!(" AND (n.kind = ${} OR n.kind LIKE ${} || '.%')", next, next + 1));

        next += 2;

    }

    if f.repo_id.is_some() {

        sql.push_str(&format!(" AND n.repo_id = ${next}"));

        next += 1;

    }

    sql.push_str(") t WHERE newest = 1");

    if f.unread_only {

        sql.push_str(" AND unread > 0");

    }

    match f.reason.as_deref() {

        Some("participating") => sql.push_str(" AND participating > 0"),

        Some("watching") => sql.push_str(" AND participating = 0"),

        _ => {}

    }

    sql.push_str(&format!(" ORDER BY at_ms DESC LIMIT ${next}"));

    let mut q = sqlx::query_as::<_, Thread>(&sql).bind(account_id).bind(f.done);

    if let Some(k) = &f.kind {

        q = q.bind(k).bind(k);

    }

    if let Some(r) = f.repo_id {

        q = q.bind(r);

    }

    q.bind(limit).fetch_all(pool).await

}

/// Mark a thread read or unread, done or back in the inbox.

pub async fn set_thread(pool: &Pool, account_id: i64, thread: &str, read: Option<bool>, done: Option<bool>) -> sqlx::Result<()> {

    if let Some(r) = read {

        if r {

            sqlx::query("UPDATE notifications SET read = TRUE WHERE account_id = $1 AND thread = $2")

                .bind(account_id)

                .bind(thread)

                .execute(pool)

                .await?;

        } else {

            // Unread: the newest notification of the thread only.

            sqlx::query("UPDATE notifications SET read = FALSE WHERE id = (SELECT id FROM notifications WHERE account_id = $1 AND thread = $2 ORDER BY at_ms DESC, id DESC LIMIT 1)")

                .bind(account_id)

                .bind(thread)

                .execute(pool)

                .await?;

        }

    }

    if let Some(d) = done {

        let read_too = if d { ", read = TRUE" } else { "" };

        sqlx::query(&format!("UPDATE notifications SET done = $1{read_too} WHERE account_id = $2 AND thread = $3"))

            .bind(d)

            .bind(account_id)

            .bind(thread)

            .execute(pool)

            .await?;

    }

    Ok(())

}

/// The notification with this id, if it is the account's.

pub async fn get(pool: &Pool, account_id: i64, id: i64) -> sqlx::Result<Option<Notification>> {

    sqlx::query_as("SELECT * FROM notifications WHERE account_id = $1 AND id = $2").bind(account_id).bind(id).fetch_optional(pool).await

}

pub async fn inbox(pool: &Pool, account_id: i64, limit: i64) -> sqlx::Result<Vec<Notification>> {

    sqlx::query_as("SELECT * FROM notifications WHERE account_id = $1 ORDER BY read, at_ms DESC, id DESC LIMIT $2")

        .bind(account_id)

        .bind(limit)

        .fetch_all(pool)

        .await

}

pub async fn unread(pool: &Pool, account_id: i64) -> sqlx::Result<i64> {

    sqlx::query_scalar("SELECT COUNT(*) FROM notifications WHERE account_id = $1 AND read = FALSE AND done = FALSE")

        .bind(account_id)

        .fetch_one(pool)

        .await

}

pub async fn mark_read(pool: &Pool, account_id: i64, id: Option<i64>) -> sqlx::Result<()> {

    match id {

        Some(id) => {

            sqlx::query("UPDATE notifications SET read = TRUE WHERE account_id = $1 AND id = $2")

                .bind(account_id)

                .bind(id)

                .execute(pool)

                .await?

        }

        None => sqlx::query("UPDATE notifications SET read = TRUE WHERE account_id = $1").bind(account_id).execute(pool).await?,

    };

    Ok(())

}

// ---------------------------------------------------------------- watching

/// The accounts that own repository `repo_id`: its owner, or the owners of

/// the organisation that owns it. They watch it unless they mute it

/// (oxygenhub#183).

pub async fn owners_of(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<i64>> {

    let owner: Option<String> =

        sqlx::query_scalar("SELECT owner FROM repositories WHERE id = $1").bind(repo_id).fetch_optional(pool).await?;

    let Some(owner) = owner else { return Ok(Vec::new()) };

    if let Some(a) = super::accounts::account_by_name(pool, &owner).await? {

        return Ok(vec![a.id]);

    }

    match super::accounts::org_by_name(pool, &owner).await? {

        Some(o) => {

            Ok(super::accounts::org_members(pool, o.id).await?.into_iter().filter(|(_, r)| r == "owner").map(|(a, _)| a.id).collect())

        }

        None => Ok(Vec::new()),

    }

}

/// Whether `account_id` watches `repo_id`: a watch it chose, or, for an

/// owner, unless it muted it.

pub async fn watching(pool: &Pool, account_id: i64, repo_id: i64) -> sqlx::Result<bool> {

    let muted: Option<bool> = sqlx::query_scalar("SELECT muted FROM watches WHERE account_id = $1 AND repo_id = $2")

        .bind(account_id)

        .bind(repo_id)

        .fetch_optional(pool)

        .await?;

    match muted {

        Some(m) => Ok(!m),

        None => Ok(owners_of(pool, repo_id).await?.contains(&account_id)),

    }

}

/// Watch `repo_id`, or stop: for an owner, stopping mutes it.

pub async fn set_watch(pool: &Pool, account_id: i64, repo_id: i64, on: bool) -> sqlx::Result<()> {

    sqlx::query("INSERT INTO watches (account_id, repo_id, muted) VALUES ($1, $2, $3) ON CONFLICT (account_id, repo_id) DO UPDATE SET muted = excluded.muted")

        .bind(account_id)

        .bind(repo_id)

        .bind(!on)

        .execute(pool)

        .await?;

    Ok(())

}

/// Everyone watching `repo_id`: those who chose to, and its owners who

/// have not muted it.

pub async fn watchers(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<i64>> {

    let rows: Vec<(i64, bool)> =

        sqlx::query_as("SELECT account_id, muted FROM watches WHERE repo_id = $1").bind(repo_id).fetch_all(pool).await?;

    let mut out: Vec<i64> = rows.iter().filter(|(_, m)| !m).map(|(a, _)| *a).collect();

    for o in owners_of(pool, repo_id).await? {

        if !rows.iter().any(|(a, _)| *a == o) && !out.contains(&o) {

            out.push(o);

        }

    }

    Ok(out)

}

pub async fn subscribe(pool: &Pool, account_id: i64, kind: &str, target_id: i64) -> sqlx::Result<()> {

    sqlx::query("INSERT INTO subscriptions (account_id, kind, target_id) VALUES ($1, $2, $3) ON CONFLICT DO NOTHING")

        .bind(account_id)

        .bind(kind)

        .bind(target_id)

        .execute(pool)

        .await?;

    Ok(())

}

pub async fn unsubscribe(pool: &Pool, account_id: i64, kind: &str, target_id: i64) -> sqlx::Result<()> {

    sqlx::query("DELETE FROM subscriptions WHERE account_id = $1 AND kind = $2 AND target_id = $3")

        .bind(account_id)

        .bind(kind)

        .bind(target_id)

        .execute(pool)

        .await?;

    Ok(())

}

pub async fn subscribed(pool: &Pool, account_id: i64, kind: &str, target_id: i64) -> sqlx::Result<bool> {

    let n: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM subscriptions WHERE account_id = $1 AND kind = $2 AND target_id = $3")

        .bind(account_id)

        .bind(kind)

        .bind(target_id)

        .fetch_one(pool)

        .await?;

    Ok(n > 0)

}

pub async fn subscribers(pool: &Pool, kind: &str, target_id: i64) -> sqlx::Result<Vec<i64>> {

    sqlx::query_scalar("SELECT account_id FROM subscriptions WHERE kind = $1 AND target_id = $2")

        .bind(kind)

        .bind(target_id)

        .fetch_all(pool)

        .await

}

// ------------------------------------------------------------------ search

#[derive(Clone, Debug, FromRow)]

pub struct Hit {

    pub kind: String,

    pub repo_id: i64,

    pub target_id: i64,

    pub number: i64,

    pub title: String,

    pub snippet: String,

}

/// Replace the indexed text of one thread (title plus body plus comments).

pub async fn index(pool: &Pool, kind: &str, repo_id: i64, target_id: i64, number: i64, title: &str, body: &str) -> sqlx::Result<()> {

    sqlx::query("DELETE FROM search WHERE kind = $1 AND target_id = $2").bind(kind).bind(target_id).execute(pool).await?;

    sqlx::query("INSERT INTO search (kind, repo_id, target_id, number, title, body) VALUES ($1, $2, $3, $4, $5, $6)")

        .bind(kind)

        .bind(repo_id)

        .bind(target_id)

        .bind(number)

        .bind(title)

        .bind(body)

        .execute(pool)

        .await?;

    Ok(())

}

/// Full-text hits, best first. The query is quoted so punctuation cannot

/// reach the FTS5 parser.

#[cfg(not(feature = "postgres"))]

pub async fn search(pool: &Pool, query: &str, repo_id: Option<i64>, limit: i64) -> sqlx::Result<Vec<Hit>> {

    let terms: Vec<String> = query.split_whitespace().map(|t| format!("\"{}\"", t.replace('"', ""))).collect();

    if terms.is_empty() {

        return Ok(vec![]);

    }

    let q = terms.join(" ");

    let sql = "SELECT kind, repo_id, target_id, number, title, snippet(search, 5, '<mark>', '</mark>', '…', 12) AS snippet FROM search WHERE search MATCH $1 AND ($2 IS NULL OR repo_id = $3) ORDER BY rank LIMIT $4";

    sqlx::query_as(sql).bind(q).bind(repo_id).bind(repo_id).bind(limit).fetch_all(pool).await

}

/// Full-text hits, best first, as SQLite's: every word must appear

/// (stemmed, as FTS5's porter tokenizer does), titles weigh more, and the

/// snippet is from the body with the words marked.

#[cfg(feature = "postgres")]

pub async fn search(pool: &Pool, query: &str, repo_id: Option<i64>, limit: i64) -> sqlx::Result<Vec<Hit>> {

    if query.split_whitespace().next().is_none() {

        return Ok(vec![]);

    }

    let sql = "SELECT kind, repo_id, target_id, number, title, \

               ts_headline('english', body, q, 'StartSel=\"<mark>\", StopSel=\"</mark>\", MaxWords=12, MinWords=4') AS snippet \

               FROM search, plainto_tsquery('english', $1) q WHERE doc @@ q AND ($2 IS NULL OR repo_id = $3) \

               ORDER BY ts_rank(doc, q) DESC LIMIT $4";

    sqlx::query_as(sql).bind(query).bind(repo_id).bind(repo_id).bind(limit).fetch_all(pool).await

}