-- Migration 066: Issue due dates + file attachments
-- Adds faellig_am column to issues and creates issue_dateien table.

-- ═══════════════════════════════════════════════════════════════════════════
-- 1. Add due date column to issues
-- ═══════════════════════════════════════════════════════════════════════════

ALTER TABLE issues ADD COLUMN IF NOT EXISTS faellig_am TIMESTAMPTZ;

CREATE INDEX IF NOT EXISTS idx_issues_faellig_am ON issues(faellig_am) WHERE faellig_am IS NOT NULL;

-- ═══════════════════════════════════════════════════════════════════════════
-- 2. Issue file attachments
-- ═══════════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS issue_dateien (
  id                UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  issue_id          INT          NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
  dateiname         VARCHAR(500) NOT NULL,
  dateipfad         VARCHAR(1000) NOT NULL,
  dateityp          VARCHAR(100),
  dateigroesse      BIGINT,
  hochgeladen_von   UUID         REFERENCES users(id) ON DELETE SET NULL,
  hochgeladen_am    TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_issue_dateien_issue_id ON issue_dateien(issue_id);
