-- Migration: Modules, hardware, and variants for V2 configurator

-- Catalogue de modules standards
CREATE TABLE IF NOT EXISTS modules (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  code            TEXT UNIQUE NOT NULL,
  name            TEXT NOT NULL,
  category        TEXT NOT NULL, -- 'penderie', 'rayonnage', 'tiroirs', 'mixte'
  width_mm        INTEGER NOT NULL,
  height_mm       INTEGER,
  depth_mm        INTEGER,
  shelves_count   INTEGER DEFAULT 0,
  drawers_count   INTEGER DEFAULT 0,
  has_rod         INTEGER DEFAULT 0, -- penderie
  customizable    INTEGER DEFAULT 1,
  base_price_chf  REAL NOT NULL,
  image_url       TEXT,
  active          INTEGER DEFAULT 1,
  created_at      TEXT DEFAULT (datetime('now'))
);

-- Quincaillerie Blum
CREATE TABLE IF NOT EXISTS blum_hardware (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  code            TEXT UNIQUE NOT NULL,
  name            TEXT NOT NULL,
  type            TEXT NOT NULL, -- 'drawer_system', 'hinge', 'lift_system', 'runner'
  variant         TEXT, -- 'legrabox', 'tandembox', 'clip_top', etc.
  price_chf       REAL NOT NULL,
  specs_json      TEXT,
  image_url       TEXT,
  active          INTEGER DEFAULT 1
);

-- Variantes poignées
CREATE TABLE IF NOT EXISTS handle_variants (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  code            TEXT UNIQUE NOT NULL,
  name            TEXT NOT NULL,
  style           TEXT, -- 'moderne', 'classique', 'minimaliste', etc.
  length_mm       INTEGER,
  price_chf       REAL NOT NULL,
  image_url       TEXT,
  active          INTEGER DEFAULT 1
);

-- Variantes charnières
CREATE TABLE IF NOT EXISTS hinge_variants (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  code            TEXT UNIQUE NOT NULL,
  name            TEXT NOT NULL,
  type            TEXT NOT NULL, -- 'vissee', 'plantee'
  brand           TEXT DEFAULT 'Blum',
  price_chf       REAL NOT NULL,
  active          INTEGER DEFAULT 1
);

-- Configuration couleurs par élément (pour sauvegarder les configs)
CREATE TABLE IF NOT EXISTS element_colors (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  order_id        INTEGER REFERENCES orders(id) ON DELETE CASCADE,
  element_type    TEXT NOT NULL, -- 'plintheBas', 'portes', 'cotes', etc.
  finish_id       INTEGER NOT NULL,
  finish_label    TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_modules_category ON modules(category);
CREATE INDEX IF NOT EXISTS idx_modules_active ON modules(active);
CREATE INDEX IF NOT EXISTS idx_blum_hardware_type ON blum_hardware(type);
CREATE INDEX IF NOT EXISTS idx_handle_variants_style ON handle_variants(style);
CREATE INDEX IF NOT EXISTS idx_hinge_variants_type ON hinge_variants(type);
CREATE INDEX IF NOT EXISTS idx_element_colors_order ON element_colors(order_id);