-- =====================================================
-- Pastoral Care Module Expansion Migration
-- Date: 2026-07-29
-- =====================================================

SET FOREIGN_KEY_CHECKS = 0;

-- =====================================================
-- ALTER: prayer_requests - Add new columns
-- =====================================================
ALTER TABLE `prayer_requests`
    ADD COLUMN `prayer_number` VARCHAR(50) UNIQUE AFTER `uuid`,
    ADD COLUMN `member_name` VARCHAR(255) AFTER `member_id`,
    ADD COLUMN `ffn` VARCHAR(50) AFTER `member_name`,
    ADD COLUMN `phone` VARCHAR(50) AFTER `ffn`,
    ADD COLUMN `branch` VARCHAR(255) AFTER `phone`,
    ADD COLUMN `cell_group` VARCHAR(255) AFTER `branch`,
    ADD COLUMN `department` VARCHAR(255) AFTER `cell_group`,
    ADD COLUMN `prayer_category` ENUM('healing','family','marriage','employment','financial','business','academic','spiritual_growth','deliverance','thanksgiving','other') DEFAULT 'other' AFTER `category`,
    ADD COLUMN `prayer_title` VARCHAR(255) AFTER `prayer_category`,
    ADD COLUMN `prayer_details` TEXT AFTER `prayer_title`,
    ADD COLUMN `confidentiality` ENUM('normal','confidential') DEFAULT 'normal' AFTER `request_text`,
    ADD COLUMN `prayer_mode` ENUM('pastor_alone','phone_call','whatsapp_voice','whatsapp_video','zoom','google_meet','in_person') DEFAULT 'pastor_alone' AFTER `confidentiality`,
    ADD COLUMN `preferred_date` DATE AFTER `prayer_mode`,
    ADD COLUMN `preferred_time` TIME AFTER `preferred_date`,
    ADD COLUMN `receive_followup` BOOLEAN DEFAULT TRUE AFTER `preferred_time`,
    ADD COLUMN `attachments` TEXT AFTER `receive_followup`,
    ADD COLUMN `assigned_pastor_id` BIGINT AFTER `attachments`,
    ADD COLUMN `prayed_at` DATETIME AFTER `assigned_pastor_id`,
    ADD COLUMN `answered_at` DATETIME AFTER `prayed_at`,
    ADD COLUMN `closed_at` DATETIME AFTER `answered_at`,
    ADD COLUMN `closed_by` BIGINT AFTER `closed_at`,
    ADD COLUMN `testimony` TEXT AFTER `closed_by`,
    ADD COLUMN `outcome` ENUM('pending','prayed','answered','closed') DEFAULT 'pending' AFTER `testimony`,
    ADD INDEX `idx_prayer_number` (`prayer_number`),
    ADD INDEX `idx_prayer_assigned` (`assigned_pastor_id`),
    ADD INDEX `idx_prayer_outcome` (`outcome`),
    ADD INDEX `idx_prayer_prayer_category` (`prayer_category`),
    ADD INDEX `idx_prayer_confidentiality` (`confidentiality`);

-- =====================================================
-- ALTER: counseling_appointments - Add new columns
-- =====================================================
ALTER TABLE `counseling_appointments`
    ADD COLUMN `appointment_number` VARCHAR(50) UNIQUE AFTER `uuid`,
    ADD COLUMN `appointment_type` ENUM('marriage','family','youth','career','business','spiritual','deliverance','bereavement','relationship','general') DEFAULT 'general' AFTER `purpose`,
    ADD COLUMN `meeting_mode` ENUM('office','phone','whatsapp','zoom','google_meet','home_visit') DEFAULT 'office' AFTER `appointment_type`,
    ADD COLUMN `reason` TEXT AFTER `meeting_mode`,
    ADD COLUMN `urgency` ENUM('low','medium','high','emergency') DEFAULT 'normal' AFTER `reason`,
    ADD COLUMN `counselor_id` BIGINT AFTER `urgency`,
    ADD COLUMN `notes` TEXT AFTER `counselor_id`,
    ADD COLUMN `reschedule_reason` TEXT AFTER `notes`,
    ADD COLUMN `completed_at` DATETIME AFTER `reschedule_reason`,
    ADD COLUMN `followup_required` BOOLEAN DEFAULT FALSE AFTER `completed_at`,
    ADD COLUMN `next_appointment_date` DATE AFTER `followup_required`,
    ADD INDEX `idx_counseling_number` (`appointment_number`),
    ADD INDEX `idx_counseling_type` (`appointment_type`),
    ADD INDEX `idx_counseling_meeting_mode` (`meeting_mode`),
    ADD INDEX `idx_counseling_urgency` (`urgency`),
    ADD INDEX `idx_counseling_counselor` (`counselor_id`),
    ADD INDEX `idx_counseling_next` (`next_appointment_date`);

-- =====================================================
-- ALTER: member_visits - Add new columns
-- =====================================================
ALTER TABLE `member_visits`
    ADD COLUMN `visit_number` VARCHAR(50) UNIQUE AFTER `id`,
    ADD COLUMN `requested_by` ENUM('member','pastor','follow_up_officer') DEFAULT 'pastor' AFTER `visited_by`,
    ADD COLUMN `reason` TEXT AFTER `requested_by`,
    ADD COLUMN `address` TEXT AFTER `reason`,
    ADD COLUMN `gps_coordinates` VARCHAR(255) AFTER `address`,
    ADD COLUMN `phone_number` VARCHAR(50) AFTER `gps_coordinates`,
    ADD COLUMN `preferred_date` DATE AFTER `phone_number`,
    ADD COLUMN `priority` ENUM('low','medium','high','critical') DEFAULT 'medium' AFTER `preferred_date`,
    ADD COLUMN `assigned_pastor_id` BIGINT AFTER `priority`,
    ADD COLUMN `outcome` ENUM('visited','not_home','rescheduled','cancelled','needs_further_care','prayer_offered','communion_served','counseling_done','referral_needed') DEFAULT 'visited' AFTER `assigned_pastor_id`,
    ADD COLUMN `notes` TEXT AFTER `outcome`,
    ADD COLUMN `pictures` TEXT AFTER `notes`,
    ADD COLUMN `google_maps_link` VARCHAR(500) AFTER `pictures`,
    ADD COLUMN `distance_km` DECIMAL(8,2) AFTER `google_maps_link`,
    ADD COLUMN `area` VARCHAR(255) AFTER `distance_km`,
    ADD COLUMN `branch_id` BIGINT AFTER `area`,
    ADD INDEX `idx_visits_number` (`visit_number`),
    ADD INDEX `idx_visits_requested_by` (`requested_by`),
    ADD INDEX `idx_visits_priority` (`priority`),
    ADD INDEX `idx_visits_outcome` (`outcome`),
    ADD INDEX `idx_visits_assigned` (`assigned_pastor_id`),
    ADD INDEX `idx_visits_branch` (`branch_id`);

-- =====================================================
-- NEW TABLES: Pastoral Care Module
-- =====================================================

-- Prayer Categories
CREATE TABLE IF NOT EXISTS `pastoral_prayer_categories` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `code` VARCHAR(50),
    `description` TEXT,
    `sort_order` INT DEFAULT 0,
    `status` ENUM('active','inactive') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_cat_tenant_code` (`tenant_id`, `code`),
    INDEX `idx_cat_tenant` (`tenant_id`),
    INDEX `idx_cat_status` (`status`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Pastor Availability
CREATE TABLE IF NOT EXISTS `pastoral_availability` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `user_id` BIGINT NOT NULL,
    `day` ENUM('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday') NOT NULL,
    `start_time` TIME NOT NULL,
    `end_time` TIME NOT NULL,
    `max_appointments` INT DEFAULT 10,
    `appointment_duration` INT DEFAULT 30,
    `break_start` TIME,
    `break_end` TIME,
    `status` ENUM('available','unavailable','limited') DEFAULT 'available',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_avail_tenant` (`tenant_id`),
    INDEX `idx_avail_user_day` (`user_id`, `day`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Availability Exceptions
CREATE TABLE IF NOT EXISTS `pastoral_availability_exceptions` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `user_id` BIGINT NOT NULL,
    `exception_date` DATE NOT NULL,
    `exception_type` ENUM('vacation','blocked','public_holiday','emergency','online_only','in_person_only','hybrid') NOT NULL,
    `reason` TEXT,
    `start_time` TIME,
    `end_time` TIME,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_except_tenant` (`tenant_id`),
    INDEX `idx_except_user_date` (`user_id`, `exception_date`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Visit Outcomes
CREATE TABLE IF NOT EXISTS `pastoral_visit_outcomes` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `visit_id` BIGINT NOT NULL,
    `outcome` ENUM('visited','not_home','rescheduled','cancelled','needs_further_care','prayer_offered','communion_served','counseling_done','referral_needed'),
    `notes` TEXT,
    `pictures` TEXT,
    `prayer_offered` BOOLEAN DEFAULT FALSE,
    `communion_served` BOOLEAN DEFAULT FALSE,
    `counseling_done` BOOLEAN DEFAULT FALSE,
    `referral_needed` BOOLEAN DEFAULT FALSE,
    `referral_details` TEXT,
    `next_action` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_voutcome_tenant` (`tenant_id`),
    INDEX `idx_voutcome_visit` (`visit_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`visit_id`) REFERENCES `member_visits`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Pastoral Follow-ups (integrates with existing follow_up_tasks)
CREATE TABLE IF NOT EXISTS `pastoral_followups` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `member_id` BIGINT,
    `visitor_id` BIGINT,
    `prayer_request_id` BIGINT,
    `counseling_id` BIGINT,
    `visit_id` BIGINT,
    `task_id` BIGINT,
    `assigned_to` BIGINT,
    `followup_type` ENUM('prayer','counseling','visit','task','check_in','testimony','welcome','discipleship','cell_group','other') DEFAULT 'task',
    `description` TEXT,
    `due_date` DATE,
    `reminder_date` DATE,
    `status` ENUM('pending','in_progress','completed','overdue','cancelled') DEFAULT 'pending',
    `completion_notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_followup_tenant` (`tenant_id`),
    INDEX `idx_followup_member` (`member_id`),
    INDEX `idx_followup_assigned` (`assigned_to`, `status`),
    INDEX `idx_followup_due` (`due_date`),
    INDEX `idx_followup_status` (`status`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`visitor_id`) REFERENCES `visitors`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`prayer_request_id`) REFERENCES `prayer_requests`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`assigned_to`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Pastoral Audit Logs
CREATE TABLE IF NOT EXISTS `pastoral_audit_logs` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `user_id` BIGINT NOT NULL,
    `action` VARCHAR(100) NOT NULL,
    `module` VARCHAR(50) NOT NULL,
    `record_id` BIGINT,
    `ip_address` VARCHAR(45),
    `user_agent` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_paudit_tenant` (`tenant_id`),
    INDEX `idx_paudit_user` (`user_id`),
    INDEX `idx_paudit_action` (`action`, `module`),
    INDEX `idx_paudit_created` (`created_at`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Member Care Timeline
CREATE TABLE IF NOT EXISTS `member_care_timeline` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `member_id` BIGINT NOT NULL,
    `event_type` ENUM('joined','baptized','became_worker','attendance','prayer_request','counseling','visit','converted','cell_group','follow_up','birthday','anniversary','tithe','note') NOT NULL,
    `event_date` DATE NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `related_id` BIGINT,
    `related_type` VARCHAR(50),
    `created_by` BIGINT,
    `is_private` BOOLEAN DEFAULT FALSE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_timeline_member` (`member_id`, `event_date`),
    INDEX `idx_timeline_type` (`event_type`),
    INDEX `idx_timeline_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;