use rusqlite::Connection;

/// What `forget_host` did with the stored credential.
#[derive(Debug, Default, PartialEq, Eq)]
pub struct ForgetOutcome {
    /// Addresses that still present the same certificate key.
    pub siblings: Vec<String>,
    /// True when no address for that server remained, so the credential was
    /// dropped with it.
    pub credentials_removed: bool,
}

pub fn init_db(conn: &Connection) {
    conn.execute(
        "CREATE TABLE IF NOT EXISTS hosts (
            host TEXT PRIMARY KEY,
            last_used DATETIME DEFAULT CURRENT_TIMESTAMP,
            fingerprint TEXT
        )",
        [],
    )
    .unwrap();
    // Migration for databases created before TOFU trust.
    let _ = conn.execute("ALTER TABLE hosts ADD COLUMN fingerprint TEXT", []);

    // Credentials belong to a server identity (the certificate key), not to a
    // single address, so every address that presents the same key shares them.
    if has_column(conn, "credentials", "host") {
        migrate_credentials_to_fingerprints(conn);
    }
    conn.execute(
        "CREATE TABLE IF NOT EXISTS credentials (
            fingerprint TEXT PRIMARY KEY,
            username TEXT NOT NULL,
            password TEXT NOT NULL
        )",
        [],
    )
    .unwrap();
}

fn has_column(conn: &Connection, table: &str, column: &str) -> bool {
    let Ok(mut statement) = conn.prepare(&format!("PRAGMA table_info({table})")) else {
        return false;
    };
    let Ok(rows) = statement.query_map([], |row| row.get::<_, String>(1)) else {
        return false;
    };
    rows.flatten().any(|name| name == column)
}

/// Re-key host-bound credentials by the fingerprint of their host.
///
/// Rows whose host was already forgotten have no identity to attach to and are
/// dropped; those addresses have to re-authenticate once.
fn migrate_credentials_to_fingerprints(conn: &Connection) {
    let tx = conn.unchecked_transaction().unwrap();
    tx.execute_batch(
        "DROP TABLE IF EXISTS credentials_by_identity;
         CREATE TABLE credentials_by_identity (
             fingerprint TEXT PRIMARY KEY,
             username TEXT NOT NULL,
             password TEXT NOT NULL
         );
         INSERT OR REPLACE INTO credentials_by_identity (fingerprint, username, password)
             SELECT h.fingerprint, c.username, c.password
             FROM credentials c
             JOIN hosts h ON h.host = c.host
             WHERE h.fingerprint IS NOT NULL
             ORDER BY h.last_used ASC, h.host ASC;
         DROP TABLE credentials;
         ALTER TABLE credentials_by_identity RENAME TO credentials;",
    )
    .unwrap();
    tx.commit().unwrap();
}

pub fn get_last_host(conn: &Connection) -> Option<String> {
    let mut stmt = conn
        .prepare("SELECT host FROM hosts ORDER BY last_used DESC LIMIT 1")
        .unwrap();
    let mut rows = stmt.query([]).unwrap();
    if let Ok(Some(row)) = rows.next() {
        row.get(0).ok()
    } else {
        None
    }
}

pub fn save_host(conn: &Connection, host: &str) {
    conn.execute(
        "INSERT INTO hosts (host) VALUES (?1)
         ON CONFLICT(host) DO UPDATE SET last_used = CURRENT_TIMESTAMP",
        [host],
    )
    .unwrap();
}

/// Store credentials for a server identity (certificate fingerprint).
pub fn save_credentials(
    conn: &Connection,
    fingerprint: &str,
    username: &str,
    password: &str,
) -> Result<(), Box<dyn std::error::Error>> {
    conn.execute(
        "INSERT OR REPLACE INTO credentials (fingerprint, username, password)
         VALUES (?1, ?2, ?3)",
        (fingerprint, username, password),
    )?;
    Ok(())
}

/// Credentials for a server identity, regardless of which address reached it.
pub fn get_credentials(conn: &Connection, fingerprint: &str) -> Option<(String, String)> {
    let mut stmt = conn
        .prepare("SELECT username, password FROM credentials WHERE fingerprint = ?1")
        .unwrap();
    let mut rows = stmt.query([fingerprint]).unwrap();
    if let Ok(Some(row)) = rows.next() {
        let username: String = row.get(0).unwrap();
        let password: String = row.get(1).unwrap();
        Some((username, password))
    } else {
        None
    }
}

/// SHA-256 SPKI fingerprint of a host's certificate, if trusted.
pub fn get_fingerprint(conn: &Connection, host: &str) -> Option<String> {
    let mut stmt = conn
        .prepare("SELECT fingerprint FROM hosts WHERE host = ?1")
        .ok()?;
    let mut rows = stmt.query([host]).ok()?;
    match rows.next() {
        Ok(Some(row)) => row.get(0).ok(),
        _ => None,
    }
}

pub fn save_fingerprint(
    conn: &Connection,
    host: &str,
    fingerprint: &str,
) -> Result<(), rusqlite::Error> {
    // Hosts may not exist yet (fingerprint can be saved before first
    // successful connect completes save_host).
    conn.execute("INSERT OR IGNORE INTO hosts (host) VALUES (?1)", [host])?;
    conn.execute(
        "UPDATE hosts SET fingerprint = ?2 WHERE host = ?1",
        (host, fingerprint),
    )?;
    Ok(())
}

/// Addresses that present `fingerprint`, most recently used first.
pub fn hosts_with_fingerprint(conn: &Connection, fingerprint: &str) -> Vec<String> {
    let mut stmt = conn
        .prepare("SELECT host FROM hosts WHERE fingerprint = ?1 ORDER BY last_used DESC")
        .unwrap();
    let rows = stmt.query_map([fingerprint], |row| row.get::<_, String>(0)).unwrap();
    rows.filter_map(|row| row.ok()).collect()
}

/// Remove trust for one address ("forget this host").
///
/// Credentials are shared by every address with the same certificate key, so
/// they are only dropped once the last such address is forgotten.
pub fn forget_host(conn: &Connection, host: &str) -> Result<ForgetOutcome, rusqlite::Error> {
    let fingerprint = get_fingerprint(conn, host);
    conn.execute("DELETE FROM hosts WHERE host = ?1", [host])?;

    let Some(fingerprint) = fingerprint else {
        return Ok(ForgetOutcome::default());
    };

    let siblings = hosts_with_fingerprint(conn, &fingerprint);
    if siblings.is_empty() {
        conn.execute(
            "DELETE FROM credentials WHERE fingerprint = ?1",
            [&fingerprint],
        )?;
        return Ok(ForgetOutcome {
            siblings,
            credentials_removed: true,
        });
    }

    Ok(ForgetOutcome {
        siblings,
        credentials_removed: false,
    })
}

/// List all remembered hosts with their fingerprints.
pub fn get_all_hosts(conn: &Connection) -> Vec<(String, Option<String>)> {
    let mut stmt = conn
        .prepare("SELECT host, fingerprint FROM hosts ORDER BY last_used DESC")
        .unwrap();
    let rows = stmt.query_map([], |row| {
        Ok((row.get(0)?, row.get(1)?))
    })
    .unwrap();
    rows.filter_map(|r| r.ok()).collect()
}