--  REFERENCE TABLES


CREATE TABLE public.election_type (
    election_type_id int8 NOT NULL,
    type_name        varchar(60) NOT NULL,
    CONSTRAINT election_type_pkey PRIMARY KEY (election_type_id),
    CONSTRAINT uk_election_type_name UNIQUE (type_name)
);


CREATE TABLE public.region_type (
    region_type_id int8  NOT NULL,
    "name"         varchar(50) NOT NULL,
    description    text NULL,
    CONSTRAINT region_type_pkey PRIMARY KEY (region_type_id),
    CONSTRAINT uk_region_type_name UNIQUE (name)
);



CREATE TABLE public.winner_method (
    method_id   int8 NOT NULL,
    method_name varchar(255) NOT NULL,
    CONSTRAINT uk_winner_method_name UNIQUE (method_name),
    CONSTRAINT winner_method_pkey PRIMARY KEY (method_id)
);



--  REGION (self-referencing hierarchy)

CREATE TABLE public.region (
    region_id        int8  NOT NULL,
    "name"           varchar(100) NOT NULL,
    region_type_id   int8  NOT NULL,
    parent_region_id int8  NULL,
    CONSTRAINT region_pkey PRIMARY KEY (region_id),
    CONSTRAINT uk_region_name UNIQUE (name, parent_region_id),
    CONSTRAINT fk_region_parent FOREIGN KEY (parent_region_id)
        REFERENCES public.region(region_id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_region_type  FOREIGN KEY (region_type_id)
        REFERENCES public.region_type(region_type_id) ON DELETE RESTRICT ON UPDATE CASCADE
);


--  ELECTION


CREATE TABLE public.election (
    election_id     int8  NOT NULL,
    "name"          varchar(100) NOT NULL,
    election_type_id int8 NOT NULL,
    region_id       int8  NOT NULL,
    election_date   date  NOT NULL,
    description     text  NULL,
    status          int2  NOT NULL DEFAULT 1,
    winner_method_id int8 NOT NULL,
    total_seats     int4  NULL,
    CONSTRAINT chk_total_seats CHECK ((total_seats IS NULL) OR (total_seats > 0)),
    CONSTRAINT election_pkey PRIMARY KEY (election_id),
    CONSTRAINT election_status_check CHECK (status = ANY (ARRAY[0, 1])),
    CONSTRAINT uk_election_name UNIQUE (name),
    CONSTRAINT fk_election_region FOREIGN KEY (region_id)
        REFERENCES public.region(region_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_election_type FOREIGN KEY (election_type_id)
        REFERENCES public.election_type(election_type_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_election_winner_method FOREIGN KEY (winner_method_id)
        REFERENCES public.winner_method(method_id) ON DELETE RESTRICT ON UPDATE CASCADE
);


CREATE TABLE public.election_cycle (
    cycle_id      int8 NOT NULL,
    election_id   int8 NOT NULL,
    round_number  int4 NOT NULL,
    CONSTRAINT election_cycle_pkey PRIMARY KEY (cycle_id),
    CONSTRAINT uk_cycle UNIQUE (election_id, round_number),
    CONSTRAINT fk_cycle_election FOREIGN KEY (election_id)
        REFERENCES public.election(election_id) ON DELETE CASCADE ON UPDATE CASCADE
);


CREATE TABLE public.electoral_district (
    district_id     int8 NOT NULL,
    election_id     int8 NOT NULL,
    region_id       int8 NOT NULL,
    seats_available int4 NULL,
    CONSTRAINT chk_seats CHECK ((seats_available IS NULL) OR (seats_available > 0)),
    CONSTRAINT electoral_district_pkey PRIMARY KEY (district_id),
    CONSTRAINT uk_district UNIQUE (election_id, region_id),
    CONSTRAINT fk_district_election FOREIGN KEY (election_id)
        REFERENCES public.election(election_id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_district_region FOREIGN KEY (region_id)
        REFERENCES public.region(region_id) ON DELETE RESTRICT ON UPDATE CASCADE
);


--  PARTIES, COALITIONS, POLITICAL ENTITIES


CREATE TABLE public.party_coalition (
    coalition_id int8 NOT NULL,
    "name"       varchar(100) NOT NULL,
    election_id  int8 NOT NULL,
    CONSTRAINT party_coalition_pkey PRIMARY KEY (coalition_id),
    CONSTRAINT uk_coalition_name UNIQUE (name),
    CONSTRAINT fk_coalition_election FOREIGN KEY (election_id)
        REFERENCES public.election(election_id) ON DELETE CASCADE ON UPDATE CASCADE
);


CREATE TABLE public.party_leader (
    leader_id int8 NOT NULL,
    person_id int8 NOT NULL,
    CONSTRAINT party_leader_pkey PRIMARY KEY (leader_id),
    CONSTRAINT uk_party_leader_person UNIQUE (person_id),
    CONSTRAINT fk_party_leader_person FOREIGN KEY (person_id)
        REFERENCES public.person(person_id) ON DELETE RESTRICT ON UPDATE CASCADE
);


CREATE TABLE public.political_party (
    party_id       int8 NOT NULL,
    "name"         varchar(100) NOT NULL,
    abbreviation   varchar(20) NULL,
    description    text NULL,
    founded_year   int4 NULL,
    ideology       text NULL,
    leader_id      int8 NULL,
    parent_party_id int8 NULL,
    CONSTRAINT political_party_pkey PRIMARY KEY (party_id),
    CONSTRAINT uk_party_name UNIQUE (name),
    CONSTRAINT fk_party_leader FOREIGN KEY (leader_id)
        REFERENCES public.party_leader(leader_id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_party_parent FOREIGN KEY (parent_party_id)
        REFERENCES public.political_party(party_id) ON DELETE SET NULL ON UPDATE CASCADE
);


CREATE TABLE public.coalition_member (
    coalition_member_id int8 NOT NULL,
    coalition_id        int8 NOT NULL,
    party_id            int8 NOT NULL,
    CONSTRAINT coalition_member_pkey PRIMARY KEY (coalition_member_id),
    CONSTRAINT uk_coalition_member UNIQUE (coalition_id, party_id),
    CONSTRAINT fk_coalition_member_coalition FOREIGN KEY (coalition_id)
        REFERENCES public.party_coalition(coalition_id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_coalition_member_party FOREIGN KEY (party_id)
        REFERENCES public.political_party(party_id) ON DELETE CASCADE ON UPDATE CASCADE
);


CREATE TABLE public.political_entity (
    entity_id      int8 NOT NULL,
    "name"         varchar(100) NOT NULL,
    "type"         varchar(20) NULL,
    party_id       int8 NULL,
    coalition_id   int8 NULL,
    candidate_id   int8 NULL,
    is_independent bool NOT NULL DEFAULT false,
    CONSTRAINT chk_entity_type CHECK (
        ((party_id IS NOT NULL) AND (coalition_id IS NULL) AND (is_independent = false))
        OR ((party_id IS NULL) AND (coalition_id IS NOT NULL) AND (is_independent = false))
        OR ((party_id IS NULL) AND (coalition_id IS NULL) AND (is_independent = true) AND (candidate_id IS NOT NULL))
    ),
    CONSTRAINT political_entity_pkey PRIMARY KEY (entity_id),
    CONSTRAINT uk_entity_name UNIQUE (name),
    CONSTRAINT fk_entity_candidate FOREIGN KEY (candidate_id)
        REFERENCES public.candidate(candidate_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_entity_coalition FOREIGN KEY (coalition_id)
        REFERENCES public.party_coalition(coalition_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_entity_party FOREIGN KEY (party_id)
        REFERENCES public.political_party(party_id) ON DELETE RESTRICT ON UPDATE CASCADE
);


--  PERSON, VOTER, CANDIDATE


CREATE TABLE public.person (
    person_id     int8  NOT NULL,
    "name"        varchar(50) NOT NULL,
    surname       varchar(50) NOT NULL,
    date_of_birth date  NOT NULL,
    gender        bpchar(1) NOT NULL,
    municipality_id int8 NULL,
    CONSTRAINT person_gender_check CHECK (gender = ANY (ARRAY['M'::bpchar, 'F'::bpchar, 'O'::bpchar])),
    CONSTRAINT person_pkey PRIMARY KEY (person_id),
    CONSTRAINT uk_person UNIQUE (name, surname, date_of_birth),
    CONSTRAINT fk_person_municipality FOREIGN KEY (municipality_id)
        REFERENCES public.region(region_id)
);


CREATE TABLE public.candidate (
    candidate_id int8 NOT NULL,
    person_id    int8 NOT NULL,
    CONSTRAINT candidate_pkey PRIMARY KEY (candidate_id),
    CONSTRAINT fk_candidate_person FOREIGN KEY (person_id)
        REFERENCES public.person(person_id) ON DELETE RESTRICT ON UPDATE CASCADE
);


CREATE TABLE public.candidate_party (
    candidate_party_id int8 NOT NULL,
    candidate_id       int8 NOT NULL,
    party_id           int8 NOT NULL,
    election_id        int8 NOT NULL,
    CONSTRAINT candidate_party_pkey PRIMARY KEY (candidate_party_id),
    CONSTRAINT uk_candidate_party UNIQUE (candidate_id, party_id, election_id),
    CONSTRAINT fk_cp_candidate FOREIGN KEY (candidate_id)
        REFERENCES public.candidate(candidate_id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_cp_election FOREIGN KEY (election_id)
        REFERENCES public.election(election_id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_cp_party FOREIGN KEY (party_id)
        REFERENCES public.political_party(party_id) ON DELETE CASCADE ON UPDATE CASCADE
);


CREATE TABLE public.candidate_list (
    list_id     int8 NOT NULL,
    election_id int8 NOT NULL,
    entity_id   int8 NOT NULL,
    CONSTRAINT candidate_list_pkey PRIMARY KEY (list_id),
    CONSTRAINT uk_candidate_list UNIQUE (election_id, entity_id),
    CONSTRAINT fk_candidate_list_election FOREIGN KEY (election_id)
        REFERENCES public.election(election_id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_candidate_list_entity FOREIGN KEY (entity_id)
        REFERENCES public.political_entity(entity_id) ON DELETE CASCADE ON UPDATE CASCADE
);


CREATE TABLE public.candidate_list_item (
    list_item_id int8 NOT NULL,
    list_id      int8 NOT NULL,
    candidate_id int8 NOT NULL,
    "position"   int4 NOT NULL,
    CONSTRAINT candidate_list_item_pkey PRIMARY KEY (list_item_id),
    CONSTRAINT uk_list_item_candidate UNIQUE (list_id, candidate_id),
    CONSTRAINT uk_list_item_position  UNIQUE (list_id, "position"),
    CONSTRAINT fk_list_item_candidate FOREIGN KEY (candidate_id)
        REFERENCES public.candidate(candidate_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_list_item_list FOREIGN KEY (list_id)
        REFERENCES public.candidate_list(list_id) ON DELETE CASCADE ON UPDATE CASCADE
);


--  POLLING STATIONS AND VOTERS


CREATE TABLE public.polling_station (
    station_id       int8  NOT NULL,
    "name"           varchar(100) NOT NULL,
    municipality_id  int8  NOT NULL,
    address          text  NULL,
    registered_voter int4  NULL,
    voting_object    varchar(255) NULL,
    CONSTRAINT polling_station_pkey PRIMARY KEY (station_id),
    CONSTRAINT uk_station_name_municipality UNIQUE (name, municipality_id),
    CONSTRAINT fk_station_municipality FOREIGN KEY (municipality_id)
        REFERENCES public.region(region_id) ON DELETE RESTRICT ON UPDATE CASCADE
);


CREATE TABLE public.station_election (
    station_election_id int8 NOT NULL,
    station_id          int8 NOT NULL,
    election_id         int8 NOT NULL,
    CONSTRAINT station_election_pkey PRIMARY KEY (station_election_id),
    CONSTRAINT uk_se UNIQUE (station_id, election_id),
    CONSTRAINT fk_se_election FOREIGN KEY (election_id)
        REFERENCES public.election(election_id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_se_station FOREIGN KEY (station_id)
        REFERENCES public.polling_station(station_id) ON DELETE CASCADE ON UPDATE CASCADE
);


CREATE TABLE public.voter (
    voter_id   int8 NOT NULL,
    person_id  int8 NOT NULL,
    station_id int8 NOT NULL,
    CONSTRAINT idx_voter_person_id UNIQUE (person_id),
    CONSTRAINT voter_pkey PRIMARY KEY (voter_id),
    CONSTRAINT fk_voter_person FOREIGN KEY (person_id)
        REFERENCES public.person(person_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_voter_station FOREIGN KEY (station_id)
        REFERENCES public.polling_station(station_id) ON DELETE RESTRICT ON UPDATE CASCADE
);


CREATE TABLE public.voter_election (
    voter_election_id  int8  NOT NULL,
    voter_id           int8  NOT NULL,
    station_id         int8  NOT NULL,
    election_id        int8  NOT NULL,
    checkin_timestamp  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uk_ve_voter_election UNIQUE (voter_id, election_id),
    CONSTRAINT voter_election_pkey PRIMARY KEY (voter_election_id),
    CONSTRAINT fk_ve_election FOREIGN KEY (election_id)
        REFERENCES public.election(election_id),
    CONSTRAINT fk_ve_station FOREIGN KEY (station_id)
        REFERENCES public.polling_station(station_id),
    CONSTRAINT fk_ve_voter FOREIGN KEY (voter_id)
        REFERENCES public.voter(voter_id)
);


--  ELECTION PARTICIPANTS AND VOTE RESULTS

CREATE TABLE public.election_participant (
    participant_id int8 NOT NULL,
    district_id    int8 NOT NULL,
    entity_id      int8 NOT NULL,
    CONSTRAINT election_participant_pkey PRIMARY KEY (participant_id),
    CONSTRAINT uk_participant UNIQUE (district_id, entity_id),
    CONSTRAINT fk_participant_district FOREIGN KEY (district_id)
        REFERENCES public.electoral_district(district_id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_participant_entity FOREIGN KEY (entity_id)
        REFERENCES public.political_entity(entity_id) ON DELETE CASCADE ON UPDATE CASCADE
);


CREATE TABLE public.vote_result (
    result_id    int8 NOT NULL,
    election_id  int8 NOT NULL,
    station_id   int8 NOT NULL,
    entity_id    int8 NOT NULL,
    candidate_id int8 NULL,
    votes        int4 NOT NULL,
    CONSTRAINT uk_vote_result UNIQUE (election_id, entity_id, station_id, candidate_id),
    CONSTRAINT vote_result_pkey PRIMARY KEY (result_id),
    CONSTRAINT vote_result_votes_check CHECK (votes >= 0),
    CONSTRAINT fk_vr_candidate FOREIGN KEY (candidate_id)
        REFERENCES public.candidate(candidate_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_vr_election FOREIGN KEY (election_id)
        REFERENCES public.election(election_id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_vr_entity FOREIGN KEY (entity_id)
        REFERENCES public.political_entity(entity_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_vr_station FOREIGN KEY (station_id)
        REFERENCES public.polling_station(station_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_vr_station_election FOREIGN KEY (station_id, election_id)
        REFERENCES public.station_election(station_id, election_id)
);


--  BALLOT  

CREATE TABLE public.ballot (
    ballot_id         int8  NOT NULL GENERATED ALWAYS AS IDENTITY,
    election_id       int8  NOT NULL,
    station_id        int8  NOT NULL,
    entity_id         int8  NULL,
    candidate_id      int8  NULL,
    is_valid          bool  NOT NULL DEFAULT true,
    ballot_timestamp  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT ballot_pkey PRIMARY KEY (ballot_id),
    CONSTRAINT chk_ballot_validity CHECK (
        (is_valid = false) OR (entity_id IS NOT NULL) OR (candidate_id IS NOT NULL)
    ),
    CONSTRAINT fk_ballot_candidate FOREIGN KEY (candidate_id)
        REFERENCES public.candidate(candidate_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_ballot_election FOREIGN KEY (election_id)
        REFERENCES public.election(election_id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_ballot_entity FOREIGN KEY (entity_id)
        REFERENCES public.political_entity(entity_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_ballot_station FOREIGN KEY (station_id)
        REFERENCES public.polling_station(station_id) ON DELETE RESTRICT ON UPDATE CASCADE
);