//! The `proposals`, `proposal_comments`, `proposal_reviews` and

//! `proposal_events` tables, and the per-repository number space.

use super::{now_ms, Pool};

use sqlx::FromRow;

#[derive(Clone, Debug, FromRow)]

pub struct ProposalRow {

    pub id: i64,

    pub repo_id: i64,

    pub number: i64,

    pub title: String,

    pub body: String,

    pub author_id: Option<i64>,

    pub author: String,

    pub state: String,

    pub target: String,

    pub source_kind: String,

    pub source_repo_id: Option<i64>,

    pub source_bookmark: String,

    pub source_changes: String,

    pub projected_state: String,

    pub projected_heads: String,

    pub projected_target: String,

    pub projected_changes: String,

    pub conflicted: bool,

    pub landed_state: String,

    pub landed_by: String,

    pub landed_via: String,

    pub landed_ms: i64,

    pub created_ms: i64,

    pub updated_ms: i64,

}

impl ProposalRow {

    pub fn projected_heads(&self) -> Vec<String> {

        self.projected_heads.split(',').filter(|s| !s.is_empty()).map(String::from).collect()

    }

    pub fn projected_changes(&self) -> Vec<String> {

        self.projected_changes.split(',').filter(|s| !s.is_empty()).map(String::from).collect()

    }

    pub fn source_changes(&self) -> Vec<String> {

        self.source_changes.split(',').filter(|s| !s.is_empty()).map(String::from).collect()

    }

    pub fn is_open(&self) -> bool {

        self.state == "open" || self.state == "draft"

    }

}

#[derive(Clone, Debug, FromRow)]

pub struct CommentRow {

    pub id: i64,

    pub proposal_id: i64,

    pub author_id: Option<i64>,

    pub author: String,

    pub body: String,

    pub path: String,

    pub anchor_change: String,

    pub anchor_index: i64,

    pub resolved: bool,

    pub created_ms: i64,

}

#[derive(Clone, Debug, FromRow)]

pub struct ReviewRow {

    pub id: i64,

    pub proposal_id: i64,

    /// `None` once the author's account was deleted (its name stays).

    pub author_id: Option<i64>,

    pub author: String,

    pub verdict: String,

    pub body: String,

    pub created_ms: i64,

    /// The AI assistant that answered for the author (migration 0036),

    /// empty for the author's own review.

    #[sqlx(default)]

    pub via: String,

}

#[derive(Clone, Debug, FromRow)]

pub struct EventRow {

    pub id: i64,

    pub at_ms: i64,

    pub actor: String,

    pub event: String,

    pub detail: String,

}

/// Take the next number of a repository's shared number space.

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

    let mut tx = super::begin_write(pool).await?;

    sqlx::query("INSERT OR IGNORE INTO numbers (repo_id, next) VALUES (?, 1)").bind(repo_id).execute(&mut *tx).await?;

    let n: i64 = sqlx::query_scalar("SELECT next FROM numbers WHERE repo_id = ?").bind(repo_id).fetch_one(&mut *tx).await?;

    sqlx::query("UPDATE numbers SET next = next + 1 WHERE repo_id = ?").bind(repo_id).execute(&mut *tx).await?;

    tx.commit().await?;

    Ok(n)

}

pub struct NewProposal<'a> {

    pub repo_id: i64,

    pub title: &'a str,

    pub body: &'a str,

    pub author_id: Option<i64>,

    pub author: &'a str,

    pub draft: bool,

    pub target: &'a str,

    pub source_kind: &'a str,

    pub source_repo_id: Option<i64>,

    pub source_bookmark: &'a str,

    pub source_changes: &'a str,

}

pub async fn create(pool: &Pool, p: NewProposal<'_>) -> sqlx::Result<ProposalRow> {

    let number = next_number(pool, p.repo_id).await?;

    let now = now_ms();

    sqlx::query_as(

        "INSERT INTO proposals (repo_id, number, title, body, author_id, author, state, target, source_kind, source_repo_id, source_bookmark, source_changes, created_ms, updated_ms) \

         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING *",

    )

    .bind(p.repo_id)

    .bind(number)

    .bind(p.title)

    .bind(p.body)

    .bind(p.author_id)

    .bind(p.author)

    .bind(if p.draft { "draft" } else { "open" })

    .bind(p.target)

    .bind(p.source_kind)

    .bind(p.source_repo_id)

    .bind(p.source_bookmark)

    .bind(p.source_changes)

    .bind(now)

    .bind(now)

    .fetch_one(pool)

    .await

}

pub async fn get(pool: &Pool, repo_id: i64, number: i64) -> sqlx::Result<Option<ProposalRow>> {

    sqlx::query_as("SELECT * FROM proposals WHERE repo_id = ? AND number = ?").bind(repo_id).bind(number).fetch_optional(pool).await

}

pub async fn list(pool: &Pool, repo_id: i64, states: &[&str]) -> sqlx::Result<Vec<ProposalRow>> {

    let all: Vec<ProposalRow> =

        sqlx::query_as("SELECT * FROM proposals WHERE repo_id = ? ORDER BY number DESC").bind(repo_id).fetch_all(pool).await?;

    Ok(all.into_iter().filter(|p| states.is_empty() || states.contains(&p.state.as_str())).collect())

}

/// Open proposals of every repository, for the front page.

pub async fn open_for_author(pool: &Pool, author_id: i64) -> sqlx::Result<Vec<ProposalRow>> {

    sqlx::query_as("SELECT * FROM proposals WHERE author_id = ? AND state IN ('open', 'draft') ORDER BY updated_ms DESC")

        .bind(author_id)

        .fetch_all(pool)

        .await

}

pub async fn set_projection(

    pool: &Pool,

    id: i64,

    state: &str,

    heads: &str,

    target_state: &str,

    changes: &str,

    conflicted: bool,

) -> sqlx::Result<()> {

    sqlx::query("UPDATE proposals SET projected_state = ?, projected_heads = ?, projected_target = ?, projected_changes = ?, conflicted = ? WHERE id = ?")

        .bind(state)

        .bind(heads)

        .bind(target_state)

        .bind(changes)

        .bind(conflicted)

        .bind(id)

        .execute(pool)

        .await?;

    Ok(())

}

pub async fn set_state(pool: &Pool, id: i64, state: &str, landed_state: &str) -> sqlx::Result<()> {

    sqlx::query("UPDATE proposals SET state = ?, landed_state = ?, updated_ms = ? WHERE id = ?")

        .bind(state)

        .bind(landed_state)

        .bind(now_ms())

        .bind(id)

        .execute(pool)

        .await?;

    Ok(())

}

/// Mark a proposal landed, with who landed it and how.

pub async fn set_landed(pool: &Pool, id: i64, landed_state: &str, by: &str, via: &str) -> sqlx::Result<()> {

    let now = now_ms();

    sqlx::query(

        "UPDATE proposals SET state = 'landed', landed_state = ?, landed_by = ?, landed_via = ?, landed_ms = ?, updated_ms = ? WHERE id = ?",

    )

    .bind(landed_state)

    .bind(by)

    .bind(via)

    .bind(now)

    .bind(now)

    .bind(id)

    .execute(pool)

    .await?;

    Ok(())

}

pub async fn edit(pool: &Pool, id: i64, title: &str, body: &str, target: &str) -> sqlx::Result<()> {

    sqlx::query("UPDATE proposals SET title = ?, body = ?, target = ?, updated_ms = ? WHERE id = ?")

        .bind(title)

        .bind(body)

        .bind(target)

        .bind(now_ms())

        .bind(id)

        .execute(pool)

        .await?;

    Ok(())

}

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

    sqlx::query("UPDATE proposals SET updated_ms = ? WHERE id = ?").bind(now_ms()).bind(id).execute(pool).await?;

    Ok(())

}

pub async fn comments(pool: &Pool, proposal_id: i64) -> sqlx::Result<Vec<CommentRow>> {

    sqlx::query_as("SELECT * FROM proposal_comments WHERE proposal_id = ? ORDER BY created_ms, id").bind(proposal_id).fetch_all(pool).await

}

pub async fn add_comment(

    pool: &Pool,

    proposal_id: i64,

    author_id: Option<i64>,

    author: &str,

    body: &str,

    path: &str,

    anchor: Option<(&str, i64)>,

) -> sqlx::Result<CommentRow> {

    let row = sqlx::query_as(

        "INSERT INTO proposal_comments (proposal_id, author_id, author, body, path, anchor_change, anchor_index, created_ms) VALUES (?, ?, ?, ?, ?, ?, ?, ?) RETURNING *",

    )

    .bind(proposal_id)

    .bind(author_id)

    .bind(author)

    .bind(body)

    .bind(path)

    .bind(anchor.map(|a| a.0).unwrap_or(""))

    .bind(anchor.map(|a| a.1).unwrap_or(0))

    .bind(now_ms())

    .fetch_one(pool)

    .await?;

    touch(pool, proposal_id).await?;

    Ok(row)

}

pub async fn set_comment_resolved(pool: &Pool, id: i64, resolved: bool) -> sqlx::Result<()> {

    sqlx::query("UPDATE proposal_comments SET resolved = ? WHERE id = ?").bind(resolved).bind(id).execute(pool).await?;

    Ok(())

}

pub async fn reviews(pool: &Pool, proposal_id: i64) -> sqlx::Result<Vec<ReviewRow>> {

    sqlx::query_as("SELECT * FROM proposal_reviews WHERE proposal_id = ? ORDER BY created_ms, id").bind(proposal_id).fetch_all(pool).await

}

pub async fn add_review(pool: &Pool, proposal_id: i64, author_id: i64, author: &str, verdict: &str, body: &str) -> sqlx::Result<ReviewRow> {

    add_review_via(pool, proposal_id, author_id, author, verdict, body, "").await

}

/// A review answered by an AI assistant (`via`) for `author`, who asked.

pub async fn add_review_via(

    pool: &Pool,

    proposal_id: i64,

    author_id: i64,

    author: &str,

    verdict: &str,

    body: &str,

    via: &str,

) -> sqlx::Result<ReviewRow> {

    let row = sqlx::query_as(

        "INSERT INTO proposal_reviews (proposal_id, author_id, author, verdict, body, created_ms, via) VALUES (?, ?, ?, ?, ?, ?, ?) RETURNING *",

    )

    .bind(proposal_id)

    .bind(author_id)

    .bind(author)

    .bind(verdict)

    .bind(body)

    .bind(now_ms())

    .bind(via)

    .fetch_one(pool)

    .await?;

    touch(pool, proposal_id).await?;

    Ok(row)

}

pub async fn events(pool: &Pool, proposal_id: i64) -> sqlx::Result<Vec<EventRow>> {

    sqlx::query_as("SELECT id, at_ms, actor, event, detail FROM proposal_events WHERE proposal_id = ? ORDER BY at_ms, id")

        .bind(proposal_id)

        .fetch_all(pool)

        .await

}

pub async fn add_event(pool: &Pool, proposal_id: i64, actor: &str, event: &str, detail: &str) -> sqlx::Result<()> {

    sqlx::query("INSERT INTO proposal_events (proposal_id, at_ms, actor, event, detail) VALUES (?, ?, ?, ?, ?)")

        .bind(proposal_id)

        .bind(now_ms())

        .bind(actor)

        .bind(event)

        .bind(detail)

        .execute(pool)

        .await?;

    Ok(())

}

/// Landing requirements of a repository.

#[derive(Clone, Copy, Debug, Default, FromRow)]

pub struct Requirements {

    pub required_approvals: i64,

    pub require_owners: bool,

    pub allow_conflicts: bool,

}

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

    sqlx::query_as("SELECT required_approvals, require_owners, allow_conflicts FROM repositories WHERE id = ?")

        .bind(repo_id)

        .fetch_one(pool)

        .await

}

pub async fn set_requirements(pool: &Pool, repo_id: i64, r: Requirements) -> sqlx::Result<()> {

    sqlx::query("UPDATE repositories SET required_approvals = ?, require_owners = ?, allow_conflicts = ? WHERE id = ?")

        .bind(r.required_approvals)

        .bind(r.require_owners)

        .bind(r.allow_conflicts)

        .bind(repo_id)

        .execute(pool)

        .await?;

    Ok(())

}

// ------------------------------------------------------- review requests

/// Ask an account to review a proposal; false when it was already asked.

pub async fn request_review(pool: &Pool, proposal_id: i64, account_id: i64, by: &str) -> sqlx::Result<bool> {

    let r = sqlx::query(

        "INSERT OR IGNORE INTO proposal_review_requests (proposal_id, account_id, requested_by, requested_ms) VALUES (?, ?, ?, ?)",

    )

    .bind(proposal_id)

    .bind(account_id)

    .bind(by)

    .bind(now_ms())

    .execute(pool)

    .await?;

    Ok(r.rows_affected() == 1)

}

/// Withdraw a request, or mark it answered; false when there was none.

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

    let r = sqlx::query("DELETE FROM proposal_review_requests WHERE proposal_id = ? AND account_id = ?")

        .bind(proposal_id)

        .bind(account_id)

        .execute(pool)

        .await?;

    Ok(r.rows_affected() == 1)

}

/// The usernames asked to review a proposal and not yet reviewing, in the

/// order they were asked.

pub async fn review_requests(pool: &Pool, proposal_id: i64) -> sqlx::Result<Vec<String>> {

    sqlx::query_scalar(

        "SELECT a.username FROM proposal_review_requests r JOIN accounts a ON a.id = r.account_id \

         WHERE r.proposal_id = ? ORDER BY r.requested_ms, a.username",

    )

    .bind(proposal_id)

    .fetch_all(pool)

    .await

}