-- Create the database and tables for the login demo.
CREATE DATABASE IF NOT EXISTS tomato_hub CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE tomato_hub;

CREATE TABLE IF NOT EXISTS contact_messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  subject VARCHAR(255) DEFAULT NULL,
  message TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS products (
  id VARCHAR(255) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  variety VARCHAR(255) NOT NULL,
  image VARCHAR(255) DEFAULT NULL,
  price_per_kg INT NOT NULL,
  unit VARCHAR(20) NOT NULL DEFAULT 'kg',
  location VARCHAR(255) NOT NULL,
  district VARCHAR(255) NOT NULL,
  meta_description VARCHAR(255) DEFAULT NULL,
  source_farm VARCHAR(255) NOT NULL,
  harvest_date DATE NOT NULL,
  delivery VARCHAR(255) NOT NULL,
  stock_kg INT NOT NULL DEFAULT 0,
  source_person_name VARCHAR(255) NOT NULL,
  source_person_note TEXT DEFAULT NULL,
  status ENUM('in_season','available_in_stock','unavailable') NOT NULL DEFAULT 'in_season',
  is_draft TINYINT(1) NOT NULL DEFAULT 0,
  is_hidden TINYINT(1) NOT NULL DEFAULT 0,
  description TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT IGNORE INTO products (
  id,
  name,
  variety,
  image,
  price_per_kg,
  unit,
  location,
  district,
  meta_description,
  source_farm,
  harvest_date,
  delivery,
  stock_kg,
  source_person_name,
  source_person_note,
  status,
  is_draft,
  is_hidden,
  description
) VALUES
  (
    'roma-musanze',
    'Vine-Ripened Roma',
    'Roma',
    'roma-musanze',
    1200,
    'kg',
    'Musanze',
    'Musanze',
    'Dense, low-water Roma tomatoes perfect for sauces and stews.',
    'Musanze Family Farm',
    '2026-04-25',
    '48h nationwide',
    420,
    'Jean-Baptiste U.',
    'Hand-picked at peak ripeness from the volcanic soils of Musanze.',
    'in_season',
    0,
    0,
    'Dense, low-water Roma tomatoes — perfect for sauces, stews, and slow cooking. Hand-picked at peak ripeness from the volcanic soils of Musanze.'
  ),
  (
    'beefsteak-huye',
    'Highland Beefsteak',
    'Beefsteak',
    'beefsteak-huye',
    1450,
    'kg',
    'Huye',
    'Huye',
    'Large, meaty beefsteak tomatoes grown for grilling and salads.',
    'Huye Harvest',
    '2026-04-26',
    '48h nationwide',
    280,
    'Marie-Claire N.',
    'Large, meaty beefsteak tomatoes grown in the cool highlands of Huye. Ideal for sandwiches, salads, and grilling.',
    'available_in_stock',
    0,
    0,
    'Large, meaty beefsteak tomatoes grown in the cool highlands of Huye. Ideal for sandwiches, salads, and grilling.'
  ),
  (
    'cherry-rwamagana',
    'Sun-Sweet Cherry',
    'Cherry',
    'cherry-rwamagana',
    1800,
    'kg',
    'Rwamagana',
    'Rwamagana',
    'Tiny, sugar-sweet cherry tomatoes that burst with flavor.',
    'Rwamagana Orchards',
    '2026-04-27',
    '48h nationwide',
    160,
    'Eric M.',
    'Perfect for snacking, salads, and fresh garnishes.',
    'available_in_stock',
    0,
    0,
    'Tiny, sugar-sweet cherry tomatoes that burst with flavor. Snack-ready, salad-ready, kid-approved.'
  ),
  (
    'heirloom-nyamagabe',
    'Heritage Heirloom Mix',
    'Heirloom',
    'heirloom-nyamagabe',
    2200,
    'kg',
    'Nyamagabe',
    'Nyamagabe',
    'A curated mix of heirloom varieties in red, orange, and yellow.',
    'Nyamagabe Heritage Farm',
    '2026-05-04',
    'Restocking soon',
    0,
    'Agnès K.',
    'A limited heirloom harvest with deep flavor and colorful variety.',
    'unavailable',
    0,
    0,
    'A curated mix of heirloom varieties in red, orange, and yellow. Limited harvest — restocking next week.'
  );
