CREATE TABLE product (
	product_name varChar(50) PRIMARY KEY,
	company_name varChar(50) NOT NULL 
);

CREATE TABLE drug (
	drug_name varchar(50) PRIMARY KEY
);

CREATE TABLE product_drug_relation (
	product_name varChar(50),
	drug_name varChar(50),
	
	FOREIGN KEY (product_name) REFERENCES product(product_name),
	FOREIGN KEY (drug_name) REFERENCES drug(drug_name),
	
	PRIMARY KEY (product_name, drug_name)
);

CREATE SEQUENCE drug_category_serial START 1;

CREATE TABLE drug_category (
	drug_category_ID integer PRIMARY KEY DEFAULT nextval('drug_category_serial'),
	drug_category_name varChar(30) UNIQUE NOT NULL
);

CREATE TABLE drug_in_category (
	drug_name varChar(50),
	drug_category_ID integer,
	
	FOREIGN KEY (drug_name) REFERENCES drug(drug_name),
	FOREIGN KEY (drug_category_ID) REFERENCES drug_category(drug_category_ID),
	
	PRIMARY KEY(drug_name, drug_category_ID)
);

CREATE TABLE disease_category (
	disease_category_ID integer PRIMARY KEY,
	disease_category_name varChar(50) UNIQUE NOT NULL
);

CREATE TABLE disease (
	disease_name varChar(50) PRIMARY KEY,
	disease_category_ID integer,
	
	FOREIGN KEY (disease_category_ID) REFERENCES disease_category(disease_category_ID)
);

CREATE TABLE treats (
	drug_name varChar(50),
	disease_name varChar(50),
	
	FOREIGN KEY (drug_name) REFERENCES drug(drug_name),
	FOREIGN KEY (disease_name) REFERENCES disease(disease_name),
	
	PRIMARY KEY(drug_name, disease_name)
);

CREATE SEQUENCE side_effect_serial START 1;

CREATE TABLE side_effect (
	side_effect_ID integer PRIMARY KEY DEFAULT nextval('side_effect_serial'),
	side_effect_name varChar(30) UNIQUE NOT NULL
);

CREATE TABLE drug_side_effects (
	drug_name varChar(50),
	side_effect_ID integer,
	
	FOREIGN KEY (drug_name) REFERENCES drug(drug_name),
	FOREIGN KEY (side_effect_ID) REFERENCES side_effect(side_effect_ID),
	
	PRIMARY KEY(drug_name, side_effect_ID)
);

CREATE TABLE drug_interaction (
	drug_name_A varChar(50),
	drug_name_B varChar(50),
	
	FOREIGN KEY (drug_name_A) REFERENCES drug(drug_name),
	FOREIGN KEY (drug_name_B) REFERENCES drug(drug_name),
	
	PRIMARY KEY(drug_name_A,drug_name_B)
);

CREATE TABLE institution (
	institution_name varChar(50) PRIMARY KEY,
	address varChar(50) NOT NULL,
	country varChar(50) NOT NULL,

	UNIQUE (address, country)
);

CREATE SEQUENCE researcher_serial START 1;

CREATE TABLE researcher (
	researcher_ID integer PRIMARY KEY DEFAULT nextval('researcher_serial'),
	researcher_name varChar(50) NOT NULL,
	institution_name varChar(50),
	
	FOREIGN KEY (institution_name) REFERENCES institution(institution_name)
);

CREATE SEQUENCE condition_serial START 1;

CREATE TABLE condition (
	condition_ID integer PRIMARY KEY DEFAULT nextval('condition_serial'),
	condition_name varChar(30) UNIQUE NOT NULL
);

CREATE TABLE trial (
	trial_ID integer PRIMARY KEY,
	title varChar(100)  UNIQUE NOT NULL,
	start_date time NOT NULL,
	completion_date time NOT NULL,
	participants integer NOT NULL,
	status varChar(20) NOT NULL,
	institution_name varChar(50),
	main_researcher_ID integer,
	condition_ID integer,
	
	FOREIGN KEY (institution_name) REFERENCES institution(institution_name),
	FOREIGN KEY (main_researcher_ID) REFERENCES researcher(researcher_ID),
	FOREIGN KEY (condition_ID) REFERENCES condition(condition_ID)
);

CREATE TABLE trial_researchers (
    trial_ID integer,
	researcher_ID integer,
	
	FOREIGN KEY (trial_ID) REFERENCES trial(trial_ID),
	FOREIGN KEY (researcher_ID) REFERENCES researcher(researcher_ID),
	
	PRIMARY KEY(trial_ID, researcher_ID)
);

CREATE TABLE trial_drug_disease (
    trial_ID integer,
	drug_name varChar(50),
	disease_name varChar(50),
	
	FOREIGN KEY (trial_ID) REFERENCES trial(trial_ID),
	FOREIGN KEY (drug_name) REFERENCES drug(drug_name),
	FOREIGN KEY (disease_name) REFERENCES disease(disease_name),
	
	PRIMARY KEY(trial_ID, drug_name, disease_name)
);