-- V004 - contracts, approvals, instrument lifecycle and logistics milestones
ALTER TABLE operations ADD COLUMN IF NOT EXISTS contract_status VARCHAR(30) NOT NULL DEFAULT 'not_started' AFTER compliance_status;
ALTER TABLE operations ADD COLUMN IF NOT EXISTS logistics_status VARCHAR(30) NOT NULL DEFAULT 'not_started' AFTER contract_status;
ALTER TABLE bank_instruments ADD COLUMN IF NOT EXISTS custody_status VARCHAR(40) NOT NULL DEFAULT 'pending' AFTER status;
ALTER TABLE bank_instruments ADD COLUMN IF NOT EXISTS current_holder VARCHAR(190) NULL AFTER custody_status;
ALTER TABLE bank_instruments ADD COLUMN IF NOT EXISTS available_value DECIMAL(20,2) NULL AFTER face_value;

CREATE TABLE IF NOT EXISTS operation_contracts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 operation_document_id BIGINT UNSIGNED NULL,
 contract_type VARCHAR(80) NOT NULL,
 contract_number VARCHAR(100) NULL,
 title VARCHAR(190) NOT NULL,
 effective_date DATE NULL,
 expiry_date DATE NULL,
 governing_law VARCHAR(120) NULL,
 jurisdiction VARCHAR(120) NULL,
 total_value DECIMAL(20,2) NOT NULL DEFAULT 0,
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 status ENUM('draft','internal_review','client_review','signature_pending','active','suspended','completed','terminated') NOT NULL DEFAULT 'draft',
 signed_at DATETIME 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(operation_document_id) REFERENCES operation_documents(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(operation_id,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS contract_obligations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 contract_id BIGINT UNSIGNED NOT NULL,
 responsible_party ENUM('company','client','supplier','bank','broker','carrier','other') NOT NULL,
 obligation_type VARCHAR(100) NOT NULL,
 description VARCHAR(255) NOT NULL,
 due_date DATE NULL,
 amount DECIMAL(20,2) NULL,
 currency CHAR(3) NULL,
 status ENUM('pending','in_progress','fulfilled','overdue','waived','cancelled') NOT NULL DEFAULT 'pending',
 evidence_path VARCHAR(255) NULL,
 completed_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(contract_id) REFERENCES operation_contracts(id) ON DELETE CASCADE,
 INDEX(contract_id,status,due_date)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS approval_requests (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 entity_type VARCHAR(80) NOT NULL,
 entity_id BIGINT UNSIGNED NULL,
 approval_type VARCHAR(100) NOT NULL,
 requested_by BIGINT UNSIGNED NULL,
 assigned_role VARCHAR(40) NULL,
 assigned_user_id BIGINT UNSIGNED NULL,
 status ENUM('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
 request_notes TEXT NULL,
 decision_notes TEXT NULL,
 requested_at DATETIME NOT NULL,
 decided_at DATETIME NULL,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(requested_by) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(assigned_user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(status,assigned_role), INDEX(operation_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS bank_instrument_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 bank_instrument_id BIGINT UNSIGNED NOT NULL,
 operation_id BIGINT UNSIGNED NULL,
 event_type VARCHAR(100) NOT NULL,
 event_date DATETIME NOT NULL,
 from_party VARCHAR(190) NULL,
 to_party VARCHAR(190) NULL,
 bank_name VARCHAR(190) NULL,
 swift_reference VARCHAR(120) NULL,
 amount DECIMAL(20,2) NULL,
 currency CHAR(3) NULL,
 status VARCHAR(40) NOT NULL DEFAULT 'recorded',
 notes TEXT NULL,
 attachment_path VARCHAR(255) NULL,
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 FOREIGN KEY(bank_instrument_id) REFERENCES bank_instruments(id) ON DELETE CASCADE,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(bank_instrument_id,event_date), INDEX(swift_reference)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS shipments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 shipment_number VARCHAR(100) NOT NULL,
 transport_mode ENUM('sea','air','road','rail','multimodal') NOT NULL DEFAULT 'sea',
 carrier VARCHAR(190) NULL,
 vessel_or_vehicle VARCHAR(190) NULL,
 origin VARCHAR(190) NULL,
 destination VARCHAR(190) NULL,
 load_port VARCHAR(190) NULL,
 discharge_port VARCHAR(190) NULL,
 quantity DECIMAL(20,3) NULL,
 unit VARCHAR(30) NULL,
 etd DATETIME NULL,
 eta DATETIME NULL,
 actual_departure DATETIME NULL,
 actual_arrival DATETIME NULL,
 tracking_reference VARCHAR(120) NULL,
 status ENUM('planned','booking','loading','in_transit','customs','delivered','cancelled') NOT NULL DEFAULT 'planned',
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 UNIQUE KEY uq_shipment_number(shipment_number),
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 INDEX(operation_id,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS shipment_milestones (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 shipment_id BIGINT UNSIGNED NOT NULL,
 milestone_type VARCHAR(100) NOT NULL,
 location VARCHAR(190) NULL,
 planned_at DATETIME NULL,
 completed_at DATETIME NULL,
 status ENUM('pending','completed','delayed','cancelled') NOT NULL DEFAULT 'pending',
 notes TEXT NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(shipment_id) REFERENCES shipments(id) ON DELETE CASCADE,
 INDEX(shipment_id,status,planned_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS operation_messages (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 sender_user_id BIGINT UNSIGNED NULL,
 recipient_role VARCHAR(40) NULL,
 message TEXT NOT NULL,
 visibility ENUM('internal','client','broker','all') NOT NULL DEFAULT 'internal',
 read_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(sender_user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(operation_id,created_at)
) ENGINE=InnoDB;