CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  role VARCHAR(40) NOT NULL DEFAULT 'admin',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY users_email_unique (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS products (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL,
  price DECIMAL(12,2) NOT NULL,
  category VARCHAR(100) NOT NULL,
  season VARCHAR(100) NOT NULL,
  description TEXT NOT NULL,
  image TEXT NOT NULL,
  secondary_image TEXT NULL,
  featured TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY products_slug_unique (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS journal_posts (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL,
  excerpt TEXT NOT NULL,
  body TEXT NOT NULL,
  image TEXT NOT NULL,
  category VARCHAR(100) NOT NULL,
  published_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY journal_slug_unique (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS subscribers (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY subscribers_email_unique (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS appointments (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  date DATETIME NOT NULL,
  service_type VARCHAR(255) NOT NULL,
  notes TEXT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO users (email, name, role) VALUES
('studio@vesperahouse.com', 'Vespera Studio', 'admin');

INSERT IGNORE INTO products (name, slug, price, category, season, description, image, secondary_image, featured) VALUES
('The Nocturne Dress', 'the-nocturne-dress', 2480, 'Evening', 'Atelier 2026', 'A liquid black silk column cut with an open back and hand-finished shoulder drape.', 'https://images.unsplash.com/photo-1539008835657-9e8e9680c956?auto=format&fit=crop&w=1400&q=85', 'https://images.unsplash.com/photo-1595777457583-95e059d581b8?auto=format&fit=crop&w=1000&q=85', 1),
('Sculpted Light', 'sculpted-light', 1920, 'Tailoring', 'Atelier 2026', 'Ivory silk-wool tailoring with a softened shoulder and a single, architectural closure.', 'https://images.unsplash.com/photo-1551488831-00ddcb6c6bd3?auto=format&fit=crop&w=1400&q=85', 'https://images.unsplash.com/photo-1550639525-c97d455acf70?auto=format&fit=crop&w=1000&q=85', 1),
('Vesper Skin Ritual', 'vesper-skin-ritual', 360, 'Beauty', 'Core', 'A three-step evening ritual of oil, balm, and veil for skin that catches candlelight.', 'https://images.unsplash.com/photo-1611930022073-b7a4ba5fcccd?auto=format&fit=crop&w=1400&q=85', NULL, 1),
('After Hours Coat', 'after-hours-coat', 3180, 'Outerwear', 'Atelier 2026', 'A sweeping wool coat in deep forest, finished with satin lapels and a hidden belt.', 'https://images.unsplash.com/photo-1539109136881-3be0616acf4b?auto=format&fit=crop&w=1400&q=85', 'https://images.unsplash.com/photo-1529139574466-a303027c1d8b?auto=format&fit=crop&w=1000&q=85', 0);

INSERT IGNORE INTO journal_posts (title, slug, excerpt, body, image, category) VALUES
('The Art of Arriving', 'the-art-of-arriving', 'On presence, preparation, and the quiet power of a look that feels inevitable.', 'The most memorable entrance is rarely the loudest. It is the one where every detail feels considered, and nothing appears to be trying too hard.', 'https://images.unsplash.com/photo-1483985988355-763728e1935b?auto=format&fit=crop&w=1400&q=85', 'Point of View'),
('A Study in Candlelight', 'a-study-in-candlelight', 'Why the evening changes the way we think about texture, tone, and skin.', 'Candlelight is a generous editor. It softens edges, deepens tone, and asks us to consider the materials closest to the body.', 'https://images.unsplash.com/photo-1525507119028-ed4c629a60a3?auto=format&fit=crop&w=1400&q=85', 'Beauty Notes'),
('The Private Wardrobe', 'the-private-wardrobe', 'Inside the new language of personal dressing: fewer pieces, more feeling.', 'A private wardrobe is not a collection of things. It is a vocabulary, built slowly through trust, repetition, and a willingness to edit.', 'https://images.unsplash.com/photo-1490481651871-ab68de25d43d?auto=format&fit=crop&w=1400&q=85', 'The House');

INSERT IGNORE INTO subscribers (email) VALUES ('hello@vesperahouse.com');
INSERT INTO appointments (name, email, date, service_type, notes, status)
SELECT 'Elena Rossi', 'elena@example.com', '2026-08-14 18:30:00', 'Private styling', 'Looking for an evening look for a September gala.', 'confirmed'
WHERE NOT EXISTS (SELECT 1 FROM appointments);