-- =========================
-- BANKA
-- =========================
CREATE TABLE banka (
    banka_id SERIAL PRIMARY KEY,
    ime_na_banka VARCHAR(100) NOT NULL DEFAULT 'unknown_bank',
    edb VARCHAR(13) NOT NULL UNIQUE,
    datum_na_osnovanje DATE,

    CONSTRAINT chk_banka_edb_len CHECK (length(edb) = 13)
);

-- =========================
-- VALUTA
-- =========================
CREATE TABLE valuta (
    valuta_id SERIAL PRIMARY KEY,
    kod CHAR(3) NOT NULL UNIQUE,
    ime VARCHAR(100) NOT NULL,
    simbol VARCHAR(5)
);

-- =========================
-- KURSNA_LISTA
-- =========================
CREATE TABLE kursna_lista (
    kurs_id SERIAL PRIMARY KEY,
    datum DATE NOT NULL DEFAULT CURRENT_DATE,
    kupoven_kurs NUMERIC(10,4) NOT NULL,
    sreden_kurs NUMERIC(10,4) NOT NULL,
    prodazen_kurs NUMERIC(10,4) NOT NULL,
    valuta_od_id INT NOT NULL,
    valuta_do_id INT NOT NULL,

    CONSTRAINT fk_kurs_valuta_od FOREIGN KEY (valuta_od_id)
        REFERENCES valuta(valuta_id) ON DELETE RESTRICT,
    CONSTRAINT fk_kurs_valuta_do FOREIGN KEY (valuta_do_id)
        REFERENCES valuta(valuta_id) ON DELETE RESTRICT,

    CONSTRAINT uq_kurs UNIQUE (datum, valuta_od_id, valuta_do_id),
    CONSTRAINT chk_kurs_positive CHECK (kupoven_kurs > 0 AND sreden_kurs > 0 AND prodazen_kurs > 0),
    CONSTRAINT chk_kurs_order CHECK (kupoven_kurs <= sreden_kurs AND sreden_kurs <= prodazen_kurs),
    CONSTRAINT chk_kurs_diff_valuti CHECK (valuta_od_id <> valuta_do_id)
);

-- =========================
-- BANK_USER / ROLE / PRIVILEGII
-- =========================
CREATE TABLE bank_user (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'AKTIVEN',

    CONSTRAINT chk_bank_user_status CHECK (status IN ('AKTIVEN', 'NEAKTIVEN'))
);

CREATE TABLE role (
    role_id SERIAL PRIMARY KEY,
    ime VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE privilegii (
    privilegija_id SERIAL PRIMARY KEY,
    privilegija VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE role_privilegii (
    role_id INT NOT NULL,
    privilegija_id INT NOT NULL,
    PRIMARY KEY (role_id, privilegija_id),

    CONSTRAINT fk_role_privilegii_role FOREIGN KEY (role_id)
        REFERENCES role(role_id) ON DELETE CASCADE,
    CONSTRAINT fk_role_privilegii_privilegii FOREIGN KEY (privilegija_id)
        REFERENCES privilegii(privilegija_id) ON DELETE CASCADE
);

CREATE TABLE role_user (
    role_id INT NOT NULL,
    user_id INT NOT NULL,
    PRIMARY KEY (role_id, user_id),

    CONSTRAINT fk_role_user_role FOREIGN KEY (role_id)
        REFERENCES role(role_id) ON DELETE CASCADE,
    CONSTRAINT fk_role_user_user FOREIGN KEY (user_id)
        REFERENCES bank_user(user_id) ON DELETE CASCADE
);

-- =========================
-- FILIJALA
-- =========================
CREATE TABLE filijala (
    filijala_id SERIAL PRIMARY KEY,
    ime VARCHAR(100) NOT NULL,
    banka_id INT NOT NULL,

    CONSTRAINT fk_filijala_banka FOREIGN KEY (banka_id)
        REFERENCES banka(banka_id) ON DELETE RESTRICT
);

-- =========================
-- VRABOTEN
-- =========================
CREATE TABLE vraboten (
    vraboten_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    ime VARCHAR(100) NOT NULL,
    prezime VARCHAR(100) NOT NULL,
    tatkovo_ime VARCHAR(100),
    datum_ragjanje DATE,
    embg CHAR(13) NOT NULL UNIQUE,

    CONSTRAINT fk_vraboten_user FOREIGN KEY (user_id)
        REFERENCES bank_user(user_id) ON DELETE RESTRICT,
    CONSTRAINT chk_vraboten_embg_len CHECK (length(embg) = 13)
);

-- Extra table kept from your model.
CREATE TABLE raboti_vo (
    vraboten_id INT NOT NULL,
    filijala_id INT NOT NULL,
    raboti_od DATE NOT NULL,
    raboti_do DATE,

    PRIMARY KEY (vraboten_id, filijala_id, raboti_od),

    CONSTRAINT fk_raboti_vo_vraboten FOREIGN KEY (vraboten_id)
        REFERENCES vraboten(vraboten_id) ON DELETE RESTRICT,
    CONSTRAINT fk_raboti_vo_filijala FOREIGN KEY (filijala_id)
        REFERENCES filijala(filijala_id) ON DELETE RESTRICT,
    CONSTRAINT chk_raboti_vo_dates CHECK (raboti_do IS NULL OR raboti_do > raboti_od)
);

-- =========================
-- KLIENT
-- =========================
CREATE TABLE klient (
    klient_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    ime VARCHAR(100) NOT NULL,
    prezime VARCHAR(100) NOT NULL,
    datum_ragjanje DATE NOT NULL,
    tatkovo_ime VARCHAR(100),
    embg CHAR(13) NOT NULL UNIQUE,

    CONSTRAINT fk_klient_user FOREIGN KEY (user_id)
        REFERENCES bank_user(user_id) ON DELETE RESTRICT,
    CONSTRAINT chk_klient_embg_len CHECK (length(embg) = 13)
);

-- =========================
-- IZVESTUVANJE
-- =========================
CREATE TABLE izvestuvanje (
    izvestuvanje_id SERIAL PRIMARY KEY,
    naslov VARCHAR(100) NOT NULL,
    poraka TEXT NOT NULL,
    datum_isprakjanje DATE NOT NULL DEFAULT CURRENT_DATE,
    klient_id INT NOT NULL,
    banka_id INT NOT NULL,

    CONSTRAINT fk_izvestuvanje_klient FOREIGN KEY (klient_id)
        REFERENCES klient(klient_id) ON DELETE RESTRICT,
    CONSTRAINT fk_izvestuvanje_banka FOREIGN KEY (banka_id)
        REFERENCES banka(banka_id) ON DELETE RESTRICT
);

-- =========================
-- CONTACT TABLES FROM ER
-- Each row belongs to exactly one owner: klient OR vraboten OR banka OR filijala.
-- =========================
CREATE TABLE telefon (
    telefon_id SERIAL PRIMARY KEY,
    telefonski_broj VARCHAR(20) NOT NULL UNIQUE,
    tip_telefon VARCHAR(50),
    klient_id INT,
    vraboten_id INT,
    banka_id INT,
    filijala_id INT,

    CONSTRAINT fk_telefon_klient FOREIGN KEY (klient_id)
        REFERENCES klient(klient_id) ON DELETE CASCADE,
    CONSTRAINT fk_telefon_vraboten FOREIGN KEY (vraboten_id)
        REFERENCES vraboten(vraboten_id) ON DELETE CASCADE,
    CONSTRAINT fk_telefon_banka FOREIGN KEY (banka_id)
        REFERENCES banka(banka_id) ON DELETE CASCADE,
    CONSTRAINT fk_telefon_filijala FOREIGN KEY (filijala_id)
        REFERENCES filijala(filijala_id) ON DELETE CASCADE,

    CONSTRAINT chk_telefon_one_owner CHECK (
        (CASE WHEN klient_id IS NULL THEN 0 ELSE 1 END) +
        (CASE WHEN vraboten_id IS NULL THEN 0 ELSE 1 END) +
        (CASE WHEN banka_id IS NULL THEN 0 ELSE 1 END) +
        (CASE WHEN filijala_id IS NULL THEN 0 ELSE 1 END) = 1
    )
);

CREATE TABLE email (
    email_id SERIAL PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE,
    tip_email VARCHAR(50),
    klient_id INT,
    vraboten_id INT,
    banka_id INT,
    filijala_id INT,

    CONSTRAINT fk_email_klient FOREIGN KEY (klient_id)
        REFERENCES klient(klient_id) ON DELETE CASCADE,
    CONSTRAINT fk_email_vraboten FOREIGN KEY (vraboten_id)
        REFERENCES vraboten(vraboten_id) ON DELETE CASCADE,
    CONSTRAINT fk_email_banka FOREIGN KEY (banka_id)
        REFERENCES banka(banka_id) ON DELETE CASCADE,
    CONSTRAINT fk_email_filijala FOREIGN KEY (filijala_id)
        REFERENCES filijala(filijala_id) ON DELETE CASCADE,

    CONSTRAINT chk_email_one_owner CHECK (
        (CASE WHEN klient_id IS NULL THEN 0 ELSE 1 END) +
        (CASE WHEN vraboten_id IS NULL THEN 0 ELSE 1 END) +
        (CASE WHEN banka_id IS NULL THEN 0 ELSE 1 END) +
        (CASE WHEN filijala_id IS NULL THEN 0 ELSE 1 END) = 1
    )
);

CREATE TABLE adresa (
    adresa_id SERIAL PRIMARY KEY,
    drzava VARCHAR(100) NOT NULL,
    grad VARCHAR(100) NOT NULL,
    opstina VARCHAR(100),
    naselba VARCHAR(100),
    ulica VARCHAR(150),
    broj VARCHAR(20),
    stanben_broj VARCHAR(20),
    tip_adresa VARCHAR(50),
    klient_id INT,
    vraboten_id INT,
    banka_id INT,
    filijala_id INT,

    CONSTRAINT fk_adresa_klient FOREIGN KEY (klient_id)
        REFERENCES klient(klient_id) ON DELETE CASCADE,
    CONSTRAINT fk_adresa_vraboten FOREIGN KEY (vraboten_id)
        REFERENCES vraboten(vraboten_id) ON DELETE CASCADE,
    CONSTRAINT fk_adresa_banka FOREIGN KEY (banka_id)
        REFERENCES banka(banka_id) ON DELETE CASCADE,
    CONSTRAINT fk_adresa_filijala FOREIGN KEY (filijala_id)
        REFERENCES filijala(filijala_id) ON DELETE CASCADE,

    CONSTRAINT chk_adresa_one_owner CHECK (
        (CASE WHEN klient_id IS NULL THEN 0 ELSE 1 END) +
        (CASE WHEN vraboten_id IS NULL THEN 0 ELSE 1 END) +
        (CASE WHEN banka_id IS NULL THEN 0 ELSE 1 END) +
        (CASE WHEN filijala_id IS NULL THEN 0 ELSE 1 END) = 1
    )
);

-- =========================
-- USLUGA
-- =========================
CREATE TABLE usluga (
    usluga_id SERIAL PRIMARY KEY,
    ime VARCHAR(100) NOT NULL,
    opis VARCHAR(255),
    datum_od DATE,
    datum_do DATE,
    tip_usluga VARCHAR(100),
    status VARCHAR(30) NOT NULL DEFAULT 'AKTIVNA',
    banka_id INT NOT NULL,
    filijala_id INT NOT NULL,

    CONSTRAINT fk_usluga_banka FOREIGN KEY (banka_id)
        REFERENCES banka(banka_id) ON DELETE RESTRICT,
    CONSTRAINT fk_usluga_filijala FOREIGN KEY (filijala_id)
        REFERENCES filijala(filijala_id) ON DELETE RESTRICT,

    CONSTRAINT chk_usluga_dates CHECK (datum_do IS NULL OR datum_od IS NULL OR datum_do > datum_od),
    CONSTRAINT chk_usluga_status CHECK (status IN ('AKTIVNA', 'NEAKTIVNA'))
);

-- =========================
-- TIP_KREDIT / KREDIT
-- =========================
CREATE TABLE tip_kredit (
    tip_kredit_id SERIAL PRIMARY KEY,
    tip VARCHAR(100) NOT NULL UNIQUE,
    opis VARCHAR(255)
);

CREATE TABLE kredit (
    kredit_id SERIAL PRIMARY KEY,
    kamatna_stapka NUMERIC(5,2) NOT NULL,
    rok_otplata INT NOT NULL,
    iznos_kredit NUMERIC(15,2) NOT NULL,
    mesecna_rata NUMERIC(15,2) NOT NULL,
    tip_kredit_id INT NOT NULL,
    usluga_id INT NOT NULL,
    valuta_id INT NOT NULL,

    CONSTRAINT fk_kredit_tip FOREIGN KEY (tip_kredit_id)
        REFERENCES tip_kredit(tip_kredit_id) ON DELETE RESTRICT,
    CONSTRAINT fk_kredit_usluga FOREIGN KEY (usluga_id)
        REFERENCES usluga(usluga_id) ON DELETE RESTRICT,
    CONSTRAINT fk_kredit_valuta FOREIGN KEY (valuta_id)
        REFERENCES valuta(valuta_id) ON DELETE RESTRICT,

    CONSTRAINT chk_kredit_iznos CHECK (iznos_kredit >= 0),
    CONSTRAINT chk_kredit_rata CHECK (mesecna_rata >= 0),
    CONSTRAINT chk_kredit_kamata CHECK (kamatna_stapka >= 0),
    CONSTRAINT chk_kredit_rok CHECK (rok_otplata > 0)
);

-- =========================
-- DOGOVOR / POTPISNIK
-- =========================
CREATE TABLE dogovor (
    dogovor_id SERIAL PRIMARY KEY,
    naslov VARCHAR(255) NOT NULL,
    datum_kreiranje DATE NOT NULL DEFAULT CURRENT_DATE,
    datum_posledna_promena DATE,
    datum_potpisuvanje DATE,
    status VARCHAR(30) NOT NULL DEFAULT 'KREIRAN',
    klient_id INT NOT NULL,
    banka_id INT NOT NULL,
    usluga_id INT NOT NULL,
    filijala_id INT,

    CONSTRAINT fk_dogovor_klient FOREIGN KEY (klient_id)
        REFERENCES klient(klient_id) ON DELETE RESTRICT,
    CONSTRAINT fk_dogovor_banka FOREIGN KEY (banka_id)
        REFERENCES banka(banka_id) ON DELETE RESTRICT,
    CONSTRAINT fk_dogovor_usluga FOREIGN KEY (usluga_id)
        REFERENCES usluga(usluga_id) ON DELETE RESTRICT,
    CONSTRAINT fk_dogovor_filijala FOREIGN KEY (filijala_id)
        REFERENCES filijala(filijala_id) ON DELETE RESTRICT,

    CONSTRAINT chk_dogovor_status CHECK (status IN ('KREIRAN', 'POTPISAN', 'OTKAZAN', 'ISTECEN')),
    CONSTRAINT chk_dogovor_dates CHECK (datum_potpisuvanje IS NULL OR datum_potpisuvanje >= datum_kreiranje)
);

CREATE TABLE potpisnik (
    potpisnik_id SERIAL PRIMARY KEY,
    datum_potpisuvanje DATE NOT NULL DEFAULT CURRENT_DATE,
    klient_id INT NOT NULL,
    dogovor_id INT NOT NULL,

    CONSTRAINT fk_potpisnik_klient FOREIGN KEY (klient_id)
        REFERENCES klient(klient_id) ON DELETE RESTRICT,
    CONSTRAINT fk_potpisnik_dogovor FOREIGN KEY (dogovor_id)
        REFERENCES dogovor(dogovor_id) ON DELETE RESTRICT,

    CONSTRAINT uq_potpisnik UNIQUE (klient_id, dogovor_id)
);

-- =========================
-- SMETKA
-- =========================
CREATE TABLE smetka (
    smetka_id SERIAL PRIMARY KEY,
    broj_smetka VARCHAR(20) NOT NULL UNIQUE,
    datum_otvaranje DATE NOT NULL DEFAULT CURRENT_DATE,
    status VARCHAR(30) NOT NULL DEFAULT 'AKTIVNA',
    tip_smetka VARCHAR(100) NOT NULL,
    usluga_id INT NOT NULL,
    klient_id INT NOT NULL,
    kredit_id INT,
    banka_id INT NOT NULL,
    valuta_id INT NOT NULL,
    saldo NUMERIC(15,2) DEFAULT 0,

    CONSTRAINT fk_smetka_usluga FOREIGN KEY (usluga_id)
        REFERENCES usluga(usluga_id) ON DELETE RESTRICT,
    CONSTRAINT fk_smetka_klient FOREIGN KEY (klient_id)
        REFERENCES klient(klient_id) ON DELETE RESTRICT,
    CONSTRAINT fk_smetka_kredit FOREIGN KEY (kredit_id)
        REFERENCES kredit(kredit_id) ON DELETE RESTRICT,
    CONSTRAINT fk_smetka_banka FOREIGN KEY (banka_id)
        REFERENCES banka(banka_id) ON DELETE RESTRICT,
    CONSTRAINT fk_smetka_valuta FOREIGN KEY (valuta_id)
        REFERENCES valuta(valuta_id) ON DELETE RESTRICT,

    CONSTRAINT chk_smetka_status CHECK (status IN ('AKTIVNA', 'BLOKIRANA', 'ZATVORENA'))
);

-- =========================
-- DEPOZIT
-- =========================
CREATE TABLE depozit (
    depozit_id SERIAL PRIMARY KEY,
    iznos_depozit NUMERIC(15,2) NOT NULL,
    rok_depozit INT NOT NULL,
    kamatna_stapka NUMERIC(5,2) NOT NULL,
    datum_odobruvanje DATE,
    datum_aktiviranje DATE,
    momentalna_sostojba NUMERIC(15,2),
    tip_depozit VARCHAR(100),
    usluga_id INT NOT NULL,
    smetka_id INT NOT NULL,
    valuta_id INT NOT NULL,

    CONSTRAINT fk_depozit_usluga FOREIGN KEY (usluga_id)
        REFERENCES usluga(usluga_id) ON DELETE RESTRICT,
    CONSTRAINT fk_depozit_smetka FOREIGN KEY (smetka_id)
        REFERENCES smetka(smetka_id) ON DELETE RESTRICT,
    CONSTRAINT fk_depozit_valuta FOREIGN KEY (valuta_id)
        REFERENCES valuta(valuta_id) ON DELETE RESTRICT,

    CONSTRAINT chk_depozit_iznos CHECK (iznos_depozit >= 0),
    CONSTRAINT chk_depozit_sostojba CHECK (momentalna_sostojba IS NULL OR momentalna_sostojba >= 0),
    CONSTRAINT chk_depozit_rok CHECK (rok_depozit > 0),
    CONSTRAINT chk_depozit_kamata CHECK (kamatna_stapka >= 0)
);

-- =========================
-- TIP_KARTICKA / KARTICKA
-- =========================
CREATE TABLE tip_karticka (
    tip_karticka_id SERIAL PRIMARY KEY,
    ime VARCHAR(50) NOT NULL UNIQUE,
    opis VARCHAR(255)
);

CREATE TABLE karticka (
    karticka_id SERIAL PRIMARY KEY,
    broj_karticka VARCHAR(16) NOT NULL UNIQUE,
    datum_izdavanje DATE NOT NULL DEFAULT CURRENT_DATE,
    datum_istekuvanje DATE NOT NULL,
    cvc_kod VARCHAR(3) NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'AKTIVNA',
    smetka_id INT NOT NULL,
    tip_karticka_id INT NOT NULL,

    CONSTRAINT fk_karticka_smetka FOREIGN KEY (smetka_id)
        REFERENCES smetka(smetka_id) ON DELETE RESTRICT,
    CONSTRAINT fk_karticka_tip FOREIGN KEY (tip_karticka_id)
        REFERENCES tip_karticka(tip_karticka_id) ON DELETE RESTRICT,

    CONSTRAINT chk_karticka_broj CHECK (length(broj_karticka) = 16),
    CONSTRAINT chk_karticka_cvc CHECK (length(cvc_kod) = 3),
    CONSTRAINT chk_karticka_dates CHECK (datum_istekuvanje > datum_izdavanje),
    CONSTRAINT chk_karticka_status CHECK (status IN ('AKTIVNA', 'BLOKIRANA', 'ISTECENA'))
);

-- =========================
-- NALOG
-- =========================
CREATE TABLE nalog (
    nalog_id SERIAL PRIMARY KEY,
    datum_na_valuta DATE NOT NULL DEFAULT CURRENT_DATE,
    povikuvanje_na_broj_odobruvanje VARCHAR(100),
    iznos NUMERIC(15,2) NOT NULL,
    danocen_broj_embg VARCHAR(13),
    svrha_na_plakjanje VARCHAR(255),
    smetka_primalac_id INT,
    cel_na_doznaka VARCHAR(255),
    hitno BOOLEAN NOT NULL DEFAULT FALSE,
    uplateno_mesto VARCHAR(100),
    smetka_na_budetski_korisnik_edinka_korisnik VARCHAR(100),
    prihodna_sifra VARCHAR(50),
    programa VARCHAR(100),
    nacin_plakjanje VARCHAR(100),
    nalogodavac_id INT NOT NULL,
    danocen_broj_primalac VARCHAR(13),
    smetka_nalogodavac_id INT NOT NULL,
    smetka_nalogoprimac_id INT,
    smetka_nalogoprimac VARCHAR(100),
    valuta_id INT NOT NULL,
    potpisnik_id INT,

    CONSTRAINT fk_nalog_klient FOREIGN KEY (nalogodavac_id)
        REFERENCES klient(klient_id) ON DELETE RESTRICT,
    CONSTRAINT fk_nalog_smetka_nalogodavac FOREIGN KEY (smetka_nalogodavac_id)
        REFERENCES smetka(smetka_id) ON DELETE RESTRICT,
    CONSTRAINT fk_nalog_smetka_primalac FOREIGN KEY (smetka_primalac_id)
        REFERENCES smetka(smetka_id) ON DELETE RESTRICT,
    CONSTRAINT fk_nalog_smetka_nalogoprimac FOREIGN KEY (smetka_nalogoprimac_id)
        REFERENCES smetka(smetka_id) ON DELETE RESTRICT,
    CONSTRAINT fk_nalog_valuta FOREIGN KEY (valuta_id)
        REFERENCES valuta(valuta_id) ON DELETE RESTRICT,
    CONSTRAINT fk_nalog_potpisnik FOREIGN KEY (potpisnik_id)
        REFERENCES potpisnik(potpisnik_id) ON DELETE SET NULL,

    CONSTRAINT chk_nalog_iznos CHECK (iznos >= 0),
    CONSTRAINT chk_nalog_different_accounts CHECK (
        smetka_nalogoprimac_id IS NULL
        OR smetka_nalogodavac_id <> smetka_nalogoprimac_id
    )
);

-- =========================
-- TRANSAKCIJA
-- =========================
CREATE TABLE transakcija (
    transakcija_id SERIAL PRIMARY KEY,
    datum_na_valuta DATE NOT NULL DEFAULT CURRENT_DATE,
    iznos NUMERIC(15,2) NOT NULL,
    datum_transakcija TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    opis VARCHAR(255),
    smetka_isprakjac_id INT NOT NULL,
    smetka_primac_id INT NOT NULL,
    nalog_id INT,
    valuta_id INT NOT NULL,

    CONSTRAINT fk_transakcija_smetka_isprakjac FOREIGN KEY (smetka_isprakjac_id)
        REFERENCES smetka(smetka_id) ON DELETE RESTRICT,
    CONSTRAINT fk_transakcija_smetka_primac FOREIGN KEY (smetka_primac_id)
        REFERENCES smetka(smetka_id) ON DELETE RESTRICT,
    CONSTRAINT fk_transakcija_nalog FOREIGN KEY (nalog_id)
        REFERENCES nalog(nalog_id) ON DELETE SET NULL,
    CONSTRAINT fk_transakcija_valuta FOREIGN KEY (valuta_id)
        REFERENCES valuta(valuta_id) ON DELETE RESTRICT,

    CONSTRAINT chk_transakcija_iznos CHECK (iznos >= 0),
    CONSTRAINT chk_transakcija_diff_accounts CHECK (smetka_isprakjac_id <> smetka_primac_id)
);

-- =========================
-- RATA_KREDIT
-- =========================
CREATE TABLE rata_kredit (
    rata_kredit_id SERIAL PRIMARY KEY,
    datum_na_valuta DATE NOT NULL DEFAULT CURRENT_DATE,
    status VARCHAR(30) NOT NULL DEFAULT 'NEPLATENA',
    iznos_rata NUMERIC(15,2) NOT NULL,
    kredit_id INT NOT NULL,
    transakcija_id INT,

    CONSTRAINT fk_rata_kredit_kredit FOREIGN KEY (kredit_id)
        REFERENCES kredit(kredit_id) ON DELETE RESTRICT,
    CONSTRAINT fk_rata_kredit_transakcija FOREIGN KEY (transakcija_id)
        REFERENCES transakcija(transakcija_id) ON DELETE SET NULL,

    CONSTRAINT chk_rata_kredit_iznos CHECK (iznos_rata >= 0),
    CONSTRAINT chk_rata_kredit_status CHECK (status IN ('PLATENA', 'NEPLATENA', 'DOCNI'))
);