-- create a table
CREATE TABLE KOMINTENTI(
KOMINTENT_id INTEGER PRIMARY KEY, 
NAZIV VARCHAR(20) NOT NULL,
GRAD VARCHAR(20) NOT NULL,
STATUS VARCHAR(10) NOT NULL  
);
-- insert some values
INSERT INTO KOMINTENTI VALUES (1, 'MURAJ D.O.O.', 'ZADAR','DOBAVLJAC');
INSERT INTO KOMINTENTI VALUES (2, 'MURAJ D.O.O.', 'ZADAR','KUPAC');
INSERT INTO KOMINTENTI VALUES (3, 'STIL D.O.O. ', 'SPLIT','DOBAVLJAC');
INSERT INTO KOMINTENTI VALUES (4, 'T.O.JULIJA', 'PREKO','KUPAC');
INSERT INTO KOMINTENTI VALUES (5,  'UO OTOK', 'ZAGREB','KUPAC');

--SELECT * FROM KOMINTENTI WHERE STATUS = 'KUPAC';
---SELECT MAX(LEN(GRAD)) AS[NAJDUZI_GRAD] FROM KOMINTENTI


CREATE TABLE RACUNI(
RACUN_ID INTEGER PRIMARY KEY,
RACUN_BROJ VARCHAR(10) NOT NULL,
RACUN_DAT DATE NOT NULL,
RAC_DOSP DATE NOT NULL,
RAC_STAT CHAR(6),    
KOMINTENT_ID,
SUMA DECIMAL(10,2) NOT NULL,
FOREIGN KEY(KOMINTENT_ID) REFERENCES KOMINTENTI(KOMINTENT_ID)    
);

INSERT INTO RACUNI VALUES (1,'10-1-1','2024-10-15','2024-10-31','ULAZNI','1','150,00');
INSERT INTO RACUNI VALUES (2,'1-1-1','2024-12-15','2024-12-31','ULAZNI','1','15,33');
INSERT INTO RACUNI VALUES (3,'15/1/1','2024-11-05','2024-11-15','IZLAZNI','2','1050,01');
INSERT INTO RACUNI VALUES (4,'13-1-P1','2024-11-15','2024-11-30','IZLAZNI','4','300,00');
INSERT INTO RACUNI VALUES (5,'120-1-1','2024-12-01','2024-12-10','ULAZNI','5','1000,00');
INSERT INTO RACUNI VALUES (6,'12-1-1','2024-12-01','2024-12-15','ULAZNI','3','1500,00');

 
--SELECT* FROM RACUNI WHERE RAC_DOSP = '2024-12-10'
--SELECT * FROM RACUNI JOIN KOMINTENTI ON RACUNI.KOMINTENT_ID=KOMINTENTI.KOMINTENT_ID WHERE NAZIV ='MURAJ D.O.O.'

CREATE TABLE KNJIGE(
KNJIG_ID INTEGER PRIMARY KEY AUTOINCREMENT,
KNJIG_DAT DATE NOT NULL,
RACUN_ID,
KONTO VARCHAR(10) NOT NULL,
IZVOD_BR,    
FOREIGN KEY(RACUN_ID) REFERENCES RACUNI(RACUN_ID)
FOREIGN KEY(IZVOD_BR) REFERENCES PLACANJA(IZVOD_BR)    
);

INSERT INTO KNJIGE VALUES(1,'2024-10-15','1','TROSAK','5');
INSERT INTO KNJIGE VALUES(2,'2024-11-15','2','TROSAK','6');
INSERT INTO KNJIGE VALUES(3,'2024-11-15','3','PRIHOD','1');
INSERT INTO KNJIGE VALUES(4,'2024-10-15','4','PRIHOD','151');
INSERT INTO KNJIGE VALUES(5,'2024-12-15','5','PRIHOD','150');
--SELECT * FROM KNJIGE JOIN RACUNI ON KNJIGA.RACUN_ID = RACUNI.RACUN_ID WHERE RACUN_BROJ='15/1/1'
--SELECT * FROM KNJIGE JOIN RACUNI ON KNJIGE.RACUN_ID=RACUNI.RACUN_ID WHERE RACUN_BROJ='15/1/1' 
 -- SELECT * FROM KNJIGE  
CREATE TABLE PLACANJA(
IZVOD_ID INTEGER PRIMARY KEY AUTOINCREMENT,
IZVOD_DAT DATE NOT NULL,
IZVOD_BR INT NOT NULL,
UPL_IZN DECIMAL(10,2),
ISP_IZN DECIMAL(10,2)
--KNJIG_ID,
--FOREIGN KEY(KNJIG_ID) REFERENCES KNJIGE(KNJIG_ID)      
);
  -- SELECT * FROM PLACANJA
INSERT INTO PLACANJA VALUES(1,'2024-10-31','1','0,00','150,00');
INSERT INTO PLACANJA VALUES(2,'2024-12-15','5','0,00','1500,00');
INSERT INTO PLACANJA VALUES(3,'2024-11-30','6','810,55','00,00');
INSERT INTO PLACANJA VALUES(4,'2024-12-31','150','00,00','150,00');
INSERT INTO PLACANJA VALUES(5,'2024-12-31','151','510,00','0,00');

--SELECT* FROM PLACANJA
--SELECT* FROM PLACANJA WHERE IZVOD_BR='150'    
---SELECT RACUNI.SUMA FROM RACUNI WHERE RAC_STAT='ULAZNI'
---GROUP BY RACUNI.SUMA
--SELECT IZVOD_DAT FROM PLACANJA WHERE ISP_IZN>'140,00'
--SELECT UPL_IZN,IZVOD_BR FROM PLACANJA WHERE IZVOD_DAT='2024-11-30'
    --SELECT RACUNI.RACUN_BROJ, COUNT(*)AS LISTING_RACUN
--FROM RACUNI
--WHERE SUMA !='1000,00'
--GROUP BY RACUN_BROJ
--ORDER BY LISTING_RACUN 
--SELECT NOW()- RAC_DOSP FROM RACUNI WHERE RACUN_BROJ=1