yourpart-daemon/migrations/012_falukant_political_benefits_daemon.sql

-- Daemon: Amtsvorteile (reputation_periodic Ticks, optional Ernennungs-Ablauf)
-- Voraussetzung: Backend-Seeds `falukant_predefine.political_office_benefit` + ggf. `falukant_type.political_office_benefit_type`

CREATE TABLE IF NOT EXISTS falukant_data.political_benefit_last_tick (
    id                     SERIAL PRIMARY KEY,
    character_id           INTEGER NOT NULL
        REFERENCES falukant_data.character (id) ON DELETE CASCADE,
    political_office_benefit_id INTEGER NOT NULL,
    last_tick_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    ticks_count            INTEGER NOT NULL DEFAULT 0,
    CONSTRAINT uq_political_benefit_last_tick UNIQUE (character_id, political_office_benefit_id)
);

CREATE INDEX IF NOT EXISTS idx_political_benefit_last_tick_character
    ON falukant_data.political_benefit_last_tick (character_id);

COMMENT ON TABLE falukant_data.political_benefit_last_tick IS
    'Letzter reputation_periodic-Tick pro (Charakter × Benefit-Zeile aus political_office_benefit); Daemon: YpDaemon political_benefits.rs';

-- Optional: Spieler-Ernennungen (Backend legt Zeilen an; Daemon setzt nur abgelaufen)
CREATE TABLE IF NOT EXISTS falukant_data.political_appointment (
    id                      SERIAL PRIMARY KEY,
    appointer_character_id  INTEGER NOT NULL
        REFERENCES falukant_data.character (id) ON DELETE CASCADE,
    target_character_id     INTEGER
        REFERENCES falukant_data.character (id) ON DELETE SET NULL,
    office_type_id          INTEGER NOT NULL
        REFERENCES falukant_type.political_office_type (id),
    region_id               INTEGER NOT NULL
        REFERENCES falukant_data.region (id),
    status                  TEXT NOT NULL DEFAULT 'pending',
    created_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at              TIMESTAMPTZ,
    completed_office_id     INTEGER
        REFERENCES falukant_data.political_office (id) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_political_appointment_status_expires
    ON falukant_data.political_appointment (status, expires_at);

COMMENT ON TABLE falukant_data.political_appointment IS
    'Ernennungen (Backend); Daemon markiert pending → expired wenn expires_at überschritten';