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()*1000 || ' minutes')::INTERVAL,
  (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()*1000 || ' minutes')::INTERVAL
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()*1000 || ' minutes')::INTERVAL,
  (random()*9999 + 1)::BIGINT
FROM generate_series(1, 200000);

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

-- 1. STOCK DAILY RETURNS
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;

-- 2. USER PORTFOLIO VALUE
CREATE VIEW user_portfolio_value AS
SELECT
  u.id AS user_id,
  SUM(ph.quantity * s.current_price) AS total_value
FROM users u
JOIN portfolios p ON u.id = p.user_id
JOIN portfolio_holdings ph ON p.id = ph.portfolio_id
JOIN stock s ON ph.stock_id = s.id
GROUP BY u.id;

-- 3. MOST TRADED STOCKS
CREATE VIEW most_traded_stocks AS
SELECT
  stock_id,
  COUNT(*) AS trades_count,
  SUM(quantity) AS total_volume
FROM trade_transaction
GROUP BY stock_id
ORDER BY trades_count DESC;