//! Projects, their columns and cards (hub spec §8.2).

use super::{now_ms, Pool};

use sqlx::FromRow;

#[derive(Clone, Debug, FromRow)]

pub struct Project {

    pub id: i64,

    pub owner: String,

    pub number: i64,

    pub title: String,

    pub description: String,

    pub public: bool,

    pub closed: bool,

    pub created_ms: i64,

    pub updated_ms: i64,

}

#[derive(Clone, Debug, FromRow)]

pub struct Column {

    pub id: i64,

    pub project_id: i64,

    pub name: String,

    pub position: i64,

    /// Its cards are archived this many days after they joined it (#293).

    pub archive_after_days: Option<i64>,

    /// Cards whose issue closes or whose proposal lands or closes move here

    /// (#325); one column of a board at most.

    pub takes_finished: bool,

    /// Work in progress past this many cards turns its header amber (#361).

    #[sqlx(default)]

    pub wip_limit: Option<i64>,

}

#[derive(Clone, Debug, FromRow)]

pub struct Card {

    pub id: i64,

    pub project_id: i64,

    pub column_id: i64,

    pub position: i64,

    pub kind: String,

    pub repo_id: Option<i64>,

    pub number: Option<i64>,

    pub note: String,

    pub added_by: String,

    pub created_ms: i64,

    /// Off the board since then (#293): kept, and restorable.

    pub archived_ms: Option<i64>,

    /// When it joined its column.

    pub moved_ms: i64,

    /// The iteration it was archived under when that closed (#343).

    pub iteration_id: Option<i64>,

}

/// A named span of a board's days (#343). A board has one open at most;

/// closing it archives its Done column's cards under it.

#[derive(Clone, Debug, FromRow)]

pub struct Iteration {

    pub id: i64,

    pub project_id: i64,

    pub name: String,

    pub start_ms: i64,

    /// The end, exclusive: the midnight after its last day.

    pub end_ms: i64,

    pub closed_ms: Option<i64>,

}

/// A day, for the archive's ages.

const DAY_MS: i64 = 86_400_000;

pub const DEFAULT_COLUMNS: [&str; 3] = ["Backlog", "Doing", "Done"];

pub async fn create(pool: &Pool, owner: &str, title: &str, description: &str, public: bool) -> sqlx::Result<Project> {

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

    let number: i64 =

        sqlx::query_scalar("SELECT COALESCE(MAX(number), 0) + 1 FROM projects WHERE owner = $1").bind(owner).fetch_one(&mut *tx).await?;

    let now = now_ms();

    let p: Project = sqlx::query_as(

        "INSERT INTO projects (owner, number, title, description, public, created_ms, updated_ms) VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING *",

    )

    .bind(owner)

    .bind(number)

    .bind(title)

    .bind(description)

    .bind(public)

    .bind(now)

    .bind(now)

    .fetch_one(&mut *tx)

    .await?;

    // A board here now: an old address of a transferred one gives way (#125).

    sqlx::query("DELETE FROM project_redirects WHERE owner = $1 AND number = $2").bind(owner).bind(number).execute(&mut *tx).await?;

    for (i, name) in DEFAULT_COLUMNS.iter().enumerate() {

        sqlx::query("INSERT INTO project_columns (project_id, name, position, takes_finished) VALUES ($1, $2, $3, $4)")

            .bind(p.id)

            .bind(name)

            .bind(i as i64)

            .bind(*name == "Done")

            .execute(&mut *tx)

            .await?;

    }

    tx.commit().await?;

    Ok(p)

}

pub async fn of_owner(pool: &Pool, owner: &str) -> sqlx::Result<Vec<Project>> {

    sqlx::query_as("SELECT * FROM projects WHERE owner = $1 ORDER BY closed, updated_ms DESC").bind(owner).fetch_all(pool).await

}

/// Move project `id` to owner `to` (#125): it takes the next number there,

/// with its columns, cards and privacy, and its old address redirects to it.

pub async fn transfer(pool: &Pool, id: i64, to: &str) -> sqlx::Result<Project> {

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

    let old: Project = sqlx::query_as("SELECT * FROM projects WHERE id = $1").bind(id).fetch_one(&mut *tx).await?;

    let number: i64 =

        sqlx::query_scalar("SELECT COALESCE(MAX(number), 0) + 1 FROM projects WHERE owner = $1").bind(to).fetch_one(&mut *tx).await?;

    sqlx::query("INSERT INTO project_redirects (owner, number, project_id) VALUES ($1, $2, $3) ON CONFLICT (owner, number) DO UPDATE SET project_id = excluded.project_id")

        .bind(&old.owner)

        .bind(old.number)

        .bind(id)

        .execute(&mut *tx)

        .await?;

    sqlx::query("DELETE FROM project_redirects WHERE owner = $1 AND number = $2").bind(to).bind(number).execute(&mut *tx).await?;

    let moved: Project = sqlx::query_as("UPDATE projects SET owner = $1, number = $2, updated_ms = $3 WHERE id = $4 RETURNING *")

        .bind(to)

        .bind(number)

        .bind(now_ms())

        .bind(id)

        .fetch_one(&mut *tx)

        .await?;

    tx.commit().await?;

    Ok(moved)

}

/// The project an old address (`owner`, `number`) now redirects to, after

/// a transfer (#125).

pub async fn redirected(pool: &Pool, owner: &str, number: i64) -> sqlx::Result<Option<Project>> {

    sqlx::query_as("SELECT p.* FROM project_redirects r JOIN projects p ON p.id = r.project_id WHERE r.owner = $1 AND r.number = $2")

        .bind(owner)

        .bind(number)

        .fetch_optional(pool)

        .await

}

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

    sqlx::query_as("SELECT * FROM projects WHERE owner = $1 AND number = $2").bind(owner).bind(number).fetch_optional(pool).await

}

pub async fn set(pool: &Pool, id: i64, title: &str, description: &str, public: bool, closed: bool) -> sqlx::Result<()> {

    sqlx::query("UPDATE projects SET title = $1, description = $2, public = $3, closed = $4, updated_ms = $5 WHERE id = $6")

        .bind(title)

        .bind(description)

        .bind(public)

        .bind(closed)

        .bind(now_ms())

        .bind(id)

        .execute(pool)

        .await?;

    Ok(())

}

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

    sqlx::query("DELETE FROM projects WHERE id = $1").bind(id).execute(pool).await?;

    Ok(())

}

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

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

    Ok(())

}

pub async fn columns(pool: &Pool, project_id: i64) -> sqlx::Result<Vec<Column>> {

    sqlx::query_as("SELECT * FROM project_columns WHERE project_id = $1 ORDER BY position, id").bind(project_id).fetch_all(pool).await

}

pub async fn cards(pool: &Pool, project_id: i64) -> sqlx::Result<Vec<Card>> {

    sqlx::query_as("SELECT * FROM project_cards WHERE project_id = $1 ORDER BY position, id").bind(project_id).fetch_all(pool).await

}

pub async fn add_column(pool: &Pool, project_id: i64, name: &str) -> sqlx::Result<()> {

    let pos: i64 = sqlx::query_scalar("SELECT COALESCE(MAX(position), -1) + 1 FROM project_columns WHERE project_id = $1")

        .bind(project_id)

        .fetch_one(pool)

        .await?;

    sqlx::query("INSERT INTO project_columns (project_id, name, position) VALUES ($1, $2, $3)")

        .bind(project_id)

        .bind(name)

        .bind(pos)

        .execute(pool)

        .await?;

    touch(pool, project_id).await

}

pub async fn rename_column(pool: &Pool, project_id: i64, column_id: i64, name: &str) -> sqlx::Result<()> {

    sqlx::query("UPDATE project_columns SET name = $1 WHERE id = $2 AND project_id = $3")

        .bind(name)

        .bind(column_id)

        .bind(project_id)

        .execute(pool)

        .await?;

    touch(pool, project_id).await

}

/// Swap a column with its neighbour (`-1` left, `1` right).

pub async fn shift_column(pool: &Pool, project_id: i64, column_id: i64, by: i64) -> sqlx::Result<()> {

    let cols = columns(pool, project_id).await?;

    let Some(i) = cols.iter().position(|c| c.id == column_id) else { return Ok(()) };

    let j = i as i64 + by;

    if j < 0 || j as usize >= cols.len() {

        return Ok(());

    }

    let mut ids: Vec<i64> = cols.iter().map(|c| c.id).collect();

    ids.swap(i, j as usize);

    for (pos, id) in ids.iter().enumerate() {

        sqlx::query("UPDATE project_columns SET position = $1 WHERE id = $2").bind(pos as i64).bind(id).execute(pool).await?;

    }

    touch(pool, project_id).await

}

/// Move a column to `position` (0 the leftmost; past the end the rightmost).

pub async fn place_column(pool: &Pool, project_id: i64, column_id: i64, position: i64) -> sqlx::Result<()> {

    let cols = columns(pool, project_id).await?;

    let Some(i) = cols.iter().position(|c| c.id == column_id) else { return Ok(()) };

    let mut ids: Vec<i64> = cols.iter().map(|c| c.id).collect();

    let id = ids.remove(i);

    ids.insert((position.max(0) as usize).min(ids.len()), id);

    for (pos, id) in ids.iter().enumerate() {

        sqlx::query("UPDATE project_columns SET position = $1 WHERE id = $2").bind(pos as i64).bind(id).execute(pool).await?;

    }

    touch(pool, project_id).await

}

/// Remove a column; its cards go to the first remaining column. The last column stays.

pub async fn remove_column(pool: &Pool, project_id: i64, column_id: i64) -> sqlx::Result<bool> {

    let cols = columns(pool, project_id).await?;

    let Some(to) = cols.iter().find(|c| c.id != column_id) else { return Ok(false) };

    if !cols.iter().any(|c| c.id == column_id) {

        return Ok(false);

    }

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

    let base: i64 = sqlx::query_scalar("SELECT COALESCE(MAX(position), -1) + 1 FROM project_cards WHERE column_id = $1")

        .bind(to.id)

        .fetch_one(&mut *tx)

        .await?;

    log_moves(&mut *tx, Some(to.id), now_ms(), "column_id = $1 AND archived_ms IS NULL", &[column_id]).await?;

    sqlx::query("UPDATE project_cards SET column_id = $1, position = position + $2 WHERE column_id = $3")

        .bind(to.id)

        .bind(base)

        .bind(column_id)

        .execute(&mut *tx)

        .await?;

    sqlx::query("DELETE FROM project_columns WHERE id = $1").bind(column_id).execute(&mut *tx).await?;

    tx.commit().await?;

    touch(pool, project_id).await?;

    Ok(true)

}

/// Record, for every card `select` picks (`SELECT project_id, id FROM

/// project_cards WHERE …`, bound from `$1` on), that it moved to `column`

/// (`None`: off the board) at `at` (#357). Called before the update that

/// moves them, with the same conditions.

async fn log_moves<'e, E: sqlx::Executor<'e, Database = super::Db>>(

    ex: E,

    column: Option<i64>,

    at: i64,

    select: &str,

    binds: &[i64],

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

    let sql = format!(

        "INSERT INTO project_card_moves (project_id, card_id, column_id, at_ms) SELECT project_id, id, {}, {at} FROM project_cards WHERE {select}",

        match column {

            Some(c) => c.to_string(),

            None => "NULL".to_string(),

        }

    );

    let mut q = sqlx::query(&sql);

    for b in binds {

        q = q.bind(*b);

    }

    q.execute(ex).await?;

    Ok(())

}

/// A board's card moves since `since`, oldest first: (card, column or

/// `None` off the board, when).

pub async fn moves(pool: &Pool, project_id: i64, since: i64) -> sqlx::Result<Vec<(i64, Option<i64>, i64)>> {

    sqlx::query_as("SELECT card_id, column_id, at_ms FROM project_card_moves WHERE project_id = $1 AND at_ms >= $2 ORDER BY at_ms, id")

        .bind(project_id)

        .bind(since)

        .fetch_all(pool)

        .await

}

pub struct NewCard<'a> {

    pub column_id: i64,

    pub kind: &'a str,

    pub repo_id: Option<i64>,

    pub number: Option<i64>,

    pub note: &'a str,

    pub added_by: &'a str,

}

/// Add a card at the bottom of a column; `Ok(false)` when that issue or

/// proposal is already on the project.

pub async fn add_card(pool: &Pool, project_id: i64, c: NewCard<'_>) -> sqlx::Result<bool> {

    let pos: i64 = sqlx::query_scalar("SELECT COALESCE(MAX(position), -1) + 1 FROM project_cards WHERE column_id = $1")

        .bind(c.column_id)

        .fetch_one(pool)

        .await?;

    let now = now_ms();

    let id: Option<i64> = sqlx::query_scalar(

        "INSERT INTO project_cards (project_id, column_id, position, kind, repo_id, number, note, added_by, created_ms, moved_ms) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $9) ON CONFLICT DO NOTHING RETURNING id",

    )

    .bind(project_id)

    .bind(c.column_id)

    .bind(pos)

    .bind(c.kind)

    .bind(c.repo_id)

    .bind(c.number)

    .bind(c.note)

    .bind(c.added_by)

    .bind(now)

    .fetch_optional(pool)

    .await?;

    if let Some(id) = id {

        log_moves(pool, Some(c.column_id), now, "id = $1", &[id]).await?;

    }

    touch(pool, project_id).await?;

    Ok(id.is_some())

}

/// Move a card to the bottom of another column of the same project.

pub async fn move_card(pool: &Pool, project_id: i64, card_id: i64, column_id: i64) -> sqlx::Result<()> {

    let ok: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM project_columns WHERE id = $1 AND project_id = $2")

        .bind(column_id)

        .bind(project_id)

        .fetch_one(pool)

        .await?;

    if ok == 0 {

        return Ok(());

    }

    let pos: i64 = sqlx::query_scalar("SELECT COALESCE(MAX(position), -1) + 1 FROM project_cards WHERE column_id = $1")

        .bind(column_id)

        .fetch_one(pool)

        .await?;

    let now = now_ms();

    log_moves(

        pool,

        Some(column_id),

        now,

        "id = $1 AND project_id = $2 AND column_id <> $3 AND archived_ms IS NULL",

        &[card_id, project_id, column_id],

    )

    .await?;

    sqlx::query("UPDATE project_cards SET moved_ms = CASE WHEN column_id = $1 THEN moved_ms ELSE $3 END, column_id = $1, position = $2 WHERE id = $4 AND project_id = $5")

        .bind(column_id)

        .bind(pos)

        .bind(now)

        .bind(card_id)

        .bind(project_id)

        .execute(pool)

        .await?;

    touch(pool, project_id).await

}

/// Move `card_ids`, in that order, to the end of `column_id`, all or none

/// (#289).

pub async fn move_cards(pool: &Pool, project_id: i64, card_ids: &[i64], column_id: i64) -> sqlx::Result<()> {

    let mut tx = pool.begin().await?;

    let pos: i64 = sqlx::query_scalar("SELECT COALESCE(MAX(position), -1) + 1 FROM project_cards WHERE column_id = $1")

        .bind(column_id)

        .fetch_one(&mut *tx)

        .await?;

    let now = now_ms();

    for (i, id) in card_ids.iter().enumerate() {

        log_moves(

            &mut *tx,

            Some(column_id),

            now,

            "id = $1 AND project_id = $2 AND column_id <> $3 AND archived_ms IS NULL",

            &[*id, project_id, column_id],

        )

        .await?;

        sqlx::query("UPDATE project_cards SET column_id = $1, position = $2, moved_ms = $3 WHERE id = $4 AND project_id = $5")

            .bind(column_id)

            .bind(pos + i as i64)

            .bind(now)

            .bind(id)

            .bind(project_id)

            .execute(&mut *tx)

            .await?;

    }

    sqlx::query("UPDATE projects SET updated_ms = $1 WHERE id = $2").bind(now).bind(project_id).execute(&mut *tx).await?;

    tx.commit().await

}

/// Take `card_ids` off the board, kept (#293); how many were on it.

pub async fn archive_cards(pool: &Pool, project_id: i64, card_ids: &[i64]) -> sqlx::Result<u64> {

    let mut tx = pool.begin().await?;

    let now = now_ms();

    let mut n = 0;

    for id in card_ids {

        log_moves(&mut *tx, None, now, "id = $1 AND project_id = $2 AND archived_ms IS NULL", &[*id, project_id]).await?;

        n += sqlx::query("UPDATE project_cards SET archived_ms = $1 WHERE id = $2 AND project_id = $3 AND archived_ms IS NULL")

            .bind(now)

            .bind(id)

            .bind(project_id)

            .execute(&mut *tx)

            .await?

            .rows_affected();

    }

    sqlx::query("UPDATE projects SET updated_ms = $1 WHERE id = $2").bind(now).bind(project_id).execute(&mut *tx).await?;

    tx.commit().await?;

    Ok(n)

}

/// Put an archived card back on the board, at the end of its column, as

/// if it had just joined it (so its column does not archive it again at

/// once); `false` when it was not archived.

pub async fn restore_card(pool: &Pool, project_id: i64, card_id: i64) -> sqlx::Result<bool> {

    let mut tx = pool.begin().await?;

    let pos: i64 = sqlx::query_scalar(

        "SELECT COALESCE(MAX(position), -1) + 1 FROM project_cards WHERE column_id = (SELECT column_id FROM project_cards WHERE id = $1)",

    )

    .bind(card_id)

    .fetch_one(&mut *tx)

    .await?;

    let now = now_ms();

    sqlx::query(&format!(

        "INSERT INTO project_card_moves (project_id, card_id, column_id, at_ms) SELECT project_id, id, column_id, {now} FROM project_cards \

         WHERE id = $1 AND project_id = $2 AND archived_ms IS NOT NULL"

    ))

    .bind(card_id)

    .bind(project_id)

    .execute(&mut *tx)

    .await?;

    // Back on the board, it no longer counts as done in a past iteration.

    let r = sqlx::query(

        "UPDATE project_cards SET archived_ms = NULL, iteration_id = NULL, moved_ms = $1, position = $2 \

         WHERE id = $3 AND project_id = $4 AND archived_ms IS NOT NULL",

    )

    .bind(now)

    .bind(pos)

    .bind(card_id)

    .bind(project_id)

    .execute(&mut *tx)

    .await?;

    sqlx::query("UPDATE projects SET updated_ms = $1 WHERE id = $2").bind(now).bind(project_id).execute(&mut *tx).await?;

    tx.commit().await?;

    Ok(r.rows_affected() == 1)

}

/// Whether `card` joined its column more than `days` ago, at `now`.

pub fn older_than(card: &Card, days: i64, now: i64) -> bool {

    card.moved_ms < now - days * DAY_MS

}

/// A board's iterations, newest first (#343).

pub async fn iterations(pool: &Pool, project_id: i64) -> sqlx::Result<Vec<Iteration>> {

    sqlx::query_as("SELECT * FROM project_iterations WHERE project_id = $1 ORDER BY start_ms DESC, id DESC")

        .bind(project_id)

        .fetch_all(pool)

        .await

}

/// Start an iteration of `[start_ms, end_ms)`; `None` while another is open.

pub async fn start_iteration(pool: &Pool, project_id: i64, name: &str, start_ms: i64, end_ms: i64) -> sqlx::Result<Option<Iteration>> {

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

    let open: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM project_iterations WHERE project_id = $1 AND closed_ms IS NULL")

        .bind(project_id)

        .fetch_one(&mut *tx)

        .await?;

    if open > 0 {

        return Ok(None);

    }

    let it: Iteration =

        sqlx::query_as("INSERT INTO project_iterations (project_id, name, start_ms, end_ms) VALUES ($1, $2, $3, $4) RETURNING *")

            .bind(project_id)

            .bind(name)

            .bind(start_ms)

            .bind(end_ms)

            .fetch_one(&mut *tx)

            .await?;

    sqlx::query("UPDATE projects SET updated_ms = $1 WHERE id = $2").bind(now_ms()).bind(project_id).execute(&mut *tx).await?;

    tx.commit().await?;

    Ok(Some(it))

}

/// The column whose cards are done: the one that takes finished work, or

/// else the last.

pub fn done_column(columns: &[Column]) -> Option<i64> {

    columns.iter().find(|c| c.takes_finished).or(columns.last()).map(|c| c.id)

}

/// Close an open iteration: the cards on the board in `done` (its Done

/// column) are archived under it. How many, or `None` when it was not open.

pub async fn close_iteration(pool: &Pool, project_id: i64, iteration_id: i64, done: Option<i64>) -> sqlx::Result<Option<u64>> {

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

    let now = now_ms();

    let closed = sqlx::query("UPDATE project_iterations SET closed_ms = $1 WHERE id = $2 AND project_id = $3 AND closed_ms IS NULL")

        .bind(now)

        .bind(iteration_id)

        .bind(project_id)

        .execute(&mut *tx)

        .await?;

    if closed.rows_affected() == 0 {

        return Ok(None);

    }

    let mut n = 0;

    if let Some(done) = done {

        log_moves(&mut *tx, None, now, "project_id = $1 AND column_id = $2 AND archived_ms IS NULL", &[project_id, done]).await?;

        n = sqlx::query(

            "UPDATE project_cards SET archived_ms = $1, iteration_id = $2 WHERE project_id = $3 AND column_id = $4 AND archived_ms IS NULL",

        )

        .bind(now)

        .bind(iteration_id)

        .bind(project_id)

        .bind(done)

        .execute(&mut *tx)

        .await?

        .rows_affected();

    }

    sqlx::query("UPDATE projects SET updated_ms = $1 WHERE id = $2").bind(now).bind(project_id).execute(&mut *tx).await?;

    tx.commit().await?;

    Ok(Some(n))

}

/// When each card done in `it` reached the Done column `done`: those

/// archived under it, and while it is open those in `done` now or archived

/// from there during it.

pub async fn done_times(pool: &Pool, it: &Iteration, done: Option<i64>) -> sqlx::Result<Vec<i64>> {

    sqlx::query_scalar(

        "SELECT moved_ms FROM project_cards WHERE project_id = $1 AND (iteration_id = $2 \

         OR ($3 AND iteration_id IS NULL AND column_id = $4 AND (archived_ms IS NULL OR archived_ms >= $5))) ORDER BY moved_ms",

    )

    .bind(it.project_id)

    .bind(it.id)

    .bind(it.closed_ms.is_none())

    .bind(done.unwrap_or(-1))

    .bind(it.start_ms)

    .fetch_all(pool)

    .await

}

/// The burn-up of an iteration: cards done by the end of each of its days

/// so far, from `times` (when each reached Done; earlier than the start

/// counts on the first day). One entry per day up to `now` or its end.

pub fn burn_up(times: &[i64], start_ms: i64, end_ms: i64, now: i64) -> Vec<usize> {