import { server, } from './query';

const data = `
  INSERT INTO schools (address, approved, name) VALUES ('Staničná 8, Trenčín', TRUE, 'Stredná umelecká škola');
  INSERT INTO schools (address, approved, name) VALUES ('Nad Ovčírnou IV 2528, Zlín', TRUE, 'Střední průmyslová škola polytechnická');

  INSERT INTO years (id, name, opened) VALUES (2017, 'Divadelný plagát', FALSE);
  INSERT INTO years (id, name, opened) VALUES (2018, 'Módny plagát', TRUE);
`;

const functions = `
  CREATE FUNCTION delete_expired() RETURNS TRIGGER
    LANGUAGE plpgsql
      AS $$
        BEGIN
          DELETE FROM keys WHERE expires_at < CURRENT_TIMESTAMP;
          RETURN NULL;
        END;
      $$;

  CREATE FUNCTION validate_key(id CHARACTER VARYING, email CHARACTER VARYING) RETURNS BOOLEAN
    LANGUAGE sql
      AS $$
        SELECT (EXISTS (SELECT 1 FROM keys WHERE id = $1 AND email = $2 AND expires_at > CURRENT_TIMESTAMP));
      $$;

  CREATE TRIGGER delete_expired BEFORE INSERT ON keys EXECUTE PROCEDURE delete_expired();
`;

(async () => {
  try {
    (await server(`
      SET TIME ZONE 'UTC';

      DROP SCHEMA IF EXISTS public CASCADE;
      CREATE SCHEMA public AUTHORIZATION marekkobida;

      CREATE TABLE keys (
        id          CHARACTER VARYING (255)  NOT NULL,
        created_at  TIMESTAMP                DEFAULT CURRENT_TIMESTAMP NOT NULL,
        email       CHARACTER VARYING (255)  NOT NULL,
        expires_at  TIMESTAMP                DEFAULT (CURRENT_TIMESTAMP + INTERVAL '4 MINUTES') NOT NULL
      );

      CREATE TABLE posters (
        id          BIGSERIAL                NOT NULL,
        approved    BOOLEAN                  DEFAULT FALSE NOT NULL,
        created_at  TIMESTAMP                DEFAULT CURRENT_TIMESTAMP NOT NULL,
        email       CHARACTER VARYING (255)  NOT NULL,
        first_name  CHARACTER VARYING (255)  NOT NULL,
        last_name   CHARACTER VARYING (255)  NOT NULL,
        name        CHARACTER VARYING (255)  NOT NULL,
        poster      CHARACTER VARYING (255)  NOT NULL,
        school_id   BIGINT                   NOT NULL,
        year_id     BIGINT                   NOT NULL
      );

      CREATE TABLE schools (
        id          BIGSERIAL                NOT NULL,
        address     CHARACTER VARYING (255)  NOT NULL,
        approved    BOOLEAN                  DEFAULT FALSE NOT NULL,
        created_at  TIMESTAMP                DEFAULT CURRENT_TIMESTAMP NOT NULL,
        name        CHARACTER VARYING (255)  NOT NULL
      );

      CREATE TABLE years (
        id          BIGINT                   NOT NULL,
        created_at  TIMESTAMP                DEFAULT CURRENT_TIMESTAMP NOT NULL,
        name        CHARACTER VARYING (255)  NOT NULL,
        opened      BOOLEAN                  DEFAULT FALSE NOT NULL
      );

      ALTER TABLE keys     ADD CONSTRAINT keys_id_email_pkey         PRIMARY KEY (id, email);
      ALTER TABLE posters  ADD CONSTRAINT posters_email_year_id_key  UNIQUE (email, year_id);
      ALTER TABLE posters  ADD CONSTRAINT posters_pkey               PRIMARY KEY (id);
      ALTER TABLE schools  ADD CONSTRAINT schools_pkey               PRIMARY KEY (id);
      ALTER TABLE years    ADD CONSTRAINT years_pkey                 PRIMARY KEY (id);

      ALTER TABLE posters  ADD CONSTRAINT posters_school_id_fkey     FOREIGN KEY (school_id)  REFERENCES schools (id)  ON DELETE CASCADE ON UPDATE CASCADE;
      ALTER TABLE posters  ADD CONSTRAINT posters_year_id_fkey       FOREIGN KEY (year_id)    REFERENCES years (id)    ON DELETE CASCADE ON UPDATE CASCADE;

      ${functions}

      ${data}
    `));

    process.exit();
  } catch (e) {
    console.log(e);
  }
})();