SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS operational_inbox_items (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,workspace_id BIGINT UNSIGNED NOT NULL,item_key CHAR(64) NOT NULL,item_type VARCHAR(80) NOT NULL,entity_type VARCHAR(80) NOT NULL,entity_id BIGINT UNSIGNED NOT NULL,priority ENUM('critical','high','medium','low') NOT NULL DEFAULT 'medium',title VARCHAR(255) NOT NULL,action_url VARCHAR(500) NULL,status ENUM('open','snoozed','resolved') NOT NULL DEFAULT 'open',assigned_user_id BIGINT UNSIGNED NULL,due_at DATETIME NULL,snoozed_until DATETIME NULL,resolved_at DATETIME NULL,resolved_by BIGINT UNSIGNED NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,UNIQUE KEY uq_oi_workspace_key(workspace_id,item_key),INDEX idx_oi_ws_status_priority(workspace_id,status,priority),CONSTRAINT fk_oi_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,CONSTRAINT fk_oi_assigned FOREIGN KEY(assigned_user_id) REFERENCES users(id) ON DELETE SET NULL,CONSTRAINT fk_oi_resolved FOREIGN KEY(resolved_by) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS operational_inbox_events (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,workspace_id BIGINT UNSIGNED NOT NULL,item_id BIGINT UNSIGNED NOT NULL,event_type VARCHAR(80) NOT NULL,user_id BIGINT UNSIGNED NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,INDEX idx_oie_item_time(item_id,created_at),CONSTRAINT fk_oie_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,CONSTRAINT fk_oie_item FOREIGN KEY(item_id) REFERENCES operational_inbox_items(id) ON DELETE CASCADE,CONSTRAINT fk_oie_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
