-- Step 37: Add customer user account foundation
-- Feature: Public customer accounts for portal app and optional dine-in linking
-- Date: 2026-04-27
--
-- Notes:
-- - This migration is intentionally limited to the secure account foundation.
-- - Existing anonymous order, reservation, and dine-in flows remain supported.
-- - The customerUserId columns are nullable and use ON DELETE SET NULL so historical
--   business records are preserved if a customer account is deleted/anonymized.

CREATE TABLE `customerUsers` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `publicId` char(36) NOT NULL,
  `email` varchar(255) NOT NULL,
  `emailNormalized` varchar(255) NOT NULL,
  `emailVerifiedAt` datetime DEFAULT NULL,
  `passwordHash` varchar(255) DEFAULT NULL,
  `displayName` varchar(120) DEFAULT NULL,
  `defaultName` varchar(120) DEFAULT NULL,
  `defaultPhone` varchar(40) DEFAULT NULL,
  `defaultLanguage` varchar(8) NOT NULL DEFAULT 'en',
  `analyticsConsent` tinyint(1) NOT NULL DEFAULT 0,
  `marketingConsent` tinyint(1) NOT NULL DEFAULT 0,
  `status` enum('active','pending','disabled','deleted') NOT NULL DEFAULT 'active',
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  `lastLoginAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_customerUsers_publicId` (`publicId`),
  UNIQUE KEY `uk_customerUsers_email` (`email`),
  UNIQUE KEY `uk_customerUsers_emailNormalized` (`emailNormalized`),
  KEY `idx_customerUsers_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `customerUserSessions` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `userId` bigint(20) UNSIGNED NOT NULL,
  `sessionTokenHash` char(64) NOT NULL,
  `csrfTokenHash` char(64) NOT NULL,
  `createdAt` datetime NOT NULL,
  `lastUsedAt` datetime NOT NULL,
  `expiresAt` datetime NOT NULL,
  `revokedAt` datetime DEFAULT NULL,
  `ipHash` char(64) DEFAULT NULL,
  `userAgentHash` char(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_customerUserSessions_sessionTokenHash` (`sessionTokenHash`),
  KEY `idx_customerUserSessions_user` (`userId`),
  KEY `idx_customerUserSessions_expires` (`expiresAt`),
  CONSTRAINT `fk_customerUserSessions_userId` FOREIGN KEY (`userId`) REFERENCES `customerUsers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `customerUserAuthChallenges` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `userId` bigint(20) UNSIGNED DEFAULT NULL,
  `emailNormalized` varchar(255) DEFAULT NULL,
  `type` enum('email_verification','password_reset','magic_login') NOT NULL,
  `tokenHash` char(64) NOT NULL,
  `createdAt` datetime NOT NULL,
  `expiresAt` datetime NOT NULL,
  `usedAt` datetime DEFAULT NULL,
  `requestIpHash` char(64) DEFAULT NULL,
  `userAgentHash` char(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_customerUserAuthChallenges_tokenHash` (`tokenHash`),
  KEY `idx_customerUserAuthChallenges_user` (`userId`),
  KEY `idx_customerUserAuthChallenges_email` (`emailNormalized`),
  KEY `idx_customerUserAuthChallenges_type_expires` (`type`,`expiresAt`),
  CONSTRAINT `fk_customerUserAuthChallenges_userId` FOREIGN KEY (`userId`) REFERENCES `customerUsers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `customerUserSecurityEvents` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `userId` bigint(20) UNSIGNED DEFAULT NULL,
  `eventType` varchar(80) NOT NULL,
  `createdAt` datetime NOT NULL,
  `ipHash` char(64) DEFAULT NULL,
  `userAgentHash` char(64) DEFAULT NULL,
  `metadataJson` json DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_customerUserSecurityEvents_user` (`userId`),
  KEY `idx_customerUserSecurityEvents_type_time` (`eventType`,`createdAt`),
  CONSTRAINT `fk_customerUserSecurityEvents_userId` FOREIGN KEY (`userId`) REFERENCES `customerUsers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `onlineOrders`
  ADD COLUMN `customerUserId` bigint(20) UNSIGNED DEFAULT NULL AFTER `vendorId`,
  ADD KEY `idx_onlineOrders_customer_time` (`customerUserId`,`placedTimestamp`),
  ADD CONSTRAINT `fk_onlineOrders_customerUserId` FOREIGN KEY (`customerUserId`) REFERENCES `customerUsers` (`id`) ON DELETE SET NULL;

ALTER TABLE `reservations`
  ADD COLUMN `customerUserId` bigint(20) UNSIGNED DEFAULT NULL AFTER `vendorId`,
  ADD KEY `idx_reservations_customer_date` (`customerUserId`,`dateYear`,`dateMonth`,`dateDay`),
  ADD CONSTRAINT `fk_reservations_customerUserId` FOREIGN KEY (`customerUserId`) REFERENCES `customerUsers` (`id`) ON DELETE SET NULL;

ALTER TABLE `sessionCustomers`
  ADD COLUMN `customerUserId` bigint(20) UNSIGNED DEFAULT NULL AFTER `vendorId`,
  ADD KEY `idx_sessionCustomers_customer_session` (`customerUserId`,`sessionId`),
  ADD CONSTRAINT `fk_sessionCustomers_customerUserId` FOREIGN KEY (`customerUserId`) REFERENCES `customerUsers` (`id`) ON DELETE SET NULL;