-- Saber Tech Dispatch schema

DROP TABLE IF EXISTS reason_logs;
DROP TABLE IF EXISTS status_history;
DROP TABLE IF EXISTS driver_locations;
DROP TABLE IF EXISTS loads;
DROP TABLE IF EXISTS drivers;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS pricing_settings;

CREATE TABLE orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    trip_id VARCHAR(36) NOT NULL UNIQUE,
    customer_name VARCHAR(120),
    customer_contact VARCHAR(120),
    status VARCHAR(32) NOT NULL DEFAULT 'NEW',
    country VARCHAR(3) DEFAULT 'US',
    currency VARCHAR(3) DEFAULT 'USD',
    pickup_address VARCHAR(255) NOT NULL,
    pickup_city_state VARCHAR(120),
    pickup_lat DECIMAL(10,7),
    pickup_lng DECIMAL(10,7),
    dropoff_address VARCHAR(255) NOT NULL,
    dropoff_city_state VARCHAR(120),
    dropoff_lat DECIMAL(10,7),
    dropoff_lng DECIMAL(10,7),
    weight_kg DECIMAL(10,2) DEFAULT 0,
    distance_miles DECIMAL(10,2) DEFAULT 0,
    operational_subtotal_usd DECIMAL(10,2) DEFAULT 0,
    sabertech_fee_usd DECIMAL(10,2) DEFAULT 0,
    customer_total_usd DECIMAL(10,2) DEFAULT 0,
    pricing_locked TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE drivers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    driver_id VARCHAR(36) NOT NULL UNIQUE,
    name VARCHAR(120),
    email VARCHAR(160),
    phone VARCHAR(40),
    stripe_account_id VARCHAR(255) NULL,
    state VARCHAR(20) NOT NULL DEFAULT 'OFFLINE',
    last_seen TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE loads (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id INT UNSIGNED NOT NULL,
    driver_id INT UNSIGNED NULL,
    assigned_at TIMESTAMP NULL,
    accepted_at TIMESTAMP NULL,
    pickup_eta TIMESTAMP NULL,
    dropoff_eta TIMESTAMP NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (driver_id) REFERENCES drivers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE driver_locations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    driver_id INT UNSIGNED NOT NULL,
    lat DECIMAL(10,7) NOT NULL,
    lng DECIMAL(10,7) NOT NULL,
    recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (driver_id) REFERENCES drivers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE status_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id INT UNSIGNED NOT NULL,
    status VARCHAR(32) NOT NULL,
    actor VARCHAR(32) NOT NULL DEFAULT 'system',
    recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE reason_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id INT UNSIGNED NOT NULL,
    action_type VARCHAR(32) NOT NULL,
    reason TEXT,
    recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE pricing_settings (
    id TINYINT UNSIGNED PRIMARY KEY DEFAULT 1,
    per_mile_rate_usd DECIMAL(10,2) NOT NULL DEFAULT 2.50,
    tier_base_cap_usd DECIMAL(10,2) NOT NULL DEFAULT 50.00,
    tier_upper_cap_usd DECIMAL(10,2) NOT NULL DEFAULT 90.00,
    overage_increment_usd DECIMAL(10,2) NOT NULL DEFAULT 25.00,
    overage_weight_lbs INT NOT NULL DEFAULT 100,
    sabertech_fee_percent DECIMAL(5,2) NOT NULL DEFAULT 10.00,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO pricing_settings (id) VALUES (1)
    ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP;
