-- ============================================ -- CPA NETWORK PLATFORM - MySQL SCHEMA -- Import this via phpMyAdmin (cPanel) or `mysql -u user -p dbname < schema.sql` -- ============================================ CREATE TABLE IF NOT EXISTS users ( id CHAR(36) PRIMARY KEY, email VARCHAR(190) UNIQUE NOT NULL, password_hash VARCHAR(255) NOT NULL, role ENUM('admin','advertiser','publisher') NOT NULL, full_name VARCHAR(150) NOT NULL, company_name VARCHAR(150), status ENUM('pending','active','suspended','rejected') NOT NULL DEFAULT 'pending', payment_method VARCHAR(50), payment_details TEXT, referral_code VARCHAR(20) UNIQUE, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS offers ( id CHAR(36) PRIMARY KEY, advertiser_id CHAR(36) NOT NULL, name VARCHAR(200) NOT NULL, description TEXT, category VARCHAR(80), destination_url TEXT NOT NULL, preview_url TEXT, payout_type ENUM('cpa','cpl','cps','cpi','revshare') NOT NULL, payout_amount DECIMAL(10,2) NOT NULL, currency VARCHAR(10) DEFAULT 'USD', daily_cap INT NULL, total_cap INT NULL, allowed_countries TEXT, allowed_traffic_sources TEXT, conversion_flow TEXT, status ENUM('pending','active','paused','ended','rejected') NOT NULL DEFAULT 'pending', requires_approval TINYINT(1) DEFAULT 1, postback_url TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (advertiser_id) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS offer_publishers ( id CHAR(36) PRIMARY KEY, offer_id CHAR(36) NOT NULL, publisher_id CHAR(36) NOT NULL, status ENUM('pending','approved','rejected','banned') NOT NULL DEFAULT 'pending', custom_payout DECIMAL(10,2) NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uniq_offer_pub (offer_id, publisher_id), FOREIGN KEY (offer_id) REFERENCES offers(id), FOREIGN KEY (publisher_id) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS tracking_links ( id CHAR(36) PRIMARY KEY, offer_id CHAR(36) NOT NULL, publisher_id CHAR(36) NOT NULL, sub1 VARCHAR(100), sub2 VARCHAR(100), sub3 VARCHAR(100), sub4 VARCHAR(100), sub5 VARCHAR(100), tracking_code VARCHAR(20) UNIQUE NOT NULL, is_active TINYINT(1) DEFAULT 1, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (offer_id) REFERENCES offers(id), FOREIGN KEY (publisher_id) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS clicks ( id CHAR(36) PRIMARY KEY, click_id VARCHAR(40) UNIQUE NOT NULL, tracking_link_id CHAR(36) NOT NULL, offer_id CHAR(36) NOT NULL, publisher_id CHAR(36) NOT NULL, ip_address VARCHAR(45) NOT NULL, user_agent TEXT, device_type VARCHAR(30), browser VARCHAR(60), os VARCHAR(60), country VARCHAR(80), region VARCHAR(80), city VARCHAR(80), referrer TEXT, sub1 VARCHAR(100), sub2 VARCHAR(100), sub3 VARCHAR(100), sub4 VARCHAR(100), sub5 VARCHAR(100), device_fingerprint VARCHAR(100), is_fraud TINYINT(1) DEFAULT 0, fraud_score INT DEFAULT 0, fraud_reasons TEXT, fraud_status ENUM('clean','flagged','blocked') DEFAULT 'clean', is_vpn TINYINT(1) DEFAULT 0, is_proxy TINYINT(1) DEFAULT 0, is_bot TINYINT(1) DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_ip_time (ip_address, created_at), INDEX idx_fingerprint (device_fingerprint), INDEX idx_publisher (publisher_id), INDEX idx_offer (offer_id), FOREIGN KEY (tracking_link_id) REFERENCES tracking_links(id), FOREIGN KEY (offer_id) REFERENCES offers(id), FOREIGN KEY (publisher_id) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS conversions ( id CHAR(36) PRIMARY KEY, click_id VARCHAR(40) NOT NULL, offer_id CHAR(36) NOT NULL, publisher_id CHAR(36) NOT NULL, advertiser_id CHAR(36) NOT NULL, payout_amount DECIMAL(10,2) NOT NULL, status ENUM('pending','approved','rejected','reversed') NOT NULL DEFAULT 'pending', is_fraud TINYINT(1) DEFAULT 0, fraud_score INT DEFAULT 0, fraud_reasons TEXT, transaction_id VARCHAR(100), ip_address VARCHAR(45), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, approved_at DATETIME NULL, INDEX idx_conv_publisher (publisher_id), INDEX idx_conv_offer (offer_id), INDEX idx_conv_status (status), FOREIGN KEY (offer_id) REFERENCES offers(id), FOREIGN KEY (publisher_id) REFERENCES users(id), FOREIGN KEY (advertiser_id) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS fraud_blocklist ( id CHAR(36) PRIMARY KEY, type ENUM('ip','fingerprint','user_agent','publisher','subnet') NOT NULL, value VARCHAR(255) NOT NULL, reason VARCHAR(255), created_by CHAR(36), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uniq_block (type, value), FOREIGN KEY (created_by) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS ip_intelligence ( ip_address VARCHAR(45) PRIMARY KEY, is_vpn TINYINT(1) DEFAULT 0, is_proxy TINYINT(1) DEFAULT 0, is_datacenter TINYINT(1) DEFAULT 0, is_tor TINYINT(1) DEFAULT 0, risk_score INT DEFAULT 0, country VARCHAR(80), isp VARCHAR(150), checked_at DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS transactions ( id CHAR(36) PRIMARY KEY, user_id CHAR(36) NOT NULL, type ENUM('earning','payout','adjustment','deposit','spend') NOT NULL, amount DECIMAL(10,2) NOT NULL, reference_id VARCHAR(100), description VARCHAR(255), status VARCHAR(20) DEFAULT 'completed', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS audit_log ( id CHAR(36) PRIMARY KEY, actor_id CHAR(36), action VARCHAR(100) NOT NULL, target_type VARCHAR(50), target_id VARCHAR(100), details TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (actor_id) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;