CREATE TABLE vendors(
   vendor_id INTEGER PRIMARY KEY,
   name TEXT NOT NULL,
   description TEXT
);

CREATE TABLE categories(
   category_id INTEGER PRIMARY KEY,
   name TEXT NOT NULL,
   description TEXT
);

CREATE TABLE tags(
   tag_id INTEGER PRIMARY KEY,
   name TEXT NOT NULL,
   description TEXT
);

CREATE TABLE project_info(
   project_info_id INTEGER PRIMARY KEY,
   name TEXT NOT NULL,
   vendors_fk INTEGER NOT NULL,
   categories_fk INTEGER NOT NULL,
   FOREIGN KEY(vendors_fk) REFERENCES vendors(vendor_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
   FOREIGN KEY(categories_fk) REFERENCES categories(category_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);
CREATE INDEX project_info__vendors ON project_info(vendors_fk);
CREATE INDEX project_info__categories ON project_info(categories_fk);

CREATE TABLE components(
   component_id INTEGER PRIMARY KEY,
   name TEXT NOT NULL,
   description TEXT
);

CREATE TABLE project_tags(
   project_tag_id INTEGER PRIMARY KEY,
   tags_fk INTEGER NOT NULL,
   project_info_fk INTEGER NOT NULL,
   FOREIGN KEY(tags_fk) REFERENCES tags(tag_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
   FOREIGN KEY(project_info_fk) REFERENCES project_info(project_info_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);
CREATE INDEX project_tags__tags ON project_tags(tags_fk);
CREATE INDEX project_tags__project_info ON project_tags(project_info_fk);

CREATE TABLE project_components(
   project_component_id INTEGER PRIMARY KEY,
   project_info_fk INTEGER NOT NULL,
   components_fk INTEGER NOT NULL,
   FOREIGN KEY(project_info_fk) REFERENCES project_info(project_info_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
   FOREIGN KEY(components_fk) REFERENCES components(component_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);
CREATE INDEX project_components__project_info ON project_components(project_info_fk);
CREATE INDEX project_components__components ON project_components(components_fk);

CREATE TABLE classes(
   classe_id INTEGER PRIMARY KEY,
   name TEXT NOT NULL,
   components_fk INTEGER NOT NULL,
   FOREIGN KEY(components_fk) REFERENCES components(component_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);
CREATE INDEX classes__components ON classes(components_fk);

CREATE TABLE shared_libs(
   shared_lib_id INTEGER PRIMARY KEY,
   name TEXT NOT NULL,
   project_info_fk INTEGER,
   FOREIGN KEY(project_info_fk) REFERENCES project_info(project_info_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);
CREATE INDEX shared_libs__project_info ON shared_libs(project_info_fk);

CREATE TABLE file_info(
   file_info_id INTEGER PRIMARY KEY,
   name TEXT NOT NULL,
   project_info_fk INTEGER NOT NULL,
   FOREIGN KEY(project_info_fk) REFERENCES project_info(project_info_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);
CREATE INDEX file_info__project_info ON file_info(project_info_fk);

CREATE TABLE member_types(
   member_type_id INTEGER PRIMARY KEY,
   name TEXT NOT NULL,
   description TEXT
);

CREATE TABLE lib_usage(
   lib_usage_id INTEGER PRIMARY KEY,
   shared_libs_fk INTEGER NOT NULL,
   file_info_fk INTEGER NOT NULL,
   FOREIGN KEY(shared_libs_fk) REFERENCES shared_libs(shared_lib_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
   FOREIGN KEY(file_info_fk) REFERENCES file_info(file_info_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);
CREATE INDEX lib_usage__shared_libs ON lib_usage(shared_libs_fk);
CREATE INDEX lib_usage__file_info ON lib_usage(file_info_fk);

CREATE TABLE class_usage(
   class_usage_id INTEGER PRIMARY KEY,
   file_info_fk INTEGER NOT NULL,
   classes_fk INTEGER NOT NULL,
   FOREIGN KEY(file_info_fk) REFERENCES file_info(file_info_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
   FOREIGN KEY(classes_fk) REFERENCES classes(classe_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);
CREATE INDEX class_usage__file_info ON class_usage(file_info_fk);
CREATE INDEX class_usage__classes ON class_usage(classes_fk);

CREATE TABLE members(
   member_id INTEGER PRIMARY KEY,
   name TEXT NOT NULL,
   classes_fk INTEGER NOT NULL,
   member_types_fk INTEGER NOT NULL,
   FOREIGN KEY(classes_fk) REFERENCES classes(classe_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
   FOREIGN KEY(member_types_fk) REFERENCES member_types(member_type_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);
CREATE INDEX members__classes ON members(classes_fk);
CREATE INDEX members__member_types ON members(member_types_fk);

CREATE TABLE functions(
   function_id INTEGER PRIMARY KEY,
   name TEXT NOT NULL,
   shared_libs_fk INTEGER NOT NULL,
   FOREIGN KEY(shared_libs_fk) REFERENCES shared_libs(shared_lib_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);
CREATE INDEX functions__shared_libs ON functions(shared_libs_fk);

CREATE TABLE text_lines(
   text_line_id INTEGER PRIMARY KEY,
   name TEXT NOT NULL,
   file_info_fk INTEGER NOT NULL,
   FOREIGN KEY(file_info_fk) REFERENCES file_info(file_info_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);
CREATE INDEX text_lines__file_info ON text_lines(file_info_fk);

CREATE TABLE function_usage(
   function_usage_id INTEGER PRIMARY KEY,
   functions_fk INTEGER NOT NULL,
   text_lines_fk INTEGER NOT NULL,
   FOREIGN KEY(functions_fk) REFERENCES functions(function_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
   FOREIGN KEY(text_lines_fk) REFERENCES text_lines(text_line_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);
CREATE INDEX function_usage__functions ON function_usage(functions_fk);
CREATE INDEX function_usage__text_lines ON function_usage(text_lines_fk);

CREATE TABLE member_usage(
   member_usage_id INTEGER PRIMARY KEY,
   text_lines_fk INTEGER NOT NULL,
   members_fk INTEGER NOT NULL,
   FOREIGN KEY(text_lines_fk) REFERENCES text_lines(text_line_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
   FOREIGN KEY(members_fk) REFERENCES members(member_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);
CREATE INDEX member_usage__text_lines ON member_usage(text_lines_fk);
CREATE INDEX member_usage__members ON member_usage(members_fk);

CREATE TABLE keywords(
   keyword_id INTEGER PRIMARY KEY,
   name TEXT NOT NULL,
   description TEXT
);

CREATE TABLE keyword_usage(
   keyword_usage_id INTEGER PRIMARY KEY,
   text_lines_fk INTEGER NOT NULL,
   keywords_fk INTEGER NOT NULL,
   FOREIGN KEY(text_lines_fk) REFERENCES text_lines(text_line_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
   FOREIGN KEY(keywords_fk) REFERENCES keywords(keyword_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);
CREATE INDEX keyword_usage__text_lines ON keyword_usage(text_lines_fk);
CREATE INDEX keyword_usage__keywords ON keyword_usage(keywords_fk);