CREATE TABLE "vote_proposal" (
    "id" serial NOT NULL,
    "name" character varying(200) NOT NULL,
    "created" timestamptz NOT NULL,
    "creator" integer NOT NULL,
    "description" character varying NOT NULL,
    "max_options" smallint NOT NULL DEFAULT '1',
    "time_start" timestamptz NOT NULL,
    "time_end" timestamptz NOT NULL,
    "result" json NULL
);

COMMENT ON COLUMN "vote_proposal"."creator" IS 'globis.person.id';

ALTER TABLE "vote_proposal"
    ADD CONSTRAINT "vote_proposal_id" PRIMARY KEY ("id");

CREATE TABLE "vote_option" (
    "id" serial NOT NULL,
    "proposal" integer NOT NULL,
    "name" character varying(200) NOT NULL,
    "sort" smallint NOT NULL
);

ALTER TABLE "vote_option"
    ADD CONSTRAINT "vote_option_id" PRIMARY KEY ("id");

ALTER TABLE "vote_option"
    ADD FOREIGN KEY ("proposal") REFERENCES "vote_proposal" ("id") ON DELETE CASCADE ON UPDATE CASCADE;

CREATE TABLE "vote_given" (
    "id" serial NOT NULL,
    "proposal" integer NOT NULL,
    "voter" integer NOT NULL,
    "created" timestamptz NOT NULL,
    "options" json NOT NULL,
    "valid" boolean NOT NULL DEFAULT TRUE,
    "validator" integer NOT NULL
);

COMMENT ON COLUMN "vote_given"."voter" IS 'globis.person.id';

COMMENT ON COLUMN "vote_given"."options" IS 'id moznosti za ktore hlasoval (pole idciek z vote_options)';

COMMENT ON COLUMN "vote_given"."valid" IS 'ci je hlas platny';

COMMENT ON COLUMN "vote_given"."validator" IS 'globis.person.id, osoba ktora hlas kontrolovala (spravidla nikto, a vyplni sa iba ak niekto manualne oznaci hlas za nevalidny)';

ALTER TABLE "vote_given"
    ADD CONSTRAINT "vote_given_id" PRIMARY KEY ("id");

INSERT INTO "acl_high" ("name", "opgroup", "opname", "handler_class", "handler_param")
    VALUES ('Právo hlasovať v ALPI hlasovaniach', 'alpi_vote_proposal', 'vote', NULL, NULL), ('Právo spravovať ALPI hlasovania', 'alpi_vote_proposal', 'admin', NULL, NULL);

UPDATE
    contract_type
SET
    acl_high = JSONB_SET(acl_high::jsonb, '{alpi_vote_proposal}', '["vote"]'::jsonb)
WHERE
    id = 22;

