-- 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 users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash CHAR(64) NOT NULL,
  full_name VARCHAR(100) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS admin_credentials (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash CHAR(64) NOT NULL,
  role VARCHAR(50) NOT NULL DEFAULT 'admin',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

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.'
  );

-- Seed users with SHA-256 password hashes for demo login.
-- Password for user@example.com is Password123!
-- Password for farmer@example.com is TomatoFarm2026!
INSERT IGNORE INTO users (email, password_hash, full_name) VALUES
  ('user@example.com', SHA2('Password123!', 256), 'Demo User'),
  ('farmer@example.com', SHA2('TomatoFarm2026!', 256), 'Farmer Jane');

-- Seed a single admin credential for the admin login demo.
-- Password for admin@example.com is AdminPass2026!
INSERT IGNORE INTO admin_credentials (email, password_hash, role, is_active) VALUES
  ('admin@example.com', SHA2('AdminPass2026!', 256), 'admin', 1);