RelationalDesign: dml-3.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_id,
    portfolio_id
) VALUES
(1, 10, 1480.00, 1, 1),
(2, 5, 940.00, 2, 1),
(3, 20, 315.00, 3, 2),
(4, 15, 410.00, 4, 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),
(2, 1000.00, 900.00, 2, 1),
(3, 330.00, 310.00, 3, 2),
(4, 430.00, 400.00, 4, 3);


INSERT INTO trade_request (
    id,
    portfolio_id,
    price_per_unit,
    quantity,
    status,
    stock_id,
    timestamp,
    type
) VALUES
(1, 1, 1510.00, 5, 'PENDING', 1, '2026-02-02 09:00:00', 'BUY'),
(2, 2, 325.00, 10, 'PENDING', 3, '2026-02-02 09:30:00', 'SELL'),
(3, 1, 960.00, 3, 'PENDING', 2, '2026-02-02 10:00:00', 'BUY');


INSERT INTO transactions (
    id,
    type,
    quantity,
    price,
    timestamp,
    origin,
    user_id,
    stock_id,
    trade_request_id
) VALUES
(1, 'BUY', 5, 1510.00, '2026-02-02 09:05:00', 'INTERNAL', 1, 1, 1),
(2, 'SELL', 10, 325.00, '2026-02-02 09:35:00', 'INTERNAL', 2, 3, 2),
(3, 'BUY', 3, 960.00, '2026-02-02 10:05:00', 'INTERNAL', 1, 2, 3);


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


INSERT INTO oauth_pending_links (
    token,
    email,
    provider,
    expires_at,
    created_at,
    user_id,
    auth_provider_id
) VALUES
(
    'token-davor-123',
    'davor@example.com',
    'INTERNAL',
    '2026-02-02 11:00:00',
    '2026-02-02 10:00:00',
    1,
    1
),
(
    'token-ana-123',
    'ana@example.com',
    'GOOGLE',
    '2026-02-02 11:30:00',
    '2026-02-02 10:30:00',
    2,
    2
);