//! 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,
}

#[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,
}

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 = ?").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 (?, ?, ?, ?, ?, ?, ?) 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 = ? AND number = ?").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) VALUES (?, ?, ?)")
            .bind(p.id)
            .bind(name)
            .bind(i as i64)
            .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 = ? 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 = ?").bind(id).fetch_one(&mut *tx).await?;
    let number: i64 =
        sqlx::query_scalar("SELECT COALESCE(MAX(number), 0) + 1 FROM projects WHERE owner = ?").bind(to).fetch_one(&mut *tx).await?;
    sqlx::query("INSERT OR REPLACE INTO project_redirects (owner, number, project_id) VALUES (?, ?, ?)")
        .bind(&old.owner)
        .bind(old.number)
        .bind(id)
        .execute(&mut *tx)
        .await?;
    sqlx::query("DELETE FROM project_redirects WHERE owner = ? AND number = ?").bind(to).bind(number).execute(&mut *tx).await?;
    let moved: Project = sqlx::query_as("UPDATE projects SET owner = ?, number = ?, updated_ms = ? WHERE id = ? 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 = ? AND r.number = ?")
        .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 = ? AND number = ?").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 = ?, description = ?, public = ?, closed = ?, updated_ms = ? WHERE id = ?")
        .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 = ?").bind(id).execute(pool).await?;
    Ok(())
}

async fn touch(pool: &Pool, id: i64) -> sqlx::Result<()> {
    sqlx::query("UPDATE projects SET updated_ms = ? WHERE id = ?").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 = ? 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 = ? 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 = ?")
        .bind(project_id)
        .fetch_one(pool)
        .await?;
    sqlx::query("INSERT INTO project_columns (project_id, name, position) VALUES (?, ?, ?)")
        .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 = ? WHERE id = ? AND project_id = ?")
        .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 = ? WHERE id = ?").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 = ?")
        .bind(to.id)
        .fetch_one(&mut *tx)
        .await?;
    sqlx::query("UPDATE project_cards SET column_id = ?, position = position + ? WHERE column_id = ?")
        .bind(to.id)
        .bind(base)
        .bind(column_id)
        .execute(&mut *tx)
        .await?;
    sqlx::query("DELETE FROM project_columns WHERE id = ?").bind(column_id).execute(&mut *tx).await?;
    tx.commit().await?;
    touch(pool, project_id).await?;
    Ok(true)
}

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 = ?")
        .bind(c.column_id)
        .fetch_one(pool)
        .await?;
    let r = sqlx::query(
        "INSERT OR IGNORE INTO project_cards (project_id, column_id, position, kind, repo_id, number, note, added_by, created_ms) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
    )
    .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_ms())
    .execute(pool)
    .await?;
    touch(pool, project_id).await?;
    Ok(r.rows_affected() == 1)
}

/// 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 = ? AND project_id = ?")
        .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 = ?")
        .bind(column_id)
        .fetch_one(pool)
        .await?;
    sqlx::query("UPDATE project_cards SET column_id = ?, position = ? WHERE id = ? AND project_id = ?")
        .bind(column_id)
        .bind(pos)
        .bind(card_id)
        .bind(project_id)
        .execute(pool)
        .await?;
    touch(pool, project_id).await
}

/// Swap a card with its neighbour in its column (`-1` up, `1` down).
pub async fn shift_card(pool: &Pool, project_id: i64, card_id: i64, by: i64) -> sqlx::Result<()> {
    let Some(card): Option<Card> = sqlx::query_as("SELECT * FROM project_cards WHERE id = ? AND project_id = ?")
        .bind(card_id)
        .bind(project_id)
        .fetch_optional(pool)
        .await?
    else {
        return Ok(());
    };
    let col: Vec<Card> = sqlx::query_as("SELECT * FROM project_cards WHERE column_id = ? ORDER BY position, id")
        .bind(card.column_id)
        .fetch_all(pool)
        .await?;
    let Some(i) = col.iter().position(|c| c.id == card_id) else { return Ok(()) };
    let j = i as i64 + by;
    if j < 0 || j as usize >= col.len() {
        return Ok(());
    }
    let mut ids: Vec<i64> = col.iter().map(|c| c.id).collect();
    ids.swap(i, j as usize);
    for (pos, id) in ids.iter().enumerate() {
        sqlx::query("UPDATE project_cards SET position = ? WHERE id = ?").bind(pos as i64).bind(id).execute(pool).await?;
    }
    touch(pool, project_id).await
}

pub async fn remove_card(pool: &Pool, project_id: i64, card_id: i64) -> sqlx::Result<()> {
    sqlx::query("DELETE FROM project_cards WHERE id = ? AND project_id = ?").bind(card_id).bind(project_id).execute(pool).await?;
    touch(pool, project_id).await
}

/// The projects (and column names) an issue or proposal is on.
pub async fn placements(pool: &Pool, repo_id: i64, number: i64) -> sqlx::Result<Vec<(Project, String)>> {
    let rows: Vec<(i64, String)> = sqlx::query_as(
        "SELECT c.project_id, col.name FROM project_cards c JOIN project_columns col ON col.id = c.column_id WHERE c.repo_id = ? AND c.number = ? AND c.kind <> 'note'",
    )
    .bind(repo_id)
    .bind(number)
    .fetch_all(pool)
    .await?;
    let mut out = Vec::new();
    for (pid, col) in rows {
        if let Some(p) = sqlx::query_as::<_, Project>("SELECT * FROM projects WHERE id = ?").bind(pid).fetch_optional(pool).await? {
            out.push((p, col));
        }
    }
    Ok(out)
}