SQLiteFormatter.hpp

// SPDX-License-Identifier: Apache-2.0
#pragma once
 
#include "../SqlQueryFormatter.hpp"
 
#include <reflection-cpp/reflection.hpp>
 
#include <format>
 
namespace Lightweight
{
 
class SQLiteQueryFormatter: public SqlQueryFormatter
{
  protected:
    /// Formats a table name for use in a FROM clause.
    /// If the table name is already quoted (starts with " or [), returns it as-is.
    /// Otherwise, wraps it in double quotes.
    [[nodiscard]] static std::string FormatFromTable(std::string_view table)
    {
        if (!table.empty() && (table.front() == '"' || table.front() == '['))
            return std::string(table); // Already quoted/qualified
        return std::format(R"("{}")", table);
    }
 
  public:
    [[nodiscard]] bool RequiresTableRebuildForForeignKeyChange() const noexcept override
    {
        return true;
    }
 
    /// Builds the SQL query used to check whether a column exists on a SQLite table.
    ///
    /// The migration executor uses this to resolve the `-- LIGHTWEIGHT_SQLITE_GUARD:`
    /// sentinels emitted by @ref AlterTable for `AddColumnIfNotExists` / `DropColumnIfExists`.
    /// Keeping the pragma SQL here ensures the sentinel-emitting side and the
    /// runtime-presence-check side share a single source of truth.
    ///
    /// @param tableName Name of the table to inspect.
    /// @param columnName Name of the column whose existence to check.
    /// @return SQL string returning a single integer column: non-zero iff the column exists.
    [[nodiscard]] static std::string BuildColumnExistsQuery(std::string_view tableName, std::string_view columnName)
    {
        return std::format(R"(SELECT COUNT(*) FROM pragma_table_info('{}') WHERE name = '{}';)", tableName, columnName);
    }
 
    [[nodiscard]] std::string Insert(std::string_view intoTable,
                                     std::string_view fields,
                                     std::string_view values) const override
    {
        return std::format(R"(INSERT INTO "{}" ({}) VALUES ({}))", intoTable, fields, values);
    }
 
    [[nodiscard]] std::string Insert(std::string_view /*schema*/,
                                     std::string_view intoTable,
                                     std::string_view fields,
                                     std::string_view values) const override
    {
        // SQLite doesn't support schemas - ignore schema parameter
        return std::format(R"(INSERT INTO "{}" ({}) VALUES ({}))", intoTable, fields, values);
    }
 
    [[nodiscard]] std::string QueryLastInsertId(std::string_view /*tableName*/) const override
    {
        // This is SQLite syntax. We might want to provide aspecialized SQLite class instead.
        return "SELECT LAST_INSERT_ROWID()";
    }
 
    [[nodiscard]] std::string_view BooleanLiteral(bool literalValue) const noexcept override
    {
        return literalValue ? "TRUE" : "FALSE";
    }
 
    [[nodiscard]] std::string_view DateFunction() const noexcept override
    {
        return "date()";
    }
 
    [[nodiscard]] std::string StringLiteral(std::string_view value) const noexcept override
    {
        if (value.empty())
            return "''";
 
        std::string escaped;
        escaped.reserve(value.size() + 2);
        escaped += '\'';
        for (char const c: value)
        {
            if (c == '\'')
                escaped += "''";
            else
                escaped += c;
        }
        escaped += '\'';
        return escaped;
    }
 
    [[nodiscard]] std::string StringLiteral(char value) const noexcept override
    {
        if (value == '\'')
            return "''''";
        return std::format("'{}'", value);
    }
 
    [[nodiscard]] std::string BinaryLiteral(std::span<uint8_t const> data) const override
    {
        std::string result;
        result.reserve((data.size() * 2) + 3);
        result += "X'";
        for (uint8_t byte: data)
            result += std::format("{:02X}", byte);
        result += "'";
        return result;
    }
 
    [[nodiscard]] std::string QualifiedTableName(std::string_view schema, std::string_view table) const override
    {
        // SQLite doesn't use schemas in the same way - just return the quoted table name
        if (schema.empty())
            return std::format(R"("{}")", table);
        // For SQLite attached databases, use database.table syntax
        return std::format(R"("{}"."{}")", schema, table);
    }
 
    [[nodiscard]] std::string SelectCount(bool distinct,
                                          std::string_view fromTable,
                                          std::string_view fromTableAlias,
                                          std::string_view tableJoins,
                                          std::string_view whereCondition) const override
    {
        auto const formattedTable = FormatFromTable(fromTable);
        if (fromTableAlias.empty())
            return std::format(
                "SELECT{} COUNT(*) FROM {}{}{}", distinct ? " DISTINCT" : "", formattedTable, tableJoins, whereCondition);
        else
            return std::format(R"(SELECT{} COUNT(*) FROM {} AS "{}"{}{})",
                               distinct ? " DISTINCT" : "",
                               formattedTable,
                               fromTableAlias,
                               tableJoins,
                               whereCondition);
    }
 
    [[nodiscard]] std::string SelectAll(bool distinct,
                                        // NOLINTNEXTLINE(bugprone-easily-swappable-parameters)
                                        std::string_view fields,
                                        std::string_view fromTable,
                                        std::string_view fromTableAlias,
                                        std::string_view tableJoins,
                                        std::string_view whereCondition,
                                        std::string_view orderBy,
                                        std::string_view groupBy) const override
    {
        std::stringstream sqlQueryString;
        sqlQueryString << "SELECT ";
        if (distinct)
            sqlQueryString << "DISTINCT ";
        sqlQueryString << fields;
        sqlQueryString << " FROM " << FormatFromTable(fromTable);
        if (!fromTableAlias.empty())
            sqlQueryString << " AS \"" << fromTableAlias << '"';
        sqlQueryString << tableJoins;
        sqlQueryString << whereCondition;
        sqlQueryString << groupBy;
        sqlQueryString << orderBy;
 
        return sqlQueryString.str();
    }
 
    [[nodiscard]] std::string SelectFirst(bool distinct,
                                          // NOLINTNEXTLINE(bugprone-easily-swappable-parameters)
                                          std::string_view fields,
                                          std::string_view fromTable,
                                          std::string_view fromTableAlias,
                                          std::string_view tableJoins,
                                          std::string_view whereCondition,
                                          std::string_view orderBy,
                                          size_t count) const override
    {
        std::stringstream sqlQueryString;
        sqlQueryString << "SELECT " << fields;
        if (distinct)
            sqlQueryString << " DISTINCT";
        sqlQueryString << " FROM " << FormatFromTable(fromTable);
        if (!fromTableAlias.empty())
            sqlQueryString << " AS \"" << fromTableAlias << "\"";
        sqlQueryString << tableJoins;
        sqlQueryString << whereCondition;
        sqlQueryString << orderBy;
        sqlQueryString << " LIMIT " << count;
        return sqlQueryString.str();
    }
 
    [[nodiscard]] std::string SelectRange(bool distinct,
                                          // NOLINTNEXTLINE(bugprone-easily-swappable-parameters)
                                          std::string_view fields,
                                          std::string_view fromTable,
                                          std::string_view fromTableAlias,
                                          std::string_view tableJoins,
                                          std::string_view whereCondition,
                                          std::string_view orderBy,
                                          std::string_view groupBy,
                                          std::size_t offset,
                                          std::size_t limit) const override
    {
        std::stringstream sqlQueryString;
        sqlQueryString << "SELECT " << fields;
        if (distinct)
            sqlQueryString << " DISTINCT";
        sqlQueryString << " FROM " << FormatFromTable(fromTable);
        if (!fromTableAlias.empty())
            sqlQueryString << " AS \"" << fromTableAlias << "\"";
        sqlQueryString << tableJoins;
        sqlQueryString << whereCondition;
        sqlQueryString << groupBy;
        sqlQueryString << orderBy;
        sqlQueryString << " LIMIT " << limit << " OFFSET " << offset;
        return sqlQueryString.str();
    }
 
    [[nodiscard]] std::string Update(std::string_view table,
                                     std::string_view tableAlias,
                                     std::string_view setFields,
                                     std::string_view whereCondition) const override
    {
        auto const formattedTable = FormatFromTable(table);
        if (tableAlias.empty())
            return std::format("UPDATE {} SET {}{}", formattedTable, setFields, whereCondition);
        else
            return std::format(R"(UPDATE {} AS "{}" SET {}{})", formattedTable, tableAlias, setFields, whereCondition);
    }
 
    [[nodiscard]] std::string Delete(std::string_view fromTable,
                                     std::string_view fromTableAlias,
                                     std::string_view tableJoins,
                                     std::string_view whereCondition) const override
    {
        auto const formattedTable = FormatFromTable(fromTable);
        if (fromTableAlias.empty())
            return std::format("DELETE FROM {}{}{}", formattedTable, tableJoins, whereCondition);
        else
            return std::format(R"(DELETE FROM {} AS "{}"{}{})", formattedTable, fromTableAlias, tableJoins, whereCondition);
    }
 
    [[nodiscard]] virtual std::string BuildColumnDefinition(SqlColumnDeclaration const& column) const
    {
        std::stringstream sqlQueryString;
 
        sqlQueryString << '"' << column.name << "\" ";
 
        if (column.primaryKey != SqlPrimaryKeyType::AUTO_INCREMENT)
            sqlQueryString << ColumnType(column.type);
        else
            sqlQueryString << ColumnType(SqlColumnTypeDefinitions::Integer {});
 
        if (column.required)
            sqlQueryString << " NOT NULL";
 
        if (column.primaryKey == SqlPrimaryKeyType::AUTO_INCREMENT)
            sqlQueryString << " PRIMARY KEY AUTOINCREMENT";
        else if (column.primaryKey == SqlPrimaryKeyType::NONE && !column.index && column.unique)
            sqlQueryString << " UNIQUE";
 
        if (!column.defaultValue.empty())
            sqlQueryString << " DEFAULT " << column.defaultValue;
 
        return sqlQueryString.str();
    }
 
    [[nodiscard]] static std::string BuildForeignKeyConstraint(std::string_view tableName,
                                                               std::string_view columnName,
                                                               SqlForeignKeyReferenceDefinition const& referencedColumn)
    {
        // Double-quote the constraint name so PostgreSQL preserves its case (otherwise
        // PG would fold to lowercase, breaking the matching `DROP CONSTRAINT "FK_<…>"`
        // emitted by `DropForeignKey`).
        return std::format(R"(CONSTRAINT "{}" FOREIGN KEY ("{}") REFERENCES "{}"("{}"))",
                           BuildForeignKeyConstraintName(tableName, std::array { columnName }),
                           columnName,
                           referencedColumn.tableName,
                           referencedColumn.columnName);
    }
 
    // NOLINTNEXTLINE(readability-function-cognitive-complexity)
    [[nodiscard]] StringList CreateTable(std::string_view schema,
                                         std::string_view tableName,
                                         std::vector<SqlColumnDeclaration> const& columns,
                                         std::vector<SqlCompositeForeignKeyConstraint> const& foreignKeys,
                                         bool ifNotExists = false) const override
    {
        auto sqlQueries = StringList {};
 
        // NOLINTNEXTLINE(readability-function-cognitive-complexity)
        sqlQueries.emplace_back([&]() {
            std::stringstream sqlQueryString;
            sqlQueryString << "CREATE TABLE ";
            if (ifNotExists)
                sqlQueryString << "IF NOT EXISTS ";
            // SQLite doesn't support schemas - ignore schema parameter
            (void) schema;
            sqlQueryString << "\"" << tableName << "\" (";
            std::vector<SqlColumnDeclaration const*> pks;
            size_t currentColumn = 0;
            std::string foreignKeyConstraints;
            for (SqlColumnDeclaration const& column: columns)
            {
                if (currentColumn > 0)
                    sqlQueryString << ",";
                ++currentColumn;
                sqlQueryString << "\n    ";
                sqlQueryString << BuildColumnDefinition(column);
 
                if (column.primaryKey != SqlPrimaryKeyType::NONE)
                    pks.push_back(&column);
 
                if (column.foreignKey)
                {
                    foreignKeyConstraints += ",\n    ";
                    foreignKeyConstraints += BuildForeignKeyConstraint(tableName, column.name, *column.foreignKey);
                }
            }
 
            for (SqlCompositeForeignKeyConstraint const& fk: foreignKeys)
            {
                // Emit a deterministic CONSTRAINT name (`FK_<table>_<col1>_<col2>...`) so that
                // a later `DropForeignKey` / SQLite-rebuild can locate the constraint by name
                // instead of relying on the backend's auto-generated identifier (PostgreSQL
                // would otherwise pick `<table>_<col>_fkey`).
                foreignKeyConstraints += ",\n    CONSTRAINT \"";
                foreignKeyConstraints += BuildForeignKeyConstraintName(tableName, fk.columns);
                foreignKeyConstraints += "\" FOREIGN KEY (";
                for (size_t i = 0; i < fk.columns.size(); ++i)
                {
                    if (i > 0)
                        foreignKeyConstraints += ", ";
                    foreignKeyConstraints += '"' + fk.columns[i] + '"';
                }
                foreignKeyConstraints += ") REFERENCES \"";
                foreignKeyConstraints += fk.referencedTableName;
                foreignKeyConstraints += "\" (";
                for (size_t i = 0; i < fk.referencedColumns.size(); ++i)
                {
                    if (i > 0)
                        foreignKeyConstraints += ", ";
                    foreignKeyConstraints += '"' + fk.referencedColumns[i] + '"';
                }
                foreignKeyConstraints += ")";
            }
 
            // Filter out AUTO_INCREMENT from table-level PK constraint if it's the ONLY PK,
            // because SQLite handles it inline. But for composite keys involving auto-inc (if that's even valid/used),
            // or if we just want to be explicit.
            // SQLite restriction: "INTEGER PRIMARY KEY" must be on the column definition for auto-increment.
            // If we have AUTO_INCREMENT, it's already in BuildColumnDefinition.
 
            std::erase_if(
                pks, [](SqlColumnDeclaration const* col) { return col->primaryKey == SqlPrimaryKeyType::AUTO_INCREMENT; });
 
            if (!pks.empty())
            {
                std::ranges::sort(pks, [](SqlColumnDeclaration const* a, SqlColumnDeclaration const* b) {
                    // If both have index, use it. If one has 0 (no index), treat it as "after" indexed ones?
                    // Or just rely on stable sort?
                    // Let's assume indices are properly set for composite keys.
                    // 1-based index vs 0. 0 means "unordered".
                    if (a->primaryKeyIndex != 0 && b->primaryKeyIndex != 0)
                        return a->primaryKeyIndex < b->primaryKeyIndex;
                    if (a->primaryKeyIndex != 0)
                        return true; // a comes first
                    if (b->primaryKeyIndex != 0)
                        return false; // b comes first
                    return false;     // keep original order
                });
 
                sqlQueryString << ",\n    PRIMARY KEY (";
                for (size_t i = 0; i < pks.size(); ++i)
                {
                    if (i > 0)
                        sqlQueryString << ", ";
                    sqlQueryString << '"' << pks[i]->name << '"';
                }
                sqlQueryString << ")";
            }
 
            sqlQueryString << foreignKeyConstraints;
 
            sqlQueryString << "\n);";
            return sqlQueryString.str();
        }());
 
        for (SqlColumnDeclaration const& column: columns)
        {
            if (column.index && column.primaryKey == SqlPrimaryKeyType::NONE)
            {
                // primary keys are always indexed
                if (column.unique)
                    sqlQueries.emplace_back(std::format(R"(CREATE UNIQUE INDEX "{}_{}_index" ON "{}" ("{}");)",
                                                        tableName,
                                                        column.name,
                                                        tableName,
                                                        column.name));
                else
                    sqlQueries.emplace_back(std::format(
                        R"(CREATE INDEX "{}_{}_index" ON "{}" ("{}");)", tableName, column.name, tableName, column.name));
            }
        }
 
        return sqlQueries;
    }
 
    [[nodiscard]] StringList AlterTable(std::string_view /*schemaName*/,
                                        std::string_view tableName,
                                        std::vector<SqlAlterTableCommand> const& commands) const override
    {
        // SQLite doesn't support schemas - use table name only
        auto const formatTable = [tableName]() {
            return std::format(R"("{}")", tableName);
        };
 
        // SQLite has no native IF NOT EXISTS / IF EXISTS for columns. We emit each command
        // as its own result entry; guarded commands are prefixed with a sentinel comment
        // that the migration executor recognizes and uses to perform a pragma_table_info
        // presence check at runtime.
        StringList result;
 
        using namespace SqlAlterTableCommands;
        for (SqlAlterTableCommand const& command: commands)
        {
            std::string sql = std::visit(
                detail::overloaded {
                    [&formatTable](RenameTable const& actualCommand) -> std::string {
                        return std::format(R"(ALTER TABLE {} RENAME TO "{}";)", formatTable(), actualCommand.newTableName);
                    },
                    [&formatTable, this](AddColumn const& actualCommand) -> std::string {
                        return std::format(R"(ALTER TABLE {} ADD COLUMN "{}" {} {};)",
                                           formatTable(),
                                           actualCommand.columnName,
                                           ColumnType(actualCommand.columnType),
                                           actualCommand.nullable == SqlNullable::NotNull ? "NOT NULL" : "NULL");
                    },
                    [&formatTable, this](AlterColumn const& actualCommand) -> std::string {
                        return std::format(R"(ALTER TABLE {} ALTER COLUMN "{}" {} {};)",
                                           formatTable(),
                                           actualCommand.columnName,
                                           ColumnType(actualCommand.columnType),
                                           actualCommand.nullable == SqlNullable::NotNull ? "NOT NULL" : "NULL");
                    },
                    [&formatTable](RenameColumn const& actualCommand) -> std::string {
                        return std::format(R"(ALTER TABLE {} RENAME COLUMN "{}" TO "{}";)",
                                           formatTable(),
                                           actualCommand.oldColumnName,
                                           actualCommand.newColumnName);
                    },
                    [&formatTable](DropColumn const& actualCommand) -> std::string {
                        return std::format(R"(ALTER TABLE {} DROP COLUMN "{}";)", formatTable(), actualCommand.columnName);
                    },
                    [tableName](AddIndex const& actualCommand) -> std::string {
                        using namespace std::string_view_literals;
                        auto const uniqueStr = actualCommand.unique ? "UNIQUE "sv : ""sv;
                        return std::format(R"(CREATE {2}INDEX "{0}_{1}_index" ON "{0}" ("{1}");)",
                                           tableName,
                                           actualCommand.columnName,
                                           uniqueStr);
                    },
                    [tableName](DropIndex const& actualCommand) -> std::string {
                        return std::format(R"(DROP INDEX "{0}_{1}_index";)", tableName, actualCommand.columnName);
                    },
                    [tableName](AddForeignKey const& actualCommand) -> std::string {
                        // SQLite cannot `ALTER TABLE … ADD CONSTRAINT`. The runtime executor
                        // recognizes this sentinel and rebuilds the table from sqlite_schema.
                        // All metadata the executor needs fits in the sentinel itself. We keep
                        // a commented-out equivalent MSSQL-style ALTER below so dry-run output
                        // stays readable.
                        return std::format(
                            R"(-- LIGHTWEIGHT_SQLITE_GUARD: ADD_FOREIGN_KEY "{0}" "{1}" "{2}" "{3}"
-- ALTER TABLE "{0}" ADD {4};)",
                            tableName,
                            actualCommand.columnName,
                            actualCommand.referencedColumn.tableName,
                            actualCommand.referencedColumn.columnName,
                            BuildForeignKeyConstraint(tableName, actualCommand.columnName, actualCommand.referencedColumn));
                    },
                    [tableName](DropForeignKey const& actualCommand) -> std::string {
                        // SQLite has no `DROP CONSTRAINT`. Executor rebuilds the table without
                        // the matching `CONSTRAINT FK_<tbl>_<col>` clause.
                        return std::format(
                            R"(-- LIGHTWEIGHT_SQLITE_GUARD: DROP_FOREIGN_KEY "{0}" "{1}"
-- ALTER TABLE "{0}" DROP CONSTRAINT "{2}";)",
                            tableName,
                            actualCommand.columnName,
                            BuildForeignKeyConstraintName(tableName,
                                                          std::array { std::string_view { actualCommand.columnName } }));
                    },
                    [tableName](AddCompositeForeignKey const& /*actualCommand*/) -> std::string {
                        // SQLite limitation: ALTER TABLE ADD CONSTRAINT not supported.
                        // We return empty string or comment to satisfy visitor.
                        return std::format(R"(-- AddCompositeForeignKey not supported for {};)", tableName);
                    },
                    [&formatTable, tableName, this](AddColumnIfNotExists const& actualCommand) -> std::string {
                        // SQLite doesn't support IF NOT EXISTS for ADD COLUMN.
                        // Emit a sentinel comment so the migration executor can presence-check
                        // via pragma_table_info() before running the ALTER TABLE.
                        return std::format(
                            R"(-- LIGHTWEIGHT_SQLITE_GUARD: ADD_COLUMN_IF_NOT_EXISTS "{0}" "{1}"
ALTER TABLE {2} ADD COLUMN "{1}" {3} {4};)",
                            tableName,
                            actualCommand.columnName,
                            formatTable(),
                            ColumnType(actualCommand.columnType),
                            actualCommand.nullable == SqlNullable::NotNull ? "NOT NULL" : "NULL");
                    },
                    [&formatTable, tableName](DropColumnIfExists const& actualCommand) -> std::string {
                        // SQLite doesn't support IF EXISTS for DROP COLUMN; guarded like above.
                        return std::format(
                            R"(-- LIGHTWEIGHT_SQLITE_GUARD: DROP_COLUMN_IF_EXISTS "{0}" "{1}"
ALTER TABLE {2} DROP COLUMN "{1}";)",
                            tableName,
                            actualCommand.columnName,
                            formatTable());
                    },
                    [tableName](DropIndexIfExists const& actualCommand) -> std::string {
                        return std::format(R"(DROP INDEX IF EXISTS "{0}_{1}_index";)", tableName, actualCommand.columnName);
                    },
                },
                command);
 
            if (!sql.empty())
                result.push_back(std::move(sql));
        }
 
        return result;
    }
 
    [[nodiscard]] std::string ColumnType(SqlColumnTypeDefinition const& type) const override
    {
        using namespace SqlColumnTypeDefinitions;
        return std::visit(detail::overloaded {
                              [](Bigint const&) -> std::string { return "BIGINT"; },
                              [](Binary const&) -> std::string { return "BLOB"; },
                              [](Bool const&) -> std::string { return "BOOLEAN"; },
                              [](Char const& type) -> std::string { return std::format("CHAR({})", type.size); },
                              [](Date const&) -> std::string { return "DATE"; },
                              [](DateTime const&) -> std::string { return "DATETIME"; },
                              [](Decimal const& type) -> std::string {
                                  return std::format("DECIMAL({}, {})", type.precision, type.scale);
                              },
                              [](Guid const&) -> std::string { return "GUID"; },
                              [](Integer const&) -> std::string { return "INTEGER"; },
                              [](NChar const& type) -> std::string { return std::format("NCHAR({})", type.size); },
                              [](NVarchar const& type) -> std::string { return std::format("NVARCHAR({})", type.size); },
                              [](Real const&) -> std::string { return "REAL"; },
                              [](Smallint const&) -> std::string { return "SMALLINT"; },
                              [](Text const&) -> std::string { return "TEXT"; },
                              [](Time const&) -> std::string { return "TIME"; },
                              [](Timestamp const&) -> std::string { return "TIMESTAMP"; },
                              [](Tinyint const&) -> std::string { return "TINYINT"; },
                              [](VarBinary const& type) -> std::string { return std::format("VARBINARY({})", type.size); },
                              [](Varchar const& type) -> std::string { return std::format("VARCHAR({})", type.size); },
                          },
                          type);
    }
 
    [[nodiscard]] StringList DropTable(std::string_view /*schemaName*/,
                                       std::string_view const& tableName,
                                       bool ifExists = false,
                                       bool cascade = false) const override
    {
        // SQLite doesn't support CASCADE syntax, but if FK constraints are disabled
        // (PRAGMA foreign_keys = OFF), dropping works. The cascade flag is ignored.
        // SQLite doesn't support schemas - ignore schemaName parameter
        (void) cascade;
        if (ifExists)
            return { std::format(R"(DROP TABLE IF EXISTS "{}";)", tableName) };
        else
            return { std::format(R"(DROP TABLE "{}";)", tableName) };
    }
 
    [[nodiscard]] std::string QueryServerVersion() const override
    {
        return "SELECT sqlite_version()";
    }
};
 
} // namespace Lightweight