SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS product_sequences(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,item_type VARCHAR(20) NOT NULL,prefix VARCHAR(20) NOT NULL,padding TINYINT UNSIGNED NOT NULL DEFAULT 6,next_value BIGINT UNSIGNED NOT NULL DEFAULT 1,UNIQUE(company_id,item_type),FOREIGN KEY(company_id) REFERENCES companies(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS product_categories(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,parent_id BIGINT UNSIGNED NULL,name VARCHAR(120) NOT NULL,description TEXT NULL,is_active TINYINT(1) NOT NULL DEFAULT 1,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,UNIQUE(company_id,parent_id,name),FOREIGN KEY(company_id) REFERENCES companies(id),FOREIGN KEY(parent_id) REFERENCES product_categories(id) ON DELETE RESTRICT) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS brands(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,name VARCHAR(120) NOT NULL,logo_path VARCHAR(255),description TEXT,is_active TINYINT(1) DEFAULT 1,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,UNIQUE(company_id,name),FOREIGN KEY(company_id) REFERENCES companies(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS units(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,name VARCHAR(100) NOT NULL,symbol VARCHAR(20) NOT NULL,unit_type VARCHAR(40) NOT NULL,decimal_places TINYINT UNSIGNED DEFAULT 0,is_active TINYINT(1) DEFAULT 1,UNIQUE(company_id,symbol),FOREIGN KEY(company_id) REFERENCES companies(id),CHECK(decimal_places<=6)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS tax_rates(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,name VARCHAR(80) NOT NULL,rate DECIMAL(9,6) NOT NULL,is_default TINYINT(1) DEFAULT 0,is_active TINYINT(1) DEFAULT 1,UNIQUE(company_id,name),FOREIGN KEY(company_id) REFERENCES companies(id),CHECK(rate BETWEEN 0 AND 100)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS products(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,reference VARCHAR(80) NOT NULL,barcode VARCHAR(80),item_type VARCHAR(20) NOT NULL,name VARCHAR(190) NOT NULL,short_name VARCHAR(100),short_description VARCHAR(500),description TEXT,category_id BIGINT UNSIGNED,brand_id BIGINT UNSIGNED,unit_id BIGINT UNSIGNED NOT NULL,tax_rate_id BIGINT UNSIGNED,image_path VARCHAR(255),purchase_price DECIMAL(19,4) DEFAULT 0,sale_price DECIMAL(19,4) DEFAULT 0,min_sale_price DECIMAL(19,4) DEFAULT 0,max_discount DECIMAL(9,6) DEFAULT 0,currency_code CHAR(3) DEFAULT 'MAD',price_updated_at DATETIME,status VARCHAR(20) DEFAULT 'active',is_sellable TINYINT(1) DEFAULT 1,is_purchasable TINYINT(1) DEFAULT 1,created_by BIGINT UNSIGNED NOT NULL,updated_by BIGINT UNSIGNED,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,UNIQUE(company_id,reference),UNIQUE(company_id,barcode),FOREIGN KEY(company_id) REFERENCES companies(id),FOREIGN KEY(category_id) REFERENCES product_categories(id),FOREIGN KEY(brand_id) REFERENCES brands(id),FOREIGN KEY(unit_id) REFERENCES units(id),FOREIGN KEY(tax_rate_id) REFERENCES tax_rates(id),FOREIGN KEY(created_by) REFERENCES users(id),CHECK(item_type IN('stockable','non_stockable','service')),CHECK(purchase_price>=0 AND sale_price>=0 AND min_sale_price>=0),CHECK(status IN('active','inactive','archived'))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS price_levels(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,name VARCHAR(100) NOT NULL,discount_rate DECIMAL(9,6),is_active TINYINT(1) DEFAULT 1,UNIQUE(company_id,name),FOREIGN KEY(company_id) REFERENCES companies(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS product_prices(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,product_id BIGINT UNSIGNED NOT NULL,price_level_id BIGINT UNSIGNED NOT NULL,fixed_price DECIMAL(19,4),discount_rate DECIMAL(9,6),minimum_quantity DECIMAL(19,6) DEFAULT 1,valid_from DATE,valid_to DATE,is_active TINYINT(1) DEFAULT 1,UNIQUE(product_id,price_level_id),FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE,FOREIGN KEY(price_level_id) REFERENCES price_levels(id),CHECK(fixed_price IS NULL OR fixed_price>=0)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS quantity_prices(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,product_id BIGINT UNSIGNED NOT NULL,price_level_id BIGINT UNSIGNED,minimum_quantity DECIMAL(19,6) NOT NULL,maximum_quantity DECIMAL(19,6),unit_price DECIMAL(19,4) NOT NULL,valid_from DATE,valid_to DATE,is_active TINYINT(1) DEFAULT 1,FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE,FOREIGN KEY(price_level_id) REFERENCES price_levels(id),CHECK(minimum_quantity>0 AND unit_price>=0 AND (maximum_quantity IS NULL OR maximum_quantity>=minimum_quantity))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS product_packagings(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,product_id BIGINT UNSIGNED NOT NULL,name VARCHAR(100) NOT NULL,unit_id BIGINT UNSIGNED NOT NULL,contained_quantity DECIMAL(19,6) NOT NULL,barcode VARCHAR(80),purchase_price DECIMAL(19,4),sale_price DECIMAL(19,4),is_default TINYINT(1) DEFAULT 0,is_active TINYINT(1) DEFAULT 1,UNIQUE(product_id,name),UNIQUE(barcode),FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE,FOREIGN KEY(unit_id) REFERENCES units(id),CHECK(contained_quantity>0)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS attributes(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,name VARCHAR(100) NOT NULL,UNIQUE(company_id,name),FOREIGN KEY(company_id) REFERENCES companies(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS attribute_values(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,attribute_id BIGINT UNSIGNED NOT NULL,value VARCHAR(100) NOT NULL,UNIQUE(attribute_id,value),FOREIGN KEY(attribute_id) REFERENCES attributes(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS product_variants(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,product_id BIGINT UNSIGNED NOT NULL,reference VARCHAR(80) NOT NULL,barcode VARCHAR(80),combination_key VARCHAR(500) NOT NULL,purchase_price DECIMAL(19,4),sale_price DECIMAL(19,4),image_path VARCHAR(255),is_active TINYINT(1) DEFAULT 1,UNIQUE(product_id,combination_key),UNIQUE(reference),UNIQUE(barcode),FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS variant_values(variant_id BIGINT UNSIGNED NOT NULL,attribute_value_id BIGINT UNSIGNED NOT NULL,PRIMARY KEY(variant_id,attribute_value_id),FOREIGN KEY(variant_id) REFERENCES product_variants(id) ON DELETE CASCADE,FOREIGN KEY(attribute_value_id) REFERENCES attribute_values(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS product_components(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,product_id BIGINT UNSIGNED NOT NULL,component_product_id BIGINT UNSIGNED NOT NULL,quantity DECIMAL(19,6) NOT NULL,UNIQUE(product_id,component_product_id),FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE,FOREIGN KEY(component_product_id) REFERENCES products(id),CHECK(product_id<>component_product_id AND quantity>0)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS product_suppliers(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,product_id BIGINT UNSIGNED NOT NULL,supplier_id BIGINT UNSIGNED NOT NULL,supplier_reference VARCHAR(100),purchase_price DECIMAL(19,4) DEFAULT 0,minimum_order_quantity DECIMAL(19,6) DEFAULT 1,delivery_days SMALLINT UNSIGNED DEFAULT 0,is_primary TINYINT(1) DEFAULT 0,currency_code CHAR(3) DEFAULT 'MAD',notes TEXT,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,UNIQUE(product_id,supplier_id),FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE,FOREIGN KEY(supplier_id) REFERENCES third_parties(id),CHECK(purchase_price>=0 AND minimum_order_quantity>0)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS product_files(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,product_id BIGINT UNSIGNED NOT NULL,file_kind VARCHAR(20) NOT NULL,document_type VARCHAR(40),original_name VARCHAR(255) NOT NULL,stored_name VARCHAR(255) NOT NULL,mime_type VARCHAR(120) NOT NULL,size_bytes BIGINT UNSIGNED NOT NULL,sha256 CHAR(64) NOT NULL,uploaded_by BIGINT UNSIGNED NOT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,UNIQUE(stored_name),FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE,FOREIGN KEY(uploaded_by) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS product_history(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,product_id BIGINT UNSIGNED NOT NULL,user_id BIGINT UNSIGNED,action VARCHAR(80) NOT NULL,old_values JSON,new_values JSON,description VARCHAR(500),created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE,FOREIGN KEY(user_id) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO product_sequences(company_id,item_type,prefix,padding) VALUES(1,'product','PROD-',6),(1,'service','SERV-',6);
INSERT IGNORE INTO units(company_id,name,symbol,unit_type,decimal_places) VALUES(1,'Pièce','pce','count',0),(1,'Kilogramme','kg','weight',3),(1,'Litre','L','volume',3),(1,'Mètre','m','length',3),(1,'Heure','h','time',2),(1,'Jour','j','time',2),(1,'Carton','ctn','packaging',0);
INSERT IGNORE INTO tax_rates(company_id,name,rate,is_default) VALUES(1,'TVA 0%',0,0),(1,'TVA 7%',7,0),(1,'TVA 10%',10,0),(1,'TVA 14%',14,0),(1,'TVA 20%',20,1);
INSERT IGNORE INTO price_levels(company_id,name,discount_rate) VALUES(1,'Prix détail',0),(1,'Prix professionnel',5),(1,'Prix grossiste',10),(1,'Prix revendeur',15),(1,'Prix VIP',20);
INSERT IGNORE INTO permissions(module,action,name) VALUES('products','update','Produits — Modifier'),('products','archive','Produits — Archiver'),('products','import','Produits — Importer'),('products','view_purchase_price','Produits — Voir prix achat'),('products','view_margin','Produits — Voir marge'),('products','manage_prices','Produits — Gérer prix'),('products','manage_variants','Produits — Gérer variantes'),('products','manage_packaging','Produits — Gérer conditionnements'),('products','manage_suppliers','Produits — Gérer fournisseurs'),('products','manage_documents','Produits — Gérer documents'),('categories','update','Catégories — Modifier'),('categories','disable','Catégories — Désactiver'),('brands','view','Marques — Afficher'),('brands','create','Marques — Ajouter'),('brands','update','Marques — Modifier'),('brands','disable','Marques — Désactiver'),('units','view','Unités — Afficher'),('units','manage','Unités — Gérer'),('tax_rates','view','TVA — Afficher'),('tax_rates','manage','TVA — Gérer');
INSERT IGNORE INTO role_permissions(role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code='administrator' AND p.module IN('products','categories','brands','units','tax_rates');