DB-create-SQL

CREATE DATABASE MMA




CREATE TABLE weight__classes
(
	id SERIAL,
	name VARCHAR(100) NOT NULL,
	PRIMARY KEY (id)
);


CREATE TABLE fighters
(
	id SERIAL,
	name VARCHAR(100) NOT NULL,
	nickname VARCHAR(50),
	nationality VARCHAR(100),
	birth_date date NOT NULL,
	weight INT NOT NULL,
	weight_class INT REFERENCES weight__classes(id) NOT NULL,
	wins INT DEFAULT 0,
	losses INT DEFAULT 0,
	knockouts INT DEFAULT 0,
	submissions INT DEFAULT 0,
	PRIMARY KEY (id)
);


ALTER TABLE fighters
ADD COLUMN status SMALLINT DEFAULT 0;

COMMENT ON table fighters IS 'career status of a fighter, 0 - active, 1 - retired; some other states can be added';


CREATE TABLE locations
(
	id SERIAL,
	name VARCHAR(100) NOT NULL,
	PRIMARY KEY (id)
);


CREATE TABLE events
(
	id SERIAL,
	name VARCHAR(100) NOT NULL,
	location INT REFERENCES locations(id) NOT NULL,
	date date NOT NULL,
	description TEXT,
	PRIMARY KEY (id)
);


CREATE TABLE fights
(
	id SERIAL,
	name VARCHAR(100) NOT NULL,
	opponent_one INT REFERENCES fighters(id) NOT NULL,
	opponent_two INT REFERENCES fighters(id) NOT NULL,
	winner INT REFERENCES fighters(id),
	state SMALLINT DEFAULT 0 NOT NULL,
	weight_class INT REFERENCES weight__classes(id) NOT NULL,
	event INT REFERENCES events(id) NOT NULL,
"time" time with time zone,
	PRIMARY KEY (id)
);

COMMENT ON TABLE fights
IS 'states of fights: 0 - did not happen; 1 - finished; 2 - canceled; 3 - postponed';


CREATE TABLE ranking
(
	id SERIAL,
	fighter INT REFERENCES fighters(id) NOT NULL,
	weight_class INT REFERENCES weight__classes(id) NOT NULL,
	rank INT NOT NULL,
	PRIMARY KEY (id)
);



CREATE OR REPLACE FUNCTION add_into_ranking()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO ranking (fighter, weight_class, rank)
    VALUES (NEW.id, NEW.weight_class, COALESCE((SELECT MAX(rank) + 1 FROM ranking WHERE weight_class = NEW.weight_class), 1));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER after_insert_fighter
AFTER INSERT ON fighters
FOR EACH ROW
EXECUTE FUNCTION add_into_ranking();