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