CREATE TABLE IF NOT EXISTS products (
  id VARCHAR(255) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  price INT NOT NULL,
  image VARCHAR(1000),
  category VARCHAR(255),
  ingredients JSON,
  benefits JSON,
  usageGuide TEXT,
  stock INT DEFAULT 0,
  isBestSeller BOOLEAN DEFAULT FALSE,
  createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS categories (
  id VARCHAR(255) PRIMARY KEY,
  label VARCHAR(255) NOT NULL,
  img VARCHAR(1000)
);

CREATE TABLE IF NOT EXISTS orders (
  id VARCHAR(255) PRIMARY KEY,
  customerName VARCHAR(255),
  customerEmail VARCHAR(255),
  customerPhone VARCHAR(255),
  deliveryAddress TEXT,
  items JSON,
  totalAmount INT,
  status VARCHAR(50) DEFAULT 'pending',
  paymentMethod VARCHAR(50),
  paymentStatus VARCHAR(50) DEFAULT 'unpaid',
  createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS bookings (
  id VARCHAR(255) PRIMARY KEY,
  name VARCHAR(255),
  email VARCHAR(255),
  phone VARCHAR(255),
  date VARCHAR(50),
  time VARCHAR(50),
  concerns TEXT,
  status VARCHAR(50) DEFAULT 'pending',
  createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS settings (
  id VARCHAR(50) PRIMARY KEY,
  bankEnabled BOOLEAN DEFAULT TRUE,
  monnifyEnabled BOOLEAN DEFAULT TRUE,
  bankName VARCHAR(255),
  accountName VARCHAR(255),
  accountNumber VARCHAR(255),
  monnifyApiKey VARCHAR(255),
  monnifyContractCode VARCHAR(255),
  monnifySecretKey VARCHAR(255),
  storeAddress VARCHAR(255),
  storePhone VARCHAR(255),
  storeEmail VARCHAR(255),
  deliveryLocations JSON,
  homepageImage VARCHAR(1000),
  ceoImage VARCHAR(1000),
  adminPassword VARCHAR(255)
);

INSERT INTO settings 
  (id, bankEnabled, monnifyEnabled, bankName, accountName, accountNumber, monnifyApiKey, monnifyContractCode, monnifySecretKey, storeAddress, storePhone, storeEmail, deliveryLocations, homepageImage, ceoImage, adminPassword) 
VALUES 
  ('global', 1, 1, 'Sterling Bank PLC', 'MUMMCY AYOFE HERBAL CARE LTD', '1024451121', '', '', '', 'Plot 24B, Admiralty Way, Lekki Phase 1, Lagos, Nigeria', '+234 908 445 1121', 'wellness@eweandori.com', '[{"id":"lagos-mainland","label":"Lagos Mainland","charge":2000},{"id":"lagos-island","label":"Lagos Island / Lekki","charge":3000},{"id":"abuja-fct","label":"Abuja / FCT Express","charge":4500},{"id":"rivers-ph","label":"Rivers State (Port Harcourt)","charge":4000},{"id":"oyo-ibadan","label":"Oyo State (Ibadan)","charge":3000},{"id":"rest-of-nigeria","label":"Rest of Nigeria (Nationwide Shipping)","charge":5500}]', '/images/model_homepage.jpg', '/src/assets/images/mummy_ayofe_bags_1779911520990.png', 'admin')
ON DUPLICATE KEY UPDATE id=id;