package com.clanstop.healing.persistence;

import com.clanstop.healing.locator.Fingerprint;
import com.clanstop.healing.locator.FingerprintJson;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

/**
 * Persists element fingerprints, locator history, and heal events. Every method is
 * best-effort: if the DB is unavailable or a write fails, it logs a warning and returns
 * a safe default rather than breaking the test.
 */
public final class ElementStore {

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

    /** Upsert the last-successful fingerprint for a logical element. */
    public void saveFingerprint(String logicalKey, Fingerprint fp, long runId) {
        if (!Db.available()) {
            return;
        }
        String sql = """
            INSERT INTO element_fingerprint (logical_key, tag, fingerprint, run_id, updated_at)
            VALUES (?, ?, ?::jsonb, ?, now())
            ON CONFLICT (logical_key) DO UPDATE
              SET tag = EXCLUDED.tag,
                  fingerprint = EXCLUDED.fingerprint,
                  run_id = EXCLUDED.run_id,
                  updated_at = now()
            """;
        try (Connection c = Db.connection(); PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setString(1, logicalKey);
            ps.setString(2, fp.tag());
            ps.setString(3, FingerprintJson.toJson(fp));
            // runId < 0 means "no run row" (e.g. healing driver used without the logging
            // plugin) — store NULL rather than violating the test_run foreign key.
            if (runId >= 0) {
                ps.setLong(4, runId);
            } else {
                ps.setNull(4, Types.BIGINT);
            }
            ps.executeUpdate();
        } catch (Exception e) {
            log.warn("saveFingerprint failed for {}: {}", logicalKey, e.getMessage());
        }
    }

    public Fingerprint loadFingerprint(String logicalKey) {
        if (!Db.available()) {
            return null;
        }
        String sql = "SELECT fingerprint FROM element_fingerprint WHERE logical_key = ?";
        try (Connection c = Db.connection(); PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setString(1, logicalKey);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return FingerprintJson.fromJson(rs.getString(1));
                }
            }
        } catch (Exception e) {
            log.warn("loadFingerprint failed for {}: {}", logicalKey, e.getMessage());
        }
        return null;
    }

    /** Record (or bump) a locator that successfully found this element. */
    public void recordLocator(String logicalKey, String byType, String byValue, String origin) {
        if (!Db.available()) {
            return;
        }
        String sql = """
            INSERT INTO locator_history (logical_key, by_type, by_value, origin)
            VALUES (?, ?, ?, ?)
            ON CONFLICT (logical_key, by_type, by_value) DO UPDATE
              SET success_count = locator_history.success_count + 1,
                  last_success = now()
            """;
        try (Connection c = Db.connection(); PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setString(1, logicalKey);
            ps.setString(2, byType);
            ps.setString(3, byValue);
            ps.setString(4, origin);
            ps.executeUpdate();
        } catch (Exception e) {
            log.warn("recordLocator failed for {}: {}", logicalKey, e.getMessage());
        }
    }

    /** Historically successful locators for this element, best-first. */
    public List<StoredLocator> history(String logicalKey) {
        List<StoredLocator> out = new ArrayList<>();
        if (!Db.available()) {
            return out;
        }
        String sql = """
            SELECT by_type, by_value, origin
            FROM locator_history
            WHERE logical_key = ?
            ORDER BY last_success DESC, success_count DESC
            """;
        try (Connection c = Db.connection(); PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setString(1, logicalKey);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    out.add(new StoredLocator(rs.getString(1), rs.getString(2), rs.getString(3)));
                }
            }
        } catch (Exception e) {
            log.warn("history load failed for {}: {}", logicalKey, e.getMessage());
        }
        return out;
    }

    public void recordHeal(Long stepResultId, String logicalKey, String failedBy,
                           String healedByType, String healedByValue, double score,
                           String strategy, String changes, String screenshotPath) {
        if (!Db.available()) {
            return;
        }
        String sql = """
            INSERT INTO heal_event
              (step_result_id, logical_key, failed_by, healed_by_type, healed_by_value,
               score, strategy, changes, screenshot_path)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            """;
        try (Connection c = Db.connection(); PreparedStatement ps = c.prepareStatement(sql)) {
            if (stepResultId != null) {
                ps.setLong(1, stepResultId);
            } else {
                ps.setNull(1, Types.BIGINT);
            }
            ps.setString(2, logicalKey);
            ps.setString(3, failedBy);
            ps.setString(4, healedByType);
            ps.setString(5, healedByValue);
            ps.setDouble(6, score);
            ps.setString(7, strategy);
            ps.setString(8, changes);
            if (screenshotPath != null) {
                ps.setString(9, screenshotPath);
            } else {
                ps.setNull(9, Types.VARCHAR);
            }
            ps.executeUpdate();
        } catch (Exception e) {
            log.warn("recordHeal failed for {}: {}", logicalKey, e.getMessage());
        }
    }
}
