-- Diaspora Portal Investment Club seed data
-- Run after investment_schema.sql

INSERT INTO investment_tiers (tier_key, name, tagline, monthly_contribution_usd, sort_order)
VALUES
  ('bronze', 'Bronze Club', 'Building the Habit', 100.00, 1),
  ('silver', 'Silver Club', 'Accelerating Growth', 300.00, 2),
  ('gold', 'Gold Club', 'Building Serious Wealth', 500.00, 3),
  ('platinum', 'Platinum Club', 'Creating Legacy Wealth', 1000.00, 4)
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  tagline = VALUES(tagline),
  monthly_contribution_usd = VALUES(monthly_contribution_usd),
  sort_order = VALUES(sort_order);

INSERT INTO investment_portfolios (
  portfolio_key,
  name,
  category,
  rank_position,
  total_pool_value_usd,
  member_count,
  monthly_return_percent,
  quarterly_return_percent,
  yearly_return_percent,
  lifetime_return_percent,
  risk_level,
  liquidity_window,
  minimum_contribution_usd,
  portfolio_status,
  verification_status,
  manager_name,
  manager_note,
  latest_report_date,
  sort_order
)
VALUES
  (
    'gold-growth-club',
    'Gold Growth Club',
    'Commodities',
    1,
    250000.00,
    48,
    3.400,
    8.100,
    14.800,
    18.600,
    'Moderate',
    'Monthly window',
    500.00,
    'Open',
    'Verified',
    'Nexus Investment Desk',
    'Gold allocation outperformed this month while remaining within the club risk limits.',
    '2026-06-01',
    1
  ),
  (
    'treasury-bills-ladder',
    'Treasury Bills Ladder',
    'Treasury Investments',
    2,
    180000.00,
    62,
    2.100,
    5.700,
    11.200,
    13.400,
    'Low',
    '30-90 day ladder',
    300.00,
    'Open',
    'Verified',
    'Nexus Treasury Desk',
    'Treasury ladder anchors member portfolios with predictable income and low volatility.',
    '2026-06-01',
    2
  ),
  (
    'mmf-stability-pool',
    'MMF Stability Pool',
    'Money Market Funds',
    3,
    132500.00,
    75,
    1.300,
    3.800,
    9.600,
    10.200,
    'Low',
    'High liquidity',
    100.00,
    'Open',
    'Verified',
    'Nexus MMF Desk',
    'MMF pool is designed for capital preservation, liquidity, and disciplined contribution habits.',
    '2026-06-01',
    3
  ),
  (
    'real-estate-income-reserve',
    'Real Estate Income Reserve',
    'Income Reserve',
    4,
    94500.00,
    34,
    0.900,
    2.400,
    7.800,
    8.900,
    'Moderate',
    'Quarterly review',
    500.00,
    'Watchlist',
    'Advisor Review',
    'Nexus Portfolio Committee',
    'Reserve is under review before expansion into new income-generating opportunities.',
    '2026-06-01',
    4
  )
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  category = VALUES(category),
  rank_position = VALUES(rank_position),
  total_pool_value_usd = VALUES(total_pool_value_usd),
  member_count = VALUES(member_count),
  monthly_return_percent = VALUES(monthly_return_percent),
  quarterly_return_percent = VALUES(quarterly_return_percent),
  yearly_return_percent = VALUES(yearly_return_percent),
  lifetime_return_percent = VALUES(lifetime_return_percent),
  risk_level = VALUES(risk_level),
  liquidity_window = VALUES(liquidity_window),
  minimum_contribution_usd = VALUES(minimum_contribution_usd),
  portfolio_status = VALUES(portfolio_status),
  verification_status = VALUES(verification_status),
  manager_name = VALUES(manager_name),
  manager_note = VALUES(manager_note),
  latest_report_date = VALUES(latest_report_date),
  sort_order = VALUES(sort_order);

INSERT INTO investment_portfolio_vehicles (portfolio_id, vehicle_name, allocation_note, sort_order)
SELECT p.id, seed.vehicle_name, seed.allocation_note, seed.sort_order
FROM investment_portfolios p
JOIN (
  SELECT 'gold-growth-club' AS portfolio_key, 'Gold-linked allocation' AS vehicle_name, 'Primary growth sleeve' AS allocation_note, 1 AS sort_order
  UNION ALL SELECT 'gold-growth-club', 'USD reserve buffer', 'Liquidity and volatility buffer', 2
  UNION ALL SELECT 'gold-growth-club', 'Short-term liquidity sleeve', 'Monthly liquidity support', 3
  UNION ALL SELECT 'treasury-bills-ladder', 'Treasury bills', 'Low-risk government-backed ladder', 1
  UNION ALL SELECT 'treasury-bills-ladder', 'Cash reserve', 'Contribution and maturity buffer', 2
  UNION ALL SELECT 'treasury-bills-ladder', 'Maturity ladder', 'Rolling 30-90 day maturities', 3
  UNION ALL SELECT 'mmf-stability-pool', 'Money market funds', 'Capital preservation and steady growth', 1
  UNION ALL SELECT 'mmf-stability-pool', 'Cash management', 'High-liquidity allocation', 2
  UNION ALL SELECT 'real-estate-income-reserve', 'Income reserve', 'Income-focused reserve sleeve', 1
  UNION ALL SELECT 'real-estate-income-reserve', 'Property-linked deposits', 'Watchlist allocation pending review', 2
) seed ON seed.portfolio_key = p.portfolio_key
ON DUPLICATE KEY UPDATE
  allocation_note = VALUES(allocation_note),
  sort_order = VALUES(sort_order);

INSERT INTO investment_member_accounts (
  member_id,
  service_assignment_id,
  service_key,
  tier_id,
  account_status,
  contribution_status,
  total_contributed_usd,
  current_value_usd,
  monthly_return_usd,
  monthly_return_percent,
  lifetime_return_usd,
  lifetime_return_percent,
  next_contribution_due
)
SELECT
  'member-lameck-nyakweba',
  'service-investment-lameck',
  'my-diaspora-investment',
  t.id,
  'Active',
  'On Track',
  15000.00,
  16420.00,
  420.00,
  2.630,
  1420.00,
  9.470,
  '2026-06-30'
FROM investment_tiers t
WHERE t.tier_key = 'gold'
ON DUPLICATE KEY UPDATE
  tier_id = VALUES(tier_id),
  account_status = VALUES(account_status),
  contribution_status = VALUES(contribution_status),
  total_contributed_usd = VALUES(total_contributed_usd),
  current_value_usd = VALUES(current_value_usd),
  monthly_return_usd = VALUES(monthly_return_usd),
  monthly_return_percent = VALUES(monthly_return_percent),
  lifetime_return_usd = VALUES(lifetime_return_usd),
  lifetime_return_percent = VALUES(lifetime_return_percent),
  next_contribution_due = VALUES(next_contribution_due);

INSERT INTO investment_member_positions (
  member_account_id,
  portfolio_id,
  contributed_usd,
  current_value_usd,
  ownership_percent,
  monthly_return_usd,
  monthly_return_percent,
  lifetime_return_usd,
  lifetime_return_percent,
  position_status
)
SELECT a.id, p.id, seed.contributed_usd, seed.current_value_usd, seed.ownership_percent, seed.monthly_return_usd, seed.monthly_return_percent, seed.lifetime_return_usd, seed.lifetime_return_percent, 'Active'
FROM investment_member_accounts a
JOIN (
  SELECT 'gold-growth-club' AS portfolio_key, 5000.00 AS contributed_usd, 5740.00 AS current_value_usd, 2.0000 AS ownership_percent, 170.00 AS monthly_return_usd, 3.400 AS monthly_return_percent, 740.00 AS lifetime_return_usd, 14.800 AS lifetime_return_percent
  UNION ALL SELECT 'treasury-bills-ladder', 4500.00, 5004.00, 2.5000, 94.00, 2.100, 504.00, 11.200
  UNION ALL SELECT 'mmf-stability-pool', 3000.00, 3288.00, 2.2600, 39.00, 1.300, 288.00, 9.600
  UNION ALL SELECT 'real-estate-income-reserve', 2500.00, 2388.00, 2.6500, 22.00, 0.900, -112.00, -4.480
) seed
JOIN investment_portfolios p ON p.portfolio_key = seed.portfolio_key
WHERE a.member_id = 'member-lameck-nyakweba'
  AND a.service_assignment_id = 'service-investment-lameck'
ON DUPLICATE KEY UPDATE
  contributed_usd = VALUES(contributed_usd),
  current_value_usd = VALUES(current_value_usd),
  ownership_percent = VALUES(ownership_percent),
  monthly_return_usd = VALUES(monthly_return_usd),
  monthly_return_percent = VALUES(monthly_return_percent),
  lifetime_return_usd = VALUES(lifetime_return_usd),
  lifetime_return_percent = VALUES(lifetime_return_percent),
  position_status = VALUES(position_status);

INSERT INTO investment_portfolio_performance (
  portfolio_id,
  period_key,
  period_label,
  starting_value_usd,
  ending_value_usd,
  return_usd,
  return_percent,
  rank_position,
  manager_note,
  published_at
)
SELECT p.id, '2026-06', 'June 2026', seed.starting_value_usd, seed.ending_value_usd, seed.return_usd, seed.return_percent, seed.rank_position, seed.manager_note, CURRENT_TIMESTAMP
FROM investment_portfolios p
JOIN (
  SELECT 'gold-growth-club' AS portfolio_key, 241780.00 AS starting_value_usd, 250000.00 AS ending_value_usd, 8220.00 AS return_usd, 3.400 AS return_percent, 1 AS rank_position, 'Gold allocation led the club leaderboard this month.' AS manager_note
  UNION ALL SELECT 'treasury-bills-ladder', 176300.00, 180000.00, 3700.00, 2.100, 2, 'Treasury ladder remained stable and predictable.'
  UNION ALL SELECT 'mmf-stability-pool', 130800.00, 132500.00, 1700.00, 1.300, 3, 'MMF pool preserved liquidity and produced steady growth.'
  UNION ALL SELECT 'real-estate-income-reserve', 93650.00, 94500.00, 850.00, 0.900, 4, 'Reserve remains on watchlist pending committee review.'
) seed ON seed.portfolio_key = p.portfolio_key
ON DUPLICATE KEY UPDATE
  starting_value_usd = VALUES(starting_value_usd),
  ending_value_usd = VALUES(ending_value_usd),
  return_usd = VALUES(return_usd),
  return_percent = VALUES(return_percent),
  rank_position = VALUES(rank_position),
  manager_note = VALUES(manager_note),
  published_at = VALUES(published_at);

INSERT INTO investment_meetups (meetup_uuid, title, topic, meetup_date, meetup_status, summary)
VALUES (
  'meetup-investment-2026-06',
  'June Wealth-Building Meetup',
  'Portfolio discipline, MMFs, Treasury ladders, and gold exposure',
  '2026-06-28',
  'RSVP open',
  'Monthly virtual session for investment club members to review performance, learn strategies, and celebrate milestones.'
)
ON DUPLICATE KEY UPDATE
  title = VALUES(title),
  topic = VALUES(topic),
  meetup_date = VALUES(meetup_date),
  meetup_status = VALUES(meetup_status),
  summary = VALUES(summary);

INSERT INTO investment_reports (report_uuid, portfolio_id, report_type, report_title, period_key, report_summary, published_at)
SELECT CONCAT('report-', p.portfolio_key, '-2026-06'), p.id, 'Monthly Portfolio Report', CONCAT(p.name, ' June 2026 Report'), '2026-06', p.manager_note, CURRENT_TIMESTAMP
FROM investment_portfolios p
ON DUPLICATE KEY UPDATE
  report_title = VALUES(report_title),
  report_summary = VALUES(report_summary),
  published_at = VALUES(published_at);