SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS delivery_sequences(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,prefix VARCHAR(20) DEFAULT 'BL-',include_year TINYINT(1) DEFAULT 1,include_month TINYINT(1) DEFAULT 0,padding TINYINT UNSIGNED DEFAULT 6,next_value BIGINT UNSIGNED DEFAULT 1,current_year SMALLINT UNSIGNED,reset_annually TINYINT(1) DEFAULT 1,UNIQUE(company_id),FOREIGN KEY(company_id) REFERENCES companies(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS delivery_notes(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,number VARCHAR(50) NOT NULL,idempotency_key CHAR(36) NOT NULL,customer_order_id BIGINT UNSIGNED,customer_id BIGINT UNSIGNED NOT NULL,contact_id BIGINT UNSIGNED,shipping_address_id BIGINT UNSIGNED,warehouse_id BIGINT UNSIGNED NOT NULL,delivery_date DATE NOT NULL,delivery_time TIME,driver_id BIGINT UNSIGNED,vehicle VARCHAR(100),customer_reference VARCHAR(100),priority VARCHAR(20) DEFAULT 'normal',delivery_type VARCHAR(20) DEFAULT 'partial',internal_notes TEXT,visible_notes TEXT,status VARCHAR(30) DEFAULT 'draft',preparation_status VARCHAR(30) DEFAULT 'not_prepared',prepared_by BIGINT UNSIGNED,prepared_at DATETIME,preparation_comments TEXT,validated_by BIGINT UNSIGNED,validated_at DATETIME,delivered_at DATETIME,cancelled_at DATETIME,archived_at DATETIME,created_by BIGINT UNSIGNED NOT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,UNIQUE(company_id,number),UNIQUE(idempotency_key),INDEX idx_delivery_filters(company_id,status,warehouse_id,delivery_date),INDEX idx_delivery_order(customer_order_id,status),FOREIGN KEY(company_id)REFERENCES companies(id),FOREIGN KEY(customer_order_id)REFERENCES customer_orders(id),FOREIGN KEY(customer_id)REFERENCES third_parties(id),FOREIGN KEY(contact_id)REFERENCES third_party_contacts(id),FOREIGN KEY(shipping_address_id)REFERENCES third_party_addresses(id),FOREIGN KEY(warehouse_id)REFERENCES warehouses(id),FOREIGN KEY(driver_id)REFERENCES users(id),FOREIGN KEY(prepared_by)REFERENCES users(id),FOREIGN KEY(validated_by)REFERENCES users(id),FOREIGN KEY(created_by)REFERENCES users(id),CHECK(status IN('draft','prepared','validated','in_delivery','delivered','refused','partially_returned','cancelled','archived')),CHECK(preparation_status IN('not_prepared','in_preparation','partially_prepared','ready')),CHECK(delivery_type IN('partial','complete')))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS delivery_note_lines(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,delivery_note_id BIGINT UNSIGNED NOT NULL,customer_order_line_id BIGINT UNSIGNED,product_id BIGINT UNSIGNED NOT NULL,variant_id BIGINT UNSIGNED,packaging_id BIGINT UNSIGNED,reference_snapshot VARCHAR(100),designation_snapshot VARCHAR(255) NOT NULL,description_snapshot TEXT,unit_snapshot VARCHAR(50),ordered_quantity DECIMAL(19,6) DEFAULT 0,previously_delivered_quantity DECIMAL(19,6) DEFAULT 0,delivery_quantity DECIMAL(19,6) NOT NULL,prepared_quantity DECIMAL(19,6) DEFAULT 0,refused_quantity DECIMAL(19,6) DEFAULT 0,returned_quantity DECIMAL(19,6) DEFAULT 0,lot_serial VARCHAR(120),location_snapshot VARCHAR(120),observation VARCHAR(500),unit_price_snapshot DECIMAL(19,4),stock_movement_id BIGINT UNSIGNED,sort_order SMALLINT UNSIGNED NOT NULL,UNIQUE(delivery_note_id,sort_order),UNIQUE(stock_movement_id),FOREIGN KEY(delivery_note_id)REFERENCES delivery_notes(id)ON DELETE CASCADE,FOREIGN KEY(customer_order_line_id)REFERENCES customer_order_lines(id),FOREIGN KEY(product_id)REFERENCES products(id),FOREIGN KEY(variant_id)REFERENCES product_variants(id),FOREIGN KEY(packaging_id)REFERENCES product_packagings(id),FOREIGN KEY(stock_movement_id)REFERENCES stock_movements(id),CHECK(delivery_quantity>0))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS delivery_preparations(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,delivery_note_id BIGINT UNSIGNED NOT NULL,preparer_id BIGINT UNSIGNED NOT NULL,status VARCHAR(30) NOT NULL,comments TEXT,prepared_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(delivery_note_id)REFERENCES delivery_notes(id),FOREIGN KEY(preparer_id)REFERENCES users(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS delivery_preparation_lines(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,delivery_preparation_id BIGINT UNSIGNED NOT NULL,delivery_note_line_id BIGINT UNSIGNED NOT NULL,prepared_quantity DECIMAL(19,6) NOT NULL,location VARCHAR(120),checked TINYINT(1) DEFAULT 0,comment VARCHAR(500),FOREIGN KEY(delivery_preparation_id)REFERENCES delivery_preparations(id)ON DELETE CASCADE,FOREIGN KEY(delivery_note_line_id)REFERENCES delivery_note_lines(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS delivery_status_history(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,delivery_note_id BIGINT UNSIGNED NOT NULL,user_id BIGINT UNSIGNED,action VARCHAR(50) NOT NULL,old_status VARCHAR(30),new_status VARCHAR(30),old_values JSON,new_values JSON,comment TEXT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,INDEX(delivery_note_id,created_at),FOREIGN KEY(delivery_note_id)REFERENCES delivery_notes(id)ON DELETE CASCADE,FOREIGN KEY(user_id)REFERENCES users(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS delivery_signatures(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,delivery_note_id BIGINT UNSIGNED NOT NULL,recipient_name VARCHAR(190) NOT NULL,recipient_title VARCHAR(120),national_id VARCHAR(60),signature_path VARCHAR(255),received_status VARCHAR(30) DEFAULT 'accepted',comment TEXT,latitude DECIMAL(10,7),longitude DECIMAL(10,7),signed_at DATETIME NOT NULL,created_by BIGINT UNSIGNED NOT NULL,FOREIGN KEY(delivery_note_id)REFERENCES delivery_notes(id),FOREIGN KEY(created_by)REFERENCES users(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS delivery_proofs(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,delivery_note_id BIGINT UNSIGNED NOT NULL,proof_type VARCHAR(30) NOT NULL,original_name VARCHAR(255),stored_name VARCHAR(255),mime_type VARCHAR(120),comment TEXT,created_by BIGINT UNSIGNED NOT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(delivery_note_id)REFERENCES delivery_notes(id),FOREIGN KEY(created_by)REFERENCES users(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS delivery_refusals(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,delivery_note_id BIGINT UNSIGNED NOT NULL,reason VARCHAR(50) NOT NULL,comment TEXT,is_total TINYINT(1) DEFAULT 0,physical_return_confirmed TINYINT(1) DEFAULT 0,recorded_by BIGINT UNSIGNED NOT NULL,recorded_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(delivery_note_id)REFERENCES delivery_notes(id),FOREIGN KEY(recorded_by)REFERENCES users(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS delivery_refusal_lines(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,delivery_refusal_id BIGINT UNSIGNED NOT NULL,delivery_note_line_id BIGINT UNSIGNED NOT NULL,quantity DECIMAL(19,6) NOT NULL,return_movement_id BIGINT UNSIGNED,FOREIGN KEY(delivery_refusal_id)REFERENCES delivery_refusals(id)ON DELETE CASCADE,FOREIGN KEY(delivery_note_line_id)REFERENCES delivery_note_lines(id),FOREIGN KEY(return_movement_id)REFERENCES stock_movements(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS delivery_attachments(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,delivery_note_id BIGINT UNSIGNED 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(delivery_note_id)REFERENCES delivery_notes(id)ON DELETE CASCADE,FOREIGN KEY(uploaded_by)REFERENCES users(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS delivery_emails(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,delivery_note_id BIGINT UNSIGNED NOT NULL,recipient VARCHAR(190) NOT NULL,subject VARCHAR(255) NOT NULL,status VARCHAR(20) NOT NULL,error_message VARCHAR(500),sent_by BIGINT UNSIGNED NOT NULL,sent_at DATETIME,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(delivery_note_id)REFERENCES delivery_notes(id),FOREIGN KEY(sent_by)REFERENCES users(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO delivery_sequences(company_id,current_year)SELECT id,YEAR(CURDATE())FROM companies;
INSERT IGNORE INTO permissions(module,action,name)VALUES('deliveries','view','Livraisons — Afficher'),('deliveries','create','Livraisons — Créer'),('deliveries','create_manual','Livraisons — Création manuelle'),('deliveries','update','Livraisons — Modifier'),('deliveries','prepare','Livraisons — Préparer'),('deliveries','validate','Livraisons — Valider'),('deliveries','cancel','Livraisons — Annuler'),('deliveries','archive','Livraisons — Archiver'),('deliveries','print','Livraisons — Imprimer'),('deliveries','print_prices','Livraisons — Imprimer prix'),('deliveries','export','Livraisons — Exporter'),('deliveries','manage_signature','Livraisons — Signature'),('deliveries','manage_proof','Livraisons — Preuves'),('deliveries','record_refusal','Livraisons — Refus'),('deliveries','override_remaining_quantity','Livraisons — Dépasser le restant'),('deliveries','allow_negative_stock','Livraisons — Stock négatif'),('deliveries','manage_attachments','Livraisons — Pièces jointes');
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='deliveries';