

CREATE TABLE IF NOT EXISTS bonds (
  id VARCHAR(32) PRIMARY KEY,
  member_id VARCHAR(32) NOT NULL,
  member_name VARCHAR(255) NOT NULL,
  bond_type ENUM('SUBMITTED','ISSUED_BY_GSCX') NOT NULL,
  compensation DECIMAL(15,2) NOT NULL,
  currency VARCHAR(16) NOT NULL DEFAULT 'USD',
  date DATE NOT NULL,
  contract_url VARCHAR(500) DEFAULT '#',
  status ENUM('ACTIVE','EXPIRED') NOT NULL DEFAULT 'ACTIVE',
  FOREIGN KEY (member_id) REFERENCES members(id)
);

CREATE TABLE IF NOT EXISTS products (
  id VARCHAR(32) PRIMARY KEY,
  category ENUM('Machinery','Equipment','Hardware','Software','Commodities','Merchandize') NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  price DECIMAL(15,2) NOT NULL,
  currency VARCHAR(16) NOT NULL DEFAULT 'USD',
  quantity INT NOT NULL,
  exporter_id VARCHAR(32) NOT NULL,
  license_required TINYINT(1) NOT NULL DEFAULT 0,
  license_proof_url VARCHAR(500) DEFAULT NULL,
  ofac_status ENUM('CHECKED','NOT_CHECKED') NOT NULL DEFAULT 'NOT_CHECKED',
  is_for_sale TINYINT(1) NOT NULL DEFAULT 1,
  rating DECIMAL(2,1) DEFAULT 0.0,
  rating_count INT NOT NULL DEFAULT 0,
  FOREIGN KEY (exporter_id) REFERENCES members(id)
);

ALTER TABLE products ADD COLUMN IF NOT EXISTS hs_code VARCHAR(20) DEFAULT NULL;
ALTER TABLE products ADD COLUMN IF NOT EXISTS country_of_origin VARCHAR(100) DEFAULT NULL;
ALTER TABLE products ADD COLUMN IF NOT EXISTS unit_of_measure VARCHAR(50) DEFAULT NULL;
ALTER TABLE products ADD COLUMN IF NOT EXISTS incoterms ENUM('EXW','FCA','CPT','CIP','DAP','DPU','DDP','FAS','FOB','CFR','CIF') DEFAULT NULL;
ALTER TABLE products ADD COLUMN IF NOT EXISTS weight_kg DECIMAL(10,3) DEFAULT NULL;
ALTER TABLE products ADD COLUMN IF NOT EXISTS dimensions VARCHAR(100) DEFAULT NULL;
ALTER TABLE products ADD COLUMN IF NOT EXISTS port_of_loading VARCHAR(100) DEFAULT NULL;
ALTER TABLE products ADD COLUMN IF NOT EXISTS lead_time_days INT DEFAULT NULL;
ALTER TABLE products ADD COLUMN IF NOT EXISTS brand VARCHAR(100) DEFAULT NULL;
ALTER TABLE products ADD COLUMN IF NOT EXISTS model_number VARCHAR(100) DEFAULT NULL;
ALTER TABLE products ADD COLUMN IF NOT EXISTS certifications VARCHAR(500) DEFAULT NULL;
ALTER TABLE products ADD COLUMN IF NOT EXISTS payment_terms VARCHAR(100) DEFAULT NULL;
ALTER TABLE products ADD COLUMN IF NOT EXISTS moq INT DEFAULT NULL;
ALTER TABLE products ADD COLUMN IF NOT EXISTS eccn VARCHAR(20) DEFAULT NULL;
ALTER TABLE products ADD COLUMN IF NOT EXISTS dual_use TINYINT(1) NOT NULL DEFAULT 0;
ALTER TABLE products ADD COLUMN IF NOT EXISTS restricted_destinations TEXT DEFAULT NULL;

CREATE TABLE IF NOT EXISTS remittances (
  id VARCHAR(32) PRIMARY KEY,
  remitter_id VARCHAR(32) NOT NULL,
  beneficiary_id VARCHAR(32) NOT NULL,
  amount DECIMAL(15,2) NOT NULL,
  currency VARCHAR(16) NOT NULL DEFAULT 'USD',
  purpose ENUM('GOODS','SERVICES','INVOICE','SALARY','FAMILY','OTHER') NOT NULL DEFAULT 'OTHER',
  reference VARCHAR(255) DEFAULT NULL,
  funding_source ENUM('BANK_ACCOUNT','CARD','WALLET','CRYPTO') NOT NULL DEFAULT 'BANK_ACCOUNT',
  funding_details VARCHAR(500) DEFAULT NULL,
  source_of_funds VARCHAR(255) DEFAULT NULL,
  sanctions_confirmed TINYINT(1) NOT NULL DEFAULT 0,
  terms_accepted TINYINT(1) NOT NULL DEFAULT 0,
  ofac_status ENUM('CHECKED','NOT_CHECKED') NOT NULL DEFAULT 'NOT_CHECKED',
  status ENUM('DRAFT','PENDING','PROCESSING','COMPLETED','REJECTED','CANCELLED') NOT NULL DEFAULT 'DRAFT',
  submitted_at DATETIME NOT NULL,
  FOREIGN KEY (remitter_id) REFERENCES members(id),
  FOREIGN KEY (beneficiary_id) REFERENCES beneficiaries(id)
);

CREATE TABLE IF NOT EXISTS import_orders (
  id VARCHAR(32) PRIMARY KEY,
  importer_id VARCHAR(32) NOT NULL,

  -- Business / IOR identity
  company_name VARCHAR(255) DEFAULT NULL,
  tax_id VARCHAR(100) DEFAULT NULL,
  ior_designation VARCHAR(255) DEFAULT NULL,
  import_address TEXT DEFAULT NULL,

  -- Commercial details
  product_description TEXT NOT NULL,
  sku VARCHAR(100) DEFAULT NULL,
  quantity INT NOT NULL,
  unit_price DECIMAL(15,2) NOT NULL,
  total_value DECIMAL(15,2) NOT NULL,
  currency VARCHAR(16) NOT NULL DEFAULT 'USD',

  -- Customs & classification
  hs_code VARCHAR(20) DEFAULT NULL,
  country_of_origin VARCHAR(100) DEFAULT NULL,
  declared_value DECIMAL(15,2) DEFAULT NULL,
  product_category VARCHAR(100) DEFAULT NULL,

  -- Shipping & logistics
  delivery_address TEXT DEFAULT NULL,
  shipping_method ENUM('AIR','SEA','COURIER','RAIL','ROAD') NOT NULL DEFAULT 'SEA',
  incoterms ENUM('EXW','FCA','CPT','CIP','DAP','DPU','DDP','FAS','FOB','CFR','CIF') DEFAULT NULL,
  port_of_entry VARCHAR(100) DEFAULT NULL,
  estimated_arrival DATE DEFAULT NULL,

  -- Payment & duties
  duties_payer ENUM('IMPORTER','SELLER') NOT NULL DEFAULT 'IMPORTER',
  payment_method ENUM('BANK_ACCOUNT','CARD','WALLET','LETTER_OF_CREDIT') NOT NULL DEFAULT 'BANK_ACCOUNT',
  payment_currency VARCHAR(16) NOT NULL DEFAULT 'USD',

  -- Regulatory declarations
  import_license_required TINYINT(1) NOT NULL DEFAULT 0,
  import_license_number VARCHAR(100) DEFAULT NULL,
  fda_required TINYINT(1) NOT NULL DEFAULT 0,
  fcc_required TINYINT(1) NOT NULL DEFAULT 0,
  usda_required TINYINT(1) NOT NULL DEFAULT 0,
  restricted_goods_confirmed TINYINT(1) NOT NULL DEFAULT 0,
  end_use_declaration TEXT DEFAULT NULL,
  sanctions_confirmed TINYINT(1) NOT NULL DEFAULT 0,
  terms_accepted TINYINT(1) NOT NULL DEFAULT 0,

  -- Status
  ofac_status ENUM('CHECKED','NOT_CHECKED') NOT NULL DEFAULT 'NOT_CHECKED',
  status ENUM('DRAFT','SUBMITTED','CUSTOMS_REVIEW','CLEARED','REJECTED','CANCELLED') NOT NULL DEFAULT 'DRAFT',
  submitted_at DATETIME NOT NULL,
  FOREIGN KEY (importer_id) REFERENCES members(id)
);

CREATE TABLE IF NOT EXISTS export_profiles (
  id VARCHAR(32) PRIMARY KEY,
  exporter_id VARCHAR(32) NOT NULL,
  legal_name VARCHAR(255) NOT NULL,
  registration_number VARCHAR(100) DEFAULT NULL,
  tax_id VARCHAR(100) DEFAULT NULL,
  business_address TEXT DEFAULT NULL,
  owners_directors TEXT DEFAULT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  UNIQUE KEY uq_export_profiles_exporter (exporter_id),
  FOREIGN KEY (exporter_id) REFERENCES members(id)
);

CREATE TABLE IF NOT EXISTS export_shipments (
  id VARCHAR(32) PRIMARY KEY,
  exporter_id VARCHAR(32) NOT NULL,

  -- Commercial details
  product_name VARCHAR(255) NOT NULL,
  product_description TEXT DEFAULT NULL,
  sku VARCHAR(100) DEFAULT NULL,
  quantity INT NOT NULL,
  unit_price DECIMAL(15,2) NOT NULL,
  total_value DECIMAL(15,2) NOT NULL,
  currency VARCHAR(16) NOT NULL DEFAULT 'USD',

  -- Export classification & compliance
  hs_code VARCHAR(20) DEFAULT NULL,
  country_of_origin VARCHAR(100) DEFAULT NULL,
  eccn VARCHAR(20) DEFAULT NULL,
  dual_use TINYINT(1) NOT NULL DEFAULT 0,
  product_restrictions TEXT DEFAULT NULL,

  -- Shipping & fulfillment
  warehouse_address TEXT DEFAULT NULL,
  shipping_method ENUM('AIR','SEA','COURIER','RAIL','ROAD') NOT NULL DEFAULT 'SEA',
  carrier VARCHAR(100) DEFAULT NULL,
  port_of_loading VARCHAR(100) DEFAULT NULL,
  estimated_ship_date DATE DEFAULT NULL,
  incoterms ENUM('EXW','FCA','CPT','CIP','DAP','DPU','DDP','FAS','FOB','CFR','CIF') DEFAULT NULL,

  -- Regulatory
  export_license_required TINYINT(1) NOT NULL DEFAULT 0,
  export_license_number VARCHAR(100) DEFAULT NULL,
  sanctions_confirmed TINYINT(1) NOT NULL DEFAULT 0,
  terms_accepted TINYINT(1) NOT NULL DEFAULT 0,

  -- Status
  ofac_status ENUM('CHECKED','NOT_CHECKED') NOT NULL DEFAULT 'NOT_CHECKED',
  status ENUM('DRAFT','SUBMITTED','CUSTOMS_REVIEW','SHIPPED','DELIVERED','REJECTED','CANCELLED') NOT NULL DEFAULT 'SUBMITTED',
  submitted_at DATETIME NOT NULL,
  FOREIGN KEY (exporter_id) REFERENCES members(id)
);

CREATE TABLE IF NOT EXISTS transport_shipments (
  id VARCHAR(32) PRIMARY KEY,
  transport_id VARCHAR(32) NOT NULL,

  -- Link to an existing export shipment or import order (optional)
  reference_type ENUM('EXPORT','IMPORT','STANDALONE') NOT NULL DEFAULT 'STANDALONE',
  reference_id VARCHAR(32) DEFAULT NULL,

  -- Carrier & tracking
  tracking_number VARCHAR(100) NOT NULL,
  carrier_shipment_id VARCHAR(100) DEFAULT NULL,
  carrier VARCHAR(100) NOT NULL,
  shipping_mode ENUM('AIR','SEA','COURIER','RAIL','ROAD') NOT NULL DEFAULT 'SEA',

  -- Transport documents
  label_url VARCHAR(500) DEFAULT NULL,
  awb_number VARCHAR(100) DEFAULT NULL,
  bol_number VARCHAR(100) DEFAULT NULL,
  manifest_url VARCHAR(500) DEFAULT NULL,

  -- Addresses
  origin_address TEXT DEFAULT NULL,
  destination_address TEXT DEFAULT NULL,
  port_of_loading VARCHAR(100) DEFAULT NULL,
  port_of_destination VARCHAR(100) DEFAULT NULL,

  -- Cargo
  cargo_description TEXT DEFAULT NULL,
  weight_kg DECIMAL(10,3) DEFAULT NULL,
  volume_cbm DECIMAL(10,3) DEFAULT NULL,
  piece_count INT DEFAULT NULL,
  declared_value DECIMAL(15,2) DEFAULT NULL,
  currency VARCHAR(16) NOT NULL DEFAULT 'USD',

  -- Customs
  customs_status ENUM('PENDING','CLEARED','HELD','NOT_REQUIRED') NOT NULL DEFAULT 'PENDING',
  customs_notes TEXT DEFAULT NULL,

  -- Timeline
  estimated_pickup DATE DEFAULT NULL,
  estimated_delivery DATE DEFAULT NULL,
  actual_pickup DATETIME DEFAULT NULL,
  actual_delivery DATETIME DEFAULT NULL,

  -- Proof of Delivery
  pod_recipient VARCHAR(255) DEFAULT NULL,
  pod_signature_url VARCHAR(500) DEFAULT NULL,
  pod_photo_url VARCHAR(500) DEFAULT NULL,

  -- Status
  status ENUM('CREATED','PICKED_UP','IN_TRANSIT','AT_CUSTOMS','OUT_FOR_DELIVERY','DELIVERED','EXCEPTION','CANCELLED') NOT NULL DEFAULT 'CREATED',
  submitted_at DATETIME NOT NULL,
  FOREIGN KEY (transport_id) REFERENCES members(id)
);

CREATE TABLE IF NOT EXISTS shipment_events (
  id VARCHAR(32) PRIMARY KEY,
  shipment_id VARCHAR(32) NOT NULL,
  event_type ENUM('LABEL_CREATED','PICKED_UP','IN_TRANSIT','AT_CUSTOMS','CUSTOMS_CLEARED','CUSTOMS_HELD','OUT_FOR_DELIVERY','DELIVERED','EXCEPTION','NOTE') NOT NULL,
  location VARCHAR(255) DEFAULT NULL,
  description TEXT DEFAULT NULL,
  occurred_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (shipment_id) REFERENCES transport_shipments(id)
);

CREATE TABLE IF NOT EXISTS shipment_charges (
  id VARCHAR(32) PRIMARY KEY,
  shipment_id VARCHAR(32) NOT NULL,
  charge_type ENUM('DUTIES','BROKERAGE','SURCHARGE','DELIVERY_FEE','OTHER') NOT NULL,
  description VARCHAR(255) DEFAULT NULL,
  amount DECIMAL(15,2) NOT NULL,
  currency VARCHAR(16) NOT NULL DEFAULT 'USD',
  payer ENUM('EXPORTER','IMPORTER','PLATFORM') NOT NULL DEFAULT 'IMPORTER',
  status ENUM('PENDING','PAID','WAIVED') NOT NULL DEFAULT 'PENDING',
  created_at DATETIME NOT NULL,
  FOREIGN KEY (shipment_id) REFERENCES transport_shipments(id)
);

CREATE TABLE IF NOT EXISTS insurance_quotes (
  id VARCHAR(32) PRIMARY KEY,
  insurer_id VARCHAR(32) NOT NULL,

  -- Link to the shipment being insured
  reference_type ENUM('EXPORT','IMPORT','TRANSPORT','STANDALONE') NOT NULL DEFAULT 'STANDALONE',
  reference_id VARCHAR(32) DEFAULT NULL,

  -- What is being insured
  cargo_description TEXT DEFAULT NULL,
  transport_mode ENUM('AIR','SEA','COURIER','RAIL','ROAD') NOT NULL DEFAULT 'SEA',
  route_origin VARCHAR(255) DEFAULT NULL,
  route_destination VARCHAR(255) DEFAULT NULL,
  insured_value DECIMAL(15,2) NOT NULL,
  currency VARCHAR(16) NOT NULL DEFAULT 'USD',

  -- Coverage terms
  coverage_type ENUM('ALL_RISK','NAMED_PERILS','TOTAL_LOSS') NOT NULL DEFAULT 'ALL_RISK',
  coverage_amount DECIMAL(15,2) NOT NULL,
  premium DECIMAL(15,2) NOT NULL,
  exclusions TEXT DEFAULT NULL,
  valid_until DATE DEFAULT NULL,

  -- Status
  status ENUM('PENDING','QUOTED','ACCEPTED','REJECTED','EXPIRED') NOT NULL DEFAULT 'QUOTED',
  created_at DATETIME NOT NULL,
  FOREIGN KEY (insurer_id) REFERENCES members(id)
);

CREATE TABLE IF NOT EXISTS insurance_policies (
  id VARCHAR(32) PRIMARY KEY,
  insurer_id VARCHAR(32) NOT NULL,
  quote_id VARCHAR(32) DEFAULT NULL,

  -- Policy identity
  policy_number VARCHAR(100) NOT NULL,
  certificate_url VARCHAR(500) DEFAULT NULL,

  -- Linked shipment
  reference_type ENUM('EXPORT','IMPORT','TRANSPORT','STANDALONE') NOT NULL DEFAULT 'STANDALONE',
  reference_id VARCHAR(32) DEFAULT NULL,

  -- Coverage
  coverage_type ENUM('ALL_RISK','NAMED_PERILS','TOTAL_LOSS') NOT NULL DEFAULT 'ALL_RISK',
  coverage_amount DECIMAL(15,2) NOT NULL,
  premium DECIMAL(15,2) NOT NULL,
  currency VARCHAR(16) NOT NULL DEFAULT 'USD',
  exclusions TEXT DEFAULT NULL,
  covered_from DATE DEFAULT NULL,
  covered_until DATE DEFAULT NULL,

  -- Shipment snapshot
  insured_value DECIMAL(15,2) NOT NULL,
  cargo_description TEXT DEFAULT NULL,
  transport_mode ENUM('AIR','SEA','COURIER','RAIL','ROAD') NOT NULL DEFAULT 'SEA',
  route_origin VARCHAR(255) DEFAULT NULL,
  route_destination VARCHAR(255) DEFAULT NULL,

  -- Risk monitoring
  risk_level ENUM('LOW','MEDIUM','HIGH','FLAGGED') NOT NULL DEFAULT 'LOW',
  risk_notes TEXT DEFAULT NULL,

  -- Status
  status ENUM('DRAFT','ACTIVE','EXPIRED','CANCELLED','CLAIMED') NOT NULL DEFAULT 'ACTIVE',
  issued_at DATETIME NOT NULL,
  FOREIGN KEY (insurer_id) REFERENCES members(id)
);

CREATE TABLE IF NOT EXISTS insurance_claims (
  id VARCHAR(32) PRIMARY KEY,
  insurer_id VARCHAR(32) NOT NULL,
  policy_id VARCHAR(32) NOT NULL,

  -- Claim details
  claim_number VARCHAR(100) DEFAULT NULL,
  incident_type ENUM('DAMAGE','LOSS','THEFT','DELAY','OTHER') NOT NULL DEFAULT 'DAMAGE',
  description TEXT NOT NULL,
  evidence_urls TEXT DEFAULT NULL,

  -- Financials
  invoice_value DECIMAL(15,2) NOT NULL,
  claimed_amount DECIMAL(15,2) NOT NULL,
  approved_amount DECIMAL(15,2) DEFAULT NULL,
  currency VARCHAR(16) NOT NULL DEFAULT 'USD',

  -- Payout
  payout_recipient ENUM('IMPORTER','EXPORTER') NOT NULL DEFAULT 'IMPORTER',
  payout_method VARCHAR(100) DEFAULT NULL,
  payout_status ENUM('PENDING','PROCESSING','PAID','DENIED') NOT NULL DEFAULT 'PENDING',

  -- Status
  status ENUM('SUBMITTED','UNDER_REVIEW','APPROVED','DENIED','PAID') NOT NULL DEFAULT 'SUBMITTED',
  submitted_at DATETIME NOT NULL,
  resolved_at DATETIME DEFAULT NULL,
  FOREIGN KEY (insurer_id) REFERENCES members(id),
  FOREIGN KEY (policy_id) REFERENCES insurance_policies(id)
);

CREATE TABLE IF NOT EXISTS bank_accounts (
  id VARCHAR(32) PRIMARY KEY,
  bank_id VARCHAR(32) NOT NULL,
  member_id VARCHAR(32) NOT NULL,

  -- Account identity
  account_number VARCHAR(100) NOT NULL,
  virtual_account_id VARCHAR(100) DEFAULT NULL,
  account_type ENUM('OPERATING','CUSTODIAL','ESCROW','TRUST') NOT NULL DEFAULT 'OPERATING',
  currency VARCHAR(16) NOT NULL DEFAULT 'USD',
  label VARCHAR(100) DEFAULT NULL,

  -- Balances
  available_balance DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  ledger_balance DECIMAL(18,2) NOT NULL DEFAULT 0.00,

  -- Status
  status ENUM('ACTIVE','FROZEN','CLOSED') NOT NULL DEFAULT 'ACTIVE',
  created_at DATETIME NOT NULL,
  FOREIGN KEY (bank_id) REFERENCES members(id),
  FOREIGN KEY (member_id) REFERENCES members(id)
);

CREATE TABLE IF NOT EXISTS bank_transactions (
  id VARCHAR(32) PRIMARY KEY,
  bank_id VARCHAR(32) NOT NULL,
  account_id VARCHAR(32) NOT NULL,

  -- Movement
  transaction_type ENUM('CREDIT','DEBIT') NOT NULL,
  payment_rail ENUM('SWIFT','ACH','SEPA','WIRE','INTERNAL','CRYPTO') NOT NULL DEFAULT 'WIRE',
  amount DECIMAL(18,2) NOT NULL,
  currency VARCHAR(16) NOT NULL DEFAULT 'USD',

  -- Counterparty
  counterparty_name VARCHAR(255) DEFAULT NULL,
  counterparty_account VARCHAR(100) DEFAULT NULL,
  counterparty_bank VARCHAR(255) DEFAULT NULL,
  counterparty_swift VARCHAR(20) DEFAULT NULL,

  -- Rail-specific identifiers
  reference VARCHAR(255) DEFAULT NULL,
  swift_message_type VARCHAR(10) DEFAULT NULL,
  confirmation_number VARCHAR(100) DEFAULT NULL,
  end_to_end_id VARCHAR(100) DEFAULT NULL,

  -- Optional link to platform order
  linked_type ENUM('REMITTANCE','IMPORT','EXPORT','PAYOUT','OTHER') DEFAULT NULL,
  linked_id VARCHAR(32) DEFAULT NULL,

  -- Compliance hold
  compliance_hold TINYINT(1) NOT NULL DEFAULT 0,

  -- Status & timestamps
  status ENUM('PENDING','SETTLED','FAILED','HELD','REVERSED') NOT NULL DEFAULT 'PENDING',
  created_at DATETIME NOT NULL,
  settled_at DATETIME DEFAULT NULL,
  FOREIGN KEY (bank_id) REFERENCES members(id),
  FOREIGN KEY (account_id) REFERENCES bank_accounts(id)
);

CREATE TABLE IF NOT EXISTS fx_quotes (
  id VARCHAR(32) PRIMARY KEY,
  bank_id VARCHAR(32) NOT NULL,

  -- Pair & amounts
  from_currency VARCHAR(16) NOT NULL,
  to_currency VARCHAR(16) NOT NULL,
  from_amount DECIMAL(18,2) NOT NULL,
  to_amount DECIMAL(18,2) NOT NULL,
  exchange_rate DECIMAL(18,8) NOT NULL,
  spread_bps DECIMAL(8,2) DEFAULT NULL,
  fees DECIMAL(15,2) NOT NULL DEFAULT 0.00,

  -- Optional link to a transaction
  linked_transaction_id VARCHAR(32) DEFAULT NULL,

  -- Status
  status ENUM('QUOTED','EXECUTED','EXPIRED','CANCELLED') NOT NULL DEFAULT 'QUOTED',
  valid_until DATETIME DEFAULT NULL,
  executed_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (bank_id) REFERENCES members(id)
);


-- ═════════════════════════════════════════════════════════════════════
-- INSPECTION MODULE (Inspector Center)
-- ═════════════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS inspection_reports (
  id VARCHAR(32) PRIMARY KEY,
  inspector_id VARCHAR(32) NOT NULL,
  reference_type ENUM('EXPORT','IMPORT','TRANSPORT') NOT NULL,
  reference_id VARCHAR(32) NOT NULL,
  inspection_date DATETIME NOT NULL,
  location VARCHAR(255) DEFAULT NULL,
  result ENUM('PASS','FAIL','CONDITIONAL_PASS') NOT NULL,
  notes TEXT DEFAULT NULL,
  status ENUM('SCHEDULED','IN_PROGRESS','COMPLETED','FAILED') NOT NULL DEFAULT 'SCHEDULED',
  approval_signal ENUM('APPROVED','FAILED','PENDING') NOT NULL DEFAULT 'PENDING',
  created_at DATETIME NOT NULL,
  updated_at DATETIME DEFAULT NULL,
  FOREIGN KEY (inspector_id) REFERENCES members(id)
);

CREATE TABLE IF NOT EXISTS inspection_evidence (
  id VARCHAR(32) PRIMARY KEY,
  inspection_id VARCHAR(32) NOT NULL,
  file_url VARCHAR(500) NOT NULL,
  type ENUM('PHOTO','VIDEO') NOT NULL,
  description TEXT DEFAULT NULL,
  uploaded_at DATETIME NOT NULL,
  FOREIGN KEY (inspection_id) REFERENCES inspection_reports(id)
);

CREATE TABLE IF NOT EXISTS inspection_defects (
  id VARCHAR(32) PRIMARY KEY,
  inspection_id VARCHAR(32) NOT NULL,
  defect_type ENUM('COSMETIC','FUNCTIONAL','CRITICAL') NOT NULL,
  severity ENUM('LOW','MEDIUM','HIGH','CRITICAL') NOT NULL,
  percentage_affected DECIMAL(5,2) DEFAULT NULL,
  recommended_action TEXT DEFAULT NULL,
  notes TEXT DEFAULT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (inspection_id) REFERENCES inspection_reports(id)
);

CREATE TABLE IF NOT EXISTS compliance_flags (
  id VARCHAR(32) PRIMARY KEY,
  bank_id VARCHAR(32) NOT NULL,

  -- What is flagged
  reference_type ENUM('MEMBER','TRANSACTION','PAYMENT','ACCOUNT') NOT NULL DEFAULT 'TRANSACTION',
  reference_id VARCHAR(32) NOT NULL,

  -- Flag details
  flag_type ENUM('AML','SANCTIONS','HIGH_RISK','HOLD','ADDITIONAL_INFO_REQUIRED') NOT NULL DEFAULT 'AML',
  severity ENUM('LOW','MEDIUM','HIGH','BLOCK') NOT NULL DEFAULT 'MEDIUM',
  description TEXT NOT NULL,
  required_action TEXT DEFAULT NULL,

  -- Status
  status ENUM('OPEN','UNDER_REVIEW','RESOLVED','ESCALATED') NOT NULL DEFAULT 'OPEN',
  created_at DATETIME NOT NULL,
  resolved_at DATETIME DEFAULT NULL,
  FOREIGN KEY (bank_id) REFERENCES members(id)
);

CREATE TABLE IF NOT EXISTS reconciliation_runs (
  id VARCHAR(32) PRIMARY KEY,
  run_by VARCHAR(32) NOT NULL,
  scope ENUM('FULL','REMITTANCES','IMPORTS','EXPORTS','ACCOUNTS') NOT NULL DEFAULT 'FULL',
  total_checked INT NOT NULL DEFAULT 0,
  matched INT NOT NULL DEFAULT 0,
  unmatched INT NOT NULL DEFAULT 0,
  discrepancies INT NOT NULL DEFAULT 0,
  status ENUM('RUNNING','COMPLETED','FAILED') NOT NULL DEFAULT 'RUNNING',
  started_at DATETIME NOT NULL,
  completed_at DATETIME DEFAULT NULL,
  FOREIGN KEY (run_by) REFERENCES members(id)
);

CREATE TABLE IF NOT EXISTS reconciliation_entries (
  id VARCHAR(32) PRIMARY KEY,
  run_id VARCHAR(32) NOT NULL,
  entity_type ENUM('REMITTANCE','IMPORT_ORDER','EXPORT_SHIPMENT','ACCOUNT_BALANCE') NOT NULL,
  entity_id VARCHAR(32) NOT NULL,
  bank_transaction_id VARCHAR(32) DEFAULT NULL,
  expected_amount DECIMAL(18,2) DEFAULT NULL,
  actual_amount DECIMAL(18,2) DEFAULT NULL,
  currency VARCHAR(16) NOT NULL DEFAULT 'USD',
  result ENUM('MATCHED','UNMATCHED','DISCREPANCY','NO_BANK_TXN') NOT NULL,
  notes TEXT DEFAULT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (run_id) REFERENCES reconciliation_runs(id)
);

CREATE TABLE IF NOT EXISTS payment_routes (
  id VARCHAR(32) PRIMARY KEY,
  requested_by VARCHAR(32) NOT NULL,
  from_currency VARCHAR(16) NOT NULL,
  to_currency VARCHAR(16) NOT NULL,
  amount DECIMAL(18,2) NOT NULL,
  recipient_type ENUM('BANK','CRYPTO','INTERNAL') NOT NULL DEFAULT 'BANK',
  urgency ENUM('STANDARD','EXPRESS','URGENT') NOT NULL DEFAULT 'STANDARD',
  recommended_rail ENUM('SWIFT','ACH','SEPA','WIRE','INTERNAL','CRYPTO') DEFAULT NULL,
  route_options TEXT DEFAULT NULL,
  estimated_cost DECIMAL(15,2) DEFAULT NULL,
  estimated_time_hours INT DEFAULT NULL,
  compliance_cleared TINYINT(1) NOT NULL DEFAULT 0,
  compliance_notes TEXT DEFAULT NULL,
  status ENUM('PENDING','ROUTED','EXECUTED','FAILED','BLOCKED') NOT NULL DEFAULT 'PENDING',
  linked_transaction_id VARCHAR(32) DEFAULT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (requested_by) REFERENCES members(id)
);

CREATE TABLE IF NOT EXISTS transactions (
  id VARCHAR(32) PRIMARY KEY,
  type ENUM('ADMIN','REMITTER','IMPORTER','EXPORTER','TRANSPORT','INSURANCE','BANK','INSPECTOR') NOT NULL,
  initiator_id VARCHAR(32) NOT NULL,
  date DATETIME NOT NULL,
  details JSON NOT NULL,
  status ENUM('DRAFT','MATCHED','COMPLETED','ERROR') NOT NULL DEFAULT 'DRAFT',
  FOREIGN KEY (initiator_id) REFERENCES members(id)
);

CREATE TABLE IF NOT EXISTS crypto_transfers (
  id VARCHAR(32) PRIMARY KEY,
  member_id VARCHAR(32) NOT NULL,
  from_address VARCHAR(130) NOT NULL,
  to_address VARCHAR(130) NOT NULL,
  amount DECIMAL(18,8) NOT NULL,
  token_symbol VARCHAR(20) NOT NULL DEFAULT 'USDC',
  moochedda_tx_id VARCHAR(100) DEFAULT NULL,
  linked_type VARCHAR(50) DEFAULT NULL,
  linked_id VARCHAR(32) DEFAULT NULL,
  status ENUM('PENDING','COMPLETED','FAILED') NOT NULL DEFAULT 'PENDING',
  notes TEXT DEFAULT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (member_id) REFERENCES members(id)
);

CREATE TABLE IF NOT EXISTS crypto_invoices (
  id VARCHAR(32) PRIMARY KEY,
  member_id VARCHAR(32) NOT NULL,
  moochedda_invoice_id VARCHAR(100) NOT NULL,
  payment_url VARCHAR(500) DEFAULT NULL,
  merchant_address VARCHAR(130) NOT NULL,
  total_amount DECIMAL(18,8) NOT NULL DEFAULT 0,
  token_symbol VARCHAR(20) NOT NULL DEFAULT 'USDC',
  tax_rate DECIMAL(5,4) DEFAULT NULL,
  line_items JSON DEFAULT NULL,
  status ENUM('PENDING','PAID','EXPIRED') NOT NULL DEFAULT 'PENDING',
  linked_type VARCHAR(50) DEFAULT NULL,
  linked_id VARCHAR(32) DEFAULT NULL,
  expires_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (member_id) REFERENCES members(id)
);
