RelationalDesign: dml-2.sql

INSERT INTO users (id, email, password, role, username) VALUES
(1, 'davor@example.com', 'hashed_password_1', 'USER', 'davor'),
(2, 'ana@example.com', 'hashed_password_2', 'USER', 'ana'),
(3, 'marko@example.com', 'hashed_password_3', 'ADMIN', 'marko');

INSERT INTO stock (id, symbol, name, current_price, last_price, percentage, turnover, last_updated) VALUES
(1, 'ALK', 'Alkaloid AD Skopje', 1500.00, 1490.00, 0.67, 120000.00, '2026-01-21 10:00:00'),
(2, 'KMB', 'Komercijalna banka AD Skopje', 950.50, 940.00, 1.11, 85000.00, '2026-01-21 10:00:00'),
(3, 'MPT', 'Makpetrol AD Skopje', 320.75, 315.00, 1.84, 50000.00, '2026-01-21 10:00:00'),
(4, 'STBP', 'Stopanska banka AD Skopje', 420.25, 410.00, 2.52, 60000.00, '2026-01-21 10:00:00');

INSERT INTO portfolios (id, balance, user_id) VALUES
(1, 10000.00, 1),
(2, 5000.00, 2),
(3, 7000.00, 3);


INSERT INTO portfolio_holdings (id, quantity, avg_price, stock_symbol, portfolio_id) VALUES
(1, 10, 1480.00, 'ALK', 1),
(2, 5, 940.00, 'KMB', 1),
(3, 20, 315.00, 'MPT', 2),
(4, 15, 410.00, 'STBP', 3);

INSERT INTO stock_history (id, price, stock_id,timestamp) VALUES
(1, 1490.00, 1, '2026-01-20'),
(2, 1500.00, 1, '2026-01-21'),
(3, 940.00, 2, '2026-01-20'),
(4, 950.50, 2, '2026-01-21'),
(5, 315.00, 3, '2026-01-20'),
(6, 320.75, 3, '2026-01-21'),
(7, 410.00, 4, '2026-01-20'),
(8, 420.25, 4, '2026-01-21');

INSERT INTO user_auth_providers (user_id, auth_providers) VALUES
(1, 'INTERNAL'),
(2, 'GOOGLE'),
(3, 'INTERNAL');

INSERT INTO watchlist (id, price_above, price_below, stock_id, user_id) VALUES
(1, 1550.00, 1450.00, 1, 1),   -- Davor sledi ALK
(2, 1000.00, 900.00, 2, 1),    -- Davor sledi KMB
(3, 330.00, 310.00, 3, 2),     -- Ana sledi MPT
(4, 430.00, 400.00, 4, 3);     -- Marko sledi STBP

INSERT INTO trade_request (id, portfolio_id, price_per_unit, quantity, status, stock_symbol, timestamp, type)
VALUES
(1, 1, 1510.00, 5, 'PENDING', 'ALK', '2026-02-02 09:00:00', 'BUY'),

-- Ana saka da prodade 10 MPT po 325
(2, 2, 325.00, 10, 'PENDING', 'MPT', '2026-02-02 09:30:00', 'SELL'),

-- Davor saka da kupi 3 KMB po 960
(3, 1, 960.00, 3, 'PENDING', 'KMB', '2026-02-02 10:00:00', 'BUY');


INSERT INTO transactions (id, type, quantity, price, timestamp, origin, user_id, stock_id)
VALUES
(1, 'BUY', 5, 1510.00, '2026-02-02 09:05:00', 'INTERNAL', 1, 1),

-- Ana prodava 10 MPT
(2, 'SELL', 10, 325.00, '2026-02-02 09:35:00', 'INTERNAL', 2, 3),

-- Davor kupuva 3 KMB
(3, 'BUY', 3, 960.00, '2026-02-02 10:05:00', 'INTERNAL', 1, 2);


-- UPDATE TRADE_REQUEST STATUS
UPDATE trade_request
SET status = 'COMPLETED'
WHERE id IN (1,2,3);