63 lines
3.2 KiB
SQL
63 lines
3.2 KiB
SQL
-- 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';
|