-- Falukant: Liebhaber, Ehezufriedenheit, uneheliche Kinder (Handoff: externer Daemon)
-- Siehe docs/FALUKANT_DAEMON_HANDOFF.md

ALTER TABLE falukant_data.character
    ADD COLUMN IF NOT EXISTS reputation numeric(6,2) NOT NULL DEFAULT 50.00;

ALTER TABLE falukant_data.relationship
    ADD COLUMN IF NOT EXISTS marriage_satisfaction smallint NOT NULL DEFAULT 55
        CHECK (marriage_satisfaction >= 0 AND marriage_satisfaction <= 100),
    ADD COLUMN IF NOT EXISTS marriage_drift_high smallint NOT NULL DEFAULT 0
        CHECK (marriage_drift_high >= 0 AND marriage_drift_high < 3),
    ADD COLUMN IF NOT EXISTS marriage_drift_low smallint NOT NULL DEFAULT 0
        CHECK (marriage_drift_low >= 0 AND marriage_drift_low < 5);

COMMENT ON COLUMN falukant_data.relationship.marriage_satisfaction IS
    'Ehezufriedenheit 0..100 (married / engaged / wooing); Schreiben durch Daemon';

CREATE TABLE IF NOT EXISTS falukant_data.relationship_state (
    relationship_id integer PRIMARY KEY
        REFERENCES falukant_data.relationship (id) ON DELETE CASCADE,
    lover_role varchar(32) NOT NULL
        CHECK (lover_role IN ('secret_affair', 'lover', 'mistress_or_favorite')),
    affection smallint NOT NULL DEFAULT 50
        CHECK (affection >= 0 AND affection <= 100),
    visibility smallint NOT NULL DEFAULT 20
        CHECK (visibility >= 0 AND visibility <= 100),
    discretion smallint NOT NULL DEFAULT 50
        CHECK (discretion >= 0 AND discretion <= 100),
    maintenance_level smallint NOT NULL DEFAULT 50
        CHECK (maintenance_level >= 0 AND maintenance_level <= 100),
    status_fit smallint NOT NULL DEFAULT 0
        CHECK (status_fit >= -2 AND status_fit <= 2),
    monthly_base_cost integer NOT NULL DEFAULT 30,
    active boolean NOT NULL DEFAULT true,
    acknowledged boolean NOT NULL DEFAULT false,
    exclusive boolean,
    months_underfunded smallint NOT NULL DEFAULT 0
        CHECK (months_underfunded >= 0 AND months_underfunded < 100),
    scandal_extra_daily_pct smallint NOT NULL DEFAULT 0
        CHECK (scandal_extra_daily_pct >= 0 AND scandal_extra_daily_pct <= 100),
    last_daily_processed_at timestamptz,
    last_monthly_processed_at timestamptz
);

CREATE INDEX IF NOT EXISTS idx_relationship_state_active
    ON falukant_data.relationship_state (active)
    WHERE active = true;

COMMENT ON COLUMN falukant_data.relationship_state.last_daily_processed_at IS
    'Idempotenz: kein zweiter Daily-Tick am selben Kalendertag (Serverzeit)';
COMMENT ON COLUMN falukant_data.relationship_state.last_monthly_processed_at IS
    'Idempotenz: kein zweiter Monthly-Tick im selben Kalendermonat (Serverzeit)';

ALTER TABLE falukant_data.child_relation
    ADD COLUMN IF NOT EXISTS legitimacy varchar(32) NOT NULL DEFAULT 'legitimate'
        CHECK (legitimacy IN ('legitimate', 'acknowledged_bastard', 'hidden_bastard')),
    ADD COLUMN IF NOT EXISTS birth_context varchar(32) NOT NULL DEFAULT 'marriage'
        CHECK (birth_context IN ('marriage', 'lover')),
    ADD COLUMN IF NOT EXISTS public_known boolean NOT NULL DEFAULT true;

COMMENT ON COLUMN falukant_data.child_relation.legitimacy IS 'legitimate | acknowledged_bastard | hidden_bastard';
COMMENT ON COLUMN falukant_data.child_relation.birth_context IS 'marriage | lover';