-- This file is automatically generated using maintenance/generateSchemaSql.php.
-- Source: sql/tables.json
-- Do not modify this file directly.
-- See https://www.mediawiki.org/wiki/Manual:Schema_changes
CREATE TABLE flow_workflow (
  workflow_id TEXT NOT NULL,
  workflow_wiki TEXT NOT NULL,
  workflow_namespace INT NOT NULL,
  workflow_page_id INT NOT NULL,
  workflow_title_text TEXT NOT NULL,
  workflow_name TEXT NOT NULL,
  workflow_last_update_timestamp TIMESTAMPTZ NOT NULL,
  workflow_lock_state INT NOT NULL,
  workflow_type TEXT NOT NULL,
  PRIMARY KEY(workflow_id)
);

CREATE INDEX flow_workflow_lookup ON flow_workflow (
  workflow_wiki, workflow_namespace,
  workflow_title_text
);

CREATE INDEX flow_workflow_update_timestamp ON flow_workflow (workflow_last_update_timestamp);


CREATE TABLE flow_topic_list (
  topic_list_id TEXT NOT NULL,
  topic_id TEXT NOT NULL,
  PRIMARY KEY(topic_list_id, topic_id)
);

CREATE INDEX flow_topic_list_topic_id ON flow_topic_list (topic_id);


CREATE TABLE flow_tree_revision (
  tree_rev_id TEXT NOT NULL,
  tree_rev_descendant_id TEXT NOT NULL,
  tree_orig_user_id BIGINT NOT NULL,
  tree_orig_user_ip TEXT DEFAULT NULL,
  tree_orig_user_wiki TEXT NOT NULL,
  tree_parent_id TEXT DEFAULT NULL,
  PRIMARY KEY(tree_rev_id)
);

CREATE INDEX flow_tree_descendant_rev_id ON flow_tree_revision (
  tree_rev_descendant_id, tree_rev_id
);


CREATE TABLE flow_revision (
  rev_id TEXT NOT NULL,
  rev_type TEXT NOT NULL,
  rev_type_id TEXT DEFAULT '' NOT NULL,
  rev_user_id BIGINT NOT NULL,
  rev_user_ip TEXT DEFAULT NULL,
  rev_user_wiki TEXT NOT NULL,
  rev_parent_id TEXT DEFAULT NULL,
  rev_flags TEXT NOT NULL,
  rev_content TEXT NOT NULL,
  rev_change_type TEXT DEFAULT NULL,
  rev_mod_state TEXT NOT NULL,
  rev_mod_user_id BIGINT DEFAULT NULL,
  rev_mod_user_ip TEXT DEFAULT NULL,
  rev_mod_user_wiki TEXT DEFAULT NULL,
  rev_mod_timestamp TIMESTAMPTZ DEFAULT NULL,
  rev_mod_reason TEXT DEFAULT NULL,
  rev_last_edit_id TEXT DEFAULT NULL,
  rev_edit_user_id BIGINT DEFAULT NULL,
  rev_edit_user_ip TEXT DEFAULT NULL,
  rev_edit_user_wiki TEXT DEFAULT NULL,
  rev_content_length INT DEFAULT 0 NOT NULL,
  rev_previous_content_length INT DEFAULT 0 NOT NULL,
  PRIMARY KEY(rev_id)
);

CREATE UNIQUE INDEX flow_revision_unique_parent ON flow_revision (rev_parent_id);

CREATE INDEX flow_revision_type_id ON flow_revision (rev_type, rev_type_id);

CREATE INDEX flow_revision_user ON flow_revision (
  rev_user_id, rev_user_ip, rev_user_wiki
);


CREATE TABLE flow_tree_node (
  tree_ancestor_id TEXT NOT NULL,
  tree_descendant_id TEXT NOT NULL,
  tree_depth SMALLINT NOT NULL,
  PRIMARY KEY(
    tree_ancestor_id, tree_descendant_id
  )
);

CREATE UNIQUE INDEX flow_tree_constraint ON flow_tree_node (tree_descendant_id, tree_depth);


CREATE TABLE flow_wiki_ref (
  ref_id TEXT NOT NULL,
  ref_src_wiki TEXT NOT NULL,
  ref_src_object_id TEXT NOT NULL,
  ref_src_object_type TEXT NOT NULL,
  ref_src_workflow_id TEXT NOT NULL,
  ref_src_namespace INT NOT NULL,
  ref_src_title TEXT NOT NULL,
  ref_target_namespace INT NOT NULL,
  ref_target_title TEXT NOT NULL,
  ref_type TEXT NOT NULL,
  PRIMARY KEY(ref_id)
);

CREATE INDEX flow_wiki_ref_idx_v2 ON flow_wiki_ref (
  ref_src_wiki, ref_src_namespace,
  ref_src_title, ref_type, ref_target_namespace,
  ref_target_title, ref_src_object_type,
  ref_src_object_id
);

CREATE INDEX flow_wiki_ref_revision_v2 ON flow_wiki_ref (
  ref_src_wiki, ref_src_namespace,
  ref_src_title, ref_src_object_type,
  ref_src_object_id, ref_type, ref_target_namespace,
  ref_target_title
);


CREATE TABLE flow_ext_ref (
  ref_id TEXT NOT NULL,
  ref_src_wiki TEXT NOT NULL,
  ref_src_object_id TEXT NOT NULL,
  ref_src_object_type TEXT NOT NULL,
  ref_src_workflow_id TEXT NOT NULL,
  ref_src_namespace INT NOT NULL,
  ref_src_title TEXT NOT NULL,
  ref_target TEXT NOT NULL,
  ref_type TEXT NOT NULL,
  PRIMARY KEY(ref_id)
);

CREATE INDEX flow_ext_ref_idx_v3 ON flow_ext_ref (
  ref_src_wiki, ref_src_namespace,
  ref_src_title, ref_type, ref_target,
  ref_src_object_type, ref_src_object_id
);

CREATE INDEX flow_ext_ref_revision_v2 ON flow_ext_ref (
  ref_src_wiki, ref_src_namespace,
  ref_src_title, ref_src_object_type,
  ref_src_object_id, ref_type, ref_target
);
