-- vytvorenie tabuliek
CREATE TABLE "auction" (
  "id" serial NOT NULL,
  "name" character varying(200) NOT NULL,
  "description" character varying NOT NULL,
  "created" timestamptz NOT NULL,
  "start" timestamptz NOT NULL,
  "end" timestamptz NULL,
  "auction_timeout" smallint NOT NULL DEFAULT '3',
  "seller" integer NOT NULL,
  "starting_bid" numeric(10, 4) NOT NULL,
  "winning_bid" integer NULL
);

CREATE TABLE "auction_bid" (
  "id" serial NOT NULL,
  "auction" integer NOT NULL,
  "timestamp" timestamptz NOT NULL,
  "buyer" integer NOT NULL,
  "price" numeric(10, 4) NOT NULL
);

-- komentár
COMMENT ON COLUMN "auction"."auction_timeout" IS 'Koľko dní aukcia trvá od poslednej ponuky';

-- referenčná integrita
-- primárne kľúče

ALTER TABLE "auction"
  ADD CONSTRAINT "auction_id" PRIMARY KEY ("id");

ALTER TABLE "auction_bid"
  ADD CONSTRAINT "auction_bid_id" PRIMARY KEY ("id");

ALTER TABLE "auction"
  ADD FOREIGN KEY ("seller") REFERENCES "person" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- cudzie kľúče
ALTER TABLE "auction"
  ADD FOREIGN KEY ("winning_bid") REFERENCES "auction_bid" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE "auction_bid"
  ADD FOREIGN KEY ("auction") REFERENCES "auction" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE "auction_bid"
  ADD FOREIGN KEY ("buyer") REFERENCES "person" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- pridanie oprávnení DB uživateľovi extranet
GRANT SELECT, INSERT, UPDATE ON "auction" TO "extranet";

GRANT USAGE, SELECT ON SEQUENCE auction_id_seq
  TO extranet;

GRANT SELECT, INSERT ON "auction_bid" TO "extranet";

GRANT USAGE, SELECT ON SEQUENCE auction_bid_id_seq
  TO extranet;

-- pridanie oprávnení (ACL)
INSERT INTO "acl_high" ("name", "opgroup", "opname", "handler_class", "handler_param")
  VALUES ('Právo spravovať aukcie', 'auction', 'admin', NULL, NULL);

INSERT INTO "acl_high" ("name", "opgroup", "opname", "handler_class", "handler_param")
  VALUES ('Právo vytvárať aukcie', 'auction', 'create', NULL, NULL);

INSERT INTO "acl_high" ("name", "opgroup", "opname", "handler_class", "handler_param")
  VALUES ('Právo účastniť sa aukcií', 'auction', 'participate', NULL, NULL);

-- pridanie oprávnenia auctio.admin pre rolu "Neobmedzený admin"
INSERT INTO "acl" ("acl_high", "person", "acl_role", "row_id")
  VALUES ((
      SELECT
        "id"
      FROM
        "acl_high"
      WHERE
        "opgroup" = 'auction'
        AND "opname" = 'admin'),
      NULL,
      '10',
      NULL);

-- pridanie automatický oprávnení pre osoby so zmluvou typu "Členstvo"
UPDATE
  "contract_type"
SET
  "acl_high" = '{"auction":["create","participate"]}'
WHERE
  "id" = '22';

-- zmeny 31.7.2020
ALTER TABLE "auction"
  ADD "show" boolean NOT NULL DEFAULT TRUE;

ALTER TABLE "auction_bid"
  DROP CONSTRAINT "auction_bid_auction_fkey",
  ADD FOREIGN KEY ("auction") REFERENCES "auction" ("id") ON DELETE CASCADE ON UPDATE CASCADE;

GRANT DELETE ON "auction" TO "extranet";

ALTER TABLE "auction"
  ADD "show" boolean NOT NULL DEFAULT TRUE;

