-- =============================================================================
-- Migration 075: Buchhaltung (Accounting) Schema
-- =============================================================================

-- 1. Account types (lookup table)
CREATE TABLE IF NOT EXISTS buchhaltung_konto_typen (
  id           SERIAL PRIMARY KEY,
  bezeichnung  TEXT   NOT NULL UNIQUE,
  art          TEXT   NOT NULL CHECK (art IN ('einnahme', 'ausgabe', 'vermoegen', 'verbindlichkeit')),
  sort_order   INT    NOT NULL DEFAULT 0
);

INSERT INTO buchhaltung_konto_typen (bezeichnung, art, sort_order) VALUES
  ('Einnahmen',        'einnahme',         1),
  ('Ausgaben',         'ausgabe',          2),
  ('Vermögen',         'vermoegen',        3),
  ('Verbindlichkeiten','verbindlichkeit',  4)
ON CONFLICT (bezeichnung) DO NOTHING;

-- 2. Bank accounts
CREATE TABLE IF NOT EXISTS buchhaltung_bankkonten (
  id            SERIAL PRIMARY KEY,
  bezeichnung   TEXT        NOT NULL,
  iban          TEXT,
  bic           TEXT,
  institut      TEXT,
  ist_standard  BOOLEAN     NOT NULL DEFAULT FALSE,
  aktiv         BOOLEAN     NOT NULL DEFAULT TRUE,
  erstellt_von  UUID        REFERENCES users(id) ON DELETE SET NULL,
  erstellt_am   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  aktualisiert_am TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 3. Fiscal years
CREATE TABLE IF NOT EXISTS buchhaltung_haushaltsjahre (
  id            SERIAL PRIMARY KEY,
  jahr          INT         NOT NULL UNIQUE,
  bezeichnung   TEXT        NOT NULL,
  beginn        DATE        NOT NULL,
  ende          DATE        NOT NULL,
  abgeschlossen BOOLEAN     NOT NULL DEFAULT FALSE,
  erstellt_von  UUID,
  erstellt_am   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  aktualisiert_am TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 4. Budget accounts
CREATE TABLE IF NOT EXISTS buchhaltung_konten (
  id               SERIAL PRIMARY KEY,
  haushaltsjahr_id INT         NOT NULL REFERENCES buchhaltung_haushaltsjahre(id) ON DELETE CASCADE,
  konto_typ_id     INT         REFERENCES buchhaltung_konto_typen(id) ON DELETE SET NULL,
  kontonummer      TEXT        NOT NULL,
  bezeichnung      TEXT        NOT NULL,
  budget_betrag    NUMERIC(12,2) NOT NULL DEFAULT 0,
  notizen          TEXT,
  aktiv            BOOLEAN     NOT NULL DEFAULT TRUE,
  erstellt_von     UUID,
  erstellt_am      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  aktualisiert_am  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (haushaltsjahr_id, kontonummer)
);

-- 5. Transactions
CREATE TABLE IF NOT EXISTS buchhaltung_transaktionen (
  id                      SERIAL PRIMARY KEY,
  haushaltsjahr_id        INT           NOT NULL REFERENCES buchhaltung_haushaltsjahre(id) ON DELETE RESTRICT,
  konto_id                INT           REFERENCES buchhaltung_konten(id) ON DELETE SET NULL,
  bankkonto_id            INT           REFERENCES buchhaltung_bankkonten(id) ON DELETE SET NULL,
  laufende_nummer         INT,
  typ                     TEXT          NOT NULL CHECK (typ IN ('einnahme', 'ausgabe')),
  betrag                  NUMERIC(12,2) NOT NULL,
  datum                   DATE          NOT NULL,
  buchungsdatum           DATE,
  beschreibung            TEXT,
  empfaenger_auftraggeber TEXT,
  verwendungszweck        TEXT,
  beleg_nr                TEXT,
  status                  TEXT          NOT NULL CHECK (status IN ('entwurf', 'gebucht', 'freigegeben', 'storniert')) DEFAULT 'entwurf',
  bestellung_id           INT           REFERENCES bestellungen(id) ON DELETE SET NULL,
  erstellt_von            UUID,
  gebucht_von             UUID,
  erstellt_am             TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
  aktualisiert_am         TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);

-- 6. Receipts / attachments
CREATE TABLE IF NOT EXISTS buchhaltung_belege (
  id             SERIAL PRIMARY KEY,
  transaktion_id INT         NOT NULL REFERENCES buchhaltung_transaktionen(id) ON DELETE CASCADE,
  dateiname      TEXT        NOT NULL,
  original_name  TEXT        NOT NULL,
  dateityp       TEXT        NOT NULL,
  dateigroesse   INT         NOT NULL,
  erstellt_von   UUID,
  erstellt_am    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 7. Approvals
CREATE TABLE IF NOT EXISTS buchhaltung_freigaben (
  id              SERIAL PRIMARY KEY,
  transaktion_id  INT         NOT NULL REFERENCES buchhaltung_transaktionen(id) ON DELETE CASCADE,
  status          TEXT        NOT NULL CHECK (status IN ('ausstehend', 'genehmigt', 'abgelehnt')) DEFAULT 'ausstehend',
  kommentar       TEXT,
  freigegeben_von UUID        REFERENCES users(id) ON DELETE SET NULL,
  freigegeben_am  TIMESTAMPTZ,
  erstellt_am     TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 8. Recurring bookings
CREATE TABLE IF NOT EXISTS buchhaltung_wiederkehrend (
  id                      SERIAL PRIMARY KEY,
  bezeichnung             TEXT          NOT NULL,
  konto_id                INT           REFERENCES buchhaltung_konten(id) ON DELETE SET NULL,
  bankkonto_id            INT           REFERENCES buchhaltung_bankkonten(id) ON DELETE SET NULL,
  typ                     TEXT          NOT NULL CHECK (typ IN ('einnahme', 'ausgabe')),
  betrag                  NUMERIC(12,2) NOT NULL,
  beschreibung            TEXT,
  empfaenger_auftraggeber TEXT,
  intervall               TEXT          NOT NULL CHECK (intervall IN ('monatlich', 'quartalsweise', 'halbjaehrlich', 'jaehrlich')),
  naechste_ausfuehrung    DATE          NOT NULL,
  aktiv                   BOOLEAN       NOT NULL DEFAULT TRUE,
  erstellt_von            UUID,
  erstellt_am             TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
  aktualisiert_am         TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);

-- 9. Audit log
CREATE TABLE IF NOT EXISTS buchhaltung_audit (
  id             SERIAL PRIMARY KEY,
  transaktion_id INT         REFERENCES buchhaltung_transaktionen(id) ON DELETE SET NULL,
  aktion         TEXT        NOT NULL,
  details        JSONB,
  erstellt_von   UUID        REFERENCES users(id) ON DELETE SET NULL,
  erstellt_am    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 10. Settings
CREATE TABLE IF NOT EXISTS buchhaltung_einstellungen (
  key            TEXT        PRIMARY KEY,
  value          JSONB       NOT NULL,
  aktualisiert_am TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 11. Budget planning (Phase 7 prep)
CREATE TABLE IF NOT EXISTS buchhaltung_planung (
  id               SERIAL PRIMARY KEY,
  haushaltsjahr_id INT         NOT NULL REFERENCES buchhaltung_haushaltsjahre(id) ON DELETE CASCADE,
  bezeichnung      TEXT        NOT NULL,
  status           TEXT        NOT NULL CHECK (status IN ('entwurf', 'aktiv', 'abgeschlossen')) DEFAULT 'entwurf',
  erstellt_von     UUID,
  erstellt_am      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  aktualisiert_am  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 12. Planning line items
CREATE TABLE IF NOT EXISTS buchhaltung_planpositionen (
  id           SERIAL PRIMARY KEY,
  planung_id   INT           NOT NULL REFERENCES buchhaltung_planung(id) ON DELETE CASCADE,
  konto_id     INT           REFERENCES buchhaltung_konten(id) ON DELETE SET NULL,
  bezeichnung  TEXT          NOT NULL,
  plan_betrag  NUMERIC(12,2) NOT NULL DEFAULT 0,
  notizen      TEXT,
  sort_order   INT           NOT NULL DEFAULT 0
);

-- =============================================================================
-- Indexes
-- =============================================================================

CREATE INDEX IF NOT EXISTS idx_buch_trans_haushaltsjahr ON buchhaltung_transaktionen(haushaltsjahr_id);
CREATE INDEX IF NOT EXISTS idx_buch_trans_konto         ON buchhaltung_transaktionen(konto_id);
CREATE INDEX IF NOT EXISTS idx_buch_trans_bankkonto     ON buchhaltung_transaktionen(bankkonto_id);
CREATE INDEX IF NOT EXISTS idx_buch_trans_status        ON buchhaltung_transaktionen(status);
CREATE INDEX IF NOT EXISTS idx_buch_trans_datum         ON buchhaltung_transaktionen(datum);
CREATE INDEX IF NOT EXISTS idx_buch_trans_bestellung    ON buchhaltung_transaktionen(bestellung_id);
CREATE INDEX IF NOT EXISTS idx_buch_konten_haushaltsjahr ON buchhaltung_konten(haushaltsjahr_id);

-- =============================================================================
-- Triggers — aktualisiert_am (reuse existing update_aktualisiert_am function)
-- =============================================================================

CREATE TRIGGER trg_buch_bankkonten_updated
  BEFORE UPDATE ON buchhaltung_bankkonten
  FOR EACH ROW EXECUTE FUNCTION update_aktualisiert_am();

CREATE TRIGGER trg_buch_haushaltsjahre_updated
  BEFORE UPDATE ON buchhaltung_haushaltsjahre
  FOR EACH ROW EXECUTE FUNCTION update_aktualisiert_am();

CREATE TRIGGER trg_buch_konten_updated
  BEFORE UPDATE ON buchhaltung_konten
  FOR EACH ROW EXECUTE FUNCTION update_aktualisiert_am();

CREATE TRIGGER trg_buch_transaktionen_updated
  BEFORE UPDATE ON buchhaltung_transaktionen
  FOR EACH ROW EXECUTE FUNCTION update_aktualisiert_am();

CREATE TRIGGER trg_buch_wiederkehrend_updated
  BEFORE UPDATE ON buchhaltung_wiederkehrend
  FOR EACH ROW EXECUTE FUNCTION update_aktualisiert_am();

CREATE TRIGGER trg_buch_planung_updated
  BEFORE UPDATE ON buchhaltung_planung
  FOR EACH ROW EXECUTE FUNCTION update_aktualisiert_am();

-- =============================================================================
-- Trigger — sequential laufende_nummer per fiscal year
-- =============================================================================

CREATE OR REPLACE FUNCTION buchhaltung_assign_laufende_nummer()
RETURNS TRIGGER AS $$
DECLARE
  next_num INT;
BEGIN
  -- Assign laufende_nummer when status becomes 'gebucht' and not yet assigned
  IF NEW.status = 'gebucht' AND NEW.laufende_nummer IS NULL THEN
    IF TG_OP = 'INSERT' OR (TG_OP = 'UPDATE' AND (OLD.status IS NULL OR OLD.status != 'gebucht')) THEN
      SELECT COALESCE(MAX(laufende_nummer), 0) + 1
        INTO next_num
        FROM buchhaltung_transaktionen
       WHERE haushaltsjahr_id = NEW.haushaltsjahr_id;
      NEW.laufende_nummer := next_num;
    END IF;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_buch_transaktionen_laufende_nummer
  BEFORE INSERT OR UPDATE ON buchhaltung_transaktionen
  FOR EACH ROW EXECUTE FUNCTION buchhaltung_assign_laufende_nummer();
