-- Migration: Enhanced Formex materials with dimensions, variants, images
-- Adds support for multiple dimension formats and color variants per product

-- Add new columns to formex_materials
ALTER TABLE formex_materials ADD COLUMN category TEXT;
ALTER TABLE formex_materials ADD COLUMN image_url TEXT;
ALTER TABLE formex_materials ADD COLUMN price_chf REAL;
ALTER TABLE formex_materials ADD COLUMN active INTEGER DEFAULT 1;

-- Dimension variants (multiple dimensions per material)
CREATE TABLE IF NOT EXISTS formex_dimensions (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  material_id     INTEGER NOT NULL REFERENCES formex_materials(id) ON DELETE CASCADE,
  width_mm        INTEGER NOT NULL,
  height_mm       INTEGER NOT NULL,
  thickness_mm    INTEGER NOT NULL,
  is_default      INTEGER DEFAULT 0
);

-- Color/decor variants (linked products)
CREATE TABLE IF NOT EXISTS formex_variants (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  material_id     INTEGER NOT NULL REFERENCES formex_materials(id) ON DELETE CASCADE,
  variant_code    TEXT NOT NULL,
  variant_name    TEXT NOT NULL,
  variant_url     TEXT,
  image_url       TEXT
);

-- Create indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_formex_dimensions_material ON formex_dimensions(material_id);
CREATE INDEX IF NOT EXISTS idx_formex_variants_material ON formex_variants(material_id);
CREATE INDEX IF NOT EXISTS idx_formex_materials_active ON formex_materials(active);
CREATE INDEX IF NOT EXISTS idx_formex_materials_collection ON formex_materials(collection);