CREATE TABLE users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 full_name VARCHAR(120) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE,
 phone VARCHAR(24) NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 role ENUM('customer','admin') NOT NULL DEFAULT 'customer',
 status ENUM('active','suspended') NOT NULL DEFAULT 'active',
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE wallets (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL UNIQUE,
 balance_kobo BIGINT NOT NULL DEFAULT 0,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_wallet_user FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE services (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 code VARCHAR(60) NOT NULL UNIQUE,
 name VARCHAR(120) NOT NULL,
 category ENUM('data','airtime','electricity','tv','other') NOT NULL,
 provider_code VARCHAR(80) NULL,
 price_kobo BIGINT UNSIGNED NOT NULL DEFAULT 0,
 active TINYINT(1) NOT NULL DEFAULT 0,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE transactions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 service_id BIGINT UNSIGNED NULL,
 reference VARCHAR(80) NOT NULL UNIQUE,
 type ENUM('wallet_funding','purchase','refund','adjustment') NOT NULL,
 amount_kobo BIGINT UNSIGNED NOT NULL,
 status ENUM('pending','successful','failed','reversed') NOT NULL DEFAULT 'pending',
 provider_reference VARCHAR(120) NULL,
 description VARCHAR(255) NOT NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX idx_tx_user_created(user_id,created_at),
 CONSTRAINT fk_tx_user FOREIGN KEY(user_id) REFERENCES users(id),
 CONSTRAINT fk_tx_service FOREIGN KEY(service_id) REFERENCES services(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO services(code,name,category,active) VALUES
('mtn-data','MTN Data','data',0),
('airtel-data','Airtel Data','data',0),
('glo-data','Glo Data','data',0),
('9mobile-data','9mobile Data','data',0),
('airtime','Airtime','airtime',0),
('electricity','Electricity Bills','electricity',0),
('tv','TV Subscription','tv',0),
('other','Other Digital Services','other',0);