using Invercargill;
using InvercargillSql;
using InvercargillSql.Dialects;

/**
 * Shared dual-backend test context for the ORM, migration and projection
 * suites.
 *
 * By default the context opens an in-memory SQLite database exactly like
 * the suites did before. When the PG_TEST_CONN environment variable is
 * set to a reachable PostgreSQL connection string, the context instead
 * opens PostgreSQL and isolates each call in a freshly created schema:
 *
 *   - the schema is dropped (CASCADE) if it exists, then created;
 *   - search_path is pointed at it for the connection's lifetime.
 *
 * Schemas are named with a monotonically increasing counter, so repeated
 * setup calls within one process never see each other's tables. The
 * PostgresDialect introspection already resolves information_schema rows
 * for the current schema only, keeping ORM schema discovery correct.
 */
public class TestBackendContext : Object {
    private static int _schema_counter = 0;

    /** Connection string actually used for this context. */
    public string connection_string { get; private set; }

    /** Open connection (SQLite in-memory, or PostgreSQL in its schema). */
    public Connection connection { get; private set; }

    /** Dialect matching the backend. */
    public SqlDialect dialect { get; private set; }

    /** Backend identifier: "sqlite" or "postgres". */
    public string backend { get; private set; }

    /** Name of the isolating PostgreSQL schema, or null for SQLite. */
    public string? schema_name { get; private set; }

    public TestBackendContext() throws SqlError {
        string? pg = GLib.Environment.get_variable("PG_TEST_CONN");
        if (pg != null && pg.length > 0) {
            backend = "postgres";
            connection_string = pg;
            connection = ConnectionFactory.create_and_open(pg);
            dialect = new PostgresDialect();
            _schema_counter++;
            schema_name = "t_%d".printf(_schema_counter);
            connection.execute("DROP SCHEMA IF EXISTS %s CASCADE".printf(schema_name));
            connection.execute("CREATE SCHEMA %s".printf(schema_name));
            connection.execute("SET search_path TO %s".printf(schema_name));
        } else {
            backend = "sqlite";
            connection_string = "sqlite::memory:";
            connection = ConnectionFactory.create_and_open("sqlite::memory:");
            dialect = new SqliteDialect();
            schema_name = null;
        }
    }

    /**
     * True when running against PostgreSQL. Tests use this to skip
     * backend-specific assertions (e.g. exact SQL text comparisons).
     */
    public bool is_postgres() {
        return backend == "postgres";
    }

    /**
     * Executes a CREATE TABLE statement in a backend-portable way.
     *
     * The DDL is written in the suites' existing SQLite-flavoured style
     * (INTEGER PRIMARY KEY AUTOINCREMENT, TEXT, REAL, INTEGER, BOOLEAN,
     * BLOB). On PostgreSQL the text is translated to equivalent native
     * DDL: identity columns, BIGINT/DOUBLE PRECISION/BYTEA types and the
     * same column names, so entity mappers introspect identically.
     */
    public void create_table(string sqlite_ddl, string[] boolean_columns = {},
            string[] datetime_columns = {}) throws SqlError {
        if (backend == "sqlite") {
            connection.execute(sqlite_ddl);
            return;
        }
        connection.execute(translate_ddl(sqlite_ddl, boolean_columns, datetime_columns));
    }

    /**
     * Backend-portable table existence check used where the suites
     * previously queried sqlite_master directly.
     */
    public bool table_exists(string table_name) throws SqlError {
        if (backend == "sqlite") {
            var cmd = connection.create_command(
                "SELECT name FROM sqlite_master WHERE type='table' AND name = :name"
            );
            cmd.with_parameter<string>("name", table_name);
            return cmd.execute_query().any();
        }
        var cmd = connection.create_command(
            "SELECT table_name FROM information_schema.tables WHERE table_name = :name AND table_schema = :schema"
        );
        cmd.with_parameter<string>("name", table_name);
        cmd.with_parameter<string>("schema", schema_name);
        return cmd.execute_query().any();
    }

    /**
     * Drops a table if it exists (portable DROP ... IF EXISTS).
     */
    public void drop_table(string table_name) throws SqlError {
        connection.execute("DROP TABLE IF EXISTS %s".printf(table_name));
    }

    /**
     * Translates SQLite-flavoured CREATE TABLE DDL to PostgreSQL.
     *
     * Test-only helper: production code goes through MigrationRunner and
     * the dialect's create_table_sql. Recognises the shapes used by the
     * test suites (one column per line, closing paren on its own line).
     */
    private string translate_ddl(string ddl, string[] boolean_columns = {},
            string[] datetime_columns = {}) {
        var sb = new StringBuilder();
        string[] lines = ddl.split("\n");
        var columns = new string[0];
        string header = "";
        foreach (string raw_line in lines) {
            string line = raw_line.strip();
            if (line.length == 0) {
                continue;
            }
            if (line.has_prefix("--")) {
                continue;
            }
            string lower = line.down();
            if (lower.has_prefix("create table")) {
                // The header may already open the column list, e.g.
                // "CREATE TABLE users ("; strip the trailing "(" so the
                // generator below can add its own.
                if (line.has_suffix("(")) {
                    header = line.substring(0, line.length - 1).strip();
                } else {
                    header = line;
                }
                continue;
            }
            if (line.has_prefix(")")) {
                continue;
            }
            if (line.has_suffix(",")) {
                line = line.substring(0, line.length - 1).strip();
            }
            columns += translate_column_line(line, boolean_columns, datetime_columns);
        }
        sb.append(header);
        sb.append(" (\n");
        for (int i = 0; i < columns.length; i++) {
            sb.append("    ");
            sb.append(columns[i]);
            if (i < columns.length - 1) {
                sb.append(",");
            }
            sb.append("\n");
        }
        sb.append(")");
        return sb.str;
    }

    private string translate_column_line(string line, string[] boolean_columns = {},
            string[] datetime_columns = {}) {
        string upper = line.up();
        if ("AUTOINCREMENT" in upper) {
            string col_name = line.split(" ")[0];
            return "%s BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY".printf(col_name);
        }
        // SQLite stores booleans as INTEGER 0/1; the ORM binds bools as
        // PostgresBoolElement ("t"/"f"), which a BIGINT column rejects.
        // Columns named by the caller become native BOOLEAN instead, and
        // PostgresBoolElement round-trips through them.
        string col_name = line.split(" ")[0];
        foreach (string boolean_column in boolean_columns) {
            if (col_name == boolean_column) {
                string rest = line.substring(col_name.length).strip();
                rest = replace_type(rest, "INTEGER", "BOOLEAN");
                return "%s %s".printf(col_name, rest);
            }
        }
        // SQLite has no native date/time type, so the suites store
        // DateTime as INTEGER (Unix epoch). PostgreSQL does have one, so
        // hinted columns become TIMESTAMPTZ and DateTime binds as ISO 8601
        // text, which the adapter parses back to DateTime on read.
        foreach (string datetime_column in datetime_columns) {
            if (col_name == datetime_column) {
                string rest = line.substring(col_name.length).strip();
                rest = replace_type(rest, "INTEGER", "TIMESTAMPTZ");
                return "%s %s".printf(col_name, rest);
            }
        }
        string result = line;
        result = replace_type(result, " INTEGER", " BIGINT");
        result = replace_type(result, " REAL", " DOUBLE PRECISION");
        result = replace_type(result, " BLOB", " BYTEA");
        return result;
    }

    private string replace_type(string line, string from, string to) {
        string result = line;
        string up = result.up();
        int idx = up.index_of(from.up());
        while (idx >= 0) {
            bool word_start = idx == 0 || !is_ident_char(up[idx - 1]);
            int end = idx + from.length;
            bool word_end = end >= up.length || !is_ident_char(up[end]);
            if (word_start && word_end) {
                result = result.substring(0, idx) + to + result.substring(end);
                up = result.up();
                idx = up.index_of(from.up(), idx + to.length);
            } else {
                idx = up.index_of(from.up(), idx + 1);
            }
        }
        return result;
    }

    private bool is_ident_char(char c) {
        return (c >= 'a' && c <= 'z') || (c >= 'A' && c <= 'Z')
            || (c >= '0' && c <= '9') || c == '_';
    }
}