-- Migration 033: Create befoerderungen table (FDISK sync)
CREATE TABLE IF NOT EXISTS befoerderungen (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  datum           DATE,
  dienstgrad      VARCHAR(64) NOT NULL,
  fdisk_sync_key  VARCHAR(255),
  created_at      TIMESTAMPTZ DEFAULT NOW(),
  updated_at      TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(user_id, fdisk_sync_key)
);

CREATE INDEX IF NOT EXISTS idx_befoerderungen_user_id ON befoerderungen(user_id);
