-- Diaspora Portal Investment Club schema
-- Target database: MySQL 8+ / MariaDB 10.6+
-- Product: My Diaspora Investment
-- Vision: member investment club, pooled portfolios, tiered contributions, transparent tracking, leaderboards, allocation rules, and advisor/admin workflows.

CREATE TABLE IF NOT EXISTS investment_tiers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tier_key VARCHAR(80) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL,
  tagline VARCHAR(180) NOT NULL,
  monthly_contribution_usd DECIMAL(12,2) NOT NULL,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_investment_tiers_active_sort (is_active, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS investment_member_accounts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  member_id VARCHAR(120) NOT NULL,
  service_assignment_id VARCHAR(160) NOT NULL,
  service_key VARCHAR(120) NOT NULL DEFAULT 'my-diaspora-investment',
  tier_id BIGINT UNSIGNED NOT NULL,
  display_name VARCHAR(180) NULL,
  privacy_mode VARCHAR(80) NOT NULL DEFAULT 'initials-country',
  account_status VARCHAR(80) NOT NULL DEFAULT 'Active',
  contribution_status VARCHAR(80) NOT NULL DEFAULT 'On Track',
  total_contributed_usd DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  current_value_usd DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  monthly_return_usd DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  monthly_return_percent DECIMAL(7,3) NOT NULL DEFAULT 0.000,
  lifetime_return_usd DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  lifetime_return_percent DECIMAL(7,3) NOT NULL DEFAULT 0.000,
  active_portfolios INT UNSIGNED NOT NULL DEFAULT 0,
  contribution_consistency_score DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  next_contribution_due DATE NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_investment_member_service (member_id, service_assignment_id),
  INDEX idx_investment_member_account_member (member_id),
  INDEX idx_investment_member_account_service (service_assignment_id),
  INDEX idx_investment_member_account_status (account_status),
  INDEX idx_investment_member_account_tier (tier_id),
  INDEX idx_investment_member_account_value (current_value_usd),
  CONSTRAINT fk_investment_account_tier
    FOREIGN KEY (tier_id)
    REFERENCES investment_tiers(id)
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS investment_portfolios (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  portfolio_key VARCHAR(120) NOT NULL UNIQUE,
  name VARCHAR(180) NOT NULL,
  category VARCHAR(120) NOT NULL,
  rank_position INT UNSIGNED NULL,
  total_pool_value_usd DECIMAL(16,2) NOT NULL DEFAULT 0.00,
  member_count INT UNSIGNED NOT NULL DEFAULT 0,
  monthly_return_percent DECIMAL(7,3) NOT NULL DEFAULT 0.000,
  quarterly_return_percent DECIMAL(7,3) NOT NULL DEFAULT 0.000,
  yearly_return_percent DECIMAL(7,3) NOT NULL DEFAULT 0.000,
  lifetime_return_percent DECIMAL(7,3) NOT NULL DEFAULT 0.000,
  risk_level VARCHAR(80) NOT NULL DEFAULT 'Moderate',
  liquidity_window VARCHAR(120) NOT NULL,
  minimum_contribution_usd DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  portfolio_status VARCHAR(80) NOT NULL DEFAULT 'Open',
  verification_status VARCHAR(80) NOT NULL DEFAULT 'Under Review',
  manager_name VARCHAR(180) NOT NULL DEFAULT 'Nexus Investment Desk',
  manager_note TEXT NULL,
  latest_report_date DATE NULL,
  is_visible TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_investment_portfolios_visible_sort (is_visible, sort_order),
  INDEX idx_investment_portfolios_category (category),
  INDEX idx_investment_portfolios_rank (rank_position),
  INDEX idx_investment_portfolios_status (portfolio_status),
  INDEX idx_investment_portfolios_verification (verification_status),
  INDEX idx_investment_portfolios_risk (risk_level),
  INDEX idx_investment_portfolios_pool (total_pool_value_usd)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS investment_portfolio_vehicles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  portfolio_id BIGINT UNSIGNED NOT NULL,
  vehicle_name VARCHAR(180) NOT NULL,
  allocation_note VARCHAR(240) NULL,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  UNIQUE KEY uniq_investment_vehicle (portfolio_id, vehicle_name),
  INDEX idx_investment_vehicle_sort (portfolio_id, sort_order),
  CONSTRAINT fk_investment_vehicle_portfolio
    FOREIGN KEY (portfolio_id)
    REFERENCES investment_portfolios(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS investment_allocation_rules (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tier_id BIGINT UNSIGNED NOT NULL,
  portfolio_id BIGINT UNSIGNED NOT NULL,
  allocation_percent DECIMAL(6,3) NOT NULL DEFAULT 0.000,
  rationale VARCHAR(255) NULL,
  effective_from DATE NOT NULL,
  effective_to DATE NULL,
  rule_status VARCHAR(80) NOT NULL DEFAULT 'Active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_allocation_rule_window (tier_id, portfolio_id, effective_from),
  INDEX idx_allocation_rules_tier (tier_id, rule_status),
  INDEX idx_allocation_rules_portfolio (portfolio_id),
  INDEX idx_allocation_rules_window (effective_from, effective_to),
  CONSTRAINT fk_allocation_rule_tier
    FOREIGN KEY (tier_id)
    REFERENCES investment_tiers(id)
    ON DELETE CASCADE,
  CONSTRAINT fk_allocation_rule_portfolio
    FOREIGN KEY (portfolio_id)
    REFERENCES investment_portfolios(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS investment_member_positions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  member_account_id BIGINT UNSIGNED NOT NULL,
  portfolio_id BIGINT UNSIGNED NOT NULL,
  contributed_usd DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  current_value_usd DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  ownership_percent DECIMAL(9,4) NOT NULL DEFAULT 0.0000,
  monthly_return_usd DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  monthly_return_percent DECIMAL(7,3) NOT NULL DEFAULT 0.000,
  lifetime_return_usd DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  lifetime_return_percent DECIMAL(7,3) NOT NULL DEFAULT 0.000,
  position_status VARCHAR(80) NOT NULL DEFAULT 'Active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_member_portfolio_position (member_account_id, portfolio_id),
  INDEX idx_investment_position_member (member_account_id),
  INDEX idx_investment_position_portfolio (portfolio_id),
  INDEX idx_investment_position_status (position_status),
  INDEX idx_investment_position_stake (portfolio_id, current_value_usd),
  CONSTRAINT fk_investment_position_account
    FOREIGN KEY (member_account_id)
    REFERENCES investment_member_accounts(id)
    ON DELETE CASCADE,
  CONSTRAINT fk_investment_position_portfolio
    FOREIGN KEY (portfolio_id)
    REFERENCES investment_portfolios(id)
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS investment_contribution_schedules (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  member_account_id BIGINT UNSIGNED NOT NULL,
  tier_id BIGINT UNSIGNED NOT NULL,
  scheduled_amount_usd DECIMAL(12,2) NOT NULL,
  schedule_frequency VARCHAR(40) NOT NULL DEFAULT 'Monthly',
  next_due_date DATE NOT NULL,
  schedule_status VARCHAR(80) NOT NULL DEFAULT 'Active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_contribution_schedule_account (member_account_id),
  INDEX idx_contribution_schedule_due (next_due_date, schedule_status),
  CONSTRAINT fk_contribution_schedule_account
    FOREIGN KEY (member_account_id)
    REFERENCES investment_member_accounts(id)
    ON DELETE CASCADE,
  CONSTRAINT fk_contribution_schedule_tier
    FOREIGN KEY (tier_id)
    REFERENCES investment_tiers(id)
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS investment_contributions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contribution_uuid VARCHAR(160) NOT NULL UNIQUE,
  member_account_id BIGINT UNSIGNED NOT NULL,
  contribution_type VARCHAR(80) NOT NULL DEFAULT 'monthly-contribution',
  amount_usd DECIMAL(14,2) NOT NULL,
  contribution_status VARCHAR(80) NOT NULL DEFAULT 'Pending',
  received_at TIMESTAMP NULL,
  admin_notes TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_investment_contribution_account (member_account_id),
  INDEX idx_investment_contribution_status (contribution_status),
  INDEX idx_investment_contribution_created (created_at),
  CONSTRAINT fk_investment_contribution_account
    FOREIGN KEY (member_account_id)
    REFERENCES investment_member_accounts(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS investment_contribution_allocations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contribution_id BIGINT UNSIGNED NOT NULL,
  portfolio_id BIGINT UNSIGNED NOT NULL,
  allocation_percent DECIMAL(6,3) NOT NULL DEFAULT 0.000,
  allocated_amount_usd DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  allocation_status VARCHAR(80) NOT NULL DEFAULT 'Allocated',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_contribution_portfolio_allocation (contribution_id, portfolio_id),
  INDEX idx_contribution_allocation_portfolio (portfolio_id),
  CONSTRAINT fk_contribution_allocation_contribution
    FOREIGN KEY (contribution_id)
    REFERENCES investment_contributions(id)
    ON DELETE CASCADE,
  CONSTRAINT fk_contribution_allocation_portfolio
    FOREIGN KEY (portfolio_id)
    REFERENCES investment_portfolios(id)
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS investment_portfolio_performance (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  portfolio_id BIGINT UNSIGNED NOT NULL,
  period_key VARCHAR(20) NOT NULL,
  period_label VARCHAR(80) NOT NULL,
  starting_value_usd DECIMAL(16,2) NOT NULL DEFAULT 0.00,
  ending_value_usd DECIMAL(16,2) NOT NULL DEFAULT 0.00,
  return_usd DECIMAL(16,2) NOT NULL DEFAULT 0.00,
  return_percent DECIMAL(7,3) NOT NULL DEFAULT 0.000,
  rank_position INT UNSIGNED NULL,
  manager_note TEXT NULL,
  published_at TIMESTAMP NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_investment_performance_period (portfolio_id, period_key),
  INDEX idx_investment_performance_period (period_key),
  INDEX idx_investment_performance_rank (period_key, rank_position),
  CONSTRAINT fk_investment_performance_portfolio
    FOREIGN KEY (portfolio_id)
    REFERENCES investment_portfolios(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS investment_leaderboard_snapshots (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  period_key VARCHAR(20) NOT NULL,
  scope_type VARCHAR(80) NOT NULL,
  scope_id VARCHAR(120) NOT NULL,
  member_account_id BIGINT UNSIGNED NOT NULL,
  rank_position INT UNSIGNED NOT NULL,
  stake_usd DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  ownership_percent DECIMAL(9,4) NOT NULL DEFAULT 0.0000,
  monthly_gain_usd DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  lifetime_gain_usd DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  consistency_score DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_leaderboard_entry (period_key, scope_type, scope_id, member_account_id),
  INDEX idx_leaderboard_scope_rank (period_key, scope_type, scope_id, rank_position),
  INDEX idx_leaderboard_member (member_account_id),
  CONSTRAINT fk_leaderboard_member_account
    FOREIGN KEY (member_account_id)
    REFERENCES investment_member_accounts(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS investment_action_requests (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  request_uuid VARCHAR(160) NOT NULL UNIQUE,
  member_id VARCHAR(120) NOT NULL,
  service_assignment_id VARCHAR(160) NOT NULL,
  service_key VARCHAR(120) NOT NULL DEFAULT 'my-diaspora-investment',
  member_account_id BIGINT UNSIGNED NULL,
  portfolio_id BIGINT UNSIGNED NULL,
  action_type VARCHAR(80) NOT NULL,
  amount_usd DECIMAL(14,2) NULL,
  request_status VARCHAR(80) NOT NULL DEFAULT 'Pending Admin Review',
  member_notes TEXT NULL,
  admin_notes TEXT NULL,
  assigned_advisor VARCHAR(180) NULL,
  next_follow_up_at VARCHAR(80) NULL,
  submitted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_investment_action_member (member_id),
  INDEX idx_investment_action_service (service_assignment_id),
  INDEX idx_investment_action_account (member_account_id),
  INDEX idx_investment_action_portfolio (portfolio_id),
  INDEX idx_investment_action_status (request_status),
  INDEX idx_investment_action_submitted (submitted_at),
  CONSTRAINT fk_investment_action_account
    FOREIGN KEY (member_account_id)
    REFERENCES investment_member_accounts(id)
    ON DELETE SET NULL,
  CONSTRAINT fk_investment_action_portfolio
    FOREIGN KEY (portfolio_id)
    REFERENCES investment_portfolios(id)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS investment_meetups (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  meetup_uuid VARCHAR(160) NOT NULL UNIQUE,
  title VARCHAR(180) NOT NULL,
  topic VARCHAR(240) NOT NULL,
  meetup_date DATE NOT NULL,
  meetup_status VARCHAR(80) NOT NULL DEFAULT 'RSVP open',
  meeting_url VARCHAR(500) NULL,
  recording_url VARCHAR(500) NULL,
  summary TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_investment_meetup_date (meetup_date),
  INDEX idx_investment_meetup_status (meetup_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS investment_reports (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  report_uuid VARCHAR(160) NOT NULL UNIQUE,
  portfolio_id BIGINT UNSIGNED NULL,
  report_type VARCHAR(80) NOT NULL DEFAULT 'Monthly Portfolio Report',
  report_title VARCHAR(220) NOT NULL,
  period_key VARCHAR(20) NOT NULL,
  document_url VARCHAR(500) NULL,
  report_summary TEXT NULL,
  published_at TIMESTAMP NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_investment_report_portfolio (portfolio_id),
  INDEX idx_investment_report_period (period_key),
  INDEX idx_investment_report_type (report_type),
  CONSTRAINT fk_investment_report_portfolio
    FOREIGN KEY (portfolio_id)
    REFERENCES investment_portfolios(id)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;