RelationalDesign: polnenje.sql

set search_path = project;

INSERT INTO Korisnik (k_ime, k_adresa, k_telefon, k_email, k_embg, k_is_admin, k_lozinka)
VALUES 
    ('Pavel Paunovski', 'Aerodrom 123', '075500000', 'john.doe@example.com', '1610002450450', false, 'pp'),
    ('Darko Sekulovski', 'Gjorce 123', '076500000', 'jane.smith@example.com', '0403002450450', false, 'ds'),
    ('Petar Gruevski', 'Dracevo 123', '077500000', 'bob.johnson@example.com', '1305002450450', false, 'pg'),
   ('Vangel Ajanovski', 'Finki 123', '078500000', 'bob.johnson@example.com', '1212975450450', true, 'va');


INSERT INTO Patnik (k_id) VALUES (1);



INSERT INTO Vraboten (k_id, v_plata, v_datum_na_vrabotuvanje, v_datum_prekin_vrabotuvanje)
VALUES 
    (2, 70000.00, '2020-04-01', NULL),
    (3, 65000.00, '2020-05-01', NULL);



INSERT INTO Vozac (k_id) VALUES (3);
insert into Kondukter(k_id) values (2);

--INSERT INTO Dokument (d_broj_na_dokumnet, d_datum_na_izdavanje, d_datum_ist, d_koj_go_izdal, l_id)
--VALUES 
--    ('ABC123', '2023-01-01', '2023-12-31', 'Government', 1),
--    ('XYZ456', '2023-02-01', '2023-11-30', 'Company XYZ', 2),
--    ('123DEF', '2023-03-01', '2023-10-31', 'Authority ABC', 3);

INSERT INTO Avtobus (a_registracija, a_seriski_broj, a_broj_sedista)
VALUES 
    ('ABC12345', 'ABC987654', 50),
    ('XYZ54321', 'XYZ123456', 45),
    ('123ABCD', '123XYZ789', 55),
    ('456DEFG', '456XYZ789', 40),
    ('789HIJK', '789XYZ123', 60);

INSERT INTO Linija (li_id, li_ime, li_pravec)
VALUES 
(1, 'Line 1', 'Aerodrom-Gjorce'), 
(2, 'Line 1', 'Gjorce-Aerodrom'), 
(3, 'Line 2', 'Gjorce-Dracevo'),
(4, 'Line 2', 'Dracevo-Gjorce'),
(5, 'Line 3', 'Dracevo-Aerodrom'), 
(6, 'Line 3', 'Aerodrom-Dracevo'),
(7, 'Line 4', 'Butel-Centar'), 
(8, 'Line 4', 'Centar-Butel'),
(9, 'Line 5', 'Vodno-Chair'),
(10, 'Line 5', 'Chair-Vodno');

INSERT INTO InstancaNaLinija (inl_datum_start, inl_datum_end, vozac_k_id, a_registracija, li_id)
VALUES 
    ('2023-01-01 12:00:00', '2023-01-01 13:00:00', 3, 'ABC12345', 1),
    ('2023-02-01 12:00:00', '2023-02-01 13:00:00', 3, 'XYZ54321', 2),
    ('2023-03-01 12:00:00', '2023-03-01 13:00:00', 3, '123ABCD', 3),
    ('2023-04-01 12:00:00', '2023-04-01 13:00:00', 3, '456DEFG', 4),
    ('2023-05-01 12:00:00', '2023-05-01 13:00:00', 3, '789HIJK', 5);

INSERT INTO Mesto (m_grad, m_opstina, m_ulica)
VALUES 
    ('Skopje', 'Aerodrom', 'Lake Street 2'),
    ('Skopje', 'Aerodrom', 'Mountain Street 3'),
    ('Skopje', 'Aerodrom', 'Valley Street 4'),
    ('Skopje', 'Aerodrom', 'River Street 5'),
    ('Skopje', 'Aerodrom', 'Mountain Avenue 12'),
    ('Skopje', 'Aerodrom', 'Valley Lane 8'),
    ('Skopje', 'Aerodrom', 'Ocean View 3'),
    ('Skopje', 'Aerodrom', 'Forest Drive 7'),
        ('Skopje', 'Aerodrom', 'Sunset Boulevard 10'),
        ('Skopje', 'Aerodrom', 'Meadow Street 21'),
        ('Skopje', 'Aerodrom', 'Highland Place 14'),
        ('Skopje', 'Aerodrom', 'Hillside Road 19'),
        ('Skopje', 'Aerodrom', 'Lake Shore 6'),
        ('Skopje', 'Aerodrom', 'Park Avenue 25'),
        ('Skopje', 'Aerodrom', 'Creek Side 17'),
        ('Skopje', 'Aerodrom', 'Greenway 4'),
        ('Skopje', 'Aerodrom', 'Ridge Street 11'),
        ('Skopje', 'Aerodrom', 'Harbor View 9'),
        ('Skopje', 'Aerodrom', 'Maple Lane 13'),
        ('Skopje', 'Aerodrom', 'Pinecrest 16'),
    ('Skopje', 'Centar', 'Mountain Ridge 5'),
    ('Skopje', 'Centar', 'Valley Court 8'),
    ('Skopje', 'Centar', 'Riverfront Avenue 11'),
    ('Skopje', 'Centar', 'Forest View 7'),
    ('Skopje', 'Centar', 'Sunset Plaza 10'),
    ('Skopje', 'Centar', 'Meadow Lane 21'),
    ('Skopje', 'Centar', 'Highland Terrace 14'),
    ('Skopje', 'Centar', 'Hillside Place 19'),
    ('Skopje', 'Centar', 'Lakeview Drive 6'),
    ('Skopje', 'Centar', 'Parkside Circle 25'),
    ('Skopje', 'Centar', 'Creek Road 17'),
    ('Skopje', 'Centar', 'Greenwood Street 4'),
    ('Skopje', 'Centar', 'Ridgeview Court 13'),
    ('Skopje', 'Centar', 'Harbor Lane 9'),
    ('Skopje', 'Centar', 'Maple Grove 16'),
    ('Skopje', 'Centar', 'Pineview Avenue 22'),
    ('Skopje', 'Centar', 'Riverside Terrace 18'),
    ('Skopje', 'Centar', 'Blossom Lane 23'),
    ('Skopje', 'Centar', 'Winding Street 20'),
    ('Skopje', 'Centar', 'Sunnyvale Place 15'),
    ('Skopje', 'Karposh', 'Mountain Summit 27'),
    ('Skopje', 'Karposh', 'Valley Heights 33'),
    ('Skopje', 'Karposh', 'River Bend 41'),
    ('Skopje', 'Karposh', 'Forest Grove 29'),
    ('Skopje', 'Karposh', 'Sunset Ridge 36'),
    ('Skopje', 'Karposh', 'Meadow Haven 45'),
    ('Skopje', 'Karposh', 'Highland Crest 38'),
    ('Skopje', 'Karposh', 'Hilltop View 43'),
    ('Skopje', 'Karposh', 'Lakefront Lane 30'),
    ('Skopje', 'Karposh', 'Parkview Terrace 55'),
    ('Skopje', 'Karposh', 'Creek Side 47'),
    ('Skopje', 'Karposh', 'Greenfield Place 24'),
    ('Skopje', 'Karposh', 'Ridgecrest Court 35'),
    ('Skopje', 'Karposh', 'Harbor Side 39'),
    ('Skopje', 'Karposh', 'Maple Heights 46'),
    ('Skopje', 'Karposh', 'Pinecrest Lane 52'),
    ('Skopje', 'Karposh', 'Riverside Court 48'),
    ('Skopje', 'Karposh', 'Blossom View 53'),
    ('Skopje', 'Karposh', 'Winding Path 50'),
    ('Skopje', 'Karposh', 'Sunnyvale Ridge 37')
    ;

INSERT INTO Postojka (p_id, p_ime, m_id)
VALUES 
    (1, 'Station 1', 1),
    (2, 'Station 2', 2),
    (3, 'Station 3', 3),
    (4, 'Station 4', 4),
    (5, 'Station 5', 5),
    (6, 'Station 6', 6),
    (7, 'Station 7', 7),
    (8, 'Station 8', 8),
    (9, 'Station 9', 9),
    (10, 'Station 10', 10),
    (11, 'Station 11', 11),
    (12, 'Station 12', 12),
    (13, 'Station 13', 13),
    (14, 'Station 14', 14),
    (15, 'Station 15', 15),
    (16, 'Station 16', 16),
    (17, 'Station 17', 17),
    (18, 'Station 18', 18),
    (19, 'Station 19', 19),
    (20, 'Station 20', 20),
    (21, 'Station 21', 21),
    (22, 'Station 22', 22),
    (23, 'Station 23', 23),
    (24, 'Station 24', 24),
    (25, 'Station 25', 25),
    (26, 'Station 26', 26),
    (27, 'Station 27', 27),
    (28, 'Station 28', 28),
    (29, 'Station 29', 29),
    (30, 'Station 30', 30),
    (31, 'Station 31', 31),
    (32, 'Station 32', 32),
    (33, 'Station 33', 33),
    (34, 'Station 34', 34),
    (35, 'Station 35', 35),
    (36, 'Station 36', 36),
    (37, 'Station 37', 37),
    (38, 'Station 38', 38),
    (39, 'Station 39', 39),
    (40, 'Station 40', 40),
    (41, 'Station 41', 41),
    (42, 'Station 42', 42),
    (43, 'Station 43', 43),
    (44, 'Station 44', 44),
    (45, 'Station 45', 45),
    (46, 'Station 46', 46),
    (47, 'Station 47', 47),
    (48, 'Station 48', 48),
    (49, 'Station 49', 49),
    (50, 'Station 50', 50),
    (51, 'Station 51', 51),
    (52, 'Station 52', 52),
    (53, 'Station 53', 53),
    (54, 'Station 54', 54),
    (55, 'Station 55', 55),
    (56, 'Station 56', 56),
    (57, 'Station 57', 57),
    (58, 'Station 58', 58),
    (59, 'Station 59', 59),
    (60, 'Station 60', 60);

INSERT INTO PostojkaNaLinija (pnl_reden_broj, li_id, p_id)
VALUES 
(1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 1, 4),
(5, 1, 5),
(6, 1, 6),
(7, 1, 7),
(8, 1, 8),
(9, 1, 9),
(10, 1, 10),
(11, 1, 11),
(12, 1, 12),
(13, 1, 13),
(14, 1, 14),
(14, 1, 60),
(13, 1, 58),
(12, 1, 56),
(11, 1, 54),
(10, 1, 52),
(9, 1, 50),
(8, 1, 48),
(7, 1, 46),
(6, 1, 44),
(5, 1, 42),
(4, 1, 40),
(3, 1, 38),
(2, 1, 36),
(1, 1, 34),
(1, 2, 1),
(2, 2, 4),
(3, 2, 7),
(4, 2, 10),
(5, 2, 13),
(6, 2, 16),
(7, 2, 19),
(8, 2, 22),
(9, 2, 25),
(10, 2, 28),
(11, 2, 31),
(12, 2, 34),
(13, 2, 37),
(14, 2, 40),
(10, 2, 60),
(9, 2, 58),
(8, 2, 56),
(7, 2, 54),
(6, 2, 52),
(5, 2, 50),
(4, 2, 48),
(3, 2, 46),
(2, 2, 44),
(1, 2, 42),
(1, 3, 1),
(2, 3, 4),
(3, 3, 7),
(4, 3, 10),
(5, 3, 13),
(6, 3, 16),
(7, 3, 19),
(8, 3, 22),
(9, 3, 25),
(10, 3, 28),
(11, 3, 31),
(12, 3, 34),
(12, 3, 60),
(11, 3, 56),
(10, 3, 52),
(9, 3, 48),
(8, 3, 44),
(7, 3, 40),
(6, 3, 36),
(5, 3, 32),
(4, 3, 28),
(3, 3, 24),
(2, 3, 20),
(1, 3, 16),
(1, 4, 1),
(2, 4, 5),
(3, 4, 9),
(4, 4, 13),
(5, 4, 17),
(6, 4, 21),
(7, 4, 25),
(8, 4, 29),
(9, 4, 33),
(10, 4, 37),
(11, 4, 41),
(12, 4, 45),
(13, 4, 60),
(12, 4, 59),
(11, 4, 58),
(10, 4, 57),
(9, 4, 56),
(8, 4, 55),
(7, 4, 54),
(6, 4, 53),
(5, 4, 52),
(4, 4, 51),
(3, 4, 50),
(2, 4, 49),
(1, 4, 48),
(1, 5, 1),
(2, 5, 3),
(3, 5, 5),
(4, 5, 7),
(5, 5, 9),
(6, 5, 11),
(7, 5, 13),
(8, 5, 15),
(9, 5, 17),
(10, 5, 19),
(14, 5, 60),
(13, 5, 57),
(12, 5, 54),
(11, 5, 51),
(10, 5, 48),
(9, 5, 45),
(8, 5, 42),
(7, 5, 39),
(6, 5, 36),
(5, 5, 33),
(4, 5, 30),
(3, 5, 27),
(2, 5, 24),
(1, 5, 21);





INSERT INTO TipBilet  (tb_id, tb_trajnost, tb_ime)
VALUES 
    (1, 3600, 'Часовен'),
    (2, 86400, 'Дневен'),
    (3, 604800, 'Неделен'),
    (4, 2419200, 'Месечен'),
    (5, 29030400, 'Годишен');

INSERT INTO Bilet (b_datum_na_kupuvanje, b_status, patnik_k_id, tb_id)
VALUES 
    ('2023-01-01 08:30:00', 'INACTIVE', 1, 1),
    ('2023-02-01 09:30:00', 'INACTIVE', 1, 2),
    ('2023-03-01 10:30:00', 'INACTIVE', 1, 3),
    ('2023-04-01 11:30:00', 'INACTIVE', 1, 4),
    ('2023-05-01 12:30:00', 'INACTIVE', 1, 5);
   
-- TODO: DA SE STAVI TRIGGER ZA KOGA SE KUCA (STARTUVA VOZENJE) DA SE UPDATE-NE STATUSOT VO ACTIVE AKO VEKE NE E ACTIVE!!!!!!!!!
   
INSERT INTO Vozenje (vozenje_start, vozenje_end, patnik_k_id, kacuva_pnl_id, inl_id, b_id, vozenje_status)
VALUES 
    ('2023-01-01 08:00:00', '2023-01-01 10:00:00', 1, 1, 1, 1, 'ACTIVE'),
    ('2023-02-01 09:00:00', '2023-02-01 11:00:00', 1, 30, 2, 2, 'ACTIVE'),
    ('2023-03-01 10:00:00', '2023-03-01 12:00:00', 1, 66, 3, 3, 'ACTIVE'),
    ('2023-04-01 11:00:00', '2023-04-01 13:00:00', 1, 100, 4, 4, 'ACTIVE'),
    ('2023-05-01 12:00:00', '2023-05-01 14:00:00', 1, 122, 5, 5, 'ACTIVE');
   
INSERT INTO Kontroli (kontrola_datum, kondukter_k_id, inl_id)
VALUES 
    ('2023-01-01 12:10:00', 2, 1),
    ('2023-02-01 12:01:00', 2, 2),
    ('2023-03-01 12:20:00', 2, 3),
    ('2023-04-01 12:08:00', 2, 4),
    ('2023-05-01 12:33:00', 2, 5);

INSERT INTO Kazna (kz_iznos, kz_plateno, kz_datum, kondukter_k_id, kontrola_id, kz_datum_plateno, kz_dokument)
VALUES 
    (100.00, false, '2023-01-01', 2, 1, null, 'A12345'),
    (150.00, false, '2023-02-01', 2, 2, null, 'C12345');
   
insert into KaznaZaRegistriran(kz_id, patnik_k_id)
values 
        (1, 1);

insert into KaznaZaNeregistriran(kz_id, kzn_telefon, kzn_ime, kzn_adresa)
values 
        (2, '075111222', 'Stojko Neregistriranovski', 'ulica Rugjer Boshkovikj 12');

--INSERT INTO Se_validira (vozenje_id, b_id)
--VALUES 
--    (3, 3),
--    (4, 4),
--    (5, 5);

INSERT INTO Se_simnuva_na (pnl_id, vozenje_id)
VALUES 
    (8, 1),
    (35, 2),
    (70, 3),
    (102, 4),
    (125, 5);