-- ==============================================================================
-- GEMPIRE Ceylon - Complete Production Database Schema & Seed Data
-- Target Database: newmsgro_Gempire (cPanel MySQL / MariaDB)
-- Character Set: utf8mb4_unicode_ci
-- ==============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- 1. USERS TABLE
CREATE TABLE IF NOT EXISTS `users` (
  `id` VARCHAR(64) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `passwordHash` VARCHAR(255) NOT NULL,
  `role` VARCHAR(32) NOT NULL DEFAULT 'customer',
  `phone` VARCHAR(64) DEFAULT NULL,
  `avatarUrl` VARCHAR(255) DEFAULT NULL,
  `country` VARCHAR(128) DEFAULT NULL,
  `vipTier` VARCHAR(32) NOT NULL DEFAULT 'standard',
  `isActive` TINYINT(1) NOT NULL DEFAULT 1,
  `emailVerifiedAt` VARCHAR(64) DEFAULT NULL,
  `lastLoginAt` VARCHAR(64) DEFAULT NULL,
  `createdAt` VARCHAR(64) NOT NULL,
  `updatedAt` VARCHAR(64) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_users_email` (`email`),
  KEY `idx_users_role` (`role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. GEMS TABLE
CREATE TABLE IF NOT EXISTS `gems` (
  `id` VARCHAR(64) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `category` VARCHAR(64) NOT NULL,
  `shape` VARCHAR(64) NOT NULL,
  `carat` DECIMAL(8, 2) NOT NULL,
  `dimensions` VARCHAR(128) NOT NULL,
  `origin` VARCHAR(255) NOT NULL,
  `treatment` VARCHAR(255) NOT NULL,
  `color` VARCHAR(128) NOT NULL,
  `reportNumber` VARCHAR(128) NOT NULL,
  `lab` VARCHAR(32) NOT NULL,
  `priceUSD` DECIMAL(12, 2) NOT NULL,
  `imagePath` VARCHAR(255) NOT NULL,
  `hue` VARCHAR(32) NOT NULL,
  `description` TEXT NOT NULL,
  `miningDistrict` VARCHAR(255) NOT NULL,
  `transparency` VARCHAR(255) NOT NULL,
  `status` VARCHAR(32) NOT NULL DEFAULT 'available',
  `featured` TINYINT(1) DEFAULT 1,
  `createdAt` VARCHAR(64) NOT NULL,
  `updatedAt` VARCHAR(64) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_gems_category` (`category`),
  KEY `idx_gems_status` (`status`),
  KEY `idx_gems_featured` (`featured`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. GEM GALLERY IMAGES
CREATE TABLE IF NOT EXISTS `gem_images` (
  `id` VARCHAR(64) NOT NULL,
  `gemId` VARCHAR(64) NOT NULL,
  `imageUrl` VARCHAR(255) NOT NULL,
  `caption` VARCHAR(255) DEFAULT NULL,
  `displayOrder` INT NOT NULL DEFAULT 0,
  `createdAt` VARCHAR(64) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_gem_images_gemId` (`gemId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. BANK ACCOUNTS TABLE
CREATE TABLE IF NOT EXISTS `bank_accounts` (
  `id` VARCHAR(64) NOT NULL,
  `bankName` VARCHAR(255) NOT NULL,
  `accountName` VARCHAR(255) NOT NULL,
  `accountNumber` VARCHAR(128) NOT NULL,
  `branch` VARCHAR(255) NOT NULL,
  `swiftCode` VARCHAR(64) NOT NULL,
  `currency` VARCHAR(16) NOT NULL DEFAULT 'USD',
  `instructions` TEXT,
  `isPrimary` TINYINT(1) DEFAULT 0,
  `createdAt` VARCHAR(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. ORDERS TABLE
CREATE TABLE IF NOT EXISTS `orders` (
  `id` VARCHAR(64) NOT NULL,
  `orderNumber` VARCHAR(64) NOT NULL,
  `userId` VARCHAR(64) DEFAULT NULL,
  `customerName` VARCHAR(255) NOT NULL,
  `customerEmail` VARCHAR(255) NOT NULL,
  `customerPhone` VARCHAR(64) NOT NULL,
  `shippingAddress` TEXT NOT NULL,
  `city` VARCHAR(128) NOT NULL,
  `country` VARCHAR(128) NOT NULL,
  `postalCode` VARCHAR(64) NOT NULL,
  `itemsJson` LONGTEXT NOT NULL,
  `totalAmountUSD` DECIMAL(12, 2) NOT NULL,
  `currency` VARCHAR(16) NOT NULL DEFAULT 'USD',
  `paymentMethod` VARCHAR(64) NOT NULL DEFAULT 'bank_transfer',
  `bankAccountId` VARCHAR(64) DEFAULT NULL,
  `slipUrl` TEXT DEFAULT NULL,
  `slipRefNumber` VARCHAR(128) DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `status` VARCHAR(32) NOT NULL DEFAULT 'pending_verification',
  `createdAt` VARCHAR(64) NOT NULL,
  `updatedAt` VARCHAR(64) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_orders_number` (`orderNumber`),
  KEY `idx_orders_customerEmail` (`customerEmail`),
  KEY `idx_orders_status` (`status`),
  KEY `idx_orders_userId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. PRIVATE VIEWINGS TABLE
CREATE TABLE IF NOT EXISTS `private_viewings` (
  `id` VARCHAR(64) NOT NULL,
  `userId` VARCHAR(64) DEFAULT NULL,
  `clientName` VARCHAR(255) NOT NULL,
  `contactEmail` VARCHAR(255) NOT NULL,
  `contactPhone` VARCHAR(64) DEFAULT NULL,
  `location` VARCHAR(255) NOT NULL,
  `preferredDate` VARCHAR(64) NOT NULL,
  `notes` TEXT DEFAULT NULL,
  `status` VARCHAR(32) NOT NULL DEFAULT 'pending',
  `createdAt` VARCHAR(64) NOT NULL,
  `updatedAt` VARCHAR(64) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_viewings_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. INQUIRIES TABLE
CREATE TABLE IF NOT EXISTS `inquiries` (
  `id` VARCHAR(64) NOT NULL,
  `gemId` VARCHAR(64) DEFAULT NULL,
  `clientName` VARCHAR(255) NOT NULL,
  `clientEmail` VARCHAR(255) NOT NULL,
  `clientPhone` VARCHAR(64) DEFAULT NULL,
  `subject` VARCHAR(255) NOT NULL,
  `message` TEXT NOT NULL,
  `offerUSD` DECIMAL(12, 2) DEFAULT NULL,
  `status` VARCHAR(32) NOT NULL DEFAULT 'unread',
  `createdAt` VARCHAR(64) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_inquiries_gemId` (`gemId`),
  KEY `idx_inquiries_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. SETTINGS TABLE
CREATE TABLE IF NOT EXISTS `settings` (
  `key` VARCHAR(128) NOT NULL,
  `value` TEXT NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. AUDIT LOGS TABLE
CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id` VARCHAR(64) NOT NULL,
  `userId` VARCHAR(64) DEFAULT NULL,
  `action` VARCHAR(128) NOT NULL,
  `detailsJson` TEXT DEFAULT NULL,
  `ipAddress` VARCHAR(64) DEFAULT NULL,
  `createdAt` VARCHAR(64) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_audit_logs_userId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;