-- Jarvis Produkt-Erkenner · MySQL Schema v0.2.1
-- Für spätere PHP/MySQL-Version. UTF8MB4 empfohlen.

CREATE TABLE IF NOT EXISTS pe_brands (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  brand_type ENUM('manufacturer_brand','retailer_private_label','unknown') DEFAULT 'unknown',
  retailer VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_brand_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pe_companies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  legal_name VARCHAR(255) NOT NULL,
  register_country VARCHAR(10) NULL,
  register_court VARCHAR(255) NULL,
  register_type VARCHAR(50) NULL,
  register_number VARCHAR(80) NULL,
  address TEXT NULL,
  source_url TEXT NULL,
  verification_status ENUM('research_lead','needs_source','verified','rejected') DEFAULT 'research_lead',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_company_name (legal_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pe_facilities (
  id INT AUTO_INCREMENT PRIMARY KEY,
  approval_number VARCHAR(80) NULL,
  country_code VARCHAR(10) NULL,
  official_name VARCHAR(255) NULL,
  address TEXT NULL,
  company_id INT NULL,
  categories TEXT NULL,
  source_type VARCHAR(80) NULL,
  source_url TEXT NULL,
  checked_at DATETIME NULL,
  verification_status ENUM('research_lead','needs_source','verified','rejected') DEFAULT 'research_lead',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_approval_number (approval_number),
  INDEX idx_facility_company (company_id),
  CONSTRAINT fk_facility_company FOREIGN KEY (company_id) REFERENCES pe_companies(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pe_products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_type ENUM('brand_product','private_label','unknown') DEFAULT 'unknown',
  name VARCHAR(255) NOT NULL,
  brand_id INT NULL,
  barcode VARCHAR(32) NULL,
  category VARCHAR(255) NULL,
  sub_category VARCHAR(255) NULL,
  package_size DECIMAL(10,2) NULL,
  package_unit VARCHAR(30) NULL,
  current_price DECIMAL(10,2) NULL,
  currency CHAR(3) DEFAULT 'EUR',
  price_store VARCHAR(255) NULL,
  price_observed_at DATE NULL,
  facility_id INT NULL,
  company_id INT NULL,
  printed_producer_name VARCHAR(255) NULL,
  printed_address TEXT NULL,
  ingredients_text TEXT NULL,
  ingredients_normalized JSON NULL,
  nutrition_per_100g JSON NULL,
  image_front_url TEXT NULL,
  data_source VARCHAR(80) NULL,
  verification_status ENUM('research_lead','needs_photo','needs_source','verified','rejected') DEFAULT 'research_lead',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_product_name (name),
  INDEX idx_product_barcode (barcode),
  INDEX idx_product_facility (facility_id),
  CONSTRAINT fk_product_brand FOREIGN KEY (brand_id) REFERENCES pe_brands(id) ON DELETE SET NULL,
  CONSTRAINT fk_product_facility FOREIGN KEY (facility_id) REFERENCES pe_facilities(id) ON DELETE SET NULL,
  CONSTRAINT fk_product_company FOREIGN KEY (company_id) REFERENCES pe_companies(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pe_product_identification_marks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  raw_value VARCHAR(120) NOT NULL,
  normalized_value VARCHAR(120) NOT NULL,
  detected_by ENUM('manual','ocr','import','api') DEFAULT 'manual',
  confidence DECIMAL(4,3) DEFAULT 0.900,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_mark_normalized (normalized_value),
  CONSTRAINT fk_mark_product FOREIGN KEY (product_id) REFERENCES pe_products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pe_scans (
  id INT AUTO_INCREMENT PRIMARY KEY,
  input_type ENUM('camera_photo','upload','barcode','manual_text') DEFAULT 'upload',
  scan_mode VARCHAR(50) DEFAULT 'auto',
  raw_ocr_text MEDIUMTEXT NULL,
  detected_barcode VARCHAR(32) NULL,
  detected_vet_numbers JSON NULL,
  detected_producer TEXT NULL,
  confidence JSON NULL,
  image_path TEXT NULL,
  needs_admin_review TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pe_matches (
  id INT AUTO_INCREMENT PRIMARY KEY,
  private_label_product_id INT NOT NULL,
  brand_product_id INT NOT NULL,
  match_score DECIMAL(5,4) NOT NULL DEFAULT 0,
  result_label VARCHAR(255) NOT NULL,
  evidence JSON NOT NULL,
  warning TEXT NULL,
  verification_status ENUM('research_lead','needs_photo','needs_source','verified','rejected') DEFAULT 'research_lead',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_match_score (match_score),
  CONSTRAINT fk_match_private FOREIGN KEY (private_label_product_id) REFERENCES pe_products(id) ON DELETE CASCADE,
  CONSTRAINT fk_match_brand FOREIGN KEY (brand_product_id) REFERENCES pe_products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- v0.2 · Login-/Profil-Erweiterung
-- User-Accounts werden absichtlich nicht neu erzeugt, sondern über die vorhandene Tabelle jarvis_finance_users genutzt.
-- Erwartet: jarvis_finance_users.id, username, email, role, status, admin.

CREATE TABLE IF NOT EXISTS pe_user_food_profiles (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  selected_allergens_json JSON NULL,
  custom_terms_json JSON NULL,
  daily_profile ENUM('adult_default') NOT NULL DEFAULT 'adult_default',
  notes TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_pe_food_profile_user (user_id),
  INDEX idx_pe_food_profile_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pe_ingredient_rules (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  rule_key VARCHAR(120) NOT NULL,
  label VARCHAR(190) NOT NULL,
  rule_type ENUM('eu_allergen','watchlist','daily_limit','custom') NOT NULL,
  aliases_json JSON NULL,
  severity ENUM('info','watch','warn','danger') NOT NULL DEFAULT 'info',
  note TEXT NULL,
  source_ref VARCHAR(190) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_pe_ingredient_rule_key (rule_key),
  INDEX idx_pe_ingredient_rule_type (rule_type),
  INDEX idx_pe_ingredient_rule_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pe_product_ingredient_flags (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NULL,
  scan_id INT NULL,
  user_id BIGINT NULL,
  rule_key VARCHAR(120) NOT NULL,
  detected_alias VARCHAR(190) NULL,
  match_context VARCHAR(255) NULL,
  severity ENUM('info','watch','warn','danger') NOT NULL DEFAULT 'info',
  confidence DECIMAL(4,3) DEFAULT 0.850,
  source ENUM('ocr','open_food_facts','manual','admin') NOT NULL DEFAULT 'ocr',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_pe_flag_product (product_id),
  INDEX idx_pe_flag_scan (scan_id),
  INDEX idx_pe_flag_user (user_id),
  INDEX idx_pe_flag_rule (rule_key),
  CONSTRAINT fk_pe_flag_product FOREIGN KEY (product_id) REFERENCES pe_products(id) ON DELETE CASCADE,
  CONSTRAINT fk_pe_flag_scan FOREIGN KEY (scan_id) REFERENCES pe_scans(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- v0.2.1 · Open Registration Kompatibilität
-- Deine bestehende Tabelle jarvis_finance_users reicht laut aktuellem DB-Auszug aus.
-- registration_secret_verified_at darf NULL bleiben und wird bei offener Registrierung nicht benötigt.

-- Optionaler Check/Repair, nur ausführen falls einzelne Spalten fehlen:
-- ALTER TABLE jarvis_finance_users ADD COLUMN role ENUM('user','admin') NOT NULL DEFAULT 'user';
-- ALTER TABLE jarvis_finance_users ADD COLUMN status ENUM('active','locked','deleted') NOT NULL DEFAULT 'active';
-- ALTER TABLE jarvis_finance_users ADD COLUMN admin TINYINT(1) NOT NULL DEFAULT 0;
-- ALTER TABLE jarvis_finance_users ADD COLUMN registration_secret_verified_at TIMESTAMP NULL DEFAULT NULL;
