// A SQL Server database from a program: a table made, rows written with parameters, one row of each
// column type read back, two result sets, a transfer as one transaction, and what the server's
// errors say.
//
//   MSSQL_HOST=127.0.0.1 MSSQL_USER=sa MSSQL_PASSWORD=... MSSQL_DATABASE=shop quillo run mssql.qwe
//   then open http://localhost:8080/connect, /rows, /types, /many, /transaction, /held and /failures
//
// TLS is required by default, as Azure SQL requires it. On a bench whose server has a self-signed
// certificate, MSSQL_CA names it; MSSQL_ENCRYPT=none sends passwords in the clear, for a stand-in only.

use "mssql.qwer"

let VERSION = mssql_connect("main", { timeout_ms: 5000 })

route GET /connect {
    VERSION?
    { version: starts_with(VERSION, "Microsoft SQL Server"), again: mssql_one("main", "SELECT @P1 + 1 AS n", [41])?.n }
}

route GET /rows {
    mssql_batch("main", "CREATE TABLE accounts (id INT PRIMARY KEY, name NVARCHAR(50) NOT NULL UNIQUE, balance FLOAT, note NVARCHAR(100), photo VARBINARY(100)); INSERT INTO accounts (id, name, balance) VALUES (1, 'seed', 0)")?
    mssql_execute("main", "INSERT INTO accounts (id, name, balance, note, photo) VALUES (@P1, @P2, @P3, @P4, @P5)", [2, "Zoë", 100.5, null, { "$binary": "0xCAFE" }])?
    let more = mssql_execute("main", "INSERT INTO accounts (id, name, balance) VALUES (@P1, @P2, @P3)", [3, "bob", 20])?
    let moved = mssql_execute("main", "UPDATE accounts SET balance = balance + @P1 WHERE id > @P2", [1, 0])?
    {
        one: more.rows_affected,
        moved: moved.counts,
        row: mssql_one("main", "SELECT id, name, balance, note, photo FROM accounts WHERE name = @P1", ["Zoë"])?,
        none: mssql_one("main", "SELECT * FROM accounts WHERE id = @P1", [99])?,
        count: len(mssql_query("main", "SELECT id FROM accounts", null)?),
        long: len(mssql_one("main", "SELECT @P1 AS text", [repeat("long ", 3000)])?.text),
    }
}

route GET /types {
    VERSION?
    let rows = mssql_query("main", "SELECT * FROM every_type", null)?
    { row: rows[0], nulls: rows[1] }
}

route GET /many {
    let sets = mssql_query_many("main", "SELECT name FROM accounts WHERE id = 2; SELECT COUNT(*) AS n FROM accounts", null)?
    { first: sets[0], second: sets[1], big: len(mssql_query("main", "SELECT * FROM big_rows", null)?) }
}

route GET /transaction {
    let done = mssql_transaction("main", [
        { sql: "UPDATE accounts SET balance = balance - @P1 WHERE id = @P2", params: [10, 2] },
        { sql: "UPDATE accounts SET balance = balance + @P1 WHERE id = @P2", params: [10, 3] },
    ])?
    let failed = mssql_transaction("main", [
        { sql: "UPDATE accounts SET balance = 0 WHERE id = @P1", params: [2] },
        { sql: "INSERT INTO accounts (id, name) VALUES (@P1, @P2)", params: [2, "twin"] },
    ])
    { counts: done, error: failed.error, kept: mssql_one("main", "SELECT balance FROM accounts WHERE id = @P1", [2])?.balance }
}

route GET /held {
    mssql_begin("main")?
    mssql_execute("main", "UPDATE accounts SET note = @P1 WHERE id = @P2", ["draft", 3])?
    let inside = mssql_one("main", "SELECT note FROM accounts WHERE id = @P1", [3])?.note
    mssql_rollback("main")?
    let after = mssql_one("main", "SELECT note FROM accounts WHERE id = @P1", [3])?.note
    mssql_begin("main")?
    mssql_execute("main", "UPDATE accounts SET note = @P1 WHERE id = @P2", ["kept", 3])?
    mssql_commit("main")?
    { inside, after, committed: mssql_one("main", "SELECT note FROM accounts WHERE id = @P1", [3])?.note }
}

route GET /failures = {
    syntax: mssql_query("main", "SELEC 1", null).error,
    missing: mssql_query("main", "SELECT * FROM absent", null).error,
    duplicate: mssql_execute("main", "INSERT INTO accounts (id, name) VALUES (@P1, @P2)", [1, "x"]).error,
    bad_param: mssql_query("main", "SELECT 1", [[1, 2]]).error,
    not_list: mssql_query("main", "SELECT 1", "x").error,
    no_connection: mssql_query("nobody", "SELECT 1", null).error,
    bad_login: mssql_connect("wrong", { database: "denied" }).error,
    bad_level: mssql_connect("x", { encrypt: "maybe" }).error,
    still_works: mssql_one("main", "SELECT 1 AS one", null)?.one,
}