-- Migration 040: Permission updates
-- 1. Add bestellungen + shop feature groups and their permissions
-- 2. Simplify calendar permissions from 13 → 4
-- 3. Migrate existing group_permissions to new calendar scheme

-- ═══════════════════════════════════════════════════════════════════════════
-- 1. New feature groups
-- ═══════════════════════════════════════════════════════════════════════════

INSERT INTO feature_groups (id, label, sort_order) VALUES
  ('bestellungen', 'Bestellungen', 11),
  ('shop',         'Shop',         12)
ON CONFLICT (id) DO NOTHING;

-- ═══════════════════════════════════════════════════════════════════════════
-- 2. Bestellungen permissions
-- ═══════════════════════════════════════════════════════════════════════════

INSERT INTO permissions (id, feature_group_id, label, description, sort_order) VALUES
  ('bestellungen:view',             'bestellungen', 'Ansehen',              'Bestellungen einsehen',                      1),
  ('bestellungen:create',           'bestellungen', 'Erstellen/Bearbeiten', 'Bestellungen erstellen und bearbeiten',      2),
  ('bestellungen:delete',           'bestellungen', 'Löschen',             'Bestellungen löschen',                       3),
  ('bestellungen:manage_vendors',   'bestellungen', 'Lieferanten verwalten','Lieferanten-Datenbank verwalten',            4),
  ('bestellungen:export',           'bestellungen', 'PDF Export',           'Bestellungen als PDF exportieren',           5),
  ('bestellungen:manage_reminders', 'bestellungen', 'Erinnerungen',        'Erinnerungen für Bestellungen verwalten',    6),
  ('bestellungen:widget',           'bestellungen', 'Widget',              'Dashboard-Widget für Bestellungen',          7)
ON CONFLICT (id) DO NOTHING;

-- ═══════════════════════════════════════════════════════════════════════════
-- 3. Shop permissions
-- ═══════════════════════════════════════════════════════════════════════════

INSERT INTO permissions (id, feature_group_id, label, description, sort_order) VALUES
  ('shop:view',              'shop', 'Katalog ansehen',       'Shop-Katalog einsehen',                          1),
  ('shop:create_request',    'shop', 'Anfrage stellen',       'Bestellanfragen an Admin stellen',               2),
  ('shop:manage_catalog',    'shop', 'Katalog verwalten',     'Artikel im Shop-Katalog verwalten',              3),
  ('shop:approve_requests',  'shop', 'Anfragen genehmigen',   'Bestellanfragen genehmigen oder ablehnen',       4),
  ('shop:link_orders',       'shop', 'Mit Bestellung verknüpfen', 'Anfragen mit Lieferantenbestellungen verknüpfen', 5),
  ('shop:widget',            'shop', 'Widget',                'Dashboard-Widget für Shop-Anfragen',             6)
ON CONFLICT (id) DO NOTHING;

-- ═══════════════════════════════════════════════════════════════════════════
-- 4. Calendar permission simplification (13 → 4)
-- ═══════════════════════════════════════════════════════════════════════════
-- New scheme:
--   kalender:view            — see events + widgets
--   kalender:create          — create/edit/cancel events, mark attendance, manage categories, view reports
--   kalender:view_bookings   — see bookings + booking widgets
--   kalender:manage_bookings — create/edit/cancel/delete bookings

-- 4a. Collect which groups had which old permissions, then map to new ones.
--     We use a temp table so we don't lose data during the transition.

CREATE TEMP TABLE _cal_migration AS
SELECT DISTINCT authentik_group,
  CASE
    -- Any group that had kalender:create OR kalender:cancel OR kalender:manage_categories
    -- OR kalender:view_reports OR kalender:mark_attendance → gets kalender:create
    WHEN permission_id IN ('kalender:create','kalender:cancel','kalender:manage_categories',
                           'kalender:view_reports','kalender:mark_attendance')
    THEN 'kalender:create'
    -- Widget permissions → kalender:view (they already have it if they had widget perms)
    WHEN permission_id IN ('kalender:widget_events','kalender:widget_quick_add')
    THEN 'kalender:view'
    -- Booking-related view widgets → kalender:view_bookings
    WHEN permission_id IN ('kalender:widget_bookings')
    THEN 'kalender:view_bookings'
    -- All booking write ops → kalender:manage_bookings
    WHEN permission_id IN ('kalender:create_bookings','kalender:edit_bookings',
                           'kalender:cancel_own_bookings','kalender:delete_bookings')
    THEN 'kalender:manage_bookings'
    ELSE permission_id
  END AS new_perm
FROM group_permissions
WHERE permission_id LIKE 'kalender:%';

-- 4b. Delete old calendar permissions from group_permissions
DELETE FROM group_permissions WHERE permission_id LIKE 'kalender:%';

-- 4c. Delete old calendar permission definitions
DELETE FROM permissions WHERE id LIKE 'kalender:%';

-- 4d. Insert new calendar permissions
INSERT INTO permissions (id, feature_group_id, label, description, sort_order) VALUES
  ('kalender:view',            'kalender', 'Termine ansehen',       'Kalender-Termine und Widgets einsehen',                        1),
  ('kalender:create',          'kalender', 'Termine verwalten',     'Termine erstellen/bearbeiten/absagen, Kategorien, Berichte',    2),
  ('kalender:view_bookings',   'kalender', 'Buchungen ansehen',     'Fahrzeugbuchungen und Buchungs-Widget einsehen',               3),
  ('kalender:manage_bookings', 'kalender', 'Buchungen verwalten',   'Buchungen erstellen/bearbeiten/stornieren/löschen',            4)
ON CONFLICT (id) DO NOTHING;

-- 4e. Re-insert migrated grants (only valid new permissions)
INSERT INTO group_permissions (authentik_group, permission_id)
SELECT DISTINCT authentik_group, new_perm
FROM _cal_migration
WHERE new_perm IN ('kalender:view','kalender:create','kalender:view_bookings','kalender:manage_bookings')
ON CONFLICT DO NOTHING;

-- Also ensure everyone who had any calendar perm gets kalender:view
INSERT INTO group_permissions (authentik_group, permission_id)
SELECT DISTINCT authentik_group, 'kalender:view'
FROM _cal_migration
ON CONFLICT DO NOTHING;

-- And everyone who had any booking perm gets kalender:view_bookings
INSERT INTO group_permissions (authentik_group, permission_id)
SELECT DISTINCT authentik_group, 'kalender:view_bookings'
FROM _cal_migration
WHERE new_perm = 'kalender:manage_bookings'
ON CONFLICT DO NOTHING;

DROP TABLE _cal_migration;

-- ═══════════════════════════════════════════════════════════════════════════
-- 5. Seed bestellungen + shop permissions for existing groups
-- ═══════════════════════════════════════════════════════════════════════════
-- kommando gets full access, other groups get view + shop request

INSERT INTO group_permissions (authentik_group, permission_id) VALUES
  -- Kommando: full bestellungen + shop
  ('dashboard_kommando', 'bestellungen:view'),
  ('dashboard_kommando', 'bestellungen:create'),
  ('dashboard_kommando', 'bestellungen:delete'),
  ('dashboard_kommando', 'bestellungen:manage_vendors'),
  ('dashboard_kommando', 'bestellungen:export'),
  ('dashboard_kommando', 'bestellungen:manage_reminders'),
  ('dashboard_kommando', 'bestellungen:widget'),
  ('dashboard_kommando', 'shop:view'),
  ('dashboard_kommando', 'shop:create_request'),
  ('dashboard_kommando', 'shop:manage_catalog'),
  ('dashboard_kommando', 'shop:approve_requests'),
  ('dashboard_kommando', 'shop:link_orders'),
  ('dashboard_kommando', 'shop:widget'),
  -- Fahrmeister: view orders + shop request
  ('dashboard_fahrmeister', 'bestellungen:view'),
  ('dashboard_fahrmeister', 'bestellungen:widget'),
  ('dashboard_fahrmeister', 'shop:view'),
  ('dashboard_fahrmeister', 'shop:create_request'),
  -- Zeugmeister: view orders + shop request
  ('dashboard_zeugmeister', 'bestellungen:view'),
  ('dashboard_zeugmeister', 'bestellungen:widget'),
  ('dashboard_zeugmeister', 'shop:view'),
  ('dashboard_zeugmeister', 'shop:create_request'),
  -- Chargen: view orders + shop request
  ('dashboard_chargen', 'bestellungen:view'),
  ('dashboard_chargen', 'bestellungen:widget'),
  ('dashboard_chargen', 'shop:view'),
  ('dashboard_chargen', 'shop:create_request'),
  -- Moderator: view orders + shop request
  ('dashboard_moderator', 'bestellungen:view'),
  ('dashboard_moderator', 'shop:view'),
  ('dashboard_moderator', 'shop:create_request'),
  -- Atemschutz: shop request only
  ('dashboard_atemschutz', 'shop:view'),
  ('dashboard_atemschutz', 'shop:create_request'),
  -- Mitglied: shop request only
  ('dashboard_mitglied', 'shop:view'),
  ('dashboard_mitglied', 'shop:create_request')
ON CONFLICT DO NOTHING;
