CREATE TABLE IF NOT EXISTS `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `openId` varchar(64) NOT NULL,
  `name` text,
  `email` varchar(320),
  `loginMethod` varchar(64),
  `role` enum('user','admin') NOT NULL DEFAULT 'user',
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `lastSignedIn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`), UNIQUE KEY `users_openId_unique` (`openId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_numbers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `externalId` varchar(64) NOT NULL,
  `phoneNumber` varchar(32) NOT NULL,
  `state` varchar(64) NOT NULL,
  `city` varchar(128),
  `zipCode` varchar(16),
  `country` varchar(64) NOT NULL DEFAULT 'United States',
  `loanLimit` decimal(14,2),
  `price` decimal(14,2) NOT NULL DEFAULT 29.00,
  `currency` varchar(3) NOT NULL DEFAULT 'USD',
  `status` enum('available','reserved','sold','disabled') NOT NULL DEFAULT 'available',
  `reservationToken` varchar(64),
  `reservedUntil` timestamp NULL,
  `soldAt` timestamp NULL,
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`), UNIQUE KEY `phone_numbers_phone_unique` (`phoneNumber`), UNIQUE KEY `phone_numbers_external_unique` (`externalId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `orders` (
  `id` int NOT NULL AUTO_INCREMENT,
  `reference` varchar(40) NOT NULL,
  `phoneNumberId` int NOT NULL,
  `customerName` varchar(160) NOT NULL,
  `customerPhone` varchar(32) NOT NULL,
  `customerEmail` varchar(320) NOT NULL,
  `address` text NOT NULL,
  `city` varchar(128) NOT NULL,
  `state` varchar(64) NOT NULL,
  `amount` decimal(14,2) NOT NULL,
  `currency` varchar(3) NOT NULL DEFAULT 'USD',
  `paymentProvider` enum('mpesa','pesapal','paystack') NOT NULL,
  `paymentReference` varchar(160),
  `paymentStatus` enum('pending','verified','failed','reversed') NOT NULL DEFAULT 'pending',
  `orderStatus` enum('pending','paid','cancelled','fulfilled') NOT NULL DEFAULT 'pending',
  `paidAt` timestamp NULL,
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`), UNIQUE KEY `orders_reference_unique` (`reference`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `payment_events` (
  `id` int NOT NULL AUTO_INCREMENT,
  `provider` enum('mpesa','pesapal','paystack') NOT NULL,
  `eventReference` varchar(180) NOT NULL,
  `orderReference` varchar(40),
  `verified` int NOT NULL DEFAULT 0,
  `payload` text NOT NULL,
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`), UNIQUE KEY `payment_events_event_unique` (`provider`,`eventReference`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;