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

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ------------------------------------------------------------------------------
-- 1. USERS TABLE (Admin, Concierge, and Registered Customers)
-- ------------------------------------------------------------------------------
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', -- 'admin', 'customer', 'concierge'
  `phone` VARCHAR(64) DEFAULT NULL,
  `avatarUrl` VARCHAR(255) DEFAULT NULL,
  `country` VARCHAR(128) DEFAULT NULL,
  `vipTier` VARCHAR(32) NOT NULL DEFAULT 'standard', -- 'standard', 'collector', 'vault_vip'
  `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 (Master Inventory / Vault Catalogue)
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `gems` (
  `id` VARCHAR(64) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `category` VARCHAR(64) NOT NULL,            -- 'sapphire', 'ruby', 'padparadscha', 'spinel'
  `shape` VARCHAR(64) NOT NULL,               -- 'cushion', 'oval', 'emerald', 'pear', 'round'
  `carat` DECIMAL(8, 2) NOT NULL,
  `dimensions` VARCHAR(128) NOT NULL,
  `origin` VARCHAR(255) NOT NULL,
  `treatment` VARCHAR(255) NOT NULL,          -- 'Unheated', 'Untreated (natural color)'
  `color` VARCHAR(128) NOT NULL,
  `reportNumber` VARCHAR(128) NOT NULL,       -- Certificate / Lab Report No.
  `lab` VARCHAR(32) NOT NULL,                 -- 'GIA', 'GRS', 'CGL'
  `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', -- 'available', 'reserved', 'sold'
  `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 (Multiple High-Res Angles per Stone)
-- ------------------------------------------------------------------------------
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 (Wire Transfer Settlement Accounts)
-- ------------------------------------------------------------------------------
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 (Customer Orders, Wire Transfers & Escrow Tracking)
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `orders` (
  `id` VARCHAR(64) NOT NULL,
  `orderNumber` VARCHAR(64) NOT NULL,
  `userId` VARCHAR(64) DEFAULT NULL,          -- Nullable for guest checkouts
  `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,                -- Payment proof / wire slip upload
  `slipRefNumber` VARCHAR(128) DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `status` VARCHAR(32) NOT NULL DEFAULT 'pending_verification', -- 'pending_verification', 'confirmed', 'shipped', 'completed', 'cancelled'
  `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 VIEWING REQUESTS (Concierge Bookings)
-- ------------------------------------------------------------------------------
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,           -- e.g. 'Ratnapura (Private Vault & Mines)', 'Colombo Suite'
  `preferredDate` VARCHAR(64) NOT NULL,
  `notes` TEXT DEFAULT NULL,
  `status` VARCHAR(32) NOT NULL DEFAULT 'pending', -- 'pending', 'confirmed', 'completed', 'cancelled'
  `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. CUSTOMER INQUIRIES & ACQUISITION OFFERS
-- ------------------------------------------------------------------------------
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', -- 'unread', 'in_progress', 'resolved'
  `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. SYSTEM SETTINGS
-- ------------------------------------------------------------------------------
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 (Admin Security & Activity Trail)
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id` VARCHAR(64) NOT NULL,
  `userId` VARCHAR(64) DEFAULT NULL,
  `action` VARCHAR(128) NOT NULL,             -- e.g. 'UPDATE_GEM_PRICE', 'VERIFY_ORDER'
  `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;