CREATE TABLE IF NOT EXISTS app_settings (
  setting_key VARCHAR(120) PRIMARY KEY,
  setting_value TEXT NOT NULL,
  description VARCHAR(255) NOT NULL DEFAULT ''
);
CREATE TABLE IF NOT EXISTS status_options (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  scope ENUM('request','quest') NOT NULL,
  value VARCHAR(60) NOT NULL,
  label VARCHAR(120) NOT NULL,
  sort_order INT NOT NULL DEFAULT 100,
  is_terminal BOOLEAN NOT NULL DEFAULT false,
  UNIQUE KEY uq_status_scope_value (scope, value)
);
CREATE TABLE IF NOT EXISTS quest_templates (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  type VARCHAR(120) NOT NULL UNIQUE,
  title_template VARCHAR(255) NOT NULL,
  department_id BIGINT NULL,
  reward_percent DECIMAL(6,4) NOT NULL DEFAULT 0,
  enabled BOOLEAN NOT NULL DEFAULT true,
  sort_order INT NOT NULL DEFAULT 100,
  CONSTRAINT fk_template_dept FOREIGN KEY (department_id) REFERENCES departments(id)
);

INSERT IGNORE INTO app_settings(setting_key,setting_value,description) VALUES
('currency','aUEC','Währung für Preise und Belohnungen'),
('default_margin_percent','10','Standardmarge der Trading-Abteilung in Prozent'),
('auto_generate_quests','true','Automatisch Quests aus neuen Anfragen erzeugen'),
('admin_emails','','Kommagetrennte Admin-E-Mails. Leer bedeutet: jeder eingeloggte Nutzer darf Admin-Seiten öffnen.');

INSERT IGNORE INTO status_options(scope,value,label,sort_order,is_terminal) VALUES
('request','new','Neu',10,false),('request','quoted','Preisvorschlag',20,false),('request','approved','Freigegeben',30,false),('request','fulfilled','Erfüllt',90,true),('request','cancelled','Abgebrochen',99,true),
('quest','waiting','Wartet auf Abhängigkeit',5,false),('quest','open','Offen',10,false),('quest','accepted','Angenommen',20,false),('quest','in_progress','In Arbeit',30,false),('quest','blocked','Blockiert',40,false),('quest','change_requested','Änderung angefragt',50,false),('quest','done','Erledigt',90,true),('quest','cancelled','Abgebrochen',99,true);

INSERT IGNORE INTO quest_templates(type,title_template,department_id,reward_percent,sort_order)
SELECT 'logistics_quote','Materialbedarf und Transport prüfen',(SELECT id FROM departments WHERE name='Logistik'),0.0300,10;
INSERT IGNORE INTO quest_templates(type,title_template,department_id,reward_percent,sort_order)
SELECT 'mining_procure','Ressourcen für {{item}} beschaffen',(SELECT id FROM departments WHERE name='Mining'),0.2500,20;
INSERT IGNORE INTO quest_templates(type,title_template,department_id,reward_percent,sort_order)
SELECT 'crafting_order','{{item}} herstellen oder bereitstellen',(SELECT id FROM departments WHERE name='Crafting'),0.1500,30;
INSERT IGNORE INTO quest_templates(type,title_template,department_id,reward_percent,sort_order)
SELECT 'delivery','{{item}} an Zielabteilung liefern',(SELECT id FROM departments WHERE name='Logistik'),0.0500,40;