-- ============================================================================
-- PHASE 7 — Service Reminders (reset-on-return cycle) & Rework Cost Tracking
-- Run after schema_phase6 permissions (seed_phase6.sql).
-- ============================================================================

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

-- ---------------------------------------------------------------------------
-- One active reminder cycle per vehicle. Reset every time the vehicle comes
-- back in for a completed service (see WorkOrder::applyServiceCompletion).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `service_reminders` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `vehicle_id` INT UNSIGNED NOT NULL,
  `last_service_date` DATE NOT NULL,
  `next_reminder_date` DATE NOT NULL,
  `status` ENUM('pending','due','sent','dismissed') NOT NULL DEFAULT 'pending',
  `email_sent_at` DATETIME DEFAULT NULL,
  `whatsapp_sent_at` DATETIME DEFAULT NULL,
  `dismissed_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_reminder_vehicle` (`vehicle_id`),
  KEY `idx_reminder_next_date` (`next_reminder_date`),
  CONSTRAINT `fk_reminder_vehicle` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
-- Rework: a redo of a previous job at no charge to the customer, but the
-- garage's own cost must still be visible to admins, tracked separately from
-- normal billable work.
-- ---------------------------------------------------------------------------
ALTER TABLE `work_orders`
  ADD COLUMN IF NOT EXISTS `is_rework` TINYINT(1) NOT NULL DEFAULT 0 AFTER `wo_type`,
  ADD COLUMN IF NOT EXISTS `rework_of_id` INT UNSIGNED DEFAULT NULL AFTER `is_rework`,
  ADD COLUMN IF NOT EXISTS `rework_reason` TEXT DEFAULT NULL AFTER `rework_of_id`;

ALTER TABLE `work_orders`
  ADD CONSTRAINT `fk_wo_rework_of` FOREIGN KEY IF NOT EXISTS (`rework_of_id`) REFERENCES `work_orders`(`id`) ON DELETE SET NULL;

-- Extend the inventory transaction type enum with a distinct rework bucket,
-- so rework consumption never gets lumped in with normal billable usage.
ALTER TABLE `inventory_transactions`
  MODIFY COLUMN `transaction_type` ENUM('purchase','service_usage','repair_usage','rework_usage','return','adjustment','damage','transfer') NOT NULL;

-- Record the real cost at time of use on work order items, so rework cost
-- reporting stays accurate even if a part's cost price changes later.
ALTER TABLE `work_order_items`
  ADD COLUMN IF NOT EXISTS `cost_at_time` DECIMAL(12,2) DEFAULT NULL AFTER `line_total`;

SET FOREIGN_KEY_CHECKS = 1;