ai-schema.sql

-- DYNAMIC FILTER SYSTEM (required for AI settings storage)
CREATE TABLE IF NOT EXISTS filter_groups (
  id              TEXT PRIMARY KEY,
  name            TEXT NOT NULL,
  description     TEXT,
  icon            TEXT,
  display_order   INTEGER NOT NULL DEFAULT 0,
  is_active       BOOLEAN NOT NULL DEFAULT true,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS filter_options (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  group_id        TEXT NOT NULL REFERENCES filter_groups(id) ON DELETE CASCADE,
  value           TEXT NOT NULL,
  label           TEXT NOT NULL,
  display_order   INTEGER NOT NULL DEFAULT 0,
  is_active       BOOLEAN NOT NULL DEFAULT true,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(group_id, value)
);

CREATE TABLE IF NOT EXISTS product_filter_values (
  id                UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  product_id        TEXT NOT NULL,
  filter_option_id  UUID NOT NULL REFERENCES filter_options(id) ON DELETE CASCADE,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(product_id, filter_option_id)
);

CREATE INDEX IF NOT EXISTS idx_filter_options_group ON filter_options(group_id);
CREATE INDEX IF NOT EXISTS idx_filter_options_active ON filter_options(is_active) WHERE is_active = true;
CREATE INDEX IF NOT EXISTS idx_product_filter_values_product ON product_filter_values(product_id);
CREATE INDEX IF NOT EXISTS idx_product_filter_values_option ON product_filter_values(filter_option_id);

-- AI ADVISOR ANALYTICS TABLE
CREATE TABLE IF NOT EXISTS ai_recommendation_logs (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  query           TEXT NOT NULL DEFAULT '',
  goal            TEXT,
  budget          DOUBLE PRECISION,
  results_count   INTEGER NOT NULL DEFAULT 0,
  top_product_id  TEXT,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_ai_logs_created ON ai_recommendation_logs(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_ai_logs_query ON ai_recommendation_logs(query);

-- SEED DATA
INSERT INTO filter_groups (id, name, description, display_order) VALUES
  ('goals', 'Goal', 'Fitness and health goals', 1),
  ('dietary', 'Dietary', 'Dietary preferences', 2),
  ('certifications', 'Certifications', 'Quality certifications and seals', 3),
  ('ai-settings', 'AI Settings', 'AI recommendation engine configuration', 999)
ON CONFLICT (id) DO NOTHING;

INSERT INTO filter_options (group_id, value, label, display_order) VALUES
  ('goals', 'muscle-building', 'Muscle Building', 1),
  ('goals', 'weight-gain', 'Weight Gain', 2),
  ('goals', 'fat-loss', 'Fat Loss', 3),
  ('goals', 'recovery', 'Recovery', 4),
  ('goals', 'strength', 'Strength', 5),
  ('goals', 'endurance', 'Endurance', 6),
  ('goals', 'energy', 'Energy', 7),
  ('goals', 'focus', 'Focus', 8),
  ('goals', 'immunity', 'Immunity', 9),

  ('dietary', 'vegetarian', 'Vegetarian', 1),
  ('dietary', 'vegan', 'Vegan', 2),
  ('dietary', 'gluten-free', 'Gluten Free', 3),
  ('dietary', 'sugar-free', 'Sugar Free', 4),
  ('dietary', 'keto-friendly', 'Keto Friendly', 5),
  ('certifications', 'informed-choice', 'Informed Choice', 1),
  ('certifications', 'banned-substance-tested', 'Banned Substance Tested', 2),
  ('certifications', 'labdoor-certified', 'Labdoor Certified', 3),
  ('certifications', 'trustified', 'Trustified', 4),
  ('certifications', 'heavy-metal-tested', 'Heavy Metal Tested', 5),
  ('certifications', 'usp-verified', 'USP Verified', 6),
  ('certifications', 'nsf-certified', 'NSF Certified', 7),
  ('certifications', 'gmp-certified', 'GMP Certified', 8),
  ('certifications', 'iso-certified', 'ISO Certified', 9),
  ('certifications', 'fssai-approved', 'Fssai Approved', 10),
  ('ai-settings', 'ai-weights', '{"weights":{"goalMatch":31,"budgetFit":25,"ratingScore":19,"dietaryMatch":13,"proteinScore":12},"maxRecommendations":10}')
ON CONFLICT (group_id, value) DO NOTHING;