CREATE TABLE IF NOT EXISTS admins(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100) NOT NULL,email VARCHAR(150) NOT NULL UNIQUE,pass_hash VARCHAR(255) NOT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS users(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100) NOT NULL,phone VARCHAR(20) NOT NULL UNIQUE,pin_hash VARCHAR(255) NOT NULL,failed INT DEFAULT 0,locked_until DATETIME NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS tokens(id INT AUTO_INCREMENT PRIMARY KEY,user_id INT NOT NULL,token_hash CHAR(64) NOT NULL UNIQUE,device VARCHAR(100),created_at DATETIME DEFAULT CURRENT_TIMESTAMP,last_used DATETIME NULL,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS savings_groups(id INT AUTO_INCREMENT PRIMARY KEY,code VARCHAR(8) NOT NULL UNIQUE,name VARCHAR(120) NOT NULL,amount INT NOT NULL,freq ENUM('DAILY','WEEKLY','MONTHLY') NOT NULL DEFAULT 'WEEKLY',network VARCHAR(20) NOT NULL,pay_number VARCHAR(20) NOT NULL,owner_id INT NOT NULL,start_date DATETIME NOT NULL,status ENUM('active','suspended') NOT NULL DEFAULT 'active',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(owner_id) REFERENCES users(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS members(id INT AUTO_INCREMENT PRIMARY KEY,group_id INT NOT NULL,user_id INT NOT NULL,name VARCHAR(100) NOT NULL,phone VARCHAR(20) NOT NULL,role ENUM('ADMIN','MEMBER') NOT NULL DEFAULT 'MEMBER',joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,UNIQUE(group_id,user_id),FOREIGN KEY(group_id) REFERENCES savings_groups(id) ON DELETE CASCADE,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS payments(id INT AUTO_INCREMENT PRIMARY KEY,group_id INT NOT NULL,member_id INT NULL,ref VARCHAR(60) NOT NULL,amount INT NOT NULL,period VARCHAR(12) NOT NULL,source ENUM('sms','manual') NOT NULL,sender_name VARCHAR(100),sender_phone VARCHAR(20),raw TEXT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,UNIQUE(group_id,ref),INDEX(group_id,period),FOREIGN KEY(group_id) REFERENCES savings_groups(id) ON DELETE CASCADE,FOREIGN KEY(member_id) REFERENCES members(id) ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS fees(id INT AUTO_INCREMENT PRIMARY KEY,user_id INT NOT NULL,amount INT NOT NULL,network VARCHAR(20),phone VARCHAR(20),ref VARCHAR(60) NOT NULL UNIQUE,status ENUM('pending','success','failed') NOT NULL DEFAULT 'pending',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS settings(skey VARCHAR(50) PRIMARY KEY,svalue VARCHAR(255) NOT NULL) ENGINE=InnoDB;
INSERT IGNORE INTO settings(skey,svalue) VALUES('fee_amount','2000');
