-- Full schema for a NEW EMPTY MySQL 8.0.16+ database.
-- Select/create your database in cPanel/phpMyAdmin before importing.
-- Do not import over an existing installation. No default admin credentials.
SET NAMES utf8mb4;

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
);


-- Existing installations: import this migration once. New installations import schema.sql then this file.
CREATE TABLE course_pdfs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 plan_id BIGINT UNSIGNED NOT NULL,
 title VARCHAR(160) NOT NULL,
 storage_key CHAR(64) NOT NULL UNIQUE,
 size_bytes INT UNSIGNED NOT NULL,
 enabled BOOLEAN NOT NULL DEFAULT TRUE,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(plan_id) REFERENCES plans(id)
);
CREATE TABLE course_access (
 user_id BIGINT UNSIGNED NOT NULL,
 plan_id BIGINT UNSIGNED NOT NULL,
 payment_reference VARCHAR(190) NOT NULL,
 granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 revoked_at TIMESTAMP NULL,
 PRIMARY KEY(user_id,plan_id),
 FOREIGN KEY(user_id) REFERENCES users(id),
 FOREIGN KEY(plan_id) REFERENCES plans(id)
);
-- Only the future verified-payment backend may grant access. No browser grant endpoint exists.


-- Import after schema.sql and pdf-courses.sql, once.
CREATE TABLE gateway_configs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 base_url VARCHAR(500) NOT NULL,
 username VARCHAR(190) NOT NULL,
 encrypted_key TEXT NOT NULL,
 enabled BOOLEAN NOT NULL DEFAULT FALSE,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE payment_orders (
 id CHAR(40) PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 plan_id BIGINT UNSIGNED NOT NULL,
 gateway_id BIGINT UNSIGNED NOT NULL,
 amount_paise BIGINT UNSIGNED NOT NULL,
 commission_snapshot JSON NOT NULL,
 status ENUM('creating','pending','paid','review') NOT NULL DEFAULT 'creating',
 payment_url TEXT NULL,
 utr VARCHAR(190) NULL UNIQUE,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 paid_at TIMESTAMP NULL,
 FOREIGN KEY(user_id) REFERENCES users(id),
 FOREIGN KEY(plan_id) REFERENCES plans(id),
 FOREIGN KEY(gateway_id) REFERENCES gateway_configs(id)
);
CREATE TABLE commission_ledger (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 order_id CHAR(40) NOT NULL,
 user_id BIGINT UNSIGNED NOT NULL,
 level TINYINT UNSIGNED NOT NULL,
 amount_paise BIGINT UNSIGNED NOT NULL,
 status ENUM('pending','available','reversed') NOT NULL DEFAULT 'pending',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 UNIQUE(order_id,level),
 FOREIGN KEY(order_id) REFERENCES payment_orders(id),
 FOREIGN KEY(user_id) REFERENCES users(id)
);