DatabaseCreation: dml.sql

-- =========================================
-- STOCK (REAL MSE DATA)
-- =========================================
INSERT INTO stock (symbol, name, current_price, last_price, percentage, turnover, last_updated)
VALUES
('ALK', 'Alkaloid AD Skopje', 18500, 18300, 1.09, 1200000, NOW()),
('KMB', 'Komercijalna Banka', 8200, 8100, 1.23, 900000, NOW()),
('STB', 'Stopanska Banka Bitola', 3100, 3050, 1.64, 300000, NOW()),
('MPT', 'Makpetrol', 95000, 94000, 1.06, 500000, NOW()),
('GRNT', 'Granit', 1200, 1180, 1.69, 150000, NOW()),
('REPL', 'Replek', 25000, 24800, 0.80, 200000, NOW()),
('TNB', 'Tutunska Banka', 7200, 7100, 1.40, 350000, NOW()),
('TTK', 'Teteks', 500, 490, 2.04, 50000, NOW());

-- =========================================
-- USERS (~10K)
-- =========================================
INSERT INTO users (email, password, role, username)
SELECT
  'user' || i || '@mail.com',
  md5('password' || i),
  CASE WHEN i % 20 = 0 THEN 'ADMIN' ELSE 'USER' END,
  'user' || i
FROM generate_series(1, 10000) i;

-- =========================================
-- PORTFOLIOS
-- =========================================
INSERT INTO portfolios (balance, user_id)
SELECT
  (random() * 100000)::NUMERIC(18,2),
  id
FROM users;

-- =========================================
-- BANK ACCOUNTS
-- =========================================
INSERT INTO bank_account (account_number, bank, users_id)
SELECT
  1000000000 + id,
  CASE WHEN id % 2 = 0 THEN 'Komercijalna Banka' ELSE 'NLB Banka' END,
  id
FROM users;

-- =========================================
-- STOCK HISTORY (~5M)
-- =========================================
INSERT INTO stock_history (price, stock_id, timestamp)
SELECT
  (100 + random()*10000),
  (random()*7 + 1)::BIGINT,
  CURRENT_DATE - (random()*3650)::INT
FROM generate_series(1, 5000000);


-- =========================================
-- TRADE REQUEST (~6M)  
-- =========================================
INSERT INTO trade_request (portfolio_id, price_per_unit, quantity, status, stock_symbol, timestamp, type)
SELECT
  (random()*9999 + 1)::BIGINT,
  (100 + random()*10000),
  (random()*100)::INT + 1,
  (ARRAY['OPEN','CLOSED','CANCELLED'])[floor(random()*3+1)],
  (ARRAY['ALK','KMB','STB','MPT','GRNT','REPL','TNB','TTK'])[floor(random()*8+1)],
  NOW() - (random() * INTERVAL '1000 minutes'),
  (ARRAY['BUY','SELL'])[floor(random()*2+1)]
FROM generate_series(1, 6000000);

-- =========================================
-- TRADE TRANSACTION (~3M)  
-- =========================================
INSERT INTO trade_transaction (price, quantity, timestamp, type, origin, user_id, stock_id)
SELECT
  tr.price_per_unit,
  tr.quantity,
  tr.timestamp,
  tr.type,
  CASE WHEN random() > 0.7 THEN 'EXTERNAL' ELSE 'INTERNAL' END,
  p.user_id,
  (random()*7 + 1)::BIGINT
FROM trade_request tr
JOIN portfolios p ON tr.portfolio_id = p.id
WHERE tr.status = 'CLOSED'
LIMIT 3000000;

-- =========================================
-- BROKER ORDER (~1M)
-- =========================================
INSERT INTO broker_order (order_type, limit_price, quantity, status, executed_price, broker_fee, mse_reference_number, submitted_at, executed_at, trade_request_id)
SELECT
  tr.type,
  tr.price_per_unit,
  tr.quantity,
  CASE WHEN tr.status = 'CLOSED' THEN 'EXECUTED' ELSE 'PENDING' END,
  tr.price_per_unit,
  (random()*50),
  'MSE-' || tr.id,
  tr.timestamp,
  tr.timestamp + INTERVAL '5 minutes',
  tr.id
FROM trade_request tr
LIMIT 1000000;

-- =========================================
-- ORDER MATCHING LOG (~2M)
-- =========================================
INSERT INTO order_matching_log (broker_order_id, checked_price, result, checked_at)
SELECT
  (random()*999999 + 1)::BIGINT,
  (100 + random()*10000),
  (ARRAY['MATCHED','NO_MATCH','PARTIAL'])[floor(random()*3+1)],
  NOW() - (random() * INTERVAL '1000 minutes')
FROM generate_series(1, 2000000);

-- =========================================
-- PAYMENT TRANSACTION (~200K)
-- =========================================
INSERT INTO payment_transaction (amount, type, status, timestamp, bank_account_id)
SELECT
  (random()*100000)::BIGINT,
  (ARRAY['DEPOSIT','WITHDRAWAL'])[floor(random()*2+1)],
  (ARRAY['PENDING','SUCCESS','FAILED'])[floor(random()*3+1)],
  NOW() - (random() * INTERVAL '1000 minutes'),
  (random()*9999 + 1)::BIGINT
FROM generate_series(1, 200000);

-- =========================================
-- PORTFOLIO HOLDINGS
-- =========================================

INSERT INTO portfolio_holdings (quantity, avg_price, stock_id, portfolio_id)
SELECT
  (random() * 500 + 1)::INT,
  (random() * 10000 + 100)::NUMERIC(38,2),
  (random() * 7 + 1)::BIGINT,
  p.id
FROM portfolios p, generate_series(1, 300);

-- =========================================
-- CFD POSITION (~50K)
-- =========================================
INSERT INTO CFD_position (status, quantity, profit_loss, opened_at, close_price, open_price, users_id, stock_id)
SELECT
  (ARRAY['OPEN','CLOSED'])[floor(random()*2+1)],
  (random() * 100 + 1)::INT,
  (random() * 10000 - 5000),
  CURRENT_DATE - (random() * 365)::INT,
  CASE WHEN random() > 0.5 THEN (random() * 10000 + 100) ELSE NULL END,
  (random() * 10000 + 100),
  (random() * 9999 + 1)::BIGINT,
  (random() * 7 + 1)::BIGINT
FROM generate_series(1, 50000);


-- =========================================
-- DIVIDEND (по 3 дивиденди за секоја акција)
-- =========================================
INSERT INTO dividend (amount_per_share, paymentDate, stock_id)
SELECT
  (random() * 500 + 10)::FLOAT8,
  CURRENT_DATE - (random() * 365 * 3)::INT,
  s.id
FROM stock s, generate_series(1, 3);


-- =========================================
-- DIVIDEND PAYMENT (~100K)
-- =========================================
INSERT INTO dividend_payment (totalAmount, paid_at, dividend_id, users_id)
SELECT
  (random() * 10000 + 100)::FLOAT8,
  d.paymentDate,
  d.id,
  (random() * 9999 + 1)::BIGINT
FROM dividend d, generate_series(1, 4000);


-- =========================================
-- OAUTH PENDING LINKS (~5K)
-- =========================================
INSERT INTO oauth_pending_links (token, email, provider, expires_at, created_at, user_id)
SELECT
  md5('token' || i),
  'user' || i || '@mail.com',
  (ARRAY['GOOGLE','INTERNAL'])[floor(random()*2+1)],
  NOW() + INTERVAL '1 hour',
  NOW() - (random() * INTERVAL '60 minutes'),
  i
FROM generate_series(1, 5000) i;


-- =========================================
-- USER AUTH PROVIDERS (за секој user)
-- =========================================
INSERT INTO user_auth_providers (user_id, auth_providers)
SELECT id, 'INTERNAL' FROM users;

-- дел од корисниците и со GOOGLE
INSERT INTO user_auth_providers (user_id, auth_providers)
SELECT id, 'GOOGLE'
FROM users
WHERE id % 5 = 0;



-- =========================================
-- WATCHLIST (~30K)
-- =========================================
INSERT INTO watchlist (price_above, price_below, stock_id, user_id)
SELECT
  (random() * 5000 + 100)::FLOAT8,
  (random() * 15000 + 6000)::FLOAT8,
  (random() * 7 + 1)::BIGINT,
  (random() * 9999 + 1)::BIGINT
FROM generate_series(1, 30000);


-- =========================================
-- SETTLEMENT RECORD (~500K)
-- потребен е broker_order_id кој постои
-- =========================================
INSERT INTO settlement_record (broker_order_id, user_id, stock_symbol, quantity, settled_price, total_cost, settlement_status, settled_at)
SELECT
  bo.id,
  p.user_id,
  tr.stock_symbol,
  bo.quantity,
  bo.executed_price,
  bo.executed_price * bo.quantity,
  (ARRAY['PENDING','SETTLED','FAILED'])[floor(random()*3+1)],
  bo.executed_at
FROM broker_order bo
JOIN trade_request tr ON bo.trade_request_id = tr.id
JOIN portfolios p ON tr.portfolio_id = p.id
WHERE bo.status = 'EXECUTED'
LIMIT 500000;

-- =========================================
-- VIEWS
-- =========================================



CREATE VIEW trade_transaction_detail_view AS
SELECT
    tt.id AS transaction_id,
    tt.price,
    tt.quantity,
    tt.timestamp,
    tt.type,
    tt.origin,
    tt.user_id,
    tt.stock_id,
    s.symbol,
    s.name AS stock_name
FROM trade_transaction tt
JOIN stock s
    ON tt.stock_id = s.id;


CREATE VIEW trade_request_full_view AS
SELECT
    tr.id AS trade_request_id,
    tr.portfolio_id,
    tr.price_per_unit,
    tr.quantity,
    tr.status,
    tr.stock_symbol,
    tr.timestamp,
    tr.type,
    p.user_id
FROM trade_request tr
JOIN portfolios p
    ON tr.portfolio_id = p.id;


CREATE VIEW stock_history_detail_view AS
SELECT
    sh.id,
    sh.stock_id,
    s.symbol,
    s.name AS stock_name,
    sh.price,
    sh.timestamp
FROM stock_history sh
JOIN stock s
    ON sh.stock_id = s.id;



--ANALYTICAL
CREATE VIEW stock_daily_returns AS
SELECT
  stock_id,
  timestamp,
  price,
  (price - LAG(price) OVER (PARTITION BY stock_id ORDER BY timestamp))
  / NULLIF(LAG(price) OVER (PARTITION BY stock_id ORDER BY timestamp),0) AS daily_return
FROM stock_history;