-- ab_direct_pay_250 schema
-- Run this once against your MySQL database before using the app.

CREATE TABLE IF NOT EXISTS ab_users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(60) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(60),
    last_name VARCHAR(60),
    address VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(100),
    country VARCHAR(100),
    zip VARCHAR(20),
    phone VARCHAR(30),
    profile_picture VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Generic per-user key/value store.
-- Mirrors the old WordPress usermeta pattern on purpose: it's what the
-- original plugins keyed almost everything off of (referral code,
-- needs_referral_payment flag, each sponsor's saved payout addresses),
-- so keeping this shape makes porting Pages 3 and 4 straightforward.
CREATE TABLE IF NOT EXISTS ab_user_meta (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    meta_key VARCHAR(100) NOT NULL,
    meta_value TEXT,
    UNIQUE KEY user_meta_key (user_id, meta_key),
    KEY user_id_idx (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- One unified sponsor-tracking system (replaces the old dual
-- ea_referral_code / pr_sponsor_id split). Sponsor relationship is a
-- first-class column, not just user_meta, since every page needs it.
CREATE TABLE IF NOT EXISTS ab_payments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    sponsor_user_id INT UNSIGNED NULL,
    payment_type ENUM('admin_fee','sponsor_fee') NOT NULL,
    payment_method VARCHAR(30) NOT NULL,     -- paypal, gpay, cashapp, zelle, venmo
    txn_id VARCHAR(255),
    payer_email VARCHAR(100),
    amount DECIMAL(10,2),
    currency VARCHAR(10) DEFAULT 'USD',
    status VARCHAR(30) NOT NULL,             -- claimed, verified, disputed
    verified TINYINT(1) NOT NULL DEFAULT 0,  -- 1 only for PayPal IPN-confirmed txns
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    KEY user_id_idx (user_id),
    KEY sponsor_user_id_idx (sponsor_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;