-- ============================================================
-- NexaNetwork MLM Platform — MySQL Schema
-- Run: mysql -u root -p < db/schema.sql
-- ============================================================

CREATE DATABASE IF NOT EXISTS nexanetwork CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE nexanetwork;

-- ── USERS ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name            VARCHAR(120) NOT NULL,
  email           VARCHAR(180) NOT NULL UNIQUE,
  password_hash   VARCHAR(255) NOT NULL,
  phone           VARCHAR(20),
  ref_code        VARCHAR(30) NOT NULL UNIQUE,
  referred_by     INT UNSIGNED NULL,         -- FK to users.id (direct upline)
  upi_id          VARCHAR(120),              -- for payouts
  bank_account    VARCHAR(30),
  bank_ifsc       VARCHAR(15),
  balance         DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  total_earned    DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  rank            ENUM('bronze','silver','gold','platinum') NOT NULL DEFAULT 'bronze',
  is_active       TINYINT(1) NOT NULL DEFAULT 1,
  email_verified  TINYINT(1) NOT NULL DEFAULT 0,
  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
) ENGINE=InnoDB;

-- ── COMMISSIONS ────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS commissions (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  to_user_id      INT UNSIGNED NOT NULL,     -- earner
  from_user_id    INT UNSIGNED NOT NULL,     -- who triggered it
  level           TINYINT UNSIGNED NOT NULL, -- 1-4
  amount          DECIMAL(10,2) NOT NULL,
  sale_amount     DECIMAL(10,2) NOT NULL,    -- base amount commission was on
  rate            DECIMAL(5,4) NOT NULL,     -- e.g. 0.1000 = 10%
  status          ENUM('pending','paid','cancelled') NOT NULL DEFAULT 'pending',
  note            VARCHAR(255),
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  paid_at         DATETIME,
  FOREIGN KEY (to_user_id)   REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (from_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── WITHDRAWALS ────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS withdrawals (
  id                    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id               INT UNSIGNED NOT NULL,
  amount                DECIMAL(10,2) NOT NULL,
  method                ENUM('upi','bank','razorpay') NOT NULL DEFAULT 'upi',
  payout_account        VARCHAR(120) NOT NULL,   -- UPI ID or account number
  status                ENUM('pending','processing','paid','failed') NOT NULL DEFAULT 'pending',
  razorpay_payout_id    VARCHAR(80),
  razorpay_fund_id      VARCHAR(80),
  failure_reason        TEXT,
  requested_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  processed_at          DATETIME,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── EMAIL INVITES ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS invites (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sender_id   INT UNSIGNED NOT NULL,
  email       VARCHAR(180) NOT NULL,
  token       VARCHAR(64) NOT NULL UNIQUE,
  used        TINYINT(1) NOT NULL DEFAULT 0,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at  DATETIME NOT NULL,
  FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── SESSIONS (optional: use JWT instead) ──────────────────
CREATE TABLE IF NOT EXISTS sessions (
  id          VARCHAR(64) PRIMARY KEY,
  user_id     INT UNSIGNED NOT NULL,
  ip          VARCHAR(45),
  user_agent  VARCHAR(255),
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at  DATETIME NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── INDEXES ───────────────────────────────────────────────
CREATE INDEX idx_users_referred_by    ON users(referred_by);
CREATE INDEX idx_users_ref_code       ON users(ref_code);
CREATE INDEX idx_commissions_to       ON commissions(to_user_id);
CREATE INDEX idx_commissions_from     ON commissions(from_user_id);
CREATE INDEX idx_withdrawals_user     ON withdrawals(user_id);
CREATE INDEX idx_withdrawals_status   ON withdrawals(status);

-- ── SEED DEMO DATA ─────────────────────────────────────────
-- Passwords are bcrypt of "demo1234"
INSERT IGNORE INTO users (id, name, email, password_hash, phone, ref_code, referred_by, balance, total_earned, rank) VALUES
(1, 'Priya Sharma',  'priya@demo.com',  '$2b$10$Xq5WZ6K1RvQnHGZ8TyU3Ou7YIVX8Dn9h3KLmNpQ2vRjS4cFbWeAiG', '+919876543210', 'REF-PRI001', NULL,   4520.00, 12400.00, 'gold'),
(2, 'Amit Verma',    'amit@demo.com',   '$2b$10$Xq5WZ6K1RvQnHGZ8TyU3Ou7YIVX8Dn9h3KLmNpQ2vRjS4cFbWeAiG', '+919988765432', 'REF-AMI002', 1,      1800.00,  3600.00, 'silver'),
(3, 'Sunita Patel',  'sunita@demo.com', '$2b$10$Xq5WZ6K1RvQnHGZ8TyU3Ou7YIVX8Dn9h3KLmNpQ2vRjS4cFbWeAiG', '+919765432109', 'REF-SUN003', 1,       950.00,  2100.00, 'silver'),
(4, 'Rahul Singh',   'rahul@demo.com',  '$2b$10$Xq5WZ6K1RvQnHGZ8TyU3Ou7YIVX8Dn9h3KLmNpQ2vRjS4cFbWeAiG', '+919654321098', 'REF-RAH004', 2,       600.00,  1200.00, 'bronze'),
(5, 'Neha Gupta',    'neha@demo.com',   '$2b$10$Xq5WZ6K1RvQnHGZ8TyU3Ou7YIVX8Dn9h3KLmNpQ2vRjS4cFbWeAiG', '+919543210987', 'REF-NEH005', 2,       430.00,   870.00, 'bronze'),
(6, 'Vikram Joshi',  'vikram@demo.com', '$2b$10$Xq5WZ6K1RvQnHGZ8TyU3Ou7YIVX8Dn9h3KLmNpQ2vRjS4cFbWeAiG', '+919432109876', 'REF-VIK006', 3,       210.00,   420.00, 'bronze');

INSERT IGNORE INTO commissions (to_user_id, from_user_id, level, amount, sale_amount, rate, status, paid_at) VALUES
(1, 2, 1, 500.00, 5000.00, 0.1000, 'paid', '2024-02-10'),
(1, 3, 1, 400.00, 4000.00, 0.1000, 'paid', '2024-02-20'),
(1, 4, 2, 200.00, 4000.00, 0.0500, 'paid', '2024-03-05'),
(2, 4, 1, 400.00, 4000.00, 0.1000, 'paid', '2024-03-05'),
(1, 5, 2, 180.00, 3600.00, 0.0500, 'paid', '2024-03-18'),
(2, 5, 1, 360.00, 3600.00, 0.1000, 'paid', '2024-03-18'),
(1, 6, 3,  90.00, 3000.00, 0.0300, 'pending', NULL),
(3, 6, 1, 300.00, 3000.00, 0.1000, 'pending', NULL);
