-- ─────────────────────────────────────────────────────────────────────────────
-- FIX 4: Add reversal-tracking columns to pharmacy_dispenses
-- ─────────────────────────────────────────────────────────────────────────────
--
-- Mirrors migration: 2026_05_06_120000_add_reversal_to_pharmacy_dispenses.php
-- Run this if you cannot execute `php artisan migrate` from the terminal.
-- Safe to re-run: every step is guarded.
--
-- Columns added:
--   reversed_at      — timestamp; null when dispense is still active
--   reversed_by      — FK to users.id; who clicked Reverse
--   reversal_reason  — text; mandatory free-text reason captured on reverse
--   refund_status    — 'not_applicable' | 'pending' | 'processed' | null
--
-- HOW TO RUN (cPanel → phpMyAdmin):
--   1. Select the production database.
--   2. SQL tab → paste this entire file → Go.
--   3. The last block also marks the migration as "run" so a future
--      php artisan migrate won't try to re-execute it.
-- ─────────────────────────────────────────────────────────────────────────────


-- ── reversed_at ─────────────────────────────────────────────────────────────
SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name   = 'pharmacy_dispenses'
      AND column_name  = 'reversed_at'
);
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE `pharmacy_dispenses` ADD COLUMN `reversed_at` TIMESTAMP NULL AFTER `dispensed_at`',
    'SELECT "reversed_at already exists" AS note'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── reversed_by ─────────────────────────────────────────────────────────────
SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name   = 'pharmacy_dispenses'
      AND column_name  = 'reversed_by'
);
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE `pharmacy_dispenses` ADD COLUMN `reversed_by` BIGINT UNSIGNED NULL AFTER `reversed_at`',
    'SELECT "reversed_by already exists" AS note'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── reversal_reason ─────────────────────────────────────────────────────────
SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name   = 'pharmacy_dispenses'
      AND column_name  = 'reversal_reason'
);
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE `pharmacy_dispenses` ADD COLUMN `reversal_reason` TEXT NULL AFTER `reversed_by`',
    'SELECT "reversal_reason already exists" AS note'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── refund_status ───────────────────────────────────────────────────────────
SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name   = 'pharmacy_dispenses'
      AND column_name  = 'refund_status'
);
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE `pharmacy_dispenses` ADD COLUMN `refund_status` VARCHAR(20) NULL AFTER `reversal_reason`',
    'SELECT "refund_status already exists" AS note'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── FK on reversed_by → users(id) ───────────────────────────────────────────
SET @fk_exists := (
    SELECT COUNT(*) FROM information_schema.key_column_usage
    WHERE table_schema    = DATABASE()
      AND table_name      = 'pharmacy_dispenses'
      AND column_name     = 'reversed_by'
      AND referenced_table_name = 'users'
);
SET @sql := IF(@fk_exists = 0,
    'ALTER TABLE `pharmacy_dispenses`
       ADD CONSTRAINT `pharmacy_dispenses_reversed_by_foreign`
       FOREIGN KEY (`reversed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL',
    'SELECT "reversed_by FK already exists" AS note'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── Index on reversed_at ────────────────────────────────────────────────────
SET @idx_exists := (
    SELECT COUNT(*) FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name   = 'pharmacy_dispenses'
      AND index_name   = 'pharmacy_dispenses_reversed_at_index'
);
SET @sql := IF(@idx_exists = 0,
    'CREATE INDEX `pharmacy_dispenses_reversed_at_index` ON `pharmacy_dispenses`(`reversed_at`)',
    'SELECT "reversed_at index already exists" AS note'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── Mark migration as run ───────────────────────────────────────────────────
INSERT INTO `migrations` (`migration`, `batch`)
SELECT '2026_05_06_120000_add_reversal_to_pharmacy_dispenses',
       COALESCE((SELECT MAX(batch) FROM (SELECT batch FROM `migrations`) m), 0) + 1
WHERE NOT EXISTS (
    SELECT 1 FROM `migrations`
    WHERE migration = '2026_05_06_120000_add_reversal_to_pharmacy_dispenses'
);

-- Verify with:
-- DESCRIBE pharmacy_dispenses;
-- Should show: reversed_at, reversed_by, reversal_reason, refund_status