-- ============================================
-- Fleet System - Incremental Schema Updates
-- Target database: gemartgm_fleet
-- Date: 2026-04-07
-- ============================================

USE `gemartgm_fleet`;

SET @db_name := DATABASE();

-- ============================================
-- locations.manager_id
-- ============================================
SET @sql := IF (
    EXISTS (
        SELECT 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @db_name
          AND TABLE_NAME = 'locations'
          AND COLUMN_NAME = 'manager_id'
    ),
    'SELECT 1',
    'ALTER TABLE `locations` ADD COLUMN `manager_id` INT NULL AFTER `address`'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF (
    EXISTS (
        SELECT 1
        FROM information_schema.STATISTICS
        WHERE TABLE_SCHEMA = @db_name
          AND TABLE_NAME = 'locations'
          AND INDEX_NAME = 'idx_locations_manager'
    ),
    'SELECT 1',
    'ALTER TABLE `locations` ADD INDEX `idx_locations_manager` (`manager_id`)'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF (
    EXISTS (
        SELECT 1
        FROM information_schema.TABLE_CONSTRAINTS
        WHERE TABLE_SCHEMA = @db_name
          AND TABLE_NAME = 'locations'
          AND CONSTRAINT_NAME = 'fk_locations_manager'
    ),
    'SELECT 1',
    'ALTER TABLE `locations` ADD CONSTRAINT `fk_locations_manager` FOREIGN KEY (`manager_id`) REFERENCES `users` (`id`) ON DELETE SET NULL'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================
-- asset_assignments.return_expected_date
-- ============================================
SET @sql := IF (
    EXISTS (
        SELECT 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @db_name
          AND TABLE_NAME = 'asset_assignments'
          AND COLUMN_NAME = 'return_expected_date'
    ),
    'SELECT 1',
    'ALTER TABLE `asset_assignments` ADD COLUMN `return_expected_date` DATE NULL AFTER `start_date`'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================
-- external_assets commercial fields used by UI
-- ============================================
SET @sql := IF (
    EXISTS (
        SELECT 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @db_name
          AND TABLE_NAME = 'external_assets'
          AND COLUMN_NAME = 'provider_name'
    ),
    'SELECT 1',
    'ALTER TABLE `external_assets` ADD COLUMN `provider_name` VARCHAR(150) NULL AFTER `name`'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF (
    EXISTS (
        SELECT 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @db_name
          AND TABLE_NAME = 'external_assets'
          AND COLUMN_NAME = 'hourly_rate'
    ),
    'SELECT 1',
    'ALTER TABLE `external_assets` ADD COLUMN `hourly_rate` DECIMAL(12,2) NULL AFTER `tracking_method`'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF (
    EXISTS (
        SELECT 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @db_name
          AND TABLE_NAME = 'external_assets'
          AND COLUMN_NAME = 'daily_rate'
    ),
    'SELECT 1',
    'ALTER TABLE `external_assets` ADD COLUMN `daily_rate` DECIMAL(12,2) NULL AFTER `hourly_rate`'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================
-- maintenance.parts_replaced
-- ============================================
SET @sql := IF (
    EXISTS (
        SELECT 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @db_name
          AND TABLE_NAME = 'maintenance'
          AND COLUMN_NAME = 'parts_replaced'
    ),
    'SELECT 1',
    'ALTER TABLE `maintenance` ADD COLUMN `parts_replaced` TEXT NULL AFTER `description`'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;