CREATE DATABASE IF NOT EXISTS milage_tracker
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE milage_tracker;

CREATE TABLE IF NOT EXISTS shifts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  source_local_id BIGINT UNSIGNED NOT NULL,
  platform_mode VARCHAR(20) NOT NULL,
  start_time DATETIME(6) NOT NULL,
  end_time DATETIME(6) NULL,
  start_odometer DECIMAL(10,2) NOT NULL,
  end_odometer DECIMAL(10,2) NULL,
  start_fuel_level DECIMAL(10,2) NULL,
  end_fuel_level DECIMAL(10,2) NULL,
  total_earnings DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  start_notes TEXT NULL,
  end_notes TEXT 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,
  PRIMARY KEY (id),
  UNIQUE KEY uq_shifts_source_local_id (source_local_id),
  KEY idx_shifts_start_time (start_time),
  KEY idx_shifts_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS trips (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  source_local_id BIGINT UNSIGNED NOT NULL,
  shift_id BIGINT UNSIGNED NULL,
  source_shift_local_id BIGINT UNSIGNED NULL,
  platform VARCHAR(20) NOT NULL,
  trip_type VARCHAR(20) NOT NULL,
  trip_time DATETIME(6) NOT NULL,
  gross_pay DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  tip DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  miles DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  duration_minutes DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  total_miles DECIMAL(10,2) NULL,
  total_duration_minutes DECIMAL(10,2) NULL,
  notes TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_trips_source_local_id (source_local_id),
  KEY idx_trips_trip_time (trip_time),
  KEY idx_trips_shift_id (shift_id),
  KEY idx_trips_source_shift_local_id (source_shift_local_id),
  CONSTRAINT fk_trips_shift
    FOREIGN KEY (shift_id) REFERENCES shifts (id)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fuel_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  source_local_id BIGINT UNSIGNED NOT NULL,
  fuel_time DATETIME(6) NOT NULL,
  odometer DECIMAL(10,2) NOT NULL,
  gallons DECIMAL(10,3) NOT NULL,
  total_cost DECIMAL(10,2) NOT NULL,
  notes TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_fuel_logs_source_local_id (source_local_id),
  KEY idx_fuel_logs_fuel_time (fuel_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS misc_income (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  source_local_id BIGINT UNSIGNED NOT NULL,
  shift_id BIGINT UNSIGNED NULL,
  source_shift_local_id BIGINT UNSIGNED NULL,
  platform VARCHAR(20) NOT NULL,
  charge_time DATETIME(6) NOT NULL,
  amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  notes TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_misc_income_source_local_id (source_local_id),
  KEY idx_misc_income_charge_time (charge_time),
  KEY idx_misc_income_shift_id (shift_id),
  KEY idx_misc_income_source_shift_local_id (source_shift_local_id),
  CONSTRAINT fk_misc_income_shift
    FOREIGN KEY (shift_id) REFERENCES shifts (id)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;