package com.clanstop.healing.persistence;

import com.clanstop.healing.config.FrameworkConfig;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Lazily-initialised HikariCP pool plus an idempotent schema bootstrap.
 *
 * <p>All persistence is best-effort: if the database is unreachable, {@link #available()}
 * returns {@code false} and callers skip logging rather than failing the test run.
 */
public final class Db {

    private static final Logger log = LoggerFactory.getLogger(Db.class);

    private static volatile HikariDataSource ds;
    private static volatile boolean initTried = false;
    private static volatile boolean available = false;

    private Db() {}

    /** @return true if a usable pool exists (schema bootstrapped). */
    public static synchronized boolean available() {
        if (!initTried) {
            initTried = true;
            init();
        }
        return available;
    }

    private static void init() {
        FrameworkConfig cfg = FrameworkConfig.get();
        try {
            connectAndBootstrap(cfg);
        } catch (Exception e) {
            // The tables are auto-created, but the database itself can't be created by the
            // connection that needs it. If it's simply missing, create it once and retry so
            // the only prerequisite is a running Postgres (the DB user needs CREATEDB rights).
            if (isMissingDatabase(e) && tryCreateDatabase(cfg)) {
                try {
                    connectAndBootstrap(cfg);
                    return;
                } catch (Exception retry) {
                    available = false;
                    log.warn("Healing persistence disabled after auto-creating the database: {}", retry.getMessage());
                    return;
                }
            }
            available = false;
            log.warn("Healing persistence disabled (DB unavailable): {}", e.getMessage());
        }
    }

    /** Open the pool against the configured database and create the tables (idempotent). */
    private static void connectAndBootstrap(FrameworkConfig cfg) throws Exception {
        HikariConfig hc = new HikariConfig();
        hc.setJdbcUrl(cfg.dbUrl());
        hc.setUsername(cfg.dbUser());
        hc.setPassword(cfg.dbPassword());
        hc.setMaximumPoolSize(4);
        hc.setConnectionTimeout(3000);   // fail fast if DB is down
        hc.setPoolName("healing-pool");
        ds = new HikariDataSource(hc);
        bootstrapSchema();
        available = true;
        log.info("Healing persistence connected: {}", cfg.dbUrl());
    }

    /** True if any cause in the chain is Postgres SQLSTATE 3D000 (database does not exist). */
    private static boolean isMissingDatabase(Throwable e) {
        for (Throwable t = e; t != null; t = t.getCause()) {
            if (t instanceof SQLException sql && "3D000".equals(sql.getSQLState())) {
                return true;
            }
        }
        return false;
    }

    /**
     * Connect to the server's maintenance database ("postgres") using the same host/port/creds
     * and {@code CREATE DATABASE <name>}. Returns true if the database now exists.
     */
    private static boolean tryCreateDatabase(FrameworkConfig cfg) {
        String url = cfg.dbUrl();
        int lastSlash = url.lastIndexOf('/');
        if (lastSlash < 0 || lastSlash + 1 >= url.length()) {
            return false;
        }
        String after = url.substring(lastSlash + 1);
        int q = after.indexOf('?');
        String dbName = (q >= 0) ? after.substring(0, q) : after;
        String params = (q >= 0) ? after.substring(q) : "";
        // Identifiers can't be bound as parameters, so only auto-create a plain, safe name.
        if (!dbName.matches("[A-Za-z0-9_]+")) {
            log.warn("Not auto-creating database with an unusual name: {}", dbName);
            return false;
        }
        String adminUrl = url.substring(0, lastSlash + 1) + "postgres" + params;
        try (Connection admin = DriverManager.getConnection(adminUrl, cfg.dbUser(), cfg.dbPassword());
             Statement st = admin.createStatement()) {
            st.execute("CREATE DATABASE \"" + dbName + "\"");   // autocommit; not in a txn block
            log.info("Auto-created database '{}'.", dbName);
            return true;
        } catch (SQLException sql) {
            if ("42P04".equals(sql.getSQLState())) {   // duplicate_database: a parallel run beat us to it
                return true;
            }
            log.warn("Could not auto-create database '{}' (does the user have CREATEDB rights?): {}", dbName, sql.getMessage());
            return false;
        } catch (Exception e) {
            log.warn("Could not auto-create database '{}': {}", dbName, e.getMessage());
            return false;
        }
    }

    private static void bootstrapSchema() throws Exception {
        String sql;
        try (InputStream in = Thread.currentThread().getContextClassLoader()
                .getResourceAsStream("db/migration/V1__init.sql")) {
            if (in == null) {
                throw new IllegalStateException("db/migration/V1__init.sql not found on classpath");
            }
            sql = new String(in.readAllBytes(), StandardCharsets.UTF_8);
        }
        try (Connection c = ds.getConnection(); Statement st = c.createStatement()) {
            for (String stmt : sql.split(";")) {
                String trimmed = stmt.strip();
                if (!trimmed.isEmpty()) {
                    st.execute(trimmed);
                }
            }
        }
    }

    public static Connection connection() throws java.sql.SQLException {
        return ds.getConnection();
    }

    public static synchronized void close() {
        if (ds != null) {
            ds.close();
            ds = null;
            available = false;
            initTried = false;
        }
    }
}
