SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS customer_order_sequences (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL,
 prefix VARCHAR(20) NOT NULL DEFAULT 'CMD-',
 include_year TINYINT(1) NOT NULL DEFAULT 1,
 include_month TINYINT(1) NOT NULL DEFAULT 0,
 padding TINYINT UNSIGNED NOT NULL DEFAULT 6,
 next_value BIGINT UNSIGNED NOT NULL DEFAULT 1,
 current_year SMALLINT UNSIGNED,
 UNIQUE(company_id), FOREIGN KEY(company_id) REFERENCES companies(id),
 CHECK(padding BETWEEN 3 AND 12)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS customer_orders (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 company_id BIGINT UNSIGNED NOT NULL, number VARCHAR(50) NOT NULL,
 quote_id BIGINT UNSIGNED, customer_id BIGINT UNSIGNED NOT NULL,
 contact_id BIGINT UNSIGNED, billing_address_id BIGINT UNSIGNED, shipping_address_id BIGINT UNSIGNED,
 salesperson_id BIGINT UNSIGNED NOT NULL, warehouse_id BIGINT UNSIGNED NOT NULL,
 order_date DATE NOT NULL, requested_date DATE, priority VARCHAR(20) NOT NULL DEFAULT 'normal',
 customer_reference VARCHAR(100), payment_terms VARCHAR(120), payment_method VARCHAR(80),
 internal_notes TEXT, customer_notes TEXT, currency_code CHAR(3) NOT NULL DEFAULT 'MAD',
 status VARCHAR(30) NOT NULL DEFAULT 'draft', preparation_status VARCHAR(30) NOT NULL DEFAULT 'not_prepared',
 delivery_status VARCHAR(30) NOT NULL DEFAULT 'not_delivered', preparation_manager_id BIGINT UNSIGNED,
 preparation_date DATETIME, preparation_comments TEXT,
 subtotal DECIMAL(19,4) NOT NULL DEFAULT 0, discount_total DECIMAL(19,4) NOT NULL DEFAULT 0,
 tax_total DECIMAL(19,4) NOT NULL DEFAULT 0, total DECIMAL(19,4) NOT NULL DEFAULT 0,
 customer_snapshot JSON, contact_snapshot JSON, billing_snapshot JSON, shipping_snapshot JSON,
 validated_by BIGINT UNSIGNED, validated_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), INDEX idx_order_filters(company_id,status,warehouse_id,order_date),
 INDEX idx_order_customer(customer_id,created_at), INDEX idx_order_quote(quote_id),
 FOREIGN KEY(company_id) REFERENCES companies(id), FOREIGN KEY(quote_id) REFERENCES quotes(id),
 FOREIGN KEY(customer_id) REFERENCES third_parties(id), FOREIGN KEY(contact_id) REFERENCES third_party_contacts(id),
 FOREIGN KEY(billing_address_id) REFERENCES third_party_addresses(id), FOREIGN KEY(shipping_address_id) REFERENCES third_party_addresses(id),
 FOREIGN KEY(salesperson_id) REFERENCES users(id), FOREIGN KEY(warehouse_id) REFERENCES warehouses(id),
 FOREIGN KEY(preparation_manager_id) REFERENCES users(id), FOREIGN KEY(validated_by) REFERENCES users(id),
 FOREIGN KEY(created_by) REFERENCES users(id),
 CHECK(priority IN('low','normal','high','urgent')),
 CHECK(status IN('draft','validated','confirmed','in_preparation','ready','partially_delivered','delivered','closed','cancelled','archived')),
 CHECK(preparation_status IN('not_prepared','in_preparation','partially_prepared','ready')),
 CHECK(delivery_status IN('not_delivered','partially_delivered','delivered'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS customer_order_lines (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, customer_order_id BIGINT UNSIGNED NOT NULL,
 line_type VARCHAR(20) NOT NULL, sort_order SMALLINT UNSIGNED NOT NULL,
 product_id BIGINT UNSIGNED, 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), quantity DECIMAL(19,6) NOT NULL DEFAULT 0,
 unit_price DECIMAL(19,4) NOT NULL DEFAULT 0, discount_rate DECIMAL(9,6) NOT NULL DEFAULT 0,
 discount_total DECIMAL(19,4) NOT NULL DEFAULT 0, tax_rate DECIMAL(9,6) NOT NULL DEFAULT 0,
 line_subtotal DECIMAL(19,4) NOT NULL DEFAULT 0, line_tax DECIMAL(19,4) NOT NULL DEFAULT 0,
 line_total DECIMAL(19,4) NOT NULL DEFAULT 0, prepared_quantity DECIMAL(19,6) NOT NULL DEFAULT 0,
 delivered_quantity DECIMAL(19,6) NOT NULL DEFAULT 0,
 UNIQUE(customer_order_id,sort_order), INDEX idx_order_line_product(product_id,variant_id),
 FOREIGN KEY(customer_order_id) REFERENCES customer_orders(id) ON DELETE CASCADE,
 FOREIGN KEY(product_id) REFERENCES products(id), FOREIGN KEY(variant_id) REFERENCES product_variants(id),
 FOREIGN KEY(packaging_id) REFERENCES product_packagings(id),
 CHECK(line_type IN('product','service','free','title','comment'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS order_status_history (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, customer_order_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 idx_order_history(customer_order_id,created_at),
 FOREIGN KEY(customer_order_id) REFERENCES customer_orders(id) ON DELETE CASCADE,
 FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS order_attachments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, customer_order_id BIGINT UNSIGNED NOT NULL,
 document_type VARCHAR(40) NOT NULL DEFAULT 'other', 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(customer_order_id) REFERENCES customer_orders(id) ON DELETE CASCADE,
 FOREIGN KEY(uploaded_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE stock_reservations ADD COLUMN IF NOT EXISTS source_line_id BIGINT UNSIGNED NULL AFTER source_id;
CREATE INDEX IF NOT EXISTS idx_reservation_source ON stock_reservations(source_type,source_id,status);

INSERT IGNORE INTO customer_order_sequences(company_id,current_year) SELECT id,YEAR(CURDATE()) FROM companies;
INSERT IGNORE INTO permissions(module,action,name) VALUES
('orders','view','Commandes — Afficher'),('orders','create','Commandes — Créer'),('orders','update','Commandes — Modifier'),
('orders','validate','Commandes — Valider'),('orders','cancel','Commandes — Annuler'),('orders','archive','Commandes — Archiver'),
('orders','export','Commandes — Exporter'),('orders','print','Commandes — Imprimer'),
('orders','reserve_stock','Commandes — Réserver le stock'),('orders','prepare','Commandes — Préparer'),
('orders','manage_attachments','Commandes — 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='orders';