CREATE TABLE users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(100) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 referral_code CHAR(16) NOT NULL UNIQUE,
 sponsor_id BIGINT UNSIGNED NULL,
 role ENUM('user','admin') NOT NULL DEFAULT 'user',
 status ENUM('inactive','active','blocked') NOT NULL DEFAULT 'inactive',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (sponsor_id) REFERENCES users(id)
);
CREATE TABLE plans (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(80) NOT NULL,
 description TEXT NOT NULL,
 price_paise BIGINT UNSIGNED NOT NULL DEFAULT 0,
 level1_paise BIGINT UNSIGNED NOT NULL DEFAULT 0,
 level2_paise BIGINT UNSIGNED NOT NULL DEFAULT 0,
 level3_paise BIGINT UNSIGNED NOT NULL DEFAULT 0,
 level4_paise BIGINT UNSIGNED NOT NULL DEFAULT 0,
 enabled BOOLEAN NOT NULL DEFAULT FALSE,
 CHECK(level1_paise+level2_paise+level3_paise+level4_paise <= price_paise)
);
INSERT INTO plans(name,description) VALUES ('Platinum','Course content to be configured'),('Gold','Course content to be configured'),('Diamond','Course content to be configured');
CREATE TABLE settings (
 id INT PRIMARY KEY,
 withdrawal_min_paise BIGINT UNSIGNED NOT NULL DEFAULT 50000,
 withdrawal_max_paise BIGINT UNSIGNED NOT NULL DEFAULT 1000000
);
INSERT INTO settings(id) VALUES(1);
CREATE TABLE audit_log (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 actor_id BIGINT UNSIGNED NOT NULL,
 action VARCHAR(100) NOT NULL,
 details TEXT NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(actor_id) REFERENCES users(id)
);
CREATE TABLE login_attempts (
 bucket CHAR(64) PRIMARY KEY,
 attempts INT UNSIGNED NOT NULL DEFAULT 0,
 window_start BIGINT NOT NULL
);