36 lines
2.0 KiB
SQL
36 lines
2.0 KiB
SQL
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');
|