-- Signály EMS → externí cíle (Loxone VI, HTTP REST), journal + idempotence + verify readback.
-- Kritické řízení výkonu (Deye, EV, TČ) zůstává v modbus_command / exporteru.

-- ------------------------------------------------------------
-- Definice signálů (globální katalog kódů)
-- ------------------------------------------------------------
CREATE TABLE ems.signal_def (
    code         TEXT PRIMARY KEY,
    value_type   TEXT NOT NULL,
    description  TEXT
);

COMMENT ON TABLE ems.signal_def IS
'Katalog signálů EMS (logické výstupy). Hodnotu pro route počítá backend dle doménové logiky.';

COMMENT ON COLUMN ems.signal_def.code IS
'Unikátní kód signálu, např. EXPORT_BAN_ACTIVE.';

COMMENT ON COLUMN ems.signal_def.value_type IS
'bool | int | float | string — očekávaný typ hodnoty po transformaci na cíl.';

INSERT INTO ems.signal_def (code, value_type, description)
VALUES (
    'EXPORT_BAN_ACTIVE',
    'bool',
    'Pravda pokud EMS aktuálně uplatňuje zákaz exportu do sítě (LED varianta B): override block_export, no_export, režimy bez exportu, AUTO se záporným výkupem při ne-negativním grid setpointu.'
)
ON CONFLICT (code) DO NOTHING;

-- ------------------------------------------------------------
-- Směrování signál → cíl (per site)
-- ------------------------------------------------------------
CREATE TABLE ems.signal_route (
    id                 SERIAL PRIMARY KEY,
    site_id            INT NOT NULL REFERENCES ems.site (id),
    destination_type   TEXT NOT NULL,
    endpoint_id        INT NOT NULL REFERENCES ems.site_endpoint (id),
    signal_code         TEXT NOT NULL REFERENCES ems.signal_def (code),
    destination_key     TEXT NOT NULL,
    route_config_json   JSONB,
    transform_json      JSONB,
    verify_readback     BOOLEAN NOT NULL DEFAULT true,
    verify_config_json  JSONB,
    enabled               BOOLEAN NOT NULL DEFAULT true,
    created_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT uq_signal_route_unique UNIQUE (site_id, destination_type, signal_code, destination_key)
);

CREATE INDEX idx_signal_route_site_enabled
    ON ems.signal_route (site_id, enabled)
    WHERE enabled = true;

COMMENT ON TABLE ems.signal_route IS
'Mapování signálu na cíl (Loxone Virtual Input, HTTP REST atd.). endpoint_id ukazuje na ems.site_endpoint (loxone_http, budoucí shelly_http, …).';

COMMENT ON COLUMN ems.signal_route.destination_type IS
'loxone_vi = GET /dev/sps/io/{destination_key}/{value}; http_rest = šablona v route_config_json.';

COMMENT ON COLUMN ems.signal_route.destination_key IS
'U Loxone název Virtual Inputu. U HTTP REST stabilní klíč pro log (např. relay0).';

COMMENT ON COLUMN ems.signal_route.route_config_json IS
'Volitelná konfigurace pro http_rest (path_template, method, …). U loxone_vi typicky NULL.';

COMMENT ON COLUMN ems.signal_route.verify_config_json IS
'Readback: u Loxone např. {"loxone_io_name":"EMS_ExportBan_Active_FB"} pro GET /dev/sps/io/{name}. U HTTP JSON path atd.';

-- ------------------------------------------------------------
-- Odchozí journal
-- ------------------------------------------------------------
CREATE TABLE ems.signal_outbound_journal (
    id                   BIGSERIAL PRIMARY KEY,
    route_id             INT NOT NULL REFERENCES ems.signal_route (id),
    site_id              INT NOT NULL REFERENCES ems.site (id),
    signal_code          TEXT NOT NULL,
    value_text           TEXT NOT NULL,
    value_num            NUMERIC,
    status               TEXT NOT NULL,
    attempt_count        INT NOT NULL DEFAULT 0,
    next_attempt_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_error            TEXT,
    http_method           TEXT,
    request_url           TEXT,
    http_status           INT,
    latency_ms            INT,
    response_body_trunc   TEXT,
    sent_at               TIMESTAMPTZ,
    verified_at           TIMESTAMPTZ,
    created_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT chk_signal_outbound_status CHECK (
        status IN ('queued', 'sent', 'verified', 'failed', 'abandoned')
    )
);

CREATE INDEX idx_signal_outbound_worker
    ON ems.signal_outbound_journal (status, next_attempt_at);

CREATE INDEX idx_signal_outbound_site_debug
    ON ems.signal_outbound_journal (site_id, signal_code, created_at DESC);

COMMENT ON TABLE ems.signal_outbound_journal IS
'Journal odchozích signálů (HTTP). Worker odesílá queued, po úspěchu sent, po readback verified nebo failed s retry.';

-- ------------------------------------------------------------
-- Poslední známý stav (idempotence)
-- ------------------------------------------------------------
CREATE TABLE ems.signal_state (
    site_id                 INT NOT NULL REFERENCES ems.site (id),
    signal_code             TEXT NOT NULL,
    destination_type        TEXT NOT NULL,
    destination_key         TEXT NOT NULL,
    last_desired_value_text TEXT,
    last_sent_value_text    TEXT,
    last_verified_value_text TEXT,
    last_sent_at             TIMESTAMPTZ,
    last_verified_at         TIMESTAMPTZ,
    updated_at               TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (site_id, signal_code, destination_type, destination_key)
);

COMMENT ON TABLE ems.signal_state IS
'Poslední požadovaná / odeslaná / ověřená hodnota signálu per cíl — idempotence a diagnostika verify.';