-- Migration 043: Feature batch
-- 1. Vehicle wartungslog: ergebnis + naechste_faelligkeit
-- 2. Shop anfragen: bestell_nummer + bestell_jahr
-- 3. Issues + issue_kommentare tables
-- 4. New feature group 'issues' + permissions
-- 5. New shop permissions (view_overview, order_for_user)

-- ═══════════════════════════════════════════════════════════════════════════
-- 1. Vehicle Wartungslog additions
-- ═══════════════════════════════════════════════════════════════════════════

ALTER TABLE fahrzeug_wartungslog
  ADD COLUMN IF NOT EXISTS ergebnis VARCHAR(30)
    CHECK (ergebnis IN ('bestanden', 'bestanden_mit_maengeln', 'nicht_bestanden'));

ALTER TABLE fahrzeug_wartungslog
  ADD COLUMN IF NOT EXISTS naechste_faelligkeit DATE;

-- ═══════════════════════════════════════════════════════════════════════════
-- 2. Shop Anfragen: unique order ID per year
-- ═══════════════════════════════════════════════════════════════════════════

ALTER TABLE shop_anfragen
  ADD COLUMN IF NOT EXISTS bestell_nummer INT;

ALTER TABLE shop_anfragen
  ADD COLUMN IF NOT EXISTS bestell_jahr INT DEFAULT EXTRACT(YEAR FROM CURRENT_DATE);

-- Make bezeichnung optional on shop_anfragen (if it exists)
-- shop_anfragen doesn't have bezeichnung — it's on positionen. No change needed.

-- ═══════════════════════════════════════════════════════════════════════════
-- 3. Issues tables
-- ═══════════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS issues (
  id              SERIAL       PRIMARY KEY,
  titel           VARCHAR(500) NOT NULL,
  beschreibung    TEXT,
  typ             VARCHAR(50)  NOT NULL DEFAULT 'bug'
                  CHECK (typ IN ('bug', 'feature', 'sonstiges')),
  prioritaet      VARCHAR(20)  NOT NULL DEFAULT 'mittel'
                  CHECK (prioritaet IN ('niedrig', 'mittel', 'hoch')),
  status          VARCHAR(20)  NOT NULL DEFAULT 'offen'
                  CHECK (status IN ('offen', 'in_bearbeitung', 'erledigt', 'abgelehnt')),
  erstellt_von    UUID         REFERENCES users(id) ON DELETE SET NULL,
  zugewiesen_an   UUID         REFERENCES users(id) ON DELETE SET NULL,
  created_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_issues_erstellt_von ON issues(erstellt_von);
CREATE INDEX IF NOT EXISTS idx_issues_status ON issues(status);
CREATE INDEX IF NOT EXISTS idx_issues_typ ON issues(typ);

CREATE TABLE IF NOT EXISTS issue_kommentare (
  id          SERIAL       PRIMARY KEY,
  issue_id    INT          NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
  autor_id    UUID         REFERENCES users(id) ON DELETE SET NULL,
  inhalt      TEXT         NOT NULL,
  created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_issue_kommentare_issue ON issue_kommentare(issue_id);

-- Auto-update trigger for issues
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_issues_updated') THEN
    CREATE TRIGGER trg_issues_updated BEFORE UPDATE ON issues
      FOR EACH ROW EXECUTE FUNCTION update_aktualisiert_am();
  END IF;
END $$;

-- ═══════════════════════════════════════════════════════════════════════════
-- 4. Issues feature group + permissions
-- ═══════════════════════════════════════════════════════════════════════════

INSERT INTO feature_groups (id, label, sort_order) VALUES
  ('issues', 'Issues', 13)
ON CONFLICT (id) DO NOTHING;

INSERT INTO permissions (id, feature_group_id, label, description, sort_order) VALUES
  ('issues:create',   'issues', 'Erstellen',       'Issues erstellen',                              1),
  ('issues:view_own', 'issues', 'Eigene ansehen',   'Eigene Issues einsehen',                       2),
  ('issues:view_all', 'issues', 'Alle ansehen',     'Alle Issues einsehen',                         3),
  ('issues:manage',   'issues', 'Verwalten',        'Issues bearbeiten, Status ändern, zuweisen',   4)
ON CONFLICT (id) DO NOTHING;

-- Seed: all groups get create + view_own; kommando gets all
INSERT INTO group_permissions (authentik_group, permission_id) VALUES
  -- Kommando: full access
  ('dashboard_kommando', 'issues:create'),
  ('dashboard_kommando', 'issues:view_own'),
  ('dashboard_kommando', 'issues:view_all'),
  ('dashboard_kommando', 'issues:manage'),
  -- Fahrmeister
  ('dashboard_fahrmeister', 'issues:create'),
  ('dashboard_fahrmeister', 'issues:view_own'),
  -- Zeugmeister
  ('dashboard_zeugmeister', 'issues:create'),
  ('dashboard_zeugmeister', 'issues:view_own'),
  -- Chargen
  ('dashboard_chargen', 'issues:create'),
  ('dashboard_chargen', 'issues:view_own'),
  -- Moderator
  ('dashboard_moderator', 'issues:create'),
  ('dashboard_moderator', 'issues:view_own'),
  -- Atemschutz
  ('dashboard_atemschutz', 'issues:create'),
  ('dashboard_atemschutz', 'issues:view_own'),
  -- Mitglied
  ('dashboard_mitglied', 'issues:create'),
  ('dashboard_mitglied', 'issues:view_own')
ON CONFLICT DO NOTHING;

-- ═══════════════════════════════════════════════════════════════════════════
-- 5. New shop permissions
-- ═══════════════════════════════════════════════════════════════════════════

INSERT INTO permissions (id, feature_group_id, label, description, sort_order) VALUES
  ('shop:view_overview',  'shop', 'Übersicht',           'Aggregierte Übersicht aller Anfragen',    7),
  ('shop:order_for_user', 'shop', 'Für Benutzer bestellen', 'Anfragen im Namen anderer erstellen', 8)
ON CONFLICT (id) DO NOTHING;

-- Kommando gets the new permissions
INSERT INTO group_permissions (authentik_group, permission_id) VALUES
  ('dashboard_kommando', 'shop:view_overview'),
  ('dashboard_kommando', 'shop:order_for_user')
ON CONFLICT DO NOTHING;
