SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS warehouses(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,code VARCHAR(40) NOT NULL,name VARCHAR(120) NOT NULL,address_line VARCHAR(255),city VARCHAR(100),region VARCHAR(100),country_code CHAR(2) DEFAULT 'MA',manager_id BIGINT UNSIGNED,phone VARCHAR(40),email VARCHAR(190),status VARCHAR(20) DEFAULT 'active',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,UNIQUE(company_id,code),FOREIGN KEY(company_id) REFERENCES companies(id),FOREIGN KEY(manager_id) REFERENCES users(id) ON DELETE SET NULL,CHECK(status IN('active','inactive','archived'))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS stock_settings(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL UNIQUE,allow_negative_stock TINYINT(1) DEFAULT 0,valuation_method VARCHAR(10) DEFAULT 'CMP',updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,FOREIGN KEY(company_id) REFERENCES companies(id),CHECK(valuation_method IN('CMP','FIFO','LIFO'))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS product_stock_policies(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,product_id BIGINT UNSIGNED NOT NULL,warehouse_id BIGINT UNSIGNED NOT NULL,minimum_quantity DECIMAL(19,6) DEFAULT 0,maximum_quantity DECIMAL(19,6),UNIQUE(product_id,warehouse_id),FOREIGN KEY(company_id) REFERENCES companies(id),FOREIGN KEY(product_id) REFERENCES products(id),FOREIGN KEY(warehouse_id) REFERENCES warehouses(id),CHECK(minimum_quantity>=0 AND (maximum_quantity IS NULL OR maximum_quantity>=minimum_quantity))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS stock_movements(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,product_id BIGINT UNSIGNED NOT NULL,variant_id BIGINT UNSIGNED,warehouse_id BIGINT UNSIGNED NOT NULL,movement_type VARCHAR(30) NOT NULL,quantity_delta DECIMAL(19,6) NOT NULL,stock_before DECIMAL(19,6) NOT NULL,stock_after DECIMAL(19,6) NOT NULL,unit_cost DECIMAL(19,4),average_cost_after DECIMAL(19,4),document_reference VARCHAR(100),source_type VARCHAR(40),source_id BIGINT UNSIGNED,transfer_group CHAR(36),supplier_id BIGINT UNSIGNED,movement_date DATETIME NOT NULL,user_id BIGINT UNSIGNED NOT NULL,reason VARCHAR(500) NOT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(company_id) REFERENCES companies(id),FOREIGN KEY(product_id) REFERENCES products(id),FOREIGN KEY(variant_id) REFERENCES product_variants(id),FOREIGN KEY(warehouse_id) REFERENCES warehouses(id),FOREIGN KEY(supplier_id) REFERENCES third_parties(id),FOREIGN KEY(user_id) REFERENCES users(id),CHECK(quantity_delta<>0),INDEX idx_stock_balance(company_id,product_id,variant_id,warehouse_id,movement_date),INDEX idx_stock_history(company_id,movement_type,movement_date),INDEX idx_stock_transfer(transfer_group)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS stock_reservations(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,product_id BIGINT UNSIGNED NOT NULL,variant_id BIGINT UNSIGNED,warehouse_id BIGINT UNSIGNED NOT NULL,quantity DECIMAL(19,6) NOT NULL,source_type VARCHAR(40) NOT NULL,source_id BIGINT UNSIGNED NOT NULL,status VARCHAR(20) DEFAULT 'active',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,released_at DATETIME,FOREIGN KEY(company_id) REFERENCES companies(id),FOREIGN KEY(product_id) REFERENCES products(id),FOREIGN KEY(variant_id) REFERENCES product_variants(id),FOREIGN KEY(warehouse_id) REFERENCES warehouses(id),CHECK(quantity>0),CHECK(status IN('active','consumed','released','cancelled')),INDEX idx_reservation_balance(product_id,variant_id,warehouse_id,status)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS inventories(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,number VARCHAR(40) NOT NULL,warehouse_id BIGINT UNSIGNED NOT NULL,category_id BIGINT UNSIGNED,inventory_date DATE NOT NULL,status VARCHAR(20) DEFAULT 'draft',notes TEXT,created_by BIGINT UNSIGNED NOT NULL,validated_by BIGINT UNSIGNED,validated_at DATETIME,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,UNIQUE(company_id,number),FOREIGN KEY(company_id) REFERENCES companies(id),FOREIGN KEY(warehouse_id) REFERENCES warehouses(id),FOREIGN KEY(category_id) REFERENCES product_categories(id),FOREIGN KEY(created_by) REFERENCES users(id),FOREIGN KEY(validated_by) REFERENCES users(id),CHECK(status IN('draft','validated','cancelled'))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS inventory_lines(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,inventory_id BIGINT UNSIGNED NOT NULL,product_id BIGINT UNSIGNED NOT NULL,variant_id BIGINT UNSIGNED,theoretical_quantity DECIMAL(19,6) NOT NULL,counted_quantity DECIMAL(19,6),difference_quantity DECIMAL(19,6),unit_cost DECIMAL(19,4),movement_id BIGINT UNSIGNED,UNIQUE(inventory_id,product_id,variant_id),FOREIGN KEY(inventory_id) REFERENCES inventories(id) ON DELETE CASCADE,FOREIGN KEY(product_id) REFERENCES products(id),FOREIGN KEY(variant_id) REFERENCES product_variants(id),FOREIGN KEY(movement_id) REFERENCES stock_movements(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS stock_alerts(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,product_id BIGINT UNSIGNED NOT NULL,variant_id BIGINT UNSIGNED,warehouse_id BIGINT UNSIGNED NOT NULL,alert_type VARCHAR(30) NOT NULL,message VARCHAR(255) NOT NULL,is_resolved TINYINT(1) DEFAULT 0,detected_at DATETIME DEFAULT CURRENT_TIMESTAMP,resolved_at DATETIME,UNIQUE(product_id,variant_id,warehouse_id,alert_type,is_resolved),FOREIGN KEY(company_id) REFERENCES companies(id),FOREIGN KEY(product_id) REFERENCES products(id),FOREIGN KEY(variant_id) REFERENCES product_variants(id),FOREIGN KEY(warehouse_id) REFERENCES warehouses(id),CHECK(alert_type IN('minimum','out_of_stock','negative','inactive_product'))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO stock_settings(company_id) VALUES(1);
INSERT IGNORE INTO permissions(module,action,name) VALUES('stock','create','Stock — Créer mouvement'),('stock','update','Stock — Ajuster'),('stock','inventory','Stock — Inventaire'),('stock','transfer','Stock — Transfert'),('stock','history','Stock — Historique'),('warehouse','view','Entrepôts — Afficher'),('warehouse','create','Entrepôts — Ajouter'),('warehouse','update','Entrepôts — Modifier'),('warehouse','archive','Entrepôts — Archiver');
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('stock','warehouse');