CREATE DATABASE IF NOT EXISTS ecom_dash CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE ecom_dash;

CREATE TABLE IF NOT EXISTS products (
  id VARCHAR(191) PRIMARY KEY,
  name VARCHAR(191) NOT NULL,
  description TEXT NOT NULL,
  price INT NOT NULL,
  image_url VARCHAR(512) NOT NULL,
  payment_link VARCHAR(512) NOT NULL DEFAULT '#',
  trending TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS orders (
  id VARCHAR(191) PRIMARY KEY,
  product_id VARCHAR(191) NOT NULL,
  customer_name VARCHAR(191) NOT NULL,
  customer_email VARCHAR(191) NOT NULL,
  shipping_address TEXT NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_orders_products FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
);

CREATE TABLE IF NOT EXISTS site_settings (
  id VARCHAR(32) PRIMARY KEY,
  phone VARCHAR(64) NOT NULL,
  email VARCHAR(191) NOT NULL,
  address TEXT NOT NULL,
  instagram VARCHAR(191) NOT NULL,
  facebook VARCHAR(191) NOT NULL,
  banner_image_url VARCHAR(512) NOT NULL DEFAULT '',
  banner_title TEXT NOT NULL,
  banner_subtitle TEXT NOT NULL,
  banner_button_text VARCHAR(191) NOT NULL,
  banner_button_link VARCHAR(512) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO site_settings (
  id,
  phone,
  email,
  address,
  instagram,
  facebook,
  banner_image_url,
  banner_title,
  banner_subtitle,
  banner_button_text,
  banner_button_link
)
VALUES (
  'default',
  '+91 98765 43210',
  'hello@foxwoodherbals.in',
  '123 Nature Way, Mumbai, India',
  '@foxwoodherbals',
  '/foxwoodherbals',
  'https://images.unsplash.com/photo-1527799820374-dcf8d9d4a388?auto=format&fit=crop&w=1600&q=80',
  'Rooted in Nature,\nCrafted for You.',
  'Discover Foxwood Herbals'' restorative haircare collection, formulated with pure plant extracts to deeply nourish and revive your hair.',
  'Shop The Collection',
  '#trending'
)
ON DUPLICATE KEY UPDATE
  phone = VALUES(phone),
  email = VALUES(email),
  address = VALUES(address),
  instagram = VALUES(instagram),
  facebook = VALUES(facebook),
  banner_image_url = VALUES(banner_image_url),
  banner_title = VALUES(banner_title),
  banner_subtitle = VALUES(banner_subtitle),
  banner_button_text = VALUES(banner_button_text),
  banner_button_link = VALUES(banner_button_link);

INSERT INTO products (id, name, description, price, image_url, payment_link, trending)
VALUES
  (
    '11111111-1111-1111-1111-111111111111',
    'Anti-Hairfall Shampoo',
    'Fortifying herbal blend to reduce hair fall and strengthen roots.',
    899,
    'https://images.unsplash.com/photo-1608248543803-ba4f8c70ae0b?auto=format&fit=crop&q=80&w=400&h=400',
    '#',
    1
  ),
  (
    '22222222-2222-2222-2222-222222222222',
    'Anti-Dandruff Shampoo',
    'Clarifying mint and tea tree formula to soothe the scalp.',
    799,
    'https://images.unsplash.com/photo-1620916566398-39f1143ab7be?auto=format&fit=crop&q=80&w=400&h=400',
    '#',
    1
  ),
  (
    '33333333-3333-3333-3333-333333333333',
    'Nourishing Herbal Conditioner',
    'Deep moisture treatment with lavender and aloe vera extracts.',
    949,
    'https://images.unsplash.com/photo-1527799820374-dcf8d9d4a388?auto=format&fit=crop&q=80&w=400&h=400',
    '#',
    1
  ),
  (
    '44444444-4444-4444-4444-444444444444',
    'Revitalizing Hair Tonic',
    'Daily scalp drops to stimulate growth and improve density.',
    1299,
    'https://images.unsplash.com/photo-1611078489935-0cb964de46d6?auto=format&fit=crop&q=80&w=400&h=400',
    '#',
    1
  )
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  description = VALUES(description),
  price = VALUES(price),
  image_url = VALUES(image_url),
  payment_link = VALUES(payment_link),
  trending = VALUES(trending);
