-- Migration 038: Bestellungen (Vendor Orders) system
-- Tables for vendors, orders, line items, file attachments, reminders, and audit trail.

-- ═══════════════════════════════════════════════════════════════════════════
-- 1. Lieferanten (Vendors)
-- ═══════════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS lieferanten (
  id              SERIAL       PRIMARY KEY,
  name            TEXT         NOT NULL,
  kontakt_name    TEXT,
  email           TEXT,
  telefon         TEXT,
  adresse         TEXT,
  website         TEXT,
  notizen         TEXT,
  erstellt_von    UUID         REFERENCES users(id) ON DELETE SET NULL,
  erstellt_am     TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  aktualisiert_am TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_lieferanten_name ON lieferanten(name);

-- ═══════════════════════════════════════════════════════════════════════════
-- 2. Bestellungen (Orders)
-- ═══════════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS bestellungen (
  id                SERIAL       PRIMARY KEY,
  bezeichnung       TEXT         NOT NULL,
  lieferant_id      INT          REFERENCES lieferanten(id) ON DELETE SET NULL,
  besteller_id      UUID         REFERENCES users(id) ON DELETE SET NULL,
  status            TEXT         NOT NULL DEFAULT 'entwurf'
                    CHECK (status IN ('entwurf','erstellt','bestellt','teillieferung','vollstaendig','abgeschlossen')),
  budget            NUMERIC(10,2),
  notizen           TEXT,
  erstellt_von      UUID         REFERENCES users(id) ON DELETE SET NULL,
  erstellt_am       TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  aktualisiert_am   TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  bestellt_am       TIMESTAMPTZ,
  abgeschlossen_am  TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_bestellungen_status ON bestellungen(status);
CREATE INDEX IF NOT EXISTS idx_bestellungen_lieferant ON bestellungen(lieferant_id);
CREATE INDEX IF NOT EXISTS idx_bestellungen_besteller ON bestellungen(besteller_id);

-- ═══════════════════════════════════════════════════════════════════════════
-- 3. Bestellpositionen (Order Line Items)
-- ═══════════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS bestellpositionen (
  id              SERIAL       PRIMARY KEY,
  bestellung_id   INT          NOT NULL REFERENCES bestellungen(id) ON DELETE CASCADE,
  bezeichnung     TEXT         NOT NULL,
  artikelnummer   TEXT,
  menge           NUMERIC      NOT NULL DEFAULT 1,
  einheit         TEXT         DEFAULT 'Stk',
  einzelpreis     NUMERIC(10,2),
  erhalten_menge  NUMERIC      NOT NULL DEFAULT 0,
  notizen         TEXT,
  erstellt_am     TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  aktualisiert_am TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_bestellpositionen_bestellung ON bestellpositionen(bestellung_id);

-- ═══════════════════════════════════════════════════════════════════════════
-- 4. Bestellung Dateien (Order File Attachments)
-- ═══════════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS bestellung_dateien (
  id               SERIAL       PRIMARY KEY,
  bestellung_id    INT          NOT NULL REFERENCES bestellungen(id) ON DELETE CASCADE,
  dateiname        TEXT         NOT NULL,
  dateipfad        TEXT         NOT NULL,
  dateityp         TEXT         NOT NULL,
  dateigroesse     INT,
  thumbnail_pfad   TEXT,
  hochgeladen_von  UUID         REFERENCES users(id) ON DELETE SET NULL,
  hochgeladen_am   TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_bestellung_dateien_bestellung ON bestellung_dateien(bestellung_id);

-- ═══════════════════════════════════════════════════════════════════════════
-- 5. Bestellung Erinnerungen (Order Reminders)
-- ═══════════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS bestellung_erinnerungen (
  id              SERIAL       PRIMARY KEY,
  bestellung_id   INT          NOT NULL REFERENCES bestellungen(id) ON DELETE CASCADE,
  faellig_am      TIMESTAMPTZ  NOT NULL,
  nachricht       TEXT,
  erledigt        BOOLEAN      NOT NULL DEFAULT FALSE,
  erstellt_von    UUID         REFERENCES users(id) ON DELETE SET NULL,
  erstellt_am     TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_bestellung_erinnerungen_faellig ON bestellung_erinnerungen(faellig_am) WHERE NOT erledigt;

-- ═══════════════════════════════════════════════════════════════════════════
-- 6. Bestellung Historie (Audit Trail)
-- ═══════════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS bestellung_historie (
  id              SERIAL       PRIMARY KEY,
  bestellung_id   INT          NOT NULL REFERENCES bestellungen(id) ON DELETE CASCADE,
  aktion          TEXT         NOT NULL,
  details         JSONB,
  erstellt_von    UUID         REFERENCES users(id) ON DELETE SET NULL,
  erstellt_am     TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_bestellung_historie_bestellung ON bestellung_historie(bestellung_id);

-- ═══════════════════════════════════════════════════════════════════════════
-- 7. Auto-update aktualisiert_am triggers
-- ═══════════════════════════════════════════════════════════════════════════

CREATE OR REPLACE FUNCTION update_aktualisiert_am()
RETURNS TRIGGER AS $$
BEGIN
  NEW.aktualisiert_am = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_lieferanten_aktualisiert') THEN
    CREATE TRIGGER trg_lieferanten_aktualisiert BEFORE UPDATE ON lieferanten
      FOR EACH ROW EXECUTE FUNCTION update_aktualisiert_am();
  END IF;

  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_bestellungen_aktualisiert') THEN
    CREATE TRIGGER trg_bestellungen_aktualisiert BEFORE UPDATE ON bestellungen
      FOR EACH ROW EXECUTE FUNCTION update_aktualisiert_am();
  END IF;

  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_bestellpositionen_aktualisiert') THEN
    CREATE TRIGGER trg_bestellpositionen_aktualisiert BEFORE UPDATE ON bestellpositionen
      FOR EACH ROW EXECUTE FUNCTION update_aktualisiert_am();
  END IF;
END $$;
