-- PostgreSQL-only migration script.
-- Dieses Projekt-Backend nutzt Schemas, JSONB und PostgreSQL-Datentypen.
-- Nicht auf MariaDB/MySQL ausführen.

BEGIN;

CREATE TABLE IF NOT EXISTS falukant_data.relationship_state (
  id serial PRIMARY KEY,
  relationship_id integer NOT NULL UNIQUE,
  marriage_satisfaction integer NOT NULL DEFAULT 55 CHECK (marriage_satisfaction >= 0 AND marriage_satisfaction <= 100),
  marriage_public_stability integer NOT NULL DEFAULT 55 CHECK (marriage_public_stability >= 0 AND marriage_public_stability <= 100),
  lover_role text NULL CHECK (lover_role IN ('secret_affair', 'lover', 'mistress_or_favorite')),
  affection integer NOT NULL DEFAULT 50 CHECK (affection >= 0 AND affection <= 100),
  visibility integer NOT NULL DEFAULT 15 CHECK (visibility >= 0 AND visibility <= 100),
  discretion integer NOT NULL DEFAULT 50 CHECK (discretion >= 0 AND discretion <= 100),
  maintenance_level integer NOT NULL DEFAULT 50 CHECK (maintenance_level >= 0 AND maintenance_level <= 100),
  status_fit integer NOT NULL DEFAULT 0 CHECK (status_fit >= -2 AND status_fit <= 2),
  monthly_base_cost integer NOT NULL DEFAULT 0 CHECK (monthly_base_cost >= 0),
  months_underfunded integer NOT NULL DEFAULT 0 CHECK (months_underfunded >= 0),
  active boolean NOT NULL DEFAULT true,
  acknowledged boolean NOT NULL DEFAULT false,
  exclusive_flag boolean NOT NULL DEFAULT false,
  last_monthly_processed_at timestamp with time zone NULL,
  last_daily_processed_at timestamp with time zone NULL,
  notes_json jsonb NULL,
  flags_json jsonb NULL,
  created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT relationship_state_relationship_fk
    FOREIGN KEY (relationship_id)
    REFERENCES falukant_data.relationship(id)
    ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS relationship_state_active_idx
  ON falukant_data.relationship_state (active);

CREATE INDEX IF NOT EXISTS relationship_state_lover_role_idx
  ON falukant_data.relationship_state (lover_role);

ALTER TABLE IF EXISTS falukant_data.child_relation
  ADD COLUMN IF NOT EXISTS legitimacy text NOT NULL DEFAULT 'legitimate';

ALTER TABLE IF EXISTS falukant_data.child_relation
  ADD COLUMN IF NOT EXISTS birth_context text NOT NULL DEFAULT 'marriage';

ALTER TABLE IF EXISTS falukant_data.child_relation
  ADD COLUMN IF NOT EXISTS public_known boolean NOT NULL DEFAULT false;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_constraint
    WHERE conname = 'child_relation_legitimacy_chk'
  ) THEN
    ALTER TABLE falukant_data.child_relation
      ADD CONSTRAINT child_relation_legitimacy_chk
      CHECK (legitimacy IN ('legitimate', 'acknowledged_bastard', 'hidden_bastard'));
  END IF;
END
$$;

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_constraint
    WHERE conname = 'child_relation_birth_context_chk'
  ) THEN
    ALTER TABLE falukant_data.child_relation
      ADD CONSTRAINT child_relation_birth_context_chk
      CHECK (birth_context IN ('marriage', 'lover'));
  END IF;
END
$$;

COMMIT;

-- Rollback separat bei Bedarf:
-- BEGIN;
-- ALTER TABLE falukant_data.child_relation DROP CONSTRAINT IF EXISTS child_relation_birth_context_chk;
-- ALTER TABLE falukant_data.child_relation DROP CONSTRAINT IF EXISTS child_relation_legitimacy_chk;
-- ALTER TABLE falukant_data.child_relation DROP COLUMN IF EXISTS public_known;
-- ALTER TABLE falukant_data.child_relation DROP COLUMN IF EXISTS birth_context;
-- ALTER TABLE falukant_data.child_relation DROP COLUMN IF EXISTS legitimacy;
-- DROP TABLE IF EXISTS falukant_data.relationship_state;
-- COMMIT;