CREATE TABLE users (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, full_name VARCHAR(120) NOT NULL, phone VARCHAR(20) NOT NULL UNIQUE, email VARCHAR(190) NOT NULL UNIQUE, pin_hash VARCHAR(255) NOT NULL, balance_ugx BIGINT UNSIGNED NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;
CREATE TABLE plans (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, amount_ugx BIGINT UNSIGNED NOT NULL UNIQUE, daily_return_ugx BIGINT UNSIGNED NOT NULL, duration_days TINYINT UNSIGNED NOT NULL DEFAULT 5, active BOOLEAN NOT NULL DEFAULT TRUE) ENGINE=InnoDB;
INSERT INTO plans(amount_ugx,daily_return_ugx) VALUES (30000,2500),(50000,4167),(100000,8333),(200000,16667),(500000,41667),(1000000,83333),(2000000,166667),(5000000,416667);
CREATE TABLE api_sessions (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,token_hash CHAR(64) NOT NULL UNIQUE,expires_at DATETIME NOT NULL,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE payments (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,plan_id BIGINT UNSIGNED NULL,type ENUM('collection','payout') NOT NULL,reference VARCHAR(30) NOT NULL UNIQUE,livepay_reference VARCHAR(100),phone VARCHAR(20) NOT NULL,amount_ugx BIGINT UNSIGNED NOT NULL,status VARCHAR(30) NOT NULL DEFAULT 'requested',raw_webhook JSON NULL,balance_credited_at DATETIME NULL,balance_debited_at DATETIME NULL,refunded_at DATETIME NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(user_id) REFERENCES users(id),FOREIGN KEY(plan_id) REFERENCES plans(id)) ENGINE=InnoDB;