DDL: kreiranje_v1.sql

-- Drop and create tables
DROP TABLE IF EXISTS kodovi;
DROP TABLE IF EXISTS oformuva;
DROP TABLE IF EXISTS se_clenovi_na;
DROP TABLE IF EXISTS e_kandidat_vo;
DROP TABLE IF EXISTS glasovi_za_lista;
DROP TABLE IF EXISTS glasovi_za_kandidat;
DROP TABLE IF EXISTS glasanja;
DROP TABLE IF EXISTS komisii;
DROP TABLE IF EXISTS kandidatski_listi;
DROP TABLE IF EXISTS kandidaturi;
DROP TABLE IF EXISTS realizacii_so_kandidaturi;
DROP TABLE IF EXISTS realizacii_so_kandidatska_lista;
DROP TABLE IF EXISTS koalicii;
DROP TABLE IF EXISTS prigovori;
DROP TABLE IF EXISTS partii;
DROP TABLE IF EXISTS realizacii_na_izbori;
DROP TABLE IF EXISTS clenovi_na_komisija;
DROP TABLE IF EXISTS kandidati;
DROP TABLE IF EXISTS izbiracki_mesta;
DROP TABLE IF EXISTS izborni_edinici;
DROP TABLE IF EXISTS gragjani;
DROP TABLE IF EXISTS adresi;
DROP TABLE IF EXISTS izbori;
DROP TABLE IF EXISTS korisnicki_profil;
DROP TABLE IF EXISTS dokumenti_za_identifikacija;
DROP TABLE IF EXISTS opstini;

-- Create tables
CREATE TABLE opstini (
    o_id SERIAL PRIMARY KEY,
    o_ime VARCHAR(255) NOT NULL
);

CREATE TABLE dokumenti_za_identifikacija (
    d_broj BIGSERIAL PRIMARY KEY,
    d_vazhi_od DATE NOT NULL,
    d_vazhi_do DATE NOT NULL,
    d_izdaden DATE NOT NULL
);

CREATE TABLE korisnicki_profil (
    kp_id BIGSERIAL PRIMARY KEY,
    kp_korisnicko_ime VARCHAR(255) NOT NULL,
    kp_lozinka VARCHAR(255) NOT NULL,
    kp_uloga VARCHAR(255) NOT NULL
);

CREATE TABLE izbori (
    i_id SERIAL PRIMARY KEY,
    i_naziv VARCHAR(255) NOT NULL
);

CREATE TABLE adresi (
    a_id BIGSERIAL PRIMARY KEY,
    a_ulica VARCHAR(255) NOT NULL,
    a_broj INT NOT NULL,
    a_vlez VARCHAR(255),
    a_stan VARCHAR(255),
    o_id SERIAL NOT NULL,
    CONSTRAINT fk_adresi_opstini FOREIGN KEY (o_id) REFERENCES opstini(o_id)
);

CREATE TABLE gragjani (
    g_id BIGSERIAL PRIMARY KEY,
    g_ime VARCHAR(255) NOT NULL,
    g_prezime VARCHAR(255) NOT NULL,
    g_embg VARCHAR(13) NOT NULL,
    g_datum_ragjanje DATE NOT NULL,
    g_godini INT NOT NULL,
    a_id BIGSERIAL NOT NULL,
    CONSTRAINT fk_gragjani_adresi FOREIGN KEY (a_id) REFERENCES adresi(a_id)
);

CREATE TABLE izborni_edinici (
    ie_id SERIAL PRIMARY KEY,
    ie_ime VARCHAR(255) NOT NULL,
    ie_broj_pratenici INT NOT NULL
);

CREATE TABLE izbiracki_mesta (
    im_id SERIAL PRIMARY KEY,
    im_mesto VARCHAR(255) NOT NULL,
    ie_id SERIAL NOT NULL,
    a_id BIGSERIAL NOT NULL,
    CONSTRAINT fk_izbiracki_mesta_izborni_edinici FOREIGN KEY (ie_id) REFERENCES izborni_edinici(ie_id),
    CONSTRAINT fk_izbiracki_mesta_adresi FOREIGN KEY (a_id) REFERENCES adresi(a_id)
);

CREATE TABLE kandidati (
    g_id BIGSERIAL PRIMARY KEY,
    CONSTRAINT fk_kandidati_gragjani FOREIGN KEY (g_id) REFERENCES gragjani(g_id)
);

CREATE TABLE clenovi_na_komisija (
    g_id BIGSERIAL PRIMARY KEY,
    CONSTRAINT fk_clenovi_na_komisija_gragjani FOREIGN KEY (g_id) REFERENCES gragjani(g_id)
);

CREATE TABLE realizacii_na_izbori (
    ri_id SERIAL PRIMARY KEY,
    ri_ime VARCHAR(255) NOT NULL,
    ri_datum DATE NOT NULL,
    i_id SERIAL NOT NULL,
    CONSTRAINT fk_realizacii_na_izbori_izbori FOREIGN KEY (i_id) REFERENCES izbori(i_id)
);

CREATE TABLE partii (
    p_id SERIAL PRIMARY KEY,
    p_ime VARCHAR(255) NOT NULL,
    a_id BIGSERIAL NOT NULL,
    CONSTRAINT fk_partii_adresi FOREIGN KEY (a_id) REFERENCES adresi(a_id)
);

CREATE TABLE prigovori (
    pr_id SERIAL PRIMARY KEY,
    pr_opis VARCHAR(255) NOT NULL,
    ri_id SERIAL NOT NULL,
    im_id SERIAL NOT NULL,
    p_id SERIAL NOT NULL,
    CONSTRAINT fk_prigovori_realizacii_na_izbori FOREIGN KEY (ri_id) REFERENCES realizacii_na_izbori(ri_id),
    CONSTRAINT fk_prigovori_izbiracki_mesta FOREIGN KEY (im_id) REFERENCES izbiracki_mesta(im_id),
    CONSTRAINT fk_prigovori_partija FOREIGN KEY (p_id) REFERENCES partii(p_id)
);

CREATE TABLE koalicii (
    koa_id SERIAL PRIMARY KEY,
    koa_ime VARCHAR(255) NOT NULL,
    koa_moto VARCHAR(255) NOT NULL,
    ri_id SERIAL NOT NULL,
    CONSTRAINT fk_koalicii_realizacii_na_izbori FOREIGN KEY (ri_id) REFERENCES realizacii_na_izbori(ri_id)
);

CREATE TABLE realizacii_so_kandidatska_lista (
    ri_id SERIAL PRIMARY KEY,
    CONSTRAINT fk_realizacii_so_kandidatska_lista_realizacii_na_izbori FOREIGN KEY (ri_id) REFERENCES realizacii_na_izbori(ri_id)
);

CREATE TABLE realizacii_so_kandidaturi (
    ri_id SERIAL PRIMARY KEY,
    CONSTRAINT fk_realizacii_so_kandidaturi_realizacii_na_izbori FOREIGN KEY (ri_id) REFERENCES realizacii_na_izbori(ri_id)
);

CREATE TABLE kandidaturi (
    kan_id SERIAL PRIMARY KEY,
    kan_opis VARCHAR(255) NOT NULL,
    g_id BIGSERIAL NOT NULL,
    ri_id SERIAL NOT NULL,
    p_id SERIAL NOT NULL,
    CONSTRAINT fk_kandidaturi_gragjani FOREIGN KEY (g_id) REFERENCES gragjani(g_id),
    CONSTRAINT fk_kandidaturi_realizacii_so_kandidat FOREIGN KEY (ri_id) REFERENCES realizacii_so_kandidaturi(ri_id),
    CONSTRAINT fk_kandidaturi_partii FOREIGN KEY (p_id) REFERENCES partii(p_id)
);

CREATE TABLE kandidatski_listi (
    kl_id SERIAL PRIMARY KEY,
    kl_opis VARCHAR(255) NOT NULL,
    ri_id SERIAL NOT NULL,
    p_id SERIAL NOT NULL,
    ie_id SERIAL NOT NULL,
    CONSTRAINT fk_kandidatski_listi_realizacii_so_kandidatska_lista FOREIGN KEY (ri_id) REFERENCES realizacii_so_kandidatska_lista(ri_id),
    CONSTRAINT fk_kandidatski_listi_partii FOREIGN KEY (p_id) REFERENCES partii(p_id),
    CONSTRAINT fk_kandidatski_listi_izborni_edinici FOREIGN KEY (ie_id) REFERENCES izborni_edinici(ie_id)
);

CREATE TABLE komisii (
    kom_id SERIAL PRIMARY KEY,
    ri_id SERIAL NOT NULL,
    im_id SERIAL NOT NULL,
    CONSTRAINT fk_komisii_realizacii_na_izbori FOREIGN KEY (ri_id) REFERENCES realizacii_na_izbori(ri_id),
    CONSTRAINT fk_komisii_izbiracki_mesta FOREIGN KEY (im_id) REFERENCES izbiracki_mesta(im_id)
);

CREATE TABLE glasanja (
    ug_id SERIAL PRIMARY KEY,
    ug_vreme TIMESTAMP NOT NULL,
    g_id BIGSERIAL NOT NULL,
    im_id SERIAL NOT NULL,
    ri_id SERIAL NOT NULL,
    CONSTRAINT fk_glasanja_gragjani FOREIGN KEY (g_id) REFERENCES gragjani(g_id),
    CONSTRAINT fk_glasanja_izbiracki_mesta FOREIGN KEY (im_id) REFERENCES izbiracki_mesta(im_id),
    CONSTRAINT fk_glasanja_realizacii_na_izbori FOREIGN KEY (ri_id) REFERENCES realizacii_na_izbori(ri_id)
);

CREATE TABLE glasovi_za_kandidat (
    ug_id SERIAL PRIMARY KEY,
    kan_id SERIAL NOT NULL,
    CONSTRAINT fk_glas_za_kandidat_glasanja FOREIGN KEY (ug_id) REFERENCES glasanja(ug_id),
    CONSTRAINT fk_glas_za_kandidat_kandidaturi FOREIGN KEY (kan_id) REFERENCES kandidaturi(kan_id)
);

CREATE TABLE glasovi_za_lista (
    ug_id SERIAL PRIMARY KEY,
    kl_id SERIAL NOT NULL,
    CONSTRAINT fk_glas_za_lista_glasanja FOREIGN KEY (ug_id) REFERENCES glasanja(ug_id),
    CONSTRAINT fk_glas_za_lista_kandidatski_listi FOREIGN KEY (kl_id) REFERENCES realizacii_so_kandidatska_lista(ri_id)
);

CREATE TABLE e_kandidat_vo (
    g_id BIGSERIAL NOT NULL,
    kl_id SERIAL NOT NULL,
    PRIMARY KEY (g_id, kl_id),
    CONSTRAINT fk_e_kandidat_vo_gragjani FOREIGN KEY (g_id) REFERENCES gragjani(g_id),
    CONSTRAINT fk_e_kandidat_vo_kandidatski_listi FOREIGN KEY (kl_id) REFERENCES kandidatski_listi(kl_id)
);

CREATE TABLE se_clenovi_na (
    g_id BIGSERIAL NOT NULL,
    kom_id SERIAL NOT NULL,
    PRIMARY KEY (g_id, kom_id),
    CONSTRAINT fk_se_clenovi_na_gragjani FOREIGN KEY (g_id) REFERENCES gragjani(g_id),
    CONSTRAINT fk_se_clenovi_na_komisii FOREIGN KEY (kom_id) REFERENCES komisii(kom_id)
);

CREATE TABLE oformuva(
    p_id SERIAL NOT NULL,
    koa_id SERIAL NOT NULL,
    PRIMARY KEY (p_id, koa_id),
    CONSTRAINT fk_oformuva_partii FOREIGN KEY (p_id) REFERENCES partii(p_id),
    CONSTRAINT fk_oformuva_koalicii FOREIGN KEY (koa_id) REFERENCES koalicii(koa_id)
);

CREATE TABLE kodovi(
    ug_id SERIAL NOT NULL,
    kod_kod VARCHAR(255) NOT NULL UNIQUE,
    kod_vazi_do DATE NOT NULL,
    PRIMARY KEY(kod_kod, ug_id),
    CONSTRAINT fk_kodovi_glasanja FOREIGN KEY (ug_id) REFERENCES glasanja(ug_id)
);