CREATE TABLE IF NOT EXISTS users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,email VARCHAR(190) UNIQUE NOT NULL,password_hash VARCHAR(255) NOT NULL,first_name VARCHAR(100),last_name VARCHAR(100),role ENUM('customer','admin') DEFAULT 'customer',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS categories (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL,slug VARCHAR(140) UNIQUE NOT NULL,description TEXT,image VARCHAR(255),sort_order INT DEFAULT 0);
CREATE TABLE IF NOT EXISTS products (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,category_id INT UNSIGNED NULL,name VARCHAR(190) NOT NULL,slug VARCHAR(210) UNIQUE NOT NULL,short_description VARCHAR(500),description TEXT,materials VARCHAR(255),care TEXT,weight_grams INT DEFAULT 0,status ENUM('draft','active','archived') DEFAULT 'active',featured TINYINT(1) DEFAULT 0,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(category_id) REFERENCES categories(id) ON DELETE SET NULL);
CREATE TABLE IF NOT EXISTS product_images (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,product_id BIGINT UNSIGNED NOT NULL,path VARCHAR(255) NOT NULL,alt_text VARCHAR(190),sort_order INT DEFAULT 0,FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS product_variants (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,product_id BIGINT UNSIGNED NOT NULL,sku VARCHAR(100) UNIQUE NOT NULL,size VARCHAR(40),option_name VARCHAR(100),price_cents INT UNSIGNED NOT NULL,stock INT NOT NULL DEFAULT 0,dimensions VARCHAR(150),active TINYINT(1) DEFAULT 1,FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS addresses (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,type ENUM('shipping','billing') DEFAULT 'shipping',line1 VARCHAR(190),line2 VARCHAR(190),postal_code VARCHAR(20),city VARCHAR(120),country CHAR(2) DEFAULT 'FR',phone VARCHAR(30),FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS orders (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,order_number VARCHAR(32) UNIQUE NOT NULL,user_id BIGINT UNSIGNED NULL,email VARCHAR(190) NOT NULL,status ENUM('pending_payment','paid','to_make','making','ready','shipped','completed','cancelled','refunded') DEFAULT 'pending_payment',subtotal_cents INT NOT NULL,shipping_cents INT NOT NULL,total_cents INT NOT NULL,shipping_method VARCHAR(60),relay_id VARCHAR(30),relay_name VARCHAR(190),relay_address TEXT,stripe_session_id VARCHAR(255),stripe_payment_intent VARCHAR(255),tracking_number VARCHAR(120),customer_note TEXT,shipping_json JSON,billing_json JSON,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL);
CREATE TABLE IF NOT EXISTS order_items (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,order_id BIGINT UNSIGNED NOT NULL,product_id BIGINT UNSIGNED,variant_id BIGINT UNSIGNED,product_name VARCHAR(190) NOT NULL,variant_label VARCHAR(190),sku VARCHAR(100),unit_price_cents INT NOT NULL,qty INT NOT NULL,total_cents INT NOT NULL,FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS settings (`key` VARCHAR(120) PRIMARY KEY,`value` TEXT);
