-- V008 - customs, cargo insurance, shipment costs and claims
CREATE TABLE IF NOT EXISTS customs_declarations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 shipment_id BIGINT UNSIGNED NULL,
 declaration_number VARCHAR(120) NOT NULL,
 declaration_type ENUM('export','import','transit','temporary','other') NOT NULL DEFAULT 'export',
 customs_authority VARCHAR(190) NULL,
 country_code CHAR(2) NULL,
 customs_broker_id BIGINT UNSIGNED NULL,
 filing_date DATE NULL,
 clearance_date DATE NULL,
 declared_value DECIMAL(20,2) NULL,
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 duties_amount DECIMAL(20,2) NOT NULL DEFAULT 0,
 taxes_amount DECIMAL(20,2) NOT NULL DEFAULT 0,
 status ENUM('draft','filed','under_review','inspection','cleared','held','rejected','cancelled') NOT NULL DEFAULT 'draft',
 document_id BIGINT UNSIGNED 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(shipment_id) REFERENCES shipments(id) ON DELETE SET NULL,
 FOREIGN KEY(customs_broker_id) REFERENCES counterparties(id) ON DELETE SET NULL,
 FOREIGN KEY(document_id) REFERENCES operation_documents(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 UNIQUE KEY uq_customs_declaration_number(declaration_number),
 INDEX(operation_id,status), INDEX(shipment_id,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS cargo_insurance_policies (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 shipment_id BIGINT UNSIGNED NULL,
 insurer_id BIGINT UNSIGNED NULL,
 policy_number VARCHAR(120) NOT NULL,
 coverage_type ENUM('all_risks','named_perils','war_risk','political_risk','credit_risk','other') NOT NULL DEFAULT 'all_risks',
 insured_party VARCHAR(190) NOT NULL,
 beneficiary VARCHAR(190) NULL,
 insured_value DECIMAL(20,2) NOT NULL,
 premium_amount DECIMAL(20,2) NOT NULL DEFAULT 0,
 deductible_amount DECIMAL(20,2) NOT NULL DEFAULT 0,
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 effective_date DATE NOT NULL,
 expiry_date DATE NULL,
 status ENUM('quoted','pending','active','expired','cancelled','claimed') NOT NULL DEFAULT 'pending',
 document_id BIGINT UNSIGNED NULL,
 terms_summary 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(shipment_id) REFERENCES shipments(id) ON DELETE SET NULL,
 FOREIGN KEY(insurer_id) REFERENCES counterparties(id) ON DELETE SET NULL,
 FOREIGN KEY(document_id) REFERENCES operation_documents(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 UNIQUE KEY uq_policy_number(policy_number),
 INDEX(operation_id,status), INDEX(shipment_id,status), INDEX(expiry_date,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS shipment_costs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 shipment_id BIGINT UNSIGNED NULL,
 counterparty_id BIGINT UNSIGNED NULL,
 cost_type ENUM('freight','port','storage','demurrage','detention','inspection','customs','insurance','handling','tax','other') NOT NULL,
 description VARCHAR(255) NOT NULL,
 planned_amount DECIMAL(20,2) NOT NULL DEFAULT 0,
 actual_amount DECIMAL(20,2) NOT NULL DEFAULT 0,
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 due_date DATE NULL,
 paid_date DATE NULL,
 status ENUM('estimated','approved','invoiced','partially_paid','paid','disputed','cancelled') NOT NULL DEFAULT 'estimated',
 invoice_reference VARCHAR(120) 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(shipment_id) REFERENCES shipments(id) ON DELETE SET NULL,
 FOREIGN KEY(counterparty_id) REFERENCES counterparties(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(operation_id,status), INDEX(shipment_id,cost_type), INDEX(due_date,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS trade_claims (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 shipment_id BIGINT UNSIGNED NULL,
 insurance_policy_id BIGINT UNSIGNED NULL,
 claim_number VARCHAR(120) NOT NULL,
 claim_type ENUM('shortage','quality','damage','delay','non_delivery','documentary','payment','insurance','other') NOT NULL,
 claimant_party VARCHAR(190) NOT NULL,
 respondent_party VARCHAR(190) NULL,
 incident_date DATE NULL,
 notification_date DATE NOT NULL,
 claimed_amount DECIMAL(20,2) NOT NULL DEFAULT 0,
 approved_amount DECIMAL(20,2) NOT NULL DEFAULT 0,
 recovered_amount DECIMAL(20,2) NOT NULL DEFAULT 0,
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 status ENUM('draft','notified','under_review','evidence_requested','negotiation','approved','partially_settled','settled','rejected','closed') NOT NULL DEFAULT 'draft',
 description TEXT NOT NULL,
 resolution TEXT NULL,
 owner_user_id BIGINT UNSIGNED NULL,
 resolved_at DATETIME 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(shipment_id) REFERENCES shipments(id) ON DELETE SET NULL,
 FOREIGN KEY(insurance_policy_id) REFERENCES cargo_insurance_policies(id) ON DELETE SET NULL,
 FOREIGN KEY(owner_user_id) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 UNIQUE KEY uq_claim_number(claim_number),
 INDEX(operation_id,status), INDEX(shipment_id,status)
) ENGINE=InnoDB;