-- ================================================
-- SYNTHEDGE — DATABASE SCHEMA
-- Run once on a fresh MySQL 8+ database
-- ================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ── USERS ──────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
  id                   BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  first_name           VARCHAR(80)  NOT NULL,
  last_name            VARCHAR(80)  NOT NULL,
  email                VARCHAR(255) NOT NULL UNIQUE,
  password_hash        VARCHAR(255) NOT NULL,
  country              CHAR(2)      NOT NULL DEFAULT '',
  affiliate_code       VARCHAR(20)  NOT NULL UNIQUE,
  referred_by          BIGINT UNSIGNED NULL,
  marketing_opt_in     TINYINT(1)   NOT NULL DEFAULT 0,
  is_verified          TINYINT(1)   NOT NULL DEFAULT 0,
  is_banned            TINYINT(1)   NOT NULL DEFAULT 0,
  kyc_status           ENUM('none','pending','approved','rejected') NOT NULL DEFAULT 'none',
  verify_token         VARCHAR(64)  NULL,
  verify_token_expires DATETIME     NULL,
  reset_token          VARCHAR(64)  NULL,
  reset_token_expires  DATETIME     NULL,
  totp_secret          VARCHAR(64)  NULL,
  two_fa_enabled       TINYINT(1)   NOT NULL DEFAULT 0,
  last_login_at        DATETIME     NULL,
  last_login_ip        VARCHAR(45)  NULL,
  created_at           DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at           DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (referred_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_email (email),
  INDEX idx_affiliate_code (affiliate_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── CHALLENGE PLANS (static config) ────────────
CREATE TABLE IF NOT EXISTS plans (
  id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug              VARCHAR(50)    NOT NULL UNIQUE,
  name              VARCHAR(100)   NOT NULL,
  type              ENUM('challenge','instant') NOT NULL DEFAULT 'challenge',
  account_size      DECIMAL(12,2)  NOT NULL,
  price             DECIMAL(8,2)   NOT NULL,
  profit_target_p1  DECIMAL(5,2)   NOT NULL COMMENT 'Percent',
  profit_target_p2  DECIMAL(5,2)   NOT NULL,
  daily_loss_limit  DECIMAL(5,2)   NOT NULL COMMENT 'Percent',
  max_drawdown      DECIMAL(5,2)   NOT NULL COMMENT 'Percent',
  profit_split      DECIMAL(5,2)   NOT NULL COMMENT 'Percent to trader',
  phases            TINYINT        NOT NULL DEFAULT 2,
  is_active         TINYINT(1)     NOT NULL DEFAULT 1,
  created_at        DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed plans
INSERT INTO plans (slug, name, type, account_size, price, profit_target_p1, profit_target_p2, daily_loss_limit, max_drawdown, profit_split, phases) VALUES
('starter-challenge',   'Starter Challenge',   'challenge', 10000,  89,  8.00, 5.00, 4.00, 10.00, 80.00, 2),
('pro-challenge',       'Pro Challenge',       'challenge', 50000,  299, 8.00, 5.00, 4.00, 10.00, 85.00, 2),
('elite-challenge',     'Elite Challenge',     'challenge', 100000, 499, 8.00, 5.00, 4.00, 10.00, 90.00, 2),
('instant-5k',          'Instant $5K',         'instant',   5000,   59,  0,    0,    5.00, 12.00, 75.00, 0),
('instant-25k',         'Instant $25K',        'instant',   25000,  199, 0,    0,    5.00, 12.00, 80.00, 0),
('instant-50k',         'Instant $50K',        'instant',   50000,  349, 0,    0,    5.00, 12.00, 80.00, 0);

-- ── TRADING ACCOUNTS ────────────────────────────
CREATE TABLE IF NOT EXISTS trading_accounts (
  id                 BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id            BIGINT UNSIGNED NOT NULL,
  plan_id            BIGINT UNSIGNED NOT NULL,
  order_id           BIGINT UNSIGNED NOT NULL,

  -- MT5 credentials (encrypted at rest using AES-256)
  mt5_login          VARCHAR(255)    NULL COMMENT 'AES-256 encrypted',
  mt5_password       VARCHAR(255)    NULL COMMENT 'AES-256 encrypted',
  mt5_server         VARCHAR(100)    NULL,

  -- Deriv sub-account info
  deriv_account_id   VARCHAR(50)     NULL,
  deriv_token        TEXT            NULL COMMENT 'AES-256 encrypted',

  phase              TINYINT         NOT NULL DEFAULT 1 COMMENT '0=instant,1=phase1,2=phase2,3=funded',
  status             ENUM('active','passed','failed','funded','closed') NOT NULL DEFAULT 'active',

  -- Financials (all in USD)
  initial_balance    DECIMAL(12,2)   NOT NULL,
  current_balance    DECIMAL(12,2)   NOT NULL,
  highest_balance    DECIMAL(12,2)   NOT NULL,
  profit_target      DECIMAL(12,2)   NOT NULL,
  daily_loss_limit   DECIMAL(12,2)   NOT NULL,
  max_drawdown_limit DECIMAL(12,2)   NOT NULL,

  -- Tracking
  start_date         DATE            NULL,
  phase_passed_at    DATETIME        NULL,
  funded_at          DATETIME        NULL,
  failed_at          DATETIME        NULL,
  failure_reason     VARCHAR(255)    NULL,

  created_at         DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at         DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (plan_id) REFERENCES plans(id),
  INDEX idx_user (user_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── ORDERS / PAYMENTS ───────────────────────────
CREATE TABLE IF NOT EXISTS orders (
  id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id          BIGINT UNSIGNED NOT NULL,
  plan_id          BIGINT UNSIGNED NOT NULL,
  amount           DECIMAL(8,2)    NOT NULL,
  currency         VARCHAR(10)     NOT NULL DEFAULT 'USD',
  payment_method   ENUM('stripe','crypto','bank') NOT NULL,
  payment_ref      VARCHAR(255)    NULL COMMENT 'Stripe payment_intent_id or crypto tx hash',
  status           ENUM('pending','paid','failed','refunded') NOT NULL DEFAULT 'pending',
  created_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  paid_at          DATETIME        NULL,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (plan_id) REFERENCES plans(id),
  INDEX idx_user_status (user_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── TRADE JOURNAL ────────────────────────────────
CREATE TABLE IF NOT EXISTS journal_entries (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id       BIGINT UNSIGNED NOT NULL,
  account_id    BIGINT UNSIGNED NOT NULL,
  instrument    VARCHAR(50)     NOT NULL,
  direction     ENUM('buy','sell') NOT NULL,
  entry_price   DECIMAL(16,5)   NULL,
  exit_price    DECIMAL(16,5)   NULL,
  lot_size      DECIMAL(10,4)   NULL,
  pnl           DECIMAL(12,2)   NOT NULL DEFAULT 0,
  risk_reward   VARCHAR(20)     NULL COMMENT 'e.g. 1:2.5',
  setup         VARCHAR(500)    NULL,
  notes         TEXT            NULL,
  emotion       ENUM('confident','calm','fearful','fomo','revenge','greedy','neutral') NULL,
  screenshot_url VARCHAR(512)   NULL,
  tags          JSON            NULL,
  outcome       ENUM('win','loss','be') GENERATED ALWAYS AS (
    CASE WHEN pnl > 0 THEN 'win' WHEN pnl < 0 THEN 'loss' ELSE 'be' END
  ) STORED,
  traded_at     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id)    REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (account_id) REFERENCES trading_accounts(id) ON DELETE CASCADE,
  INDEX idx_user_date (user_id, traded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── PAYOUTS ──────────────────────────────────────
CREATE TABLE IF NOT EXISTS payouts (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id        BIGINT UNSIGNED NOT NULL,
  account_id     BIGINT UNSIGNED NOT NULL,
  gross_amount   DECIMAL(12,2)   NOT NULL COMMENT 'Total profit',
  split_pct      DECIMAL(5,2)    NOT NULL COMMENT 'Trader split %',
  net_amount     DECIMAL(12,2)   NOT NULL COMMENT 'Amount trader receives',
  fee            DECIMAL(8,2)    NOT NULL DEFAULT 0,
  method         ENUM('usdt_trc20','usdt_erc20','bank','mpesa') NOT NULL,
  wallet_address VARCHAR(255)    NULL,
  bank_details   TEXT            NULL COMMENT 'AES-256 encrypted JSON',
  status         ENUM('pending','processing','paid','rejected') NOT NULL DEFAULT 'pending',
  tx_hash        VARCHAR(255)    NULL,
  notes          VARCHAR(500)    NULL,
  requested_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  processed_at   DATETIME        NULL,
  FOREIGN KEY (user_id)    REFERENCES users(id),
  FOREIGN KEY (account_id) REFERENCES trading_accounts(id),
  INDEX idx_status (status),
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── AFFILIATES / REFERRALS ───────────────────────
CREATE TABLE IF NOT EXISTS referrals (
  id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  referrer_id      BIGINT UNSIGNED NOT NULL,
  referred_user_id BIGINT UNSIGNED NOT NULL UNIQUE,
  ref_code         VARCHAR(20)     NOT NULL,
  commission_rate  DECIMAL(5,2)    NOT NULL DEFAULT 20.00,
  status           ENUM('pending','qualified','paid') NOT NULL DEFAULT 'pending',
  created_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (referrer_id)      REFERENCES users(id),
  FOREIGN KEY (referred_user_id) REFERENCES users(id),
  INDEX idx_referrer (referrer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS affiliate_commissions (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  referral_id  BIGINT UNSIGNED NOT NULL,
  order_id     BIGINT UNSIGNED NOT NULL,
  amount       DECIMAL(8,2)    NOT NULL,
  status       ENUM('pending','paid','cancelled') NOT NULL DEFAULT 'pending',
  paid_at      DATETIME        NULL,
  created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (referral_id) REFERENCES referrals(id),
  FOREIGN KEY (order_id)    REFERENCES orders(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── PREMIUM SUBSCRIPTIONS ────────────────────────
CREATE TABLE IF NOT EXISTS subscriptions (
  id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id             BIGINT UNSIGNED NOT NULL UNIQUE,
  plan                ENUM('free','premium') NOT NULL DEFAULT 'free',
  stripe_customer_id  VARCHAR(100)    NULL,
  stripe_sub_id       VARCHAR(100)    NULL,
  current_period_end  DATETIME        NULL,
  cancelled_at        DATETIME        NULL,
  created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── RATE LIMITING ────────────────────────────────
CREATE TABLE IF NOT EXISTS rate_limits (
  `key`       VARCHAR(100)    NOT NULL PRIMARY KEY,
  attempts    INT             NOT NULL DEFAULT 1,
  expires_at  DATETIME        NOT NULL,
  INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── AUDIT LOG ────────────────────────────────────
CREATE TABLE IF NOT EXISTS audit_log (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    BIGINT UNSIGNED NULL,
  event      VARCHAR(100)    NOT NULL,
  ip         VARCHAR(45)     NULL,
  meta       JSON            NULL,
  created_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user_event (user_id, event),
  INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── NOTIFICATIONS ────────────────────────────────
CREATE TABLE IF NOT EXISTS notifications (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    BIGINT UNSIGNED NOT NULL,
  type       VARCHAR(50)     NOT NULL,
  title      VARCHAR(255)    NOT NULL,
  body       TEXT            NOT NULL,
  is_read    TINYINT(1)      NOT NULL DEFAULT 0,
  created_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_user_unread (user_id, is_read)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;