-- Ginsau Data Scalable VTU System Database Schema
-- Designed for PHP & MySQL 8.x/5.7

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

-- --------------------------------------------------------
-- Table structure for table `users`
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `id` varchar(100) NOT NULL,
  `fullName` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role` varchar(50) NOT NULL DEFAULT 'user',
  `tier` enum('regular','reseller','api') NOT NULL DEFAULT 'regular',
  `status` enum('active','suspended') NOT NULL DEFAULT 'active',
  `referralCode` varchar(100) NOT NULL,
  `referredBy` varchar(100) DEFAULT NULL,
  `referralEarnings` decimal(15,2) NOT NULL DEFAULT '0.00',
  `kycStatus` enum('none','pending','verified','rejected') NOT NULL DEFAULT 'none',
  `kycDetails` json DEFAULT NULL,
  `monnifyAccount` json DEFAULT NULL,
  `twoFactorEnabled` tinyint(1) NOT NULL DEFAULT '0',
  `twoFactorSecret` varchar(255) DEFAULT NULL,
  `emailNotificationsEnabled` tinyint(1) NOT NULL DEFAULT '1',
  `avatar` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  UNIQUE KEY `users_referral_code_unique` (`referralCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `wallets`
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `wallets` (
  `id` varchar(100) NOT NULL,
  `userId` varchar(100) NOT NULL,
  `balance` decimal(15,2) NOT NULL DEFAULT '0.00',
  `totalFunded` decimal(15,2) NOT NULL DEFAULT '0.00',
  `totalSpent` decimal(15,2) NOT NULL DEFAULT '0.00',
  `virtualBankWema` varchar(100) DEFAULT NULL,
  `virtualBankSterling` varchar(100) DEFAULT NULL,
  `virtualBankMonnify` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `wallets_user_id_idx` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `transactions`
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `transactions` (
  `id` varchar(100) NOT NULL,
  `userId` varchar(100) NOT NULL,
  `type` varchar(100) NOT NULL,
  `details` text NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `fee` decimal(15,2) NOT NULL DEFAULT '0.00',
  `status` enum('pending','completed','failed','refunded') NOT NULL DEFAULT 'pending',
  `provider` varchar(100) NOT NULL,
  `reference` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `transactions_reference_unique` (`reference`),
  KEY `transactions_user_id_idx` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `balance_logs`
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `balance_logs` (
  `id` varchar(100) NOT NULL,
  `userId` varchar(100) NOT NULL,
  `userEmail` varchar(255) NOT NULL,
  `previousBalance` decimal(15,2) NOT NULL,
  `newBalance` decimal(15,2) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `type` enum('credit','debit') NOT NULL,
  `reference` varchar(255) NOT NULL,
  `reason` text NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `balance_logs_user_id_idx` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `tickets`
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tickets` (
  `id` varchar(100) NOT NULL,
  `userId` varchar(100) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `status` enum('open','closed','replied') NOT NULL DEFAULT 'open',
  `replies` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tickets_user_id_idx` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `feedback`
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `feedback` (
  `id` varchar(100) NOT NULL,
  `userId` varchar(100) NOT NULL,
  `userName` varchar(255) NOT NULL,
  `userEmail` varchar(255) NOT NULL,
  `feedbackType` varchar(100) NOT NULL,
  `message` text NOT NULL,
  `urgency` varchar(50) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `coupons`
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `coupons` (
  `code` varchar(100) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `usedCount` int(11) NOT NULL DEFAULT '0',
  `maxUses` int(11) NOT NULL DEFAULT '100',
  `expiresAt` timestamp NULL DEFAULT NULL,
  `redeemedBy` json DEFAULT NULL,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `api_providers`
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `api_providers` (
  `id` varchar(100) NOT NULL,
  `name` varchar(255) NOT NULL,
  `airtimeUrl` varchar(255) DEFAULT NULL,
  `dataUrl` varchar(255) DEFAULT NULL,
  `apiKey` varchar(255) NOT NULL,
  `apiSecret` varchar(255) DEFAULT NULL,
  `balance` decimal(15,2) NOT NULL DEFAULT '0.00',
  `status` enum('active','inactive') NOT NULL DEFAULT 'inactive',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `payment_gateways`
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `payment_gateways` (
  `id` varchar(100) NOT NULL,
  `name` varchar(255) NOT NULL,
  `publicKey` varchar(255) DEFAULT NULL,
  `secretKey` varchar(255) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'inactive',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `activity_logs`
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `activity_logs` (
  `id` varchar(100) NOT NULL,
  `userId` varchar(100) DEFAULT NULL,
  `userName` varchar(255) DEFAULT NULL,
  `action` varchar(255) NOT NULL,
  `ip` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `settings`
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `siteName` varchar(255) NOT NULL DEFAULT 'Ginsau Data',
  `currency` varchar(10) NOT NULL DEFAULT 'NGN',
  `supportEmail` varchar(255) NOT NULL,
  `maintenanceMode` tinyint(1) NOT NULL DEFAULT '0',
  `kycMandatory` tinyint(1) NOT NULL DEFAULT '0',
  `apiSwitchingOnFailure` tinyint(1) NOT NULL DEFAULT '1',
  `referralBonusAmount` decimal(15,2) NOT NULL DEFAULT '500.00',
  `paymentGateways` json DEFAULT NULL,
  `dataTypeConfigs` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `analytics`
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `analytics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `metricDate` date NOT NULL,
  `dailySales` decimal(15,2) NOT NULL DEFAULT '0.00',
  `dailyRevenue` decimal(15,2) NOT NULL DEFAULT '0.00',
  `totalApiRequests` int(11) NOT NULL DEFAULT '0',
  `activeUsers` int(11) NOT NULL DEFAULT '0',
  `failedTransactions` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `metricDate` (`metricDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

COMMIT;