CREATE TABLE `admin_audit_logs` (
	`id` int AUTO_INCREMENT NOT NULL,
	`adminId` int NOT NULL,
	`action` varchar(100) NOT NULL,
	`resourceType` varchar(100),
	`resourceId` varchar(100),
	`details` text,
	`ipAddress` varchar(45),
	`userAgent` text,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `admin_audit_logs_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `faqs` (
	`id` int AUTO_INCREMENT NOT NULL,
	`category` varchar(100) NOT NULL,
	`question` text NOT NULL,
	`answer` text NOT NULL,
	`order` int NOT NULL DEFAULT 0,
	`isActive` boolean NOT NULL DEFAULT true,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `faqs_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `fraud_alerts` (
	`id` int AUTO_INCREMENT NOT NULL,
	`userId` int NOT NULL,
	`transactionId` int,
	`ruleId` int NOT NULL,
	`ruleName` varchar(100) NOT NULL,
	`severity` enum('low','medium','high','critical') NOT NULL,
	`transactionAmount` decimal(15,2),
	`userDetails` text,
	`status` enum('new','reviewed','resolved','false_positive') NOT NULL DEFAULT 'new',
	`reviewedBy` int,
	`reviewedAt` timestamp,
	`notes` text,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `fraud_alerts_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `fraud_rules` (
	`id` int AUTO_INCREMENT NOT NULL,
	`name` varchar(100) NOT NULL,
	`description` text,
	`ruleType` enum('velocity','amount_threshold','device_fingerprint','geo_anomaly','custom') NOT NULL,
	`condition` text NOT NULL,
	`action` enum('alert','block','review') NOT NULL DEFAULT 'alert',
	`severity` enum('low','medium','high','critical') NOT NULL DEFAULT 'medium',
	`isEnabled` boolean NOT NULL DEFAULT true,
	`parameters` text,
	`createdBy` int NOT NULL,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `fraud_rules_id` PRIMARY KEY(`id`),
	CONSTRAINT `fraud_rules_name_unique` UNIQUE(`name`)
);
--> statement-breakpoint
CREATE TABLE `giftcard_denominations` (
	`id` int AUTO_INCREMENT NOT NULL,
	`providerId` int NOT NULL,
	`denomination` decimal(15,2) NOT NULL,
	`price` decimal(15,2) NOT NULL,
	`currency` varchar(3) NOT NULL DEFAULT 'USD',
	`stock` int NOT NULL DEFAULT 0,
	`isActive` boolean NOT NULL DEFAULT true,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `giftcard_denominations_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `giftcard_providers` (
	`id` int AUTO_INCREMENT NOT NULL,
	`name` varchar(100) NOT NULL,
	`code` varchar(50) NOT NULL,
	`category` varchar(100) NOT NULL,
	`isActive` boolean NOT NULL DEFAULT true,
	`apiKey` varchar(255),
	`apiEndpoint` varchar(255),
	`commission` decimal(5,2) NOT NULL DEFAULT '0',
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `giftcard_providers_id` PRIMARY KEY(`id`),
	CONSTRAINT `giftcard_providers_name_unique` UNIQUE(`name`),
	CONSTRAINT `giftcard_providers_code_unique` UNIQUE(`code`)
);
--> statement-breakpoint
CREATE TABLE `giftcard_transactions` (
	`id` int AUTO_INCREMENT NOT NULL,
	`userId` int NOT NULL,
	`denominationId` int NOT NULL,
	`quantity` int NOT NULL,
	`totalAmount` decimal(15,2) NOT NULL,
	`status` enum('pending','success','failed','cancelled','redeemed') NOT NULL DEFAULT 'pending',
	`reference` varchar(100),
	`providerReference` varchar(100),
	`cardCodes` text,
	`expiryDate` timestamp,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `giftcard_transactions_id` PRIMARY KEY(`id`),
	CONSTRAINT `giftcard_transactions_reference_unique` UNIQUE(`reference`)
);
--> statement-breakpoint
CREATE TABLE `support_tickets` (
	`id` int AUTO_INCREMENT NOT NULL,
	`userId` int NOT NULL,
	`ticketNumber` varchar(50) NOT NULL,
	`subject` varchar(255) NOT NULL,
	`category` varchar(100) NOT NULL,
	`priority` enum('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
	`status` enum('open','in_progress','waiting_customer','resolved','closed') NOT NULL DEFAULT 'open',
	`description` text NOT NULL,
	`assignedTo` int,
	`resolution` text,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	`closedAt` timestamp,
	CONSTRAINT `support_tickets_id` PRIMARY KEY(`id`),
	CONSTRAINT `support_tickets_ticketNumber_unique` UNIQUE(`ticketNumber`)
);
--> statement-breakpoint
CREATE TABLE `ticket_messages` (
	`id` int AUTO_INCREMENT NOT NULL,
	`ticketId` int NOT NULL,
	`userId` int NOT NULL,
	`message` text NOT NULL,
	`attachments` text,
	`isInternal` boolean NOT NULL DEFAULT false,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `ticket_messages_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `vtu_providers` (
	`id` int AUTO_INCREMENT NOT NULL,
	`name` varchar(100) NOT NULL,
	`code` varchar(50) NOT NULL,
	`type` enum('airtime','data','both') NOT NULL,
	`isActive` boolean NOT NULL DEFAULT true,
	`apiKey` varchar(255),
	`apiEndpoint` varchar(255),
	`commission` decimal(5,2) NOT NULL DEFAULT '0',
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `vtu_providers_id` PRIMARY KEY(`id`),
	CONSTRAINT `vtu_providers_name_unique` UNIQUE(`name`),
	CONSTRAINT `vtu_providers_code_unique` UNIQUE(`code`)
);
--> statement-breakpoint
CREATE TABLE `vtu_transactions` (
	`id` int AUTO_INCREMENT NOT NULL,
	`userId` int NOT NULL,
	`providerId` int NOT NULL,
	`type` enum('airtime','data') NOT NULL,
	`phoneNumber` varchar(20) NOT NULL,
	`amount` decimal(15,2) NOT NULL,
	`status` enum('pending','success','failed','cancelled') NOT NULL DEFAULT 'pending',
	`reference` varchar(100),
	`providerReference` varchar(100),
	`receipt` text,
	`errorMessage` text,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `vtu_transactions_id` PRIMARY KEY(`id`),
	CONSTRAINT `vtu_transactions_reference_unique` UNIQUE(`reference`)
);
--> statement-breakpoint
CREATE TABLE `wallet_transactions` (
	`id` int AUTO_INCREMENT NOT NULL,
	`userId` int NOT NULL,
	`type` enum('deposit','withdrawal','vtu_purchase','giftcard_purchase','refund') NOT NULL,
	`amount` decimal(15,2) NOT NULL,
	`balanceBefore` decimal(15,2) NOT NULL,
	`balanceAfter` decimal(15,2) NOT NULL,
	`status` enum('pending','success','failed','cancelled') NOT NULL DEFAULT 'pending',
	`reference` varchar(100),
	`description` text,
	`metadata` text,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `wallet_transactions_id` PRIMARY KEY(`id`),
	CONSTRAINT `wallet_transactions_reference_unique` UNIQUE(`reference`)
);
--> statement-breakpoint
ALTER TABLE `users` ADD `walletBalance` decimal(15,2) DEFAULT '0.00' NOT NULL;--> statement-breakpoint
ALTER TABLE `users` ADD `kycStatus` enum('unverified','pending','verified','rejected') DEFAULT 'unverified' NOT NULL;--> statement-breakpoint
ALTER TABLE `users` ADD `deviceFingerprint` varchar(255);--> statement-breakpoint
ALTER TABLE `users` ADD `lastIpAddress` varchar(45);--> statement-breakpoint
ALTER TABLE `users` ADD `lastLocation` text;--> statement-breakpoint
ALTER TABLE `users` ADD `isActive` boolean DEFAULT true NOT NULL;--> statement-breakpoint
CREATE INDEX `audit_admin_idx` ON `admin_audit_logs` (`adminId`);--> statement-breakpoint
CREATE INDEX `audit_action_idx` ON `admin_audit_logs` (`action`);--> statement-breakpoint
CREATE INDEX `faq_category_idx` ON `faqs` (`category`);--> statement-breakpoint
CREATE INDEX `alert_user_idx` ON `fraud_alerts` (`userId`);--> statement-breakpoint
CREATE INDEX `alert_status_idx` ON `fraud_alerts` (`status`);--> statement-breakpoint
CREATE INDEX `alert_rule_idx` ON `fraud_alerts` (`ruleId`);--> statement-breakpoint
CREATE INDEX `fraud_enabled_idx` ON `fraud_rules` (`isEnabled`);--> statement-breakpoint
CREATE INDEX `gcd_provider_idx` ON `giftcard_denominations` (`providerId`);--> statement-breakpoint
CREATE INDEX `gc_user_idx` ON `giftcard_transactions` (`userId`);--> statement-breakpoint
CREATE INDEX `gc_status_idx` ON `giftcard_transactions` (`status`);--> statement-breakpoint
CREATE INDEX `gc_ref_idx` ON `giftcard_transactions` (`reference`);--> statement-breakpoint
CREATE INDEX `ticket_user_idx` ON `support_tickets` (`userId`);--> statement-breakpoint
CREATE INDEX `ticket_status_idx` ON `support_tickets` (`status`);--> statement-breakpoint
CREATE INDEX `ticket_assign_idx` ON `support_tickets` (`assignedTo`);--> statement-breakpoint
CREATE INDEX `msg_ticket_idx` ON `ticket_messages` (`ticketId`);--> statement-breakpoint
CREATE INDEX `msg_user_idx` ON `ticket_messages` (`userId`);--> statement-breakpoint
CREATE INDEX `vtu_user_idx` ON `vtu_transactions` (`userId`);--> statement-breakpoint
CREATE INDEX `vtu_status_idx` ON `vtu_transactions` (`status`);--> statement-breakpoint
CREATE INDEX `vtu_ref_idx` ON `vtu_transactions` (`reference`);--> statement-breakpoint
CREATE INDEX `user_idx` ON `wallet_transactions` (`userId`);--> statement-breakpoint
CREATE INDEX `status_idx` ON `wallet_transactions` (`status`);--> statement-breakpoint
CREATE INDEX `ref_idx` ON `wallet_transactions` (`reference`);--> statement-breakpoint
CREATE INDEX `role_idx` ON `users` (`role`);--> statement-breakpoint
CREATE INDEX `kyc_idx` ON `users` (`kycStatus`);