-- Optimized and standardized schema for freight dispatch operations.
-- Target: MySQL 8+ (InnoDB, utf8mb4)

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS load_customs_entries;
DROP TABLE IF EXISTS load_assignments;
DROP TABLE IF EXISTS load_stops;
DROP TABLE IF EXISTS loads;
DROP TABLE IF EXISTS trailers;
DROP TABLE IF EXISTS trucks;
DROP TABLE IF EXISTS driver_recurring_pay_items;
DROP TABLE IF EXISTS driver_pay_profiles;
DROP TABLE IF EXISTS drivers;
DROP TABLE IF EXISTS company_insurance_policies;
DROP TABLE IF EXISTS company_quote_settings;
DROP TABLE IF EXISTS company_accounting_profiles;
DROP TABLE IF EXISTS company_contacts;
DROP TABLE IF EXISTS company_addresses;
DROP TABLE IF EXISTS company_roles;
DROP TABLE IF EXISTS companies;
DROP TABLE IF EXISTS equipment_types;
DROP TABLE IF EXISTS load_modes;
DROP TABLE IF EXISTS load_statuses;
DROP TABLE IF EXISTS currencies;
DROP TABLE IF EXISTS states_provinces;
DROP TABLE IF EXISTS countries;

SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE countries (
  id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  iso2 CHAR(2) NOT NULL UNIQUE,
  iso3 CHAR(3) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE states_provinces (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  country_id SMALLINT UNSIGNED NOT NULL,
  code VARCHAR(10) NOT NULL,
  name VARCHAR(100) NOT NULL,
  CONSTRAINT fk_states_country
    FOREIGN KEY (country_id) REFERENCES countries(id),
  CONSTRAINT uq_state_country_code UNIQUE (country_id, code)
) ENGINE=InnoDB;

CREATE TABLE currencies (
  code CHAR(3) PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  symbol VARCHAR(8) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE load_statuses (
  id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  code VARCHAR(30) NOT NULL UNIQUE,
  name VARCHAR(50) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE load_modes (
  id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  code VARCHAR(30) NOT NULL UNIQUE,
  name VARCHAR(50) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE equipment_types (
  id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  code VARCHAR(30) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE companies (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  company_code VARCHAR(30) NULL UNIQUE,
  legal_name VARCHAR(180) NOT NULL,
  display_name VARCHAR(180) NOT NULL,
  mc_number VARCHAR(30) NULL,
  ff_number VARCHAR(30) NULL,
  fmcsa_number VARCHAR(30) NULL,
  federal_tax_id VARCHAR(50) NULL,
  wsib_number VARCHAR(50) NULL,
  website_url VARCHAR(255) NULL,
  default_currency_code CHAR(3) NOT NULL DEFAULT 'USD',
  payment_terms VARCHAR(80) NULL,
  credit_limit DECIMAL(14,2) NULL,
  customer_rate DECIMAL(14,4) NULL,
  blacklisted TINYINT(1) NOT NULL DEFAULT 0,
  is_broker TINYINT(1) NOT NULL DEFAULT 0,
  is_corporation TINYINT(1) NOT NULL DEFAULT 0,
  show_phone_fax_on_invoice TINYINT(1) NOT NULL DEFAULT 0,
  notes_internal TEXT NULL,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_companies_currency
    FOREIGN KEY (default_currency_code) REFERENCES currencies(code)
) ENGINE=InnoDB;

CREATE TABLE company_roles (
  company_id BIGINT UNSIGNED NOT NULL,
  role_code ENUM('customer', 'carrier', 'shipper', 'consignee', 'customs_broker', 'factoring_company') NOT NULL,
  PRIMARY KEY (company_id, role_code),
  CONSTRAINT fk_company_roles_company
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE company_addresses (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  company_id BIGINT UNSIGNED NOT NULL,
  address_type ENUM('mailing', 'billing', 'physical', 'other') NOT NULL DEFAULT 'physical',
  line1 VARCHAR(180) NOT NULL,
  line2 VARCHAR(180) NULL,
  line3 VARCHAR(180) NULL,
  city VARCHAR(100) NOT NULL,
  state_province_id INT UNSIGNED NULL,
  postal_code VARCHAR(20) NULL,
  country_id SMALLINT UNSIGNED NOT NULL,
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_company_addresses_company
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_company_addresses_state
    FOREIGN KEY (state_province_id) REFERENCES states_provinces(id),
  CONSTRAINT fk_company_addresses_country
    FOREIGN KEY (country_id) REFERENCES countries(id),
  INDEX idx_company_addresses_company_type (company_id, address_type),
  INDEX idx_company_addresses_location (country_id, state_province_id, city)
) ENGINE=InnoDB;

CREATE TABLE company_contacts (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  company_id BIGINT UNSIGNED NOT NULL,
  contact_type ENUM('primary', 'secondary', 'billing', 'operations', 'other') NOT NULL DEFAULT 'other',
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NULL,
  phone VARCHAR(30) NULL,
  extension VARCHAR(12) NULL,
  fax VARCHAR(30) NULL,
  toll_free VARCHAR(30) NULL,
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_company_contacts_company
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
  INDEX idx_company_contacts_company_type (company_id, contact_type),
  INDEX idx_company_contacts_email (email)
) ENGINE=InnoDB;

CREATE TABLE company_accounting_profiles (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  company_id BIGINT UNSIGNED NOT NULL UNIQUE,
  factoring_company_id BIGINT UNSIGNED NULL,
  sales_rep_user_id BIGINT UNSIGNED NULL,
  workers_comp_number VARCHAR(60) NULL,
  duplicate_as_shipper TINYINT(1) NOT NULL DEFAULT 0,
  duplicate_as_consignee TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_company_accounting_company
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_company_accounting_factoring
    FOREIGN KEY (factoring_company_id) REFERENCES companies(id),
  CONSTRAINT fk_company_accounting_sales_rep
    FOREIGN KEY (sales_rep_user_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE company_quote_settings (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  company_id BIGINT UNSIGNED NOT NULL UNIQUE,
  show_miles_on_quote TINYINT(1) NOT NULL DEFAULT 0,
  rate_type VARCHAR(80) NULL,
  fsc_type ENUM('percent', 'amount') NOT NULL DEFAULT 'percent',
  fsc_value DECIMAL(10,4) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_company_quote_settings_company
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE company_insurance_policies (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  company_id BIGINT UNSIGNED NOT NULL,
  policy_type ENUM('liability', 'auto', 'cargo') NOT NULL,
  provider_name VARCHAR(160) NOT NULL,
  policy_number VARCHAR(80) NULL,
  contact_name VARCHAR(120) NULL,
  phone VARCHAR(30) NULL,
  extension VARCHAR(12) NULL,
  coverage_amount DECIMAL(14,2) NULL,
  rating VARCHAR(60) NULL,
  expires_on DATE NULL,
  notes TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_company_insurance_company
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
  INDEX idx_company_insurance_company_type (company_id, policy_type)
) ENGINE=InnoDB;

CREATE TABLE drivers (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  driver_code VARCHAR(30) NULL UNIQUE,
  driver_type ENUM('single', 'team') NOT NULL DEFAULT 'single',
  full_name VARCHAR(140) NOT NULL,
  email VARCHAR(190) NULL,
  phone_primary VARCHAR(30) NULL,
  phone_alt VARCHAR(30) NULL,
  pager VARCHAR(30) NULL,
  country_id SMALLINT UNSIGNED NULL,
  state_province_id INT UNSIGNED NULL,
  city VARCHAR(100) NULL,
  postal_code VARCHAR(20) NULL,
  date_of_birth DATE NULL,
  license_number VARCHAR(60) NULL,
  license_expires_on DATE NULL,
  hazmat_expires_on DATE NULL,
  passport_expires_on DATE NULL,
  medical_expires_on DATE NULL,
  drug_test_last_on DATE NULL,
  drug_test_next_on DATE NULL,
  fast_card_expires_on DATE NULL,
  termination_on DATE NULL,
  notes_internal TEXT NULL,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_drivers_country
    FOREIGN KEY (country_id) REFERENCES countries(id),
  CONSTRAINT fk_drivers_state
    FOREIGN KEY (state_province_id) REFERENCES states_provinces(id),
  INDEX idx_drivers_name (full_name),
  INDEX idx_drivers_status (status)
) ENGINE=InnoDB;

CREATE TABLE driver_pay_profiles (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  driver_id BIGINT UNSIGNED NOT NULL UNIQUE,
  pay_type ENUM('per_mile', 'flat', 'hourly', 'other') NOT NULL DEFAULT 'per_mile',
  currency_code CHAR(3) NOT NULL DEFAULT 'USD',
  loaded_miles_rate DECIMAL(12,4) NULL,
  empty_miles_rate DECIMAL(12,4) NULL,
  picks_per_rate DECIMAL(12,4) NULL,
  drops_per_rate DECIMAL(12,4) NULL,
  wait_hourly_rate DECIMAL(12,4) NULL,
  pay_summary_on_confirmation TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_driver_pay_profiles_driver
    FOREIGN KEY (driver_id) REFERENCES drivers(id) ON DELETE CASCADE,
  CONSTRAINT fk_driver_pay_profiles_currency
    FOREIGN KEY (currency_code) REFERENCES currencies(code)
) ENGINE=InnoDB;

CREATE TABLE driver_recurring_pay_items (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  driver_id BIGINT UNSIGNED NOT NULL,
  item_type ENUM('pay', 'deduction') NOT NULL,
  description VARCHAR(150) NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_driver_recurring_items_driver
    FOREIGN KEY (driver_id) REFERENCES drivers(id) ON DELETE CASCADE,
  INDEX idx_driver_recurring_items_driver_type (driver_id, item_type)
) ENGINE=InnoDB;

CREATE TABLE trucks (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  truck_number VARCHAR(30) NOT NULL UNIQUE,
  truck_type VARCHAR(80) NULL,
  license_plate VARCHAR(30) NULL,
  plate_expires_on DATE NULL,
  inspection_expires_on DATE NULL,
  mileage INT UNSIGNED NULL,
  model_year SMALLINT UNSIGNED NULL,
  axles TINYINT UNSIGNED NULL,
  fuel_type VARCHAR(40) NULL,
  include_for_ifta TINYINT(1) NOT NULL DEFAULT 0,
  registered_state_id INT UNSIGNED NULL,
  insurance_policy_number VARCHAR(60) NULL,
  empty_gross_weight_lbs INT UNSIGNED NULL,
  vin VARCHAR(60) NULL,
  dot_expires_on DATE NULL,
  ifta_start_on DATE NULL,
  deactivated_on DATE NULL,
  ownership ENUM('company', 'owner_operator', 'other') NOT NULL DEFAULT 'company',
  notes_internal TEXT NULL,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_trucks_registered_state
    FOREIGN KEY (registered_state_id) REFERENCES states_provinces(id),
  INDEX idx_trucks_status (status),
  INDEX idx_trucks_type (truck_type)
) ENGINE=InnoDB;

CREATE TABLE trailers (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  trailer_number VARCHAR(30) NOT NULL UNIQUE,
  trailer_type_id SMALLINT UNSIGNED NULL,
  license_plate VARCHAR(30) NULL,
  plate_expires_on DATE NULL,
  inspection_expires_on DATE NULL,
  model VARCHAR(80) NULL,
  model_year SMALLINT UNSIGNED NULL,
  axles TINYINT UNSIGNED NULL,
  registered_state_id INT UNSIGNED NULL,
  vin VARCHAR(60) NULL,
  dot_expires_on DATE NULL,
  activated_on DATE NULL,
  notes_internal TEXT NULL,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_trailers_trailer_type
    FOREIGN KEY (trailer_type_id) REFERENCES equipment_types(id),
  CONSTRAINT fk_trailers_registered_state
    FOREIGN KEY (registered_state_id) REFERENCES states_provinces(id),
  INDEX idx_trailers_status (status)
) ENGINE=InnoDB;

CREATE TABLE loads (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  load_number VARCHAR(30) NOT NULL UNIQUE,
  customer_company_id BIGINT UNSIGNED NOT NULL,
  bill_to_company_id BIGINT UNSIGNED NULL,
  dispatcher_user_id BIGINT UNSIGNED NOT NULL,
  sales_rep1_user_id BIGINT UNSIGNED NULL,
  sales_rep2_user_id BIGINT UNSIGNED NULL,
  load_mode_id TINYINT UNSIGNED NOT NULL,
  load_status_id TINYINT UNSIGNED NOT NULL,
  equipment_type_id SMALLINT UNSIGNED NULL,
  rate DECIMAL(14,2) NULL,
  rate_percent DECIMAL(7,4) NULL,
  pd_amount DECIMAL(14,2) NULL,
  fsc_enabled TINYINT(1) NOT NULL DEFAULT 0,
  fsc_rate_percent DECIMAL(7,4) NULL,
  other_charges DECIMAL(14,2) NULL,
  carrier_fee DECIMAL(14,2) NULL,
  currency_code CHAR(3) NOT NULL DEFAULT 'USD',
  wo_reference VARCHAR(80) NULL,
  is_open_load TINYINT(1) NOT NULL DEFAULT 1,
  show_time TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_loads_customer_company
    FOREIGN KEY (customer_company_id) REFERENCES companies(id),
  CONSTRAINT fk_loads_bill_to_company
    FOREIGN KEY (bill_to_company_id) REFERENCES companies(id),
  CONSTRAINT fk_loads_dispatcher
    FOREIGN KEY (dispatcher_user_id) REFERENCES users(id),
  CONSTRAINT fk_loads_sales_rep1
    FOREIGN KEY (sales_rep1_user_id) REFERENCES users(id),
  CONSTRAINT fk_loads_sales_rep2
    FOREIGN KEY (sales_rep2_user_id) REFERENCES users(id),
  CONSTRAINT fk_loads_mode
    FOREIGN KEY (load_mode_id) REFERENCES load_modes(id),
  CONSTRAINT fk_loads_status
    FOREIGN KEY (load_status_id) REFERENCES load_statuses(id),
  CONSTRAINT fk_loads_equipment_type
    FOREIGN KEY (equipment_type_id) REFERENCES equipment_types(id),
  CONSTRAINT fk_loads_currency
    FOREIGN KEY (currency_code) REFERENCES currencies(code),
  INDEX idx_loads_status_date (load_status_id, created_at),
  INDEX idx_loads_customer_date (customer_company_id, created_at),
  INDEX idx_loads_dispatcher_date (dispatcher_user_id, created_at),
  INDEX idx_loads_open (is_open_load)
) ENGINE=InnoDB;

CREATE TABLE load_stops (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  load_id BIGINT UNSIGNED NOT NULL,
  stop_type ENUM('shipper', 'consignee') NOT NULL,
  sequence_no TINYINT UNSIGNED NOT NULL,
  company_id BIGINT UNSIGNED NULL,
  location_name VARCHAR(160) NULL,
  location_address VARCHAR(220) NULL,
  city VARCHAR(100) NULL,
  state_province_id INT UNSIGNED NULL,
  country_id SMALLINT UNSIGNED NULL,
  postal_code VARCHAR(20) NULL,
  scheduled_at DATETIME NULL,
  description VARCHAR(255) NULL,
  commodity_type VARCHAR(100) NULL,
  quantity DECIMAL(12,3) NULL,
  weight_lbs DECIMAL(12,3) NULL,
  cargo_value DECIMAL(14,2) NULL,
  po_numbers VARCHAR(255) NULL,
  primary_notes TEXT NULL,
  internal_notes TEXT NULL,
  appointments_required TINYINT(1) NOT NULL DEFAULT 0,
  show_time TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_load_stops_load
    FOREIGN KEY (load_id) REFERENCES loads(id) ON DELETE CASCADE,
  CONSTRAINT fk_load_stops_company
    FOREIGN KEY (company_id) REFERENCES companies(id),
  CONSTRAINT fk_load_stops_state
    FOREIGN KEY (state_province_id) REFERENCES states_provinces(id),
  CONSTRAINT fk_load_stops_country
    FOREIGN KEY (country_id) REFERENCES countries(id),
  CONSTRAINT uq_load_stop_seq UNIQUE (load_id, stop_type, sequence_no),
  INDEX idx_load_stops_load_type (load_id, stop_type),
  INDEX idx_load_stops_schedule (scheduled_at)
) ENGINE=InnoDB;

CREATE TABLE load_assignments (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  load_id BIGINT UNSIGNED NOT NULL,
  assignment_type ENUM('carrier', 'driver') NOT NULL,
  carrier_company_id BIGINT UNSIGNED NULL,
  driver_id BIGINT UNSIGNED NULL,
  truck_id BIGINT UNSIGNED NULL,
  trailer_id BIGINT UNSIGNED NULL,
  equipment_type_id SMALLINT UNSIGNED NULL,
  flat_rate DECIMAL(14,2) NULL,
  pro_miles DECIMAL(10,2) NULL,
  driver_miles DECIMAL(10,2) NULL,
  hourly_rate DECIMAL(12,2) NULL,
  currency_code CHAR(3) NOT NULL DEFAULT 'USD',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_load_assignments_load
    FOREIGN KEY (load_id) REFERENCES loads(id) ON DELETE CASCADE,
  CONSTRAINT fk_load_assignments_carrier
    FOREIGN KEY (carrier_company_id) REFERENCES companies(id),
  CONSTRAINT fk_load_assignments_driver
    FOREIGN KEY (driver_id) REFERENCES drivers(id),
  CONSTRAINT fk_load_assignments_truck
    FOREIGN KEY (truck_id) REFERENCES trucks(id),
  CONSTRAINT fk_load_assignments_trailer
    FOREIGN KEY (trailer_id) REFERENCES trailers(id),
  CONSTRAINT fk_load_assignments_equipment
    FOREIGN KEY (equipment_type_id) REFERENCES equipment_types(id),
  CONSTRAINT fk_load_assignments_currency
    FOREIGN KEY (currency_code) REFERENCES currencies(code),
  INDEX idx_load_assignments_load (load_id),
  INDEX idx_load_assignments_driver (driver_id),
  INDEX idx_load_assignments_carrier (carrier_company_id)
) ENGINE=InnoDB;

CREATE TABLE load_customs_entries (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  load_id BIGINT UNSIGNED NOT NULL,
  customs_broker_company_id BIGINT UNSIGNED NOT NULL,
  border_crossing VARCHAR(120) NULL,
  phone VARCHAR(30) NULL,
  extension VARCHAR(12) NULL,
  toll_free VARCHAR(30) NULL,
  fax VARCHAR(30) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_load_customs_load
    FOREIGN KEY (load_id) REFERENCES loads(id) ON DELETE CASCADE,
  CONSTRAINT fk_load_customs_broker
    FOREIGN KEY (customs_broker_company_id) REFERENCES companies(id),
  INDEX idx_load_customs_load (load_id)
) ENGINE=InnoDB;

-- Seed baseline lookup values used by UI
INSERT INTO currencies (code, name, symbol) VALUES
  ('USD', 'US Dollar', '$'),
  ('CAD', 'Canadian Dollar', '$');

INSERT INTO load_statuses (code, name) VALUES
  ('pending', 'Pending'),
  ('open', 'Open'),
  ('refused', 'Refused'),
  ('covered', 'Covered'),
  ('dispatched', 'Dispatched'),
  ('on_route', 'On Route'),
  ('unloading', '(Un)Loading'),
  ('in_yard', 'In Yard'),
  ('delivered', 'Delivered');

INSERT INTO load_modes (code, name) VALUES
  ('25lb_sacks', '25Lb Sacks'),
  ('40lb_cartons', '40Lb Cartons'),
  ('50lb_sacks', '50Lb Sacks'),
  ('air_freight', 'Air Freight'),
  ('backhaul', 'Backhaul'),
  ('barrels', 'Barrels'),
  ('bushel', 'Bushel'),
  ('cubic_yard', 'Cubic Yard'),
  ('cwt_100lb', 'CWT/100Lb'),
  ('cwt_1lb', 'CWT/1Lb'),
  ('cwt_ton', 'CWT/Ton'),
  ('delivery', 'Delivery'),
  ('direct', 'Direct'),
  ('drayage', 'Drayage'),
  ('drop', 'Drop'),
  ('equip_rental_daily', 'Equip. Rental - Daily'),
  ('equip_rental_monthly', 'Equip. Rental - Monthly'),
  ('equip_rental_weekly', 'Equip. Rental - Weekly'),
  ('feet', 'Feet'),
  ('flatbed', 'Flatbed'),
  ('full_truckload', 'Full Truckload'),
  ('hot_shot', 'Hot Shot'),
  ('hourly', 'Hourly'),
  ('intermodal', 'Intermodal'),
  ('kilograms', 'Kilograms'),
  ('labour', 'Labour'),
  ('lbs', 'LBS'),
  ('line_haul', 'Line Haul'),
  ('ltl', 'LTL'),
  ('metric_ton', 'Metric Ton'),
  ('ocean', 'Ocean'),
  ('other', 'Other'),
  ('pallets', 'Pallets'),
  ('pick_up', 'Pick up'),
  ('piece', 'Piece'),
  ('profit_share', 'Profit Share'),
  ('rail', 'Rail'),
  ('rpm', 'RPM'),
  ('rpm_fsc', 'RPM (fsc)'),
  ('tons', 'Tons'),
  ('truck_ordered_not_used', 'Truck Ordered Not Used');