CREATE TABLE IF NOT EXISTS user_roles (
  user_id BIGINT NOT NULL,
  role VARCHAR(60) NOT NULL,
  PRIMARY KEY(user_id, role),
  CONSTRAINT fk_user_role_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS department_members (
  user_id BIGINT NOT NULL,
  department_id BIGINT NOT NULL,
  role VARCHAR(60) NOT NULL DEFAULT 'member',
  PRIMARY KEY(user_id, department_id),
  CONSTRAINT fk_dept_member_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_dept_member_dept FOREIGN KEY(department_id) REFERENCES departments(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS quest_dependencies (
  quest_id BIGINT NOT NULL,
  depends_on_quest_id BIGINT NOT NULL,
  PRIMARY KEY(quest_id, depends_on_quest_id),
  CONSTRAINT fk_qdep_quest FOREIGN KEY(quest_id) REFERENCES quests(id) ON DELETE CASCADE,
  CONSTRAINT fk_qdep_depends FOREIGN KEY(depends_on_quest_id) REFERENCES quests(id) ON DELETE CASCADE
);
ALTER TABLE quests ADD COLUMN IF NOT EXISTS claimed_by BIGINT NULL;
ALTER TABLE quests ADD COLUMN IF NOT EXISTS sequence_order INT NOT NULL DEFAULT 100;
ALTER TABLE quests ADD COLUMN IF NOT EXISTS blocked_reason TEXT NULL;
ALTER TABLE quests ADD COLUMN IF NOT EXISTS completed_at TIMESTAMP NULL;
ALTER TABLE trade_requests ADD COLUMN IF NOT EXISTS fulfilled_at TIMESTAMP NULL;
ALTER TABLE trade_requests ADD COLUMN IF NOT EXISTS workflow_rule_id BIGINT NULL;
ALTER TABLE trade_requests ADD COLUMN IF NOT EXISTS notes TEXT NULL;
CREATE INDEX IF NOT EXISTS idx_quests_dept_status ON quests(department_id,status);
CREATE INDEX IF NOT EXISTS idx_quests_request ON quests(parent_request_id);
CREATE INDEX IF NOT EXISTS idx_requests_status ON trade_requests(status);

INSERT IGNORE INTO app_settings(setting_key,setting_value,description) VALUES
('default_request_status_after_quest_generation','in_progress','Status einer Anfrage nach Quest-Erzeugung'),
('unlock_next_quests_automatically','true','Folge-Quests automatisch öffnen, wenn Abhängigkeiten erledigt sind');