DROP TABLE IF EXISTS selection;
DROP TABLE IF EXISTS subevent;
DROP TABLE IF EXISTS event;
DROP TABLE IF EXISTS betfair;
CREATE TABLE betfair (
      id    serial,
      sport VARCHAR(16),
      PRIMARY KEY(id)
    );
CREATE TABLE event (
      betfair_id INT,
      id         serial,
      name       VARCHAR(128),
      DATE       DATE,
      FOREIGN KEY(betfair_id)
        REFERENCES betfair(id),
      PRIMARY KEY(betfair_id, id)
    );
CREATE TABLE subevent (
      betfair_id INT,
      event_id   INT,
      id         INT,
      title      VARCHAR(64),
      DATE       DATE,
      TIME       TIME,
      totalAmountMatched INT, -- I do not sure what it means
      FOREIGN KEY(betfair_id, event_id)
        REFERENCES event(betfair_id, id),
      PRIMARY KEY(betfair_id, event_id, id)
    );
CREATE TABLE selection(
      betfair_id  INT,
      event_id    INT,
      subevent_id INT,
      id          INT,
      name        VARCHAR(64),
      backp1 DECIMAL(12, 2),
      backs1 DECIMAL(12, 2),
      layp1 DECIMAL(12, 2),
      lays1 DECIMAL(12, 2),
      backp2 DECIMAL(12, 2),
      backs2 DECIMAL(12, 2),
      layp2 DECIMAL(12, 2),
      lays2 DECIMAL(12, 2),
      backp3 DECIMAL(12, 2),
      backs3 DECIMAL(12, 2),
      layp3 DECIMAL(12, 2),
      lays3 DECIMAL(12, 2),
      FOREIGN KEY(betfair_id, event_id, subevent_id)
        REFERENCES subevent(betfair_id, event_id, id),
      PRIMARY KEY(betfair_id, event_id, subevent_id, id, name)
    );
PREPARE betfair_insert AS INSERT INTO betfair(
        sport
    ) VALUES($1) RETURNING id;
PREPARE event_insert AS INSERT INTO event(
         betfair_id,
         name,
         DATE
    ) VALUES($1, $2, $3) RETURNING id;
PREPARE subevent_insert AS INSERT INTO subevent(
         betfair_id,
         event_id,
         id,
         title,
         DATE,
         TIME,
         totalAmountMatched
    ) VALUES($1, $2, $3, $4, $5, $6, $7) RETURNING id;
PREPARE selection_insert AS INSERT INTO selection(
        betfair_id,
        event_id,
        subevent_id,
        id,
        name,
        backp1,
        backs1,
        layp1,
        lays1,
        backp2,
        backs2,
        layp2,
        lays2,
        backp3,
        backs3,
        layp3,
        lays3
    ) VALUES($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17) RETURNING id;