-- ─────────────────────────────────────────────────────────────────────────────
-- FIX 1: Add expenses.supplier_id (cause of "Saving Expense" 500 errors)
-- ─────────────────────────────────────────────────────────────────────────────
--
-- Mirrors migration: 2026_05_02_200000_link_expenses_to_suppliers.php
-- Run this if you cannot execute `php artisan migrate` from the terminal.
-- Safe to re-run: every step is guarded with IF NOT EXISTS / WHERE checks.
--
-- WHAT IT DOES (3 steps):
--   1. Adds expenses.supplier_id column + FK + index (if missing).
--   2. Backfills the suppliers table from any distinct vendor strings
--      that appear in expenses but are not already in suppliers.
--   3. Resolves expenses.supplier_id by matching vendor string ↔ supplier name.
--
-- HOW TO RUN (cPanel → phpMyAdmin):
--   1. Select the production database.
--   2. Click the "SQL" tab.
--   3. Paste this entire file. Run.
--   4. Also remember to mark the migration as run (last block at bottom),
--      otherwise `php artisan migrate` later will try to run it again.
--
-- AFTER RUNNING, ALSO DO:
--   - Delete contents of  storage/framework/views/   (clear blade cache)
-- ─────────────────────────────────────────────────────────────────────────────


-- ── STEP 1: Add the column, FK and index (idempotent) ───────────────────────

SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name   = 'expenses'
      AND column_name  = 'supplier_id'
);

SET @sql := IF(
    @col_exists = 0,
    'ALTER TABLE `expenses` ADD COLUMN `supplier_id` BIGINT UNSIGNED NULL AFTER `vendor`',
    'SELECT "supplier_id already exists — skipping ALTER" AS note'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
    SELECT COUNT(*) FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name   = 'expenses'
      AND index_name   = 'expenses_supplier_id_index'
);
SET @sql := IF(
    @idx_exists = 0,
    'CREATE INDEX `expenses_supplier_id_index` ON `expenses`(`supplier_id`)',
    'SELECT "supplier_id index already exists — skipping" AS note'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @fk_exists := (
    SELECT COUNT(*) FROM information_schema.key_column_usage
    WHERE table_schema    = DATABASE()
      AND table_name      = 'expenses'
      AND column_name     = 'supplier_id'
      AND referenced_table_name = 'suppliers'
);
SET @sql := IF(
    @fk_exists = 0,
    'ALTER TABLE `expenses`
       ADD CONSTRAINT `expenses_supplier_id_foreign`
       FOREIGN KEY (`supplier_id`) REFERENCES `suppliers`(`id`) ON DELETE SET NULL',
    'SELECT "supplier_id FK already exists — skipping" AS note'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;


-- ── STEP 2: Backfill suppliers table from legacy vendor strings ─────────────
--
-- Insert one supplier row per distinct non-empty vendor string that does not
-- already exist (case-insensitive name match). The PHP migration generates
-- 'LEG-XXXX-N' codes; this SQL version generates 'LEG-' + id-equivalent
-- using a session variable, then a final pass guarantees uniqueness.

INSERT INTO `suppliers` (`name`, `code`, `category`, `payment_terms_days`, `is_active`, `notes`, `created_at`, `updated_at`)
SELECT
    TRIM(e.vendor)                             AS name,
    CONCAT(
        'LEG-',
        UPPER(LEFT(REGEXP_REPLACE(TRIM(e.vendor), '[^A-Za-z0-9]', ''), 4)),
        '-',
        FLOOR(RAND() * 90000) + 10000          -- random suffix to avoid race collisions
    )                                          AS code,
    'General'                                  AS category,
    30                                         AS payment_terms_days,
    1                                          AS is_active,
    'Backfilled from legacy expense entries.'  AS notes,
    NOW(), NOW()
FROM (
    SELECT DISTINCT TRIM(vendor) AS vendor
    FROM `expenses`
    WHERE vendor IS NOT NULL AND TRIM(vendor) <> ''
) e
WHERE NOT EXISTS (
    SELECT 1 FROM `suppliers` s
    WHERE LOWER(s.name) = LOWER(TRIM(e.vendor))
);


-- ── STEP 3: Resolve expenses.supplier_id from matching supplier name ────────

UPDATE `expenses` e
JOIN `suppliers` s
  ON LOWER(TRIM(e.vendor)) = LOWER(s.name)
SET e.supplier_id = s.id
WHERE e.supplier_id IS NULL
  AND e.vendor IS NOT NULL
  AND TRIM(e.vendor) <> '';


-- ── STEP 4: Mark the migration as run so artisan won't re-run it later ──────
--
-- This must match the file basename (without .php).
-- Skip silently if the row is already there.

INSERT INTO `migrations` (`migration`, `batch`)
SELECT '2026_05_02_200000_link_expenses_to_suppliers',
       COALESCE((SELECT MAX(batch) FROM (SELECT batch FROM `migrations`) m), 0) + 1
WHERE NOT EXISTS (
    SELECT 1 FROM `migrations`
    WHERE migration = '2026_05_02_200000_link_expenses_to_suppliers'
);


-- ── DONE. Verify with: ──────────────────────────────────────────────────────
-- SELECT COUNT(*) AS expenses_with_supplier FROM expenses WHERE supplier_id IS NOT NULL;
-- SELECT COUNT(*) AS legacy_suppliers FROM suppliers WHERE notes LIKE 'Backfilled%';