CREATE DATABASE IF NOT EXISTS payflow CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE payflow;
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS reversals,audit_logs,settings,float_transactions,float_requests,commissions,notifications,devices,login_logs,otp_codes,sessions,cash_out_codes,bills,billers,biller_categories,limits,fees,transaction_entries,transactions,kyc_reviews,kyc_documents,kyc_applications,wallets,role_permissions,permissions,roles,agents,admins,users;
SET FOREIGN_KEY_CHECKS=1;

CREATE TABLE users(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,mobile VARCHAR(20) UNIQUE NOT NULL,pin_hash VARCHAR(255) NOT NULL,name VARCHAR(120) NOT NULL,status ENUM('active','pending_kyc','suspended','blocked') DEFAULT 'pending_kyc',kyc_status ENUM('unverified','pending','approved','rejected') DEFAULT 'unverified',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;
CREATE TABLE agents(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,mobile VARCHAR(20) UNIQUE NOT NULL,pin_hash VARCHAR(255) NOT NULL,name VARCHAR(120) NOT NULL,status ENUM('pending','active','suspended','blocked') DEFAULT 'pending',cash_float DECIMAL(18,2) NOT NULL DEFAULT 0,emoney_balance DECIMAL(18,2) NOT NULL DEFAULT 0,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;
CREATE TABLE admins(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,mobile VARCHAR(20) UNIQUE NOT NULL,pin_hash VARCHAR(255) NOT NULL,name VARCHAR(120) NOT NULL,status ENUM('active','suspended') DEFAULT 'active',role_name VARCHAR(40) DEFAULT 'admin',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;
CREATE TABLE roles(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(50) UNIQUE NOT NULL); CREATE TABLE permissions(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100) UNIQUE NOT NULL); CREATE TABLE role_permissions(role_id INT,permission_id INT,PRIMARY KEY(role_id,permission_id));
CREATE TABLE wallets(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id INT UNSIGNED UNIQUE NULL,balance DECIMAL(18,2) NOT NULL DEFAULT 0,status ENUM('active','frozen','closed') DEFAULT 'active',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(user_id) REFERENCES users(id)) ENGINE=InnoDB;
CREATE TABLE kyc_applications(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id INT UNSIGNED NOT NULL,status ENUM('draft','pending','approved','rejected','resubmit') DEFAULT 'draft',data_json JSON,submitted_at DATETIME NULL,FOREIGN KEY(user_id) REFERENCES users(id));
CREATE TABLE kyc_documents(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,application_id BIGINT UNSIGNED NOT NULL,type VARCHAR(40),path VARCHAR(255),mime VARCHAR(100),FOREIGN KEY(application_id) REFERENCES kyc_applications(id));
CREATE TABLE kyc_reviews(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,application_id BIGINT UNSIGNED NOT NULL,admin_id INT UNSIGNED NOT NULL,action VARCHAR(30),note TEXT,ip VARCHAR(64),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE transactions(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,ref VARCHAR(40) UNIQUE NOT NULL,type VARCHAR(40) NOT NULL,status VARCHAR(20) NOT NULL,amount DECIMAL(18,2) NOT NULL,actor_id INT NULL,meta_json JSON,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,INDEX(type,status,created_at));
CREATE TABLE transaction_entries(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,transaction_id BIGINT UNSIGNED NOT NULL,wallet_id BIGINT UNSIGNED NOT NULL,direction ENUM('debit','credit') NOT NULL,amount DECIMAL(18,2) NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(transaction_id) REFERENCES transactions(id),FOREIGN KEY(wallet_id) REFERENCES wallets(id),INDEX(wallet_id,created_at));
CREATE TABLE fees(id INT AUTO_INCREMENT PRIMARY KEY,type VARCHAR(40),flat_amount DECIMAL(18,2) DEFAULT 0,pct DECIMAL(8,4) DEFAULT 0,active TINYINT DEFAULT 1); CREATE TABLE limits(id INT AUTO_INCREMENT PRIMARY KEY,kyc_level VARCHAR(30),daily DECIMAL(18,2),monthly DECIMAL(18,2),per_txn DECIMAL(18,2));
CREATE TABLE biller_categories(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(80) UNIQUE); CREATE TABLE billers(id INT AUTO_INCREMENT PRIMARY KEY,category_id INT,name VARCHAR(120),account_pattern VARCHAR(120),commission_pct DECIMAL(8,4) DEFAULT 0,active TINYINT DEFAULT 1);
CREATE TABLE bills(id BIGINT AUTO_INCREMENT PRIMARY KEY,user_id INT UNSIGNED,biller_id INT,account_no VARCHAR(100),amount DECIMAL(18,2),status VARCHAR(20),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE cash_out_codes(id BIGINT AUTO_INCREMENT PRIMARY KEY,user_id INT UNSIGNED,agent_id INT,code_hash VARCHAR(255),amount DECIMAL(18,2),expires_at DATETIME,used_at DATETIME NULL);
CREATE TABLE otp_codes(id BIGINT AUTO_INCREMENT PRIMARY KEY,user_id INT UNSIGNED,code_hash VARCHAR(255),purpose VARCHAR(40),expires_at DATETIME,used_at DATETIME NULL);
CREATE TABLE sessions(id BIGINT AUTO_INCREMENT PRIMARY KEY,user_id INT,session_hash VARCHAR(255),ip VARCHAR(64),user_agent TEXT,last_seen DATETIME);
CREATE TABLE login_logs(id BIGINT AUTO_INCREMENT PRIMARY KEY,user_id INT NULL,ip VARCHAR(64),user_agent TEXT,success TINYINT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE devices(id BIGINT AUTO_INCREMENT PRIMARY KEY,user_id INT,device_hash VARCHAR(255),label VARCHAR(100),last_seen DATETIME);
CREATE TABLE notifications(id BIGINT AUTO_INCREMENT PRIMARY KEY,user_id INT,message TEXT,is_read TINYINT DEFAULT 0,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE commissions(id BIGINT AUTO_INCREMENT PRIMARY KEY,agent_id INT,type VARCHAR(40),txn_id BIGINT,amount DECIMAL(18,2),settled_at DATETIME NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE float_requests(id BIGINT AUTO_INCREMENT PRIMARY KEY,agent_id INT,amount DECIMAL(18,2),status VARCHAR(20) DEFAULT 'pending',note TEXT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE float_transactions(id BIGINT AUTO_INCREMENT PRIMARY KEY,agent_id INT,amount DECIMAL(18,2),direction VARCHAR(10),reference VARCHAR(60),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE settings(`key` VARCHAR(100) PRIMARY KEY,value TEXT);
CREATE TABLE audit_logs(id BIGINT AUTO_INCREMENT PRIMARY KEY,admin_id INT,action VARCHAR(80),target_type VARCHAR(40),target_id BIGINT,before_json JSON,after_json JSON,ip VARCHAR(64),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE reversals(id BIGINT AUTO_INCREMENT PRIMARY KEY,transaction_id BIGINT,admin_id INT,reason TEXT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);