DatabaseCreation: DDL_fill.sql

TRUNCATE TABLE
users,
bank_account,
portfolios,
stock,
stock_history,
portfolio_holdings,
trade_request,
broker_order,
order_matching_log,
"transaction",
payment_transaction,
settlement_record,
dividend_payment,
dividend,
CFD_position,
watchlist,
oauth_pending_links,
user_auth_providers
RESTART IDENTITY CASCADE;


INSERT INTO users (email, password, role, username)
SELECT
  'user' || i || '@mail.com',
  md5('pass' || i),
  CASE WHEN random() < 0.97 THEN 'USER' ELSE 'ADMIN' END,
  'user_' || i
FROM generate_series(1, 100000) i;

INSERT INTO bank_account (account_number, bank, users_id)
SELECT
  1000000000 + i,
  (ARRAY['NLB','Komercijalna','Stopanska'])[1 + (random()*2)::int],
  i
FROM generate_series(1, 100000) i;


INSERT INTO portfolios (balance, user_id)
SELECT
  (random()*100000)::numeric(18,2),
  i
FROM generate_series(1, 100000) i;

INSERT INTO stock (symbol, name, current_price, last_price, percentage, turnover, last_updated)
SELECT
  'STK' || i,
  'Company ' || i,
  (random()*500 + 5),
  (random()*500 + 5),
  (random()*10 - 5),
  (random()*1000000),
  now()
FROM generate_series(1, 500) i;


INSERT INTO stock_history (price, stock_id, timestamp)
SELECT
  (random()*500 + 5),
  (1 + (random()*499)::int),
  CURRENT_DATE - (random()*365)::int
FROM generate_series(1, 3000000);


INSERT INTO portfolio_holdings (quantity, avg_price, stock_id, portfolio_id)
SELECT
  (random()*200)::int,
  (random()*500 + 5),
  (1 + (random()*499)::int),
  (1 + (random()*99999)::int)
FROM generate_series(1, 800000);


INSERT INTO trade_request (
  portfolio_id,
  price_per_unit,
  quantity,
  status,
  stock_symbol,
  timestamp,
  type
)
SELECT
  (1 + (random()*99999)::int),
  (random()*500 + 5),
  (random()*100)::int,
  CASE
    WHEN random() < 0.6 THEN 'OPEN'
    WHEN random() < 0.9 THEN 'CLOSED'
    ELSE 'CANCELLED'
  END,
  'STK' || (1 + (random()*499)::int),
  now() - (random()*1000 || ' hours')::interval,
  CASE WHEN random() < 0.5 THEN 'BUY' ELSE 'SELL' END
FROM generate_series(1, 1000000);


INSERT INTO broker_order (
  order_type,
  limit_price,
  quantity,
  status,
  executed_price,
  broker_fee,
  mse_reference_number,
  submitted_at,
  executed_at,
  trade_request_id,
  order_matching_logid
)
SELECT
  CASE WHEN random() < 0.5 THEN 'BUY' ELSE 'SELL' END,
  (random()*500 + 5),
  (random()*100)::int,
  CASE
    WHEN random() < 0.7 THEN 'PENDING'
    WHEN random() < 0.9 THEN 'EXECUTED'
    ELSE 'CANCELLED'
  END,
  (random()*500 + 5),
  (random()*10),
  upper(md5(i::text)),
  now(),
  now(),
  (1 + (random()*999999)::int),
  (1 + (random()*999999)::int)
FROM generate_series(1, 1000000) i;


INSERT INTO order_matching_log (
  broker_order_id,
  checked_price,
  result,
  checked_at
)
SELECT
  (1 + (random()*999999)::int),
  (random()*500 + 5),
  CASE
    WHEN random() < 0.5 THEN 'MATCHED'
    WHEN random() < 0.8 THEN 'NO_MATCH'
    ELSE 'PARTIAL'
  END,
  now() - (random()*1000 || ' minutes')::interval
FROM generate_series(1, 1000000);


INSERT INTO "transaction" (
  price,
  quantity,
  timestamp,
  type,
  origin,
  user_id,
  stock_id
)
SELECT
  (random()*500 + 5),
  (random()*100)::int,
  now() - (random()*2000 || ' hours')::interval,
  CASE WHEN random() < 0.5 THEN 'BUY' ELSE 'SELL' END,
  CASE WHEN random() < 0.7 THEN 'INTERNAL' ELSE 'EXTERNAL' END,
  (1 + (random()*99999)::int),
  (1 + (random()*499)::int)
FROM generate_series(1, 2000000);


INSERT INTO payment_transaction (
  amount,
  type,
  status,
  timestamp,
  bank_account_id
)
SELECT
  (random()*10000)::bigint,
  CASE WHEN random() < 0.5 THEN 'DEPOSIT' ELSE 'WITHDRAWAL' END,
  CASE
    WHEN random() < 0.7 THEN 'SUCCESS'
    WHEN random() < 0.9 THEN 'PENDING'
    ELSE 'FAILED'
  END,
  now() - (random()*1000 || ' hours')::interval,
  (1 + (random()*99999)::int)
FROM generate_series(1, 500000);


INSERT INTO dividend (amount_per_share, paymentdate, stock_id)
SELECT
  (random()*5),
  CURRENT_DATE - (random()*365)::int,
  (1 + (random()*499)::int)
FROM generate_series(1, 10000);

INSERT INTO dividend_payment (totalAmount, paid_at, dividend_id, users_id)
SELECT
  (random()*1000),
  CURRENT_DATE - (random()*365)::int,
  (1 + (random()*9999)::int),
  (1 + (random()*99999)::int)
FROM generate_series(1, 50000);


INSERT INTO cfd_position (
  status,
  quantity,
  profit_loss,
  opened_at,
  close_price,
  open_price,
  users_id,
  stock_id
)
SELECT
  CASE WHEN random() < 0.5 THEN 'OPEN' ELSE 'CLOSED' END,
  (random()*100)::int,
  (random()*2000 - 1000),
  CURRENT_DATE - (random()*365)::int,
  (random()*500),
  (random()*500),
  (1 + (random()*99999)::int),
  (1 + (random()*499)::int)
FROM generate_series(1, 100000);


INSERT INTO watchlist (price_above, price_below, stock_id, user_id)
SELECT
  (random()*500 + 50),
  (random()*50),
  (1 + (random()*499)::int),
  (1 + (random()*99999)::int)
FROM generate_series(1, 200000);