-- =============================================================================
-- Follow-up Module — Phase A
-- Run date: 2026-04-27
-- Purpose: Extends `follow_ups` and creates `follow_up_attempts` for the
--          CRH daily relationship-tracker workflow.
--
-- Run this AFTER taking a backup of the `follow_ups` table (it should be
-- empty in production, but always back up before structural changes).
--
-- Safe to re-run: every ALTER uses IF NOT EXISTS where MySQL allows it.
-- The CREATE TABLE uses IF NOT EXISTS. If you need a clean re-run, run
-- the rollback at the bottom first.
-- =============================================================================

-- ── 1. Extend `follow_ups` ──────────────────────────────────────────────
ALTER TABLE `follow_ups`
    ADD COLUMN `trigger_type`        VARCHAR(30)      NULL AFTER `type`,
    ADD COLUMN `attempt_count`       TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER `trigger_type`,
    ADD COLUMN `next_attempt_due`    DATE             NULL AFTER `attempt_count`,
    ADD COLUMN `outcome`             VARCHAR(20)      NULL AFTER `response`,
    ADD COLUMN `closed_at`           TIMESTAMP        NULL AFTER `completed_at`,
    ADD COLUMN `review_eligible`     TINYINT(1)       NOT NULL DEFAULT 0 AFTER `closed_at`,
    ADD COLUMN `review_sent`         TINYINT(1)       NOT NULL DEFAULT 0 AFTER `review_eligible`,
    ADD COLUMN `review_sent_at`      TIMESTAMP        NULL AFTER `review_sent`,
    ADD COLUMN `complaint_type`      VARCHAR(30)      NULL AFTER `review_sent_at`,
    ADD COLUMN `complaint_severity`  VARCHAR(15)      NULL AFTER `complaint_type`,
    ADD COLUMN `complaint_notes`     TEXT             NULL AFTER `complaint_severity`,
    ADD COLUMN `department_snapshot` VARCHAR(50)      NULL AFTER `complaint_notes`,
    ADD COLUMN `clinician_snapshot`  VARCHAR(100)     NULL AFTER `department_snapshot`,
    ADD COLUMN `diagnosis_snapshot`  TEXT             NULL AFTER `clinician_snapshot`,
    ADD INDEX `follow_ups_status_due_idx`   (`status`, `due_date`),
    ADD INDEX `follow_ups_status_retry_idx` (`status`, `next_attempt_due`),
    ADD INDEX `follow_ups_outcome_idx`      (`outcome`, `closed_at`);

-- Backfill any pre-existing rows (defensive — likely empty).
UPDATE `follow_ups` SET `trigger_type` = 'visit_completed' WHERE `trigger_type` IS NULL;

-- ── 2. Create `follow_up_attempts` ──────────────────────────────────────
CREATE TABLE IF NOT EXISTS `follow_up_attempts` (
    `id`              BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `follow_up_id`    BIGINT UNSIGNED  NOT NULL,
    `attempt_number`  TINYINT UNSIGNED NOT NULL,
    `attempted_at`    TIMESTAMP        NOT NULL,
    `attempt_outcome` VARCHAR(20)      NOT NULL,
    `called_by`       BIGINT UNSIGNED  NULL,
    `notes`           TEXT             NULL,
    `created_at`      TIMESTAMP        NULL DEFAULT NULL,
    `updated_at`      TIMESTAMP        NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `follow_up_attempts_follow_up_id_idx` (`follow_up_id`),
    KEY `follow_up_attempts_attempted_at_idx` (`attempted_at`),
    CONSTRAINT `follow_up_attempts_follow_up_id_fk`
        FOREIGN KEY (`follow_up_id`) REFERENCES `follow_ups` (`id`) ON DELETE CASCADE,
    CONSTRAINT `follow_up_attempts_called_by_fk`
        FOREIGN KEY (`called_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 3. Register migration in `migrations` table ─────────────────────────
-- So Laravel doesn't try to re-run it via `php artisan migrate`.
-- Adjust batch number if you've run migrations after backup_pre_security_patch.
INSERT INTO `migrations` (`migration`, `batch`)
SELECT '2026_04_27_100000_create_follow_up_module_phase_a',
       COALESCE(MAX(`batch`), 0) + 1
FROM `migrations`
WHERE NOT EXISTS (
    SELECT 1 FROM `migrations`
    WHERE `migration` = '2026_04_27_100000_create_follow_up_module_phase_a'
);


-- =============================================================================
-- ROLLBACK — Run only if you need to undo this migration.
-- =============================================================================
-- DROP TABLE IF EXISTS `follow_up_attempts`;
-- ALTER TABLE `follow_ups`
--     DROP INDEX `follow_ups_status_due_idx`,
--     DROP INDEX `follow_ups_status_retry_idx`,
--     DROP INDEX `follow_ups_outcome_idx`,
--     DROP COLUMN `trigger_type`,
--     DROP COLUMN `attempt_count`,
--     DROP COLUMN `next_attempt_due`,
--     DROP COLUMN `outcome`,
--     DROP COLUMN `closed_at`,
--     DROP COLUMN `review_eligible`,
--     DROP COLUMN `review_sent`,
--     DROP COLUMN `review_sent_at`,
--     DROP COLUMN `complaint_type`,
--     DROP COLUMN `complaint_severity`,
--     DROP COLUMN `complaint_notes`,
--     DROP COLUMN `department_snapshot`,
--     DROP COLUMN `clinician_snapshot`,
--     DROP COLUMN `diagnosis_snapshot`;
-- DELETE FROM `migrations` WHERE `migration` = '2026_04_27_100000_create_follow_up_module_phase_a';