-- =============================================================================
-- Freight Client Module — raw SQL (run this if you prefer not to use artisan)
-- Safe to run on the live DB. Mirrors the three migrations:
--   2026_06_14_000001_create_freight_client_tables.php
--   2026_06_14_000002_add_location_to_freight_drivers.php
--   2026_06_14_000003_add_freight_client_sidebar_module.php
--
-- NOTE: Prefer `php artisan migrate` so Laravel records these in `migrations`.
--       Only run this SQL directly if artisan is unavailable on the server.
-- =============================================================================

-- ---------------------------------------------------------------------------
-- 1) freight_clients (self sign-up login + company/contact info)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `freight_clients` (
  `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_name`   VARCHAR(255) NULL,
  `contact_name`   VARCHAR(255) NOT NULL,
  `email`          VARCHAR(255) NOT NULL,
  `phone`          VARCHAR(255) NULL,
  `password`       VARCHAR(255) NOT NULL,
  `address`        VARCHAR(255) NULL,
  `city`           VARCHAR(255) NULL,
  `state`          VARCHAR(255) NULL,
  `zip_code`       VARCHAR(255) NULL,
  `profile_image`  VARCHAR(255) NULL,
  `is_active`      TINYINT(1) NOT NULL DEFAULT 1,
  `remember_token` VARCHAR(100) NULL,
  `created_at`     TIMESTAMP NULL,
  `updated_at`     TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `freight_clients_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- 2) freight_loads (loads/shipments posted by a client)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `freight_loads` (
  `id`                          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `freight_client_id`           BIGINT UNSIGNED NOT NULL,
  `reference_no`                VARCHAR(255) NULL,
  `title`                       VARCHAR(255) NOT NULL,
  `commodity`                   VARCHAR(255) NULL,
  `equipment_type`              VARCHAR(255) NULL,
  `weight`                      VARCHAR(255) NULL,
  `dimensions`                  VARCHAR(255) NULL,
  `pickup_location`             VARCHAR(255) NOT NULL,
  `pickup_city`                 VARCHAR(255) NULL,
  `pickup_state`                VARCHAR(255) NULL,
  `pickup_zip`                  VARCHAR(255) NULL,
  `pickup_lat`                  DECIMAL(10,7) NULL,
  `pickup_lng`                  DECIMAL(10,7) NULL,
  `pickup_date`                 DATE NULL,
  `dropoff_location`            VARCHAR(255) NOT NULL,
  `dropoff_city`                VARCHAR(255) NULL,
  `dropoff_state`               VARCHAR(255) NULL,
  `dropoff_zip`                 VARCHAR(255) NULL,
  `dropoff_lat`                 DECIMAL(10,7) NULL,
  `dropoff_lng`                 DECIMAL(10,7) NULL,
  `dropoff_date`                DATE NULL,
  `distance_miles`              VARCHAR(255) NULL,
  `rate_offer`                  DECIMAL(10,2) NULL,
  `status`                      VARCHAR(255) NOT NULL DEFAULT 'posted',
  `assigned_freight_driver_id`  BIGINT UNSIGNED NULL,
  `notes`                       TEXT NULL,
  `created_at`                  TIMESTAMP NULL,
  `updated_at`                  TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  KEY `freight_loads_freight_client_id_index` (`freight_client_id`),
  CONSTRAINT `freight_loads_freight_client_id_foreign`
    FOREIGN KEY (`freight_client_id`) REFERENCES `freight_clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- 3) live-location & availability columns on freight_drivers
--    (MySQL has no "ADD COLUMN IF NOT EXISTS"; if a column already exists the
--     statement errors harmlessly — skip the ones you already have.)
-- ---------------------------------------------------------------------------
ALTER TABLE `freight_drivers`
  ADD COLUMN `is_available`      TINYINT(1) NOT NULL DEFAULT 0 AFTER `is_active`,
  ADD COLUMN `latitude`          DECIMAL(10,7) NULL AFTER `is_available`,
  ADD COLUMN `longitude`         DECIMAL(10,7) NULL AFTER `latitude`,
  ADD COLUMN `current_location`  VARCHAR(255) NULL AFTER `longitude`,
  ADD COLUMN `last_location_at`  TIMESTAMP NULL AFTER `current_location`;

-- ---------------------------------------------------------------------------
-- 4) Admin sidebar links (only if you use the modules-driven sidebar).
--    Adjust assigned_modules per role as needed; the migration does this
--    automatically for every user_group. Manual insert shown for reference:
-- ---------------------------------------------------------------------------
INSERT INTO `modules` (`name`, `route`, `head_name`, `icon`, `parent_id`, `order`, `is_active`)
SELECT * FROM (SELECT 'Freight Clients' AS name, '/admin/freight-clients' AS route,
       'Freight Management' AS head_name, 'fas fa-user-tie' AS icon, 0 AS parent_id, 999 AS `order`, 1 AS is_active) AS t
WHERE NOT EXISTS (SELECT 1 FROM `modules` WHERE `route` = '/admin/freight-clients');

INSERT INTO `modules` (`name`, `route`, `head_name`, `icon`, `parent_id`, `order`, `is_active`)
SELECT * FROM (SELECT 'Freight Loads' AS name, '/admin/freight-loads' AS route,
       'Freight Management' AS head_name, 'fas fa-boxes-stacked' AS icon, 0 AS parent_id, 999 AS `order`, 1 AS is_active) AS t
WHERE NOT EXISTS (SELECT 1 FROM `modules` WHERE `route` = '/admin/freight-loads');