-- Self-healing test framework schema.
-- Idempotent: every statement uses IF NOT EXISTS so the bootstrap can run on every start.

CREATE TABLE IF NOT EXISTS test_run (
    id          BIGSERIAL PRIMARY KEY,
    started_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    finished_at TIMESTAMPTZ,
    git_sha     TEXT,
    env         TEXT,
    status      TEXT NOT NULL DEFAULT 'RUNNING'   -- RUNNING / PASSED / FAILED
);

CREATE TABLE IF NOT EXISTS step_result (
    id            BIGSERIAL PRIMARY KEY,
    run_id        BIGINT NOT NULL REFERENCES test_run(id),
    feature       TEXT NOT NULL,
    scenario      TEXT NOT NULL,
    step_text     TEXT NOT NULL,
    step_ordinal  INT  NOT NULL,
    status         TEXT NOT NULL DEFAULT 'RUNNING', -- RUNNING / PASSED / FAILED / SKIPPED
    duration_ms    BIGINT,
    error_message  TEXT,
    screenshot_path TEXT,                            -- captured on failure
    started_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_step_result_run ON step_result(run_id);

-- One row per logical element (ByKey): the last-successful fingerprint as JSON.
CREATE TABLE IF NOT EXISTS element_fingerprint (
    logical_key TEXT PRIMARY KEY,
    tag         TEXT,
    fingerprint JSONB NOT NULL,
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    run_id      BIGINT REFERENCES test_run(id)
);

-- Every locator that has ever successfully found a logical element (including heals).
CREATE TABLE IF NOT EXISTS locator_history (
    id            BIGSERIAL PRIMARY KEY,
    logical_key   TEXT NOT NULL,
    by_type       TEXT NOT NULL,     -- id / css / xpath / name / ...
    by_value      TEXT NOT NULL,
    success_count INT  NOT NULL DEFAULT 1,
    last_success  TIMESTAMPTZ NOT NULL DEFAULT now(),
    origin        TEXT NOT NULL,     -- PRIMARY / HEAL
    UNIQUE (logical_key, by_type, by_value)
);
CREATE INDEX IF NOT EXISTS idx_loc_hist_key ON locator_history(logical_key, last_success DESC);

CREATE TABLE IF NOT EXISTS heal_event (
    id              BIGSERIAL PRIMARY KEY,
    step_result_id  BIGINT REFERENCES step_result(id),
    logical_key     TEXT NOT NULL,
    failed_by       TEXT NOT NULL,
    healed_by_type  TEXT NOT NULL,
    healed_by_value TEXT NOT NULL,
    score           NUMERIC(4,3) NOT NULL,
    strategy        TEXT NOT NULL,   -- HISTORY / FINGERPRINT / DOM_SCAN
    changes         TEXT,            -- human-readable diff of what changed on the element
    screenshot_path TEXT,            -- page at heal time, with the healed element highlighted
    healed_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_heal_key ON heal_event(logical_key);

-- Columns added after the initial release; ADD ... IF NOT EXISTS keeps this idempotent
-- and back-fills databases that were created before these columns existed.
ALTER TABLE step_result ADD COLUMN IF NOT EXISTS screenshot_path TEXT;
ALTER TABLE heal_event  ADD COLUMN IF NOT EXISTS changes TEXT;
ALTER TABLE heal_event  ADD COLUMN IF NOT EXISTS screenshot_path TEXT;
