217 lines
7.5 KiB
SQL
217 lines
7.5 KiB
SQL
-- Configurateur armoires sur mesure — Schéma V1 (SQLite)
|
|
|
|
-- Paramètres globaux (forfait kit, plinthe, épaisseur panneaux, etc.)
|
|
CREATE TABLE IF NOT EXISTS settings (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT NOT NULL
|
|
);
|
|
|
|
-- Produits / gammes (ex. SIGNATURE)
|
|
CREATE TABLE IF NOT EXISTS products (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
code TEXT UNIQUE NOT NULL,
|
|
label TEXT NOT NULL,
|
|
active INTEGER DEFAULT 1,
|
|
created_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
-- Types de blocs (penderie, rayonnage, mixte vertical, mixte horizontal)
|
|
CREATE TABLE IF NOT EXISTS block_types (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
code TEXT UNIQUE NOT NULL,
|
|
label TEXT NOT NULL,
|
|
base_price_chf REAL NOT NULL,
|
|
width_min_mm INTEGER NOT NULL,
|
|
width_max_mm INTEGER NOT NULL,
|
|
active INTEGER DEFAULT 1
|
|
);
|
|
|
|
-- Formats de panneaux (par finition : dimensions + prix panneau entier)
|
|
CREATE TABLE IF NOT EXISTS panel_formats (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
width_mm INTEGER NOT NULL,
|
|
height_mm INTEGER NOT NULL,
|
|
price_chf REAL NOT NULL
|
|
);
|
|
|
|
-- Finitions visibles (stratifié décor, couleur, effet bois, etc.)
|
|
CREATE TABLE IF NOT EXISTS finishes (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
code TEXT UNIQUE NOT NULL,
|
|
label TEXT NOT NULL,
|
|
panel_format_id INTEGER NOT NULL REFERENCES panel_formats(id),
|
|
active INTEGER DEFAULT 1
|
|
);
|
|
|
|
-- Options de service (prise de mesure, montage)
|
|
CREATE TABLE IF NOT EXISTS service_options (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
code TEXT UNIQUE NOT NULL,
|
|
label TEXT NOT NULL,
|
|
price_chf REAL NOT NULL,
|
|
price_type TEXT NOT NULL CHECK (price_type IN ('fixed', 'per_hour', 'per_block')),
|
|
active INTEGER DEFAULT 1
|
|
);
|
|
|
|
-- Commandes / leads (config snapshot + coordonnées)
|
|
CREATE TABLE IF NOT EXISTS orders (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
config TEXT NOT NULL,
|
|
total_chf REAL,
|
|
email TEXT,
|
|
name TEXT,
|
|
phone TEXT,
|
|
notes TEXT,
|
|
created_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
-- Matériaux Formex (données scrapées)
|
|
CREATE TABLE IF NOT EXISTS formex_materials (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
formex_code TEXT UNIQUE NOT NULL,
|
|
name TEXT NOT NULL,
|
|
collection TEXT NOT NULL,
|
|
category TEXT,
|
|
width_mm INTEGER,
|
|
height_mm INTEGER,
|
|
thickness_mm INTEGER,
|
|
image_url TEXT,
|
|
formex_url TEXT,
|
|
price_chf REAL,
|
|
dominant_color TEXT,
|
|
active INTEGER DEFAULT 1,
|
|
last_synced_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
-- 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,
|
|
price_chf REAL,
|
|
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 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);
|
|
|
|
-- Journal de synchronisation
|
|
CREATE TABLE IF NOT EXISTS sync_logs (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
source TEXT NOT NULL,
|
|
status TEXT NOT NULL,
|
|
items_count INTEGER,
|
|
error TEXT,
|
|
created_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
-- Room configurations (AI-analyzed room layouts)
|
|
CREATE TABLE IF NOT EXISTS room_configs (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
order_id INTEGER REFERENCES orders(id),
|
|
image_path TEXT,
|
|
analysis_json TEXT NOT NULL,
|
|
created_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_room_configs_order ON room_configs(order_id);
|
|
|
|
-- 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);
|
|
|
|
-- Exports de plans/debitage enregistrés par commande
|
|
CREATE TABLE IF NOT EXISTS plan_exports (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
|
|
export_type TEXT NOT NULL,
|
|
file_name TEXT NOT NULL,
|
|
file_path TEXT NOT NULL,
|
|
mime_type TEXT,
|
|
created_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_plan_exports_order ON plan_exports(order_id);
|