-- V007 - sales execution, allocations, invoices and trade documentation
CREATE TABLE IF NOT EXISTS sales_orders (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 client_id BIGINT UNSIGNED NOT NULL,
 order_number VARCHAR(80) NOT NULL UNIQUE,
 product_id BIGINT UNSIGNED NOT NULL,
 quantity DECIMAL(20,3) NOT NULL,
 unit VARCHAR(30) NOT NULL,
 unit_price DECIMAL(20,4) NOT NULL,
 total_amount DECIMAL(20,2) NOT NULL,
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 incoterm VARCHAR(20) NULL,
 delivery_location VARCHAR(190) NULL,
 expected_delivery_date DATE NULL,
 payment_terms TEXT NULL,
 status ENUM('draft','pending_approval','approved','contracted','partially_allocated','allocated','in_fulfillment','fulfilled','cancelled') NOT NULL DEFAULT 'draft',
 created_by BIGINT UNSIGNED NULL,
 approved_by BIGINT UNSIGNED NULL,
 approved_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE RESTRICT,
 FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE RESTRICT,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(approved_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(operation_id,status), INDEX(client_id,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS inventory_allocations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 sales_order_id BIGINT UNSIGNED NOT NULL,
 inventory_lot_id BIGINT UNSIGNED NOT NULL,
 quantity DECIMAL(20,3) NOT NULL,
 unit VARCHAR(30) NOT NULL,
 status ENUM('reserved','confirmed','released','shipped','delivered','cancelled') NOT NULL DEFAULT 'reserved',
 reserved_until DATETIME NULL,
 notes TEXT NULL,
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(sales_order_id) REFERENCES sales_orders(id) ON DELETE CASCADE,
 FOREIGN KEY(inventory_lot_id) REFERENCES inventory_lots(id) ON DELETE RESTRICT,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(sales_order_id,status), INDEX(inventory_lot_id,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS commercial_invoices (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 sales_order_id BIGINT UNSIGNED NULL,
 invoice_number VARCHAR(80) NOT NULL UNIQUE,
 invoice_type ENUM('proforma','commercial','credit_note','debit_note') NOT NULL DEFAULT 'proforma',
 issue_date DATE NOT NULL,
 due_date DATE NULL,
 subtotal DECIMAL(20,2) NOT NULL,
 taxes DECIMAL(20,2) NOT NULL DEFAULT 0,
 fees DECIMAL(20,2) NOT NULL DEFAULT 0,
 total_amount DECIMAL(20,2) NOT NULL,
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 status ENUM('draft','issued','sent','partially_paid','paid','overdue','cancelled') NOT NULL DEFAULT 'draft',
 payment_reference VARCHAR(190) NULL,
 notes TEXT NULL,
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(sales_order_id) REFERENCES sales_orders(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(operation_id,status), INDEX(due_date,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS trade_document_checklists (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 document_code VARCHAR(80) NOT NULL,
 document_name VARCHAR(190) NOT NULL,
 responsible_party ENUM('company','client','supplier','bank','broker','carrier','inspector','other') NOT NULL DEFAULT 'company',
 required_stage VARCHAR(80) NULL,
 due_date DATE NULL,
 status ENUM('not_started','requested','received','under_review','approved','rejected','waived') NOT NULL DEFAULT 'not_started',
 operation_document_id BIGINT UNSIGNED NULL,
 notes TEXT NULL,
 reviewed_by BIGINT UNSIGNED NULL,
 reviewed_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(operation_document_id) REFERENCES operation_documents(id) ON DELETE SET NULL,
 FOREIGN KEY(reviewed_by) REFERENCES users(id) ON DELETE SET NULL,
 UNIQUE KEY uq_operation_document_code(operation_id,document_code),
 INDEX(operation_id,status), INDEX(due_date,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS shipment_allocations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 shipment_id BIGINT UNSIGNED NOT NULL,
 inventory_allocation_id BIGINT UNSIGNED NOT NULL,
 quantity DECIMAL(20,3) NOT NULL,
 unit VARCHAR(30) NOT NULL,
 created_at DATETIME NOT NULL,
 UNIQUE KEY uq_shipment_allocation(shipment_id,inventory_allocation_id),
 FOREIGN KEY(shipment_id) REFERENCES shipments(id) ON DELETE CASCADE,
 FOREIGN KEY(inventory_allocation_id) REFERENCES inventory_allocations(id) ON DELETE RESTRICT
) ENGINE=InnoDB;