-- ============================================================================
-- PHASE 8 — VIN identity, Make/Model catalog, service base pricing,
-- and the mechanic-request / storekeeper-approve spare parts workflow.
-- ============================================================================

SET NAMES utf8mb4;
USE `garage_management`;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------------
-- VIN/Chassis becomes the primary vehicle identifier: required and unique.
-- Any pre-existing vehicle with no VIN on file gets a placeholder so the
-- NOT NULL/UNIQUE constraints can be applied without losing existing rows;
-- staff can correct it from the vehicle edit screen.
-- ---------------------------------------------------------------------------
UPDATE `vehicles` SET `vin` = CONCAT('UNKNOWN-VIN-', `id`) WHERE `vin` IS NULL OR `vin` = '';
ALTER TABLE `vehicles` MODIFY COLUMN `vin` VARCHAR(50) NOT NULL;
ALTER TABLE `vehicles` ADD UNIQUE KEY IF NOT EXISTS `uq_vehicles_vin` (`vin`);

-- ---------------------------------------------------------------------------
-- Make/Model catalog — searchable, extensible dropdowns for vehicle
-- registration (replaces free-text make/model entry).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `vehicle_makes` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(60) NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_vehicle_makes_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `vehicle_models` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `make_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(60) NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_vehicle_models` (`make_id`, `name`),
  CONSTRAINT `fk_vehicle_models_make` FOREIGN KEY (`make_id`) REFERENCES `vehicle_makes`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
-- Each service category gets a predefined base price, auto-added to the Job
-- Card as a priced line item the moment the service is selected.
-- ---------------------------------------------------------------------------
ALTER TABLE `service_categories`
  ADD COLUMN IF NOT EXISTS `base_price` DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER `description`;

-- ---------------------------------------------------------------------------
-- Spare parts request/approval workflow. Mechanics request parts (no stock
-- change); a Storekeeper reviews and issues them (stock deducted only then),
-- or adds parts directly to any Job Card without waiting for a request.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `part_requests` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `work_order_id` INT UNSIGNED NOT NULL,
  `requested_by` INT UNSIGNED NOT NULL,
  `status` ENUM('pending','issued','rejected') NOT NULL DEFAULT 'pending',
  `request_notes` VARCHAR(255) DEFAULT NULL,
  `review_notes` VARCHAR(255) DEFAULT NULL,
  `reviewed_by` INT UNSIGNED DEFAULT NULL,
  `reviewed_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_part_requests_wo` (`work_order_id`),
  KEY `idx_part_requests_status` (`status`),
  CONSTRAINT `fk_part_requests_wo` FOREIGN KEY (`work_order_id`) REFERENCES `work_orders`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_part_requests_requester` FOREIGN KEY (`requested_by`) REFERENCES `users`(`id`),
  CONSTRAINT `fk_part_requests_reviewer` FOREIGN KEY (`reviewed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `part_request_items` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `part_request_id` INT UNSIGNED NOT NULL,
  `part_id` INT UNSIGNED NOT NULL,
  `quantity_requested` DECIMAL(12,2) NOT NULL,
  `quantity_issued` DECIMAL(12,2) DEFAULT NULL,
  `unit_price` DECIMAL(12,2) DEFAULT NULL,
  `work_order_item_id` INT UNSIGNED DEFAULT NULL,
  KEY `idx_pri_request` (`part_request_id`),
  CONSTRAINT `fk_pri_request` FOREIGN KEY (`part_request_id`) REFERENCES `part_requests`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_pri_part` FOREIGN KEY (`part_id`) REFERENCES `parts`(`id`),
  CONSTRAINT `fk_pri_woitem` FOREIGN KEY (`work_order_item_id`) REFERENCES `work_order_items`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;