//! Push protection's records (migration 0085, oxygenhub#268): fingerprints
//! a maintainer allowed, and the findings of recent refusals; and the
//! history scan's (migration 0086, oxygenhub#277): its findings, marked
//! resolved once the secret was revoked, and each repository's progress.

use super::{now_ms, Pool};
use sqlx::FromRow;

/// Refused pushes' findings kept per repository: the newest, for
/// maintainers to judge. The history scan's are all kept (one per value
/// and file).
const KEPT_FINDINGS: i64 = 200;

/// A finding's source: a refused push, or the scan of history.
pub const PUSH: &str = "push";
pub const HISTORY: &str = "history";

#[derive(Clone, Debug, FromRow)]
pub struct Bypass {
    pub id: i64,
    pub fingerprint: String,
    pub rule: String,
    pub reason: String,
    pub by_name: String,
    pub created_ms: i64,
}

#[derive(Clone, Debug, FromRow)]
pub struct FindingRow {
    pub id: i64,
    pub at_ms: i64,
    pub pusher: String,
    pub via: String,
    pub rule: String,
    pub description: String,
    pub path: String,
    pub line: i64,
    pub fingerprint: String,
    pub excerpt: String,
}

/// A finding of the history scan (#277): where it is, and whether a
/// maintainer marked it resolved.
#[derive(Clone, Debug, FromRow)]
pub struct HistoryRow {
    pub id: i64,
    pub at_ms: i64,
    pub rule: String,
    pub description: String,
    pub path: String,
    pub line: i64,
    pub fingerprint: String,
    pub excerpt: String,
    pub change_hash: String,
    pub bookmark: String,
    pub resolved_ms: Option<i64>,
    pub resolved_by: String,
    pub resolved_reason: String,
}

/// A finding to record.
pub struct NewFinding<'a> {
    pub rule: &'a str,
    pub description: &'a str,
    pub path: &'a str,
    pub line: i64,
    pub fingerprint: &'a str,
    pub excerpt: &'a str,
}

/// The fingerprints allowed in a repository.
pub async fn allowed(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<String>> {
    sqlx::query_scalar("SELECT fingerprint FROM secret_bypasses WHERE repo_id = $1").bind(repo_id).fetch_all(pool).await
}

pub async fn bypasses(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<Bypass>> {
    sqlx::query_as("SELECT id, fingerprint, rule, reason, by_name, created_ms FROM secret_bypasses WHERE repo_id = $1 ORDER BY id DESC")
        .bind(repo_id)
        .fetch_all(pool)
        .await
}

/// Allow `fingerprint` with `reason`; allowing it again replaces the reason.
pub async fn allow(pool: &Pool, repo_id: i64, fingerprint: &str, rule: &str, reason: &str, by: Option<(i64, &str)>) -> sqlx::Result<()> {
    sqlx::query(
        "INSERT INTO secret_bypasses (repo_id, fingerprint, rule, reason, by_account, by_name, created_ms) VALUES ($1, $2, $3, $4, $5, $6, $7) \
         ON CONFLICT (repo_id, fingerprint) DO UPDATE SET reason = excluded.reason, rule = excluded.rule, by_account = excluded.by_account, by_name = excluded.by_name, created_ms = excluded.created_ms",
    )
    .bind(repo_id)
    .bind(fingerprint)
    .bind(rule)
    .bind(reason)
    .bind(by.map(|b| b.0))
    .bind(by.map(|b| b.1).unwrap_or(""))
    .bind(now_ms())
    .execute(pool)
    .await?;
    Ok(())
}

/// Record a refused push's findings, keeping the newest few hundred.
pub async fn record(pool: &Pool, repo_id: i64, pusher: &str, via: &str, findings: &[NewFinding<'_>]) -> sqlx::Result<()> {
    let now = now_ms();
    let mut tx = super::begin_write(pool).await?;
    for f in findings {
        sqlx::query(
            "INSERT INTO secret_findings (repo_id, at_ms, pusher, via, rule, description, path, line, fingerprint, excerpt) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)",
        )
        .bind(repo_id)
        .bind(now)
        .bind(pusher)
        .bind(via)
        .bind(f.rule)
        .bind(f.description)
        .bind(f.path)
        .bind(f.line)
        .bind(f.fingerprint)
        .bind(f.excerpt)
        .execute(&mut *tx)
        .await?;
    }
    sqlx::query(
        "DELETE FROM secret_findings WHERE repo_id = $1 AND source = 'push' \
         AND id NOT IN (SELECT id FROM secret_findings WHERE repo_id = $2 AND source = 'push' ORDER BY id DESC LIMIT $3)",
    )
    .bind(repo_id)
    .bind(repo_id)
    .bind(KEPT_FINDINGS)
    .execute(&mut *tx)
    .await?;
    tx.commit().await
}

/// The newest findings of refused pushes, those not since allowed.
pub async fn recent(pool: &Pool, repo_id: i64, limit: i64) -> sqlx::Result<Vec<FindingRow>> {
    sqlx::query_as(
        "SELECT id, at_ms, pusher, via, rule, description, path, line, fingerprint, excerpt FROM secret_findings f \
         WHERE repo_id = $1 AND source = 'push' \
         AND NOT EXISTS (SELECT 1 FROM secret_bypasses b WHERE b.repo_id = f.repo_id AND b.fingerprint = f.fingerprint) \
         ORDER BY id DESC LIMIT $2",
    )
    .bind(repo_id)
    .bind(limit)
    .fetch_all(pool)
    .await
}

/// The rule a fingerprint was found under in this repository, if it was.
pub async fn rule_of(pool: &Pool, repo_id: i64, fingerprint: &str) -> sqlx::Result<Option<String>> {
    sqlx::query_scalar("SELECT rule FROM secret_findings WHERE repo_id = $1 AND fingerprint = $2 ORDER BY id DESC LIMIT 1")
        .bind(repo_id)
        .bind(fingerprint)
        .fetch_optional(pool)
        .await
}

/// Whether pushes adding possible secrets are refused here.
pub async fn set_enabled(pool: &Pool, repo_id: i64, on: bool) -> sqlx::Result<()> {
    sqlx::query("UPDATE repositories SET push_protection = $1 WHERE id = $2").bind(on).bind(repo_id).execute(pool).await?;
    Ok(())
}

/// Remove an allowed fingerprint (#277): pushes adding that value are
/// refused again. The row removed, if there was one.
pub async fn remove_bypass(pool: &Pool, repo_id: i64, fingerprint: &str) -> sqlx::Result<Option<Bypass>> {
    let row: Option<Bypass> = sqlx::query_as(
        "SELECT id, fingerprint, rule, reason, by_name, created_ms FROM secret_bypasses WHERE repo_id = $1 AND fingerprint = $2",
    )
    .bind(repo_id)
    .bind(fingerprint)
    .fetch_optional(pool)
    .await?;
    if let Some(b) = &row {
        sqlx::query("DELETE FROM secret_bypasses WHERE id = $1").bind(b.id).execute(pool).await?;
    }
    Ok(row)
}

/// A finding of the history scan to record.
pub struct NewHistoryFinding<'a> {
    pub finding: NewFinding<'a>,
    pub change_hash: String,
    pub bookmark: String,
}

/// Record the history scan's findings: one per value and file, so a value
/// found again (in a later change, or by a rescan) keeps its first row and
/// whether it was resolved. How many were new.
pub async fn record_history(pool: &Pool, repo_id: i64, findings: &[NewHistoryFinding<'_>]) -> sqlx::Result<usize> {
    let now = now_ms();
    let mut added = 0;
    for h in findings {
        let f = &h.finding;
        let r = sqlx::query(
            "INSERT INTO secret_findings (repo_id, at_ms, pusher, via, rule, description, path, line, fingerprint, excerpt, source, change_hash, bookmark) \
             VALUES ($1, $2, '', '', $3, $4, $5, $6, $7, $8, 'history', $9, $10) \
             ON CONFLICT (repo_id, fingerprint, path) WHERE source = 'history' DO NOTHING",
        )
        .bind(repo_id)
        .bind(now)
        .bind(f.rule)
        .bind(f.description)
        .bind(f.path)
        .bind(f.line)
        .bind(f.fingerprint)
        .bind(f.excerpt)
        .bind(&h.change_hash)
        .bind(&h.bookmark)
        .execute(pool)
        .await?;
        added += r.rows_affected() as usize;
    }
    Ok(added)
}

const HISTORY_COLUMNS: &str =
    "id, at_ms, rule, description, path, line, fingerprint, excerpt, change_hash, bookmark, resolved_ms, resolved_by, resolved_reason";

/// The history scan's findings still open: not resolved, their value not
/// allowed; oldest first, as history has them.
pub async fn history(pool: &Pool, repo_id: i64, limit: i64) -> sqlx::Result<Vec<HistoryRow>> {
    sqlx::query_as(&format!(
        "SELECT {HISTORY_COLUMNS} FROM secret_findings f WHERE repo_id = $1 AND source = 'history' AND resolved_ms IS NULL \
         AND NOT EXISTS (SELECT 1 FROM secret_bypasses b WHERE b.repo_id = f.repo_id AND b.fingerprint = f.fingerprint) \
         ORDER BY id LIMIT $2"
    ))
    .bind(repo_id)
    .bind(limit)
    .fetch_all(pool)
    .await
}

/// The history scan's findings marked resolved, newest mark first.
pub async fn resolved(pool: &Pool, repo_id: i64, limit: i64) -> sqlx::Result<Vec<HistoryRow>> {
    sqlx::query_as(&format!(
        "SELECT {HISTORY_COLUMNS} FROM secret_findings WHERE repo_id = $1 AND source = 'history' AND resolved_ms IS NOT NULL \
         ORDER BY resolved_ms DESC, id DESC LIMIT $2"
    ))
    .bind(repo_id)
    .bind(limit)
    .fetch_all(pool)
    .await
}

/// Mark history finding `id` resolved (the secret was revoked) with
/// `reason`; the finding, if it was an open one of this repository's.
pub async fn resolve(pool: &Pool, repo_id: i64, id: i64, by: &str, reason: &str) -> sqlx::Result<Option<HistoryRow>> {
    let done = sqlx::query(
        "UPDATE secret_findings SET resolved_ms = $1, resolved_by = $2, resolved_reason = $3 \
         WHERE id = $4 AND repo_id = $5 AND source = 'history' AND resolved_ms IS NULL",
    )
    .bind(now_ms())
    .bind(by)
    .bind(reason)
    .bind(id)
    .bind(repo_id)
    .execute(pool)
    .await?;
    if done.rows_affected() == 0 {
        return Ok(None);
    }
    sqlx::query_as(&format!("SELECT {HISTORY_COLUMNS} FROM secret_findings WHERE id = $1")).bind(id).fetch_optional(pool).await
}

/// A repository's history scan (#277): the rules it ran with, the heads
/// whose history is scanned (`done_heads`), the heads a scan under way
/// works toward (`target_heads`, empty when none is) and how far it got
/// (`position` of `pending` changes, in the scan's order), totals, and a
/// maintainer's request for a scan now.
#[derive(Clone, Debug, Default, PartialEq, Eq, FromRow)]
pub struct ScanRow {
    pub repo_id: i64,
    pub rules_version: i64,
    pub done_heads: String,
    pub target_heads: String,
    pub position: i64,
    pub pending: i64,
    pub changes: i64,
    pub bytes: i64,
    pub requested_ms: Option<i64>,
    pub pass_ms: Option<i64>,
    pub finished_ms: Option<i64>,
    pub error: String,
}

const SCAN_COLUMNS: &str =
    "repo_id, rules_version, done_heads, target_heads, position, pending, changes, bytes, requested_ms, pass_ms, finished_ms, error";

pub async fn scan_of(pool: &Pool, repo_id: i64) -> sqlx::Result<Option<ScanRow>> {
    sqlx::query_as(&format!("SELECT {SCAN_COLUMNS} FROM secret_scans WHERE repo_id = $1")).bind(repo_id).fetch_optional(pool).await
}

/// Save a repository's scan progress.
pub async fn save_scan(pool: &Pool, s: &ScanRow) -> sqlx::Result<()> {
    sqlx::query(&format!(
        "INSERT INTO secret_scans ({SCAN_COLUMNS}) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) \
         ON CONFLICT (repo_id) DO UPDATE SET rules_version = excluded.rules_version, done_heads = excluded.done_heads, \
         target_heads = excluded.target_heads, position = excluded.position, pending = excluded.pending, changes = excluded.changes, \
         bytes = excluded.bytes, requested_ms = excluded.requested_ms, pass_ms = excluded.pass_ms, finished_ms = excluded.finished_ms, \
         error = excluded.error"
    ))
    .bind(s.repo_id)
    .bind(s.rules_version)
    .bind(&s.done_heads)
    .bind(&s.target_heads)
    .bind(s.position)
    .bind(s.pending)
    .bind(s.changes)
    .bind(s.bytes)
    .bind(s.requested_ms)
    .bind(s.pass_ms)
    .bind(s.finished_ms)
    .bind(&s.error)
    .execute(pool)
    .await?;
    Ok(())
}

/// A maintainer asked for the whole history to be scanned again now:
/// what was scanned is forgotten (findings and their marks are kept) and
/// the repository goes first.
pub async fn request_scan(pool: &Pool, repo_id: i64) -> sqlx::Result<()> {
    let now = now_ms();
    sqlx::query(
        "INSERT INTO secret_scans (repo_id, requested_ms) VALUES ($1, $2) \
         ON CONFLICT (repo_id) DO UPDATE SET requested_ms = excluded.requested_ms, done_heads = '', target_heads = '', position = 0, pending = 0",
    )
    .bind(repo_id)
    .bind(now)
    .execute(pool)
    .await?;
    Ok(())
}

/// Repositories whose maintainers asked for a scan, oldest request first.
pub async fn requested(pool: &Pool) -> sqlx::Result<Vec<i64>> {
    sqlx::query_scalar("SELECT repo_id FROM secret_scans WHERE requested_ms IS NOT NULL ORDER BY requested_ms").fetch_all(pool).await
}