-- Создание таблицы игроков
CREATE TABLE players(
  dci INTEGER PRIMARY KEY CHECK (dci > 0),
  first_name CHAR(50) NOT NULL,
  last_name CHAR(50) NOT NULL,
  birth DATE,
  country CHAR(50) NOT NULL,
  judge_level INTEGER
);

-- Создание таблицы форматов
CREATE TABLE formats(
  name CHAR(10) PRIMARY KEY,
  casual BOOLEAN NOT NULL
);


-- Создание таблицы турниров
CREATE TABLE tournaments(
  id SERIAL PRIMARY KEY,
  format_name CHAR(10) NOT NULL REFERENCES formats (name) ON DELETE RESTRICT ON UPDATE CASCADE,
  date DATE NOT NULL,
  location CHAR(50) NOT NULL,
  judge INTEGER NOT NULL REFERENCES players (dci) ON DELETE RESTRICT ON UPDATE CASCADE
);


-- Создание триггера на проерку действительно ли судья турнира - судья
CREATE FUNCTION check_judge() RETURNS TRIGGER AS $cTrig$
  BEGIN
    -- проверка на то, является ли игрок с данным DCI судьей
    IF (SELECT judge_level FROM players WHERE players.dci = NEW.judge) IS NULL THEN
      RAISE EXCEPTION 'Wrong DCI (%) of judge!', NEW.judge;
    END IF;

    -- проверяем, что данный судья может судить турниры данного формата
    IF NOT (SELECT casual FROM formats WHERE formats.name = NEW.format_name) THEN
      IF (SELECT judge_level FROM players WHERE players.dci = NEW.judge) = 0 THEN
        RAISE EXCEPTION 'Judge with DCI % have lvl 0 and cant judge tournament of format %', NEW.judge, NEW.format_name;
      END IF;
    END IF;

    RETURN NEW;
  END;
$cTrig$ LANGUAGE plpgsql;

CREATE TRIGGER check_judge_trigger BEFORE INSERT OR UPDATE ON tournaments
  FOR EACH ROW EXECUTE PROCEDURE check_judge();

-- Создание таблицы карт
CREATE TABLE cards(
  name CHAR(50) PRIMARY KEY,
  text CHAR(300) NOT NULL
);

-- Создание таблицы колод
CREATE TABLE decks(
  id SERIAL PRIMARY KEY ,
  name CHAR(50) NOT NULL,
  format_name CHAR(10) NOT NULL REFERENCES formats (name) ON DELETE RESTRICT ON UPDATE CASCADE
);

-- Создание таблицы, содержащей информация по картам, содержащимся в колоде
CREATE TABLE deck_card(
  deck_id INTEGER REFERENCES decks (id) ON DELETE CASCADE ON UPDATE CASCADE,
  card_name CHAR(50) REFERENCES cards (name) ON DELETE CASCADE ON UPDATE CASCADE,
  copies INTEGER CHECK (copies < 5 AND copies > 0),

  PRIMARY KEY (deck_id, card_name)
);

-- Создание таблицы с данными о турнирах, участниках турнира, колодах участников и результатах участников
CREATE TABLE participations(
  dci INTEGER REFERENCES players (dci) ON DELETE CASCADE ON UPDATE CASCADE,
  tournament_id INTEGER REFERENCES tournaments (id) ON DELETE CASCADE,
  deck_id INTEGER REFERENCES decks (id) ON DELETE CASCADE,
  wins INTEGER NOT NULL CHECK (wins >= 0),
  loses INTEGER NOT NULL CHECK (loses >= 0),
  draws INTEGER NOT NULL CHECK (draws >= 0),

  PRIMARY KEY (dci, tournament_id)
);

-- Создание таблицы сетов
CREATE TABLE expansions(
  name CHAR(50) PRIMARY KEY,
  release DATE
);

-- Создание таблицы с информацией о цене карты из конкретного сета и ее изображении
CREATE TABLE expansion_card_price(
  card_name CHAR(50) REFERENCES cards (name) ON DELETE CASCADE ON UPDATE CASCADE,
  expansion_name CHAR(50) REFERENCES expansions (name) ON DELETE CASCADE ON UPDATE CASCADE,
  price REAL NOT NULL,
  image_url CHAR(100) NOT NULL,

  PRIMARY KEY (card_name, expansion_name)
);

-- Создание таблицы легальности сетов в форматах
CREATE TABLE format_expansion(
  expansion_name CHAR(50) REFERENCES expansions (name) ON DELETE CASCADE ON UPDATE CASCADE,
  format_name CHAR(50) REFERENCES formats (name) ON DELETE CASCADE ON UPDATE CASCADE,

  PRIMARY KEY (expansion_name, format_name)
);