CREATE TABLE IF NOT EXISTS finance_tax_code (
  id BIGSERIAL PRIMARY KEY,
  code VARCHAR(160) NOT NULL UNIQUE,
  label VARCHAR(220) NOT NULL,
  tax_rate_percent NUMERIC(6,3) NOT NULL DEFAULT 0 CHECK (tax_rate_percent >= 0 AND tax_rate_percent <= 100),
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  sort_order INTEGER NOT NULL DEFAULT 1000,
  created_by BIGINT REFERENCES app_user(id),
  updated_by BIGINT REFERENCES app_user(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS finance_expense_line (
  id BIGSERIAL PRIMARY KEY,
  expense_id BIGINT NOT NULL REFERENCES finance_expense(id) ON DELETE CASCADE,
  line_no INTEGER NOT NULL DEFAULT 1,
  description VARCHAR(255) NOT NULL,
  tax_code_id BIGINT REFERENCES finance_tax_code(id) ON DELETE SET NULL,
  tax_code_key VARCHAR(160),
  net_amount NUMERIC(14,2) NOT NULL DEFAULT 0 CHECK (net_amount >= 0),
  tax_rate_percent NUMERIC(6,3) NOT NULL DEFAULT 0 CHECK (tax_rate_percent >= 0 AND tax_rate_percent <= 100),
  tax_amount NUMERIC(14,2) NOT NULL DEFAULT 0 CHECK (tax_amount >= 0),
  gross_amount NUMERIC(14,2) NOT NULL DEFAULT 0 CHECK (gross_amount >= 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT finance_expense_line_line_no_unique UNIQUE (expense_id, line_no)
);

CREATE INDEX IF NOT EXISTS idx_finance_expense_line_expense ON finance_expense_line(expense_id);
CREATE INDEX IF NOT EXISTS idx_finance_expense_line_tax_code ON finance_expense_line(tax_code_id);

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

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

INSERT INTO finance_tax_code(code, label, tax_rate_percent, is_active, sort_order)
VALUES
  ('Kfz_Betriebskosten_19%', 'Kfz Betriebskosten 19%', 19.000, TRUE, 10),
  ('Mietwagen_Betanken_19%', 'Mietwagen Betanken 19%', 19.000, TRUE, 11),
  ('Mietwagen_Ausland', 'Mietwagen Ausland', 0.000, TRUE, 12),
  ('Bahn_ueber_50_km_19%', 'Bahn ueber 50 km 19%', 19.000, TRUE, 13),
  ('Bahn_ueber_50_km', 'Bahn ueber 50 km', 0.000, TRUE, 14),
  ('Bahn_unter_50_km_7%', 'Bahn unter 50 km 7%', 7.000, TRUE, 15),
  ('Bahn_unter_50_km', 'Bahn unter 50 km', 0.000, TRUE, 16),
  ('Bewirtungskosten_19%', 'Bewirtungskosten 19%', 19.000, TRUE, 17),
  ('Bewirtungskosten_7%', 'Bewirtungskosten 7%', 7.000, TRUE, 18),
  ('Bewirtungskosten', 'Bewirtungskosten', 0.000, TRUE, 19),
  ('Bewirtungskosten_30_19%', 'Bewirtungskosten 30 19%', 19.000, TRUE, 20),
  ('Bewirtungskosten_30_7%', 'Bewirtungskosten 30 7%', 7.000, TRUE, 21),
  ('Bewirtungskosten_30', 'Bewirtungskosten 30', 0.000, TRUE, 22),
  ('Buerobedarf_19%', 'Buerobedarf 19%', 19.000, TRUE, 23),
  ('Buerobedarf', 'Buerobedarf', 0.000, TRUE, 24),
  ('Bus_ueber_50_km_19%', 'Bus ueber 50 km 19%', 19.000, TRUE, 25),
  ('Bus_ueber_50_km', 'Bus ueber 50 km', 0.000, TRUE, 26),
  ('Bus_unter_50_km_7%', 'Bus unter 50 km 7%', 7.000, TRUE, 27),
  ('Bus_unter_50_km', 'Bus unter 50 km', 0.000, TRUE, 28),
  ('Flug_19%', 'Flug 19%', 19.000, TRUE, 29),
  ('Flug', 'Flug', 0.000, TRUE, 30),
  ('Fruehstueck_(Hotel_Pension)_19%', 'Fruehstueck (Hotel Pension) 19%', 19.000, TRUE, 31),
  ('Fruehstueck_(Hotel_Pension)', 'Fruehstueck (Hotel Pension)', 0.000, TRUE, 32),
  ('Mittagessen_(Hotel_Pension)_19%', 'Mittagessen (Hotel Pension) 19%', 19.000, TRUE, 33),
  ('Mittagessen_(Hotel_Pension)', 'Mittagessen (Hotel Pension)', 0.000, TRUE, 34),
  ('Abendessen_(Hotel_Pension)_19%', 'Abendessen (Hotel Pension) 19%', 19.000, TRUE, 35),
  ('Abendessen_(Hotel_Pension)', 'Abendessen (Hotel Pension)', 0.000, TRUE, 36),
  ('Handy_19%', 'Handy 19%', 19.000, TRUE, 37),
  ('Handy', 'Handy', 0.000, TRUE, 38),
  ('Hotel_Pension_7%', 'Hotel Pension 7%', 7.000, TRUE, 39),
  ('Hotel_Pension', 'Hotel Pension', 0.000, TRUE, 40),
  ('Internet_19%', 'Internet 19%', 19.000, TRUE, 41),
  ('Internet', 'Internet', 0.000, TRUE, 42),
  ('Lebensmittel_19%%', 'Lebensmittel 19%%', 19.000, TRUE, 43),
  ('Lebensmittel_7%', 'Lebensmittel 7%', 7.000, TRUE, 44),
  ('Lebensmittel', 'Lebensmittel', 0.000, TRUE, 45),
  ('Mietwagen_19%', 'Mietwagen 19%', 19.000, TRUE, 46),
  ('Mietwagen', 'Mietwagen', 0.000, TRUE, 47),
  ('Mietwohnung_Appartement_19%', 'Mietwohnung Appartement 19%', 19.000, TRUE, 48),
  ('Mietwohnung_Appartement', 'Mietwohnung Appartement', 0.000, TRUE, 49),
  ('Parkgebuehren_19%', 'Parkgebuehren 19%', 19.000, TRUE, 50),
  ('Parkgebuehren', 'Parkgebuehren', 0.000, TRUE, 51),
  ('Porto_19%', 'Porto 19%', 19.000, TRUE, 52),
  ('Porto', 'Porto', 0.000, TRUE, 53),
  ('Reservierung_19%', 'Reservierung 19%', 19.000, TRUE, 54),
  ('Reservierung', 'Reservierung', 0.000, TRUE, 55),
  ('Sonstiges_(19_%_MwSt.)', 'Sonstiges (19 % MwSt.)', 19.000, TRUE, 56),
  ('Sonstiges_(7_%_MwSt.)', 'Sonstiges (7 % MwSt.)', 7.000, TRUE, 57),
  ('Sonstiges', 'Sonstiges', 0.000, TRUE, 58),
  ('Tankbelege_19%', 'Tankbelege 19%', 19.000, TRUE, 59),
  ('Tankbelege', 'Tankbelege', 0.000, TRUE, 60),
  ('Taxi_ueber_50_km_19%', 'Taxi ueber 50 km 19%', 19.000, TRUE, 61),
  ('Taxi_ueber_50_km', 'Taxi ueber 50 km', 0.000, TRUE, 62),
  ('Taxi_unter_50_km_7%', 'Taxi unter 50 km 7%', 7.000, TRUE, 63),
  ('Taxi_unter_50_km', 'Taxi unter 50 km', 0.000, TRUE, 64),
  ('Telefonkosten_19%', 'Telefonkosten 19%', 19.000, TRUE, 65),
  ('Telefonkosten', 'Telefonkosten', 0.000, TRUE, 66),
  ('Abendessen_(Hotel/Pension)_19%', 'Abendessen (Hotel/Pension) 19%', 19.000, TRUE, 67)
ON CONFLICT (code) DO UPDATE
  SET label = EXCLUDED.label,
      tax_rate_percent = EXCLUDED.tax_rate_percent,
      sort_order = EXCLUDED.sort_order,
      updated_at = NOW();



INSERT INTO finance_expense_line(
  expense_id, line_no, description, tax_code_key,
  net_amount, tax_rate_percent, tax_amount, gross_amount
)
SELECT e.id,
       1,
       COALESCE(NULLIF(e.cost_category, ''), e.title, 'Rechnungsposition'),
       e.cost_category,
       e.net_amount,
       e.tax_rate_percent,
       e.tax_amount,
       e.gross_amount
  FROM finance_expense e
 WHERE NOT EXISTS (
   SELECT 1 FROM finance_expense_line l WHERE l.expense_id = e.id
 );

INSERT INTO app_page(page_key, path, label, nav_group, sort_order, description)
VALUES ('finance-tax-codes', '/finance-tax-codes', 'MwSt.-Sätze Finanzen', 'admin', 55, 'Kostenarten und MwSt.-Sätze für Rechnungspositionen pflegen')
ON CONFLICT (page_key) DO UPDATE
  SET path = EXCLUDED.path,
      label = EXCLUDED.label,
      nav_group = EXCLUDED.nav_group,
      sort_order = EXCLUDED.sort_order,
      description = EXCLUDED.description;

INSERT INTO app_permission(permission_key, label, category, description)
VALUES
  ('finance.tax_codes', 'MwSt.-Sätze verwalten', 'Finanzen', 'Kostenarten und MwSt.-Sätze für Finanz-Rechnungspositionen pflegen')
ON CONFLICT (permission_key) DO UPDATE
  SET label = EXCLUDED.label,
      category = EXCLUDED.category,
      description = EXCLUDED.description;

INSERT INTO app_role_page(role_profile_id, page_key, can_view)
SELECT rp.id, 'finance-tax-codes', TRUE
  FROM app_role_profile rp
 WHERE rp.code IN ('admin','chair')
ON CONFLICT (role_profile_id, page_key) DO UPDATE SET can_view = TRUE;

INSERT INTO app_role_permission(role_profile_id, permission_key, granted)
SELECT rp.id, 'finance.tax_codes', TRUE
  FROM app_role_profile rp
 WHERE rp.code IN ('admin','chair')
ON CONFLICT (role_profile_id, permission_key) DO UPDATE SET granted = TRUE;

ALTER TABLE finance_tax_code ENABLE ROW LEVEL SECURITY;
ALTER TABLE finance_expense_line ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS finance_tax_code_select ON finance_tax_code;
DROP POLICY IF EXISTS finance_tax_code_modify ON finance_tax_code;
CREATE POLICY finance_tax_code_select ON finance_tax_code FOR SELECT USING (
  app_has_permission('finance.read') OR app_has_permission('finance.tax_codes')
);
CREATE POLICY finance_tax_code_modify ON finance_tax_code FOR ALL USING (
  app_has_permission('finance.tax_codes') OR app_has_permission('finance.budget')
) WITH CHECK (app_has_permission('finance.tax_codes') OR app_has_permission('finance.budget'));

DROP POLICY IF EXISTS finance_expense_line_select ON finance_expense_line;
DROP POLICY IF EXISTS finance_expense_line_modify ON finance_expense_line;
CREATE POLICY finance_expense_line_select ON finance_expense_line FOR SELECT USING (
  app_has_permission('finance.read')
);
CREATE POLICY finance_expense_line_modify ON finance_expense_line FOR ALL USING (
  app_has_permission('finance.write')
) WITH CHECK (app_has_permission('finance.write'));

GRANT SELECT, INSERT, UPDATE, DELETE ON finance_tax_code, finance_expense_line TO br_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO br_app;