-- StewardGrid Database Schema
-- Multi-Tenant Church Management Platform
-- MySQL 8+ / InnoDB / utf8mb4

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =====================================================
-- CORE PLATFORM TABLES (Global, no tenant_id)
-- =====================================================

-- Tenants (Churches)
CREATE TABLE IF NOT EXISTS `tenants` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `uuid` CHAR(36) UNIQUE NOT NULL,
    `church_name` VARCHAR(255) NOT NULL,
    `slug` VARCHAR(100) UNIQUE,
    `subdomain` VARCHAR(100) UNIQUE,
    `logo` VARCHAR(255),
    `favicon` VARCHAR(255),
    `primary_color` VARCHAR(20) DEFAULT '#4F46E5',
    `secondary_color` VARCHAR(20) DEFAULT '#6366F1',
    `accent_color` VARCHAR(20) DEFAULT '#10B981',
    `email` VARCHAR(255),
    `phone` VARCHAR(50),
    `address` TEXT,
    `country` VARCHAR(100) DEFAULT 'Ghana',
    `timezone` VARCHAR(50) DEFAULT 'Africa/Accra',
    `status` ENUM('active', 'suspended', 'trial', 'expired') DEFAULT 'active',
    `subscription_plan_id` BIGINT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_tenants_status` (`status`),
    INDEX `idx_tenants_subdomain` (`subdomain`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Subscription Plans
CREATE TABLE IF NOT EXISTS `subscription_plans` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `monthly_price` DECIMAL(12,2) DEFAULT 0.00,
    `member_limit` INT DEFAULT 0,
    `storage_limit` INT DEFAULT 0,
    `features` JSON,
    `status` ENUM('active', 'inactive') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tenant Subscriptions
CREATE TABLE IF NOT EXISTS `tenant_subscriptions` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `plan_id` BIGINT NOT NULL,
    `start_date` DATE NOT NULL,
    `end_date` DATE,
    `status` ENUM('active', 'expired', 'cancelled') DEFAULT 'active',
    `payment_reference` VARCHAR(255),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`plan_id`) REFERENCES `subscription_plans`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Subscription Payments
CREATE TABLE IF NOT EXISTS `subscription_payments` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `plan_id` BIGINT NOT NULL,
    `subscription_id` BIGINT,
    `amount` DECIMAL(12,2) NOT NULL,
    `payment_method` ENUM('cash', 'momo') DEFAULT 'cash',
    `momo_number` VARCHAR(20),
    `reference_number` VARCHAR(255),
    `payment_date` DATE NOT NULL,
    `status` ENUM('pending', 'confirmed', 'rejected') DEFAULT 'pending',
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_sub_payments_tenant` (`tenant_id`),
    INDEX `idx_sub_payments_date` (`payment_date`),
    INDEX `idx_sub_payments_status` (`status`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`plan_id`) REFERENCES `subscription_plans`(`id`),
    FOREIGN KEY (`subscription_id`) REFERENCES `tenant_subscriptions`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tenant Settings
CREATE TABLE IF NOT EXISTS `tenant_settings` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `setting_key` VARCHAR(100) NOT NULL,
    `setting_value` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_tenant_setting` (`tenant_id`, `setting_key`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- AUTHENTICATION & AUTHORIZATION
-- =====================================================

-- Roles
CREATE TABLE IF NOT EXISTS `roles` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT,
    `name` VARCHAR(100) NOT NULL,
    `description` TEXT,
    `is_system_role` BOOLEAN DEFAULT FALSE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_roles_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Permissions
CREATE TABLE IF NOT EXISTS `permissions` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `permission_key` VARCHAR(100) UNIQUE NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `module` VARCHAR(50),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_permissions_module` (`module`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Role-Permission Assignments
CREATE TABLE IF NOT EXISTS `role_permissions` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `role_id` BIGINT NOT NULL,
    `permission_id` BIGINT NOT NULL,
    UNIQUE KEY `uk_role_permission` (`role_id`, `permission_id`),
    FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`permission_id`) REFERENCES `permissions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Users
CREATE TABLE IF NOT EXISTS `users` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT,
    `uuid` CHAR(36) UNIQUE NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    `phone` VARCHAR(50),
    `password_hash` VARCHAR(255) NOT NULL,
    `role_id` BIGINT,
    `avatar` VARCHAR(255),
    `status` ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    `email_verified_at` TIMESTAMP NULL,
    `last_login` TIMESTAMP NULL,
    `must_change_password` BOOLEAN DEFAULT TRUE,
    `two_factor_enabled` BOOLEAN DEFAULT FALSE,
    `two_factor_secret` VARCHAR(255),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_user_email_tenant` (`email`, `tenant_id`),
    INDEX `idx_users_tenant` (`tenant_id`),
    INDEX `idx_users_status` (`status`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- User Permission Overrides
CREATE TABLE IF NOT EXISTS `user_permissions` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `user_id` BIGINT NOT NULL,
    `permission_id` BIGINT NOT NULL,
    `allowed` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_user_permission` (`user_id`, `permission_id`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`permission_id`) REFERENCES `permissions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Password Resets
CREATE TABLE IF NOT EXISTS `password_resets` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `user_id` BIGINT NOT NULL,
    `token_hash` VARCHAR(255) NOT NULL,
    `expires_at` TIMESTAMP NOT NULL,
    `used_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_password_resets_user` (`user_id`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Login Attempts
CREATE TABLE IF NOT EXISTS `login_attempts` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `email` VARCHAR(255) NOT NULL,
    `ip_address` VARCHAR(45) NOT NULL,
    `user_agent` TEXT,
    `attempted_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_login_attempts_email` (`email`, `attempted_at`),
    INDEX `idx_login_attempts_ip` (`ip_address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- API Tokens
CREATE TABLE IF NOT EXISTS `api_tokens` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `user_id` BIGINT NOT NULL,
    `name` VARCHAR(255),
    `token` VARCHAR(255) NOT NULL,
    `abilities` JSON,
    `last_used_at` TIMESTAMP NULL,
    `expires_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_api_token` (`token`),
    INDEX `idx_api_tokens_user` (`user_id`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- User MFA
CREATE TABLE IF NOT EXISTS `user_mfa` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `user_id` BIGINT NOT NULL,
    `secret` VARCHAR(255) NOT NULL,
    `enabled` BOOLEAN DEFAULT FALSE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- MEMBER MANAGEMENT
-- =====================================================

-- Branches
CREATE TABLE IF NOT EXISTS `branches` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `code` VARCHAR(50),
    `location` VARCHAR(255),
    `pastor_name` VARCHAR(255),
    `phone` VARCHAR(50),
    `email` VARCHAR(255),
    `status` ENUM('active', 'inactive') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_branches_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Members
CREATE TABLE IF NOT EXISTS `members` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `uuid` CHAR(36) UNIQUE NOT NULL,
    `ffn` VARCHAR(50) NOT NULL,
    `first_name` VARCHAR(255) NOT NULL,
    `middle_name` VARCHAR(255),
    `last_name` VARCHAR(255) NOT NULL,
    `gender` ENUM('Male', 'Female'),
    `date_of_birth` DATE,
    `profile_photo` VARCHAR(255),
    `portal_password` VARCHAR(255),
    `portal_enabled` BOOLEAN DEFAULT FALSE,
    `portal_last_login` TIMESTAMP NULL,
    `phone` VARCHAR(50),
    `whatsapp_number` VARCHAR(50),
    `email` VARCHAR(255),
    `nationality` VARCHAR(100) DEFAULT 'Ghanaian',
    `occupation` VARCHAR(255),
    `employer` VARCHAR(255),
    `address` TEXT,
    `city` VARCHAR(100),
    `gps_location` VARCHAR(100),
    `digital_address` VARCHAR(255),
    `landmark` TEXT,
    `google_maps_link` VARCHAR(500),
    `marital_status` ENUM('Single', 'Married', 'Divorced', 'Widowed'),
    `membership_status` VARCHAR(50) DEFAULT 'Active',
    `join_date` DATE,
    `baptism_date` DATE,
    `confirmation_date` DATE,
    `department_id` BIGINT,
    `branch_id` BIGINT,
    `notes` TEXT,
    `is_staff` BOOLEAN DEFAULT FALSE,
    `tithe_number` VARCHAR(50) UNIQUE,
    `created_by` BIGINT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL,
    UNIQUE KEY `uk_member_ffn_tenant` (`ffn`, `tenant_id`),
    INDEX `idx_members_tenant` (`tenant_id`),
    INDEX `idx_members_status` (`tenant_id`, `membership_status`),
    INDEX `idx_members_name` (`tenant_id`, `first_name`, `last_name`),
    INDEX `idx_members_phone` (`tenant_id`, `phone`),
    INDEX `idx_members_email` (`tenant_id`, `email`),
    INDEX `idx_members_deleted` (`deleted_at`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Spiritual Gifts
CREATE TABLE IF NOT EXISTS `spiritual_gifts` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `description` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_spiritual_gifts_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Member Spiritual Gifts
CREATE TABLE IF NOT EXISTS `member_spiritual_gifts` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `member_id` BIGINT NOT NULL,
    `spiritual_gift_id` BIGINT NOT NULL,
    UNIQUE KEY `uk_member_gift` (`member_id`, `spiritual_gift_id`),
    FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`spiritual_gift_id`) REFERENCES `spiritual_gifts`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Skills
CREATE TABLE IF NOT EXISTS `skills` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_skills_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Member Skills
CREATE TABLE IF NOT EXISTS `member_skills` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `member_id` BIGINT NOT NULL,
    `skill_id` BIGINT NOT NULL,
    `experience_level` ENUM('Beginner', 'Intermediate', 'Advanced', 'Expert') DEFAULT 'Intermediate',
    UNIQUE KEY `uk_member_skill` (`member_id`, `skill_id`),
    FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`skill_id`) REFERENCES `skills`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Families
CREATE TABLE IF NOT EXISTS `families` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `uuid` CHAR(36) UNIQUE NOT NULL,
    `family_name` VARCHAR(255) NOT NULL,
    `address` TEXT,
    `phone` VARCHAR(50),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_families_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Family Members
CREATE TABLE IF NOT EXISTS `family_members` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `family_id` BIGINT NOT NULL,
    `member_id` BIGINT NOT NULL,
    `relationship` ENUM('Head', 'Spouse', 'Child', 'Dependent', 'Relative') NOT NULL,
    `is_head` BOOLEAN DEFAULT FALSE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_family_member` (`family_id`, `member_id`),
    FOREIGN KEY (`family_id`) REFERENCES `families`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Visitors
CREATE TABLE IF NOT EXISTS `visitors` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `first_name` VARCHAR(255) NOT NULL,
    `middle_name` VARCHAR(255),
    `last_name` VARCHAR(255),
    `gender` ENUM('Male','Female','Unknown') DEFAULT 'Unknown',
    `age_group` VARCHAR(50),
    `phone` VARCHAR(50),
    `whatsapp_number` VARCHAR(50),
    `alternative_phone` VARCHAR(50),
    `email` VARCHAR(255),
    `residential_address` TEXT,
    `town` VARCHAR(255),
    `digital_address` VARCHAR(255),
    `occupation` VARCHAR(255),
    `marital_status` ENUM('Single','Married','Divorced','Widowed','Unknown') DEFAULT 'Unknown',
    `visit_date` DATE NOT NULL,
    `church_category` ENUM('adult','316','youth','children','general') DEFAULT 'general',
    `invited_by` VARCHAR(255),
    `invited_by_member_id` BIGINT,
    `invited_by_member_name` VARCHAR(255),
    `invited_by_member_ffn` VARCHAR(50),
    `pastor` VARCHAR(255),
    `social_media` VARCHAR(255),
    `how_did_you_hear` ENUM('Radio','Friend','Facebook','Instagram','YouTube','Outreach','Walk-in','Other'),
    `prayer_request` TEXT,
    `consent_followup` BOOLEAN DEFAULT TRUE,
    `follow_up_status` VARCHAR(50) DEFAULT 'New',
    `conversion_status` ENUM('pending','converted','closed') DEFAULT 'pending',
    `converted_member_id` BIGINT,
    `converted_at` DATE,
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_visitors_tenant` (`tenant_id`),
    INDEX `idx_visitors_followup` (`follow_up_status`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`invited_by_member_id`) REFERENCES `members`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`converted_member_id`) REFERENCES `members`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Departments / Ministries
CREATE TABLE IF NOT EXISTS `departments` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `leader_id` BIGINT,
    `status` ENUM('active', 'inactive') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_departments_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`leader_id`) REFERENCES `members`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Member Departments
CREATE TABLE IF NOT EXISTS `member_departments` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `member_id` BIGINT NOT NULL,
    `department_id` BIGINT NOT NULL,
    `position` VARCHAR(255),
    `joined_date` DATE,
    UNIQUE KEY `uk_member_dept` (`member_id`, `department_id`),
    FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Small Groups / Cell Groups
CREATE TABLE IF NOT EXISTS `groups` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `type` VARCHAR(100),
    `leader_id` BIGINT,
    `location` VARCHAR(255),
    `meeting_day` VARCHAR(20),
    `status` ENUM('active', 'inactive') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_groups_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`leader_id`) REFERENCES `members`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Group Members
CREATE TABLE IF NOT EXISTS `group_members` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `group_id` BIGINT NOT NULL,
    `member_id` BIGINT NOT NULL,
    `role` VARCHAR(50) DEFAULT 'Member',
    `joined_date` DATE,
    UNIQUE KEY `uk_group_member` (`group_id`, `member_id`),
    FOREIGN KEY (`group_id`) REFERENCES `groups`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Member Documents
CREATE TABLE IF NOT EXISTS `documents` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `member_id` BIGINT NOT NULL,
    `file_name` VARCHAR(255) NOT NULL,
    `file_path` VARCHAR(500) NOT NULL,
    `file_type` VARCHAR(50),
    `file_size` INT,
    `uploaded_by` BIGINT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_documents_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Member Engagement Score
CREATE TABLE IF NOT EXISTS `member_engagement` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `member_id` BIGINT NOT NULL,
    `attendance_score` INT DEFAULT 0,
    `giving_score` INT DEFAULT 0,
    `activity_score` INT DEFAULT 0,
    `total_score` INT DEFAULT 0,
    `last_calculated` TIMESTAMP NULL,
    UNIQUE KEY `uk_member_engagement` (`tenant_id`, `member_id`),
    INDEX `idx_engagement_score` (`total_score`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Profile Change Requests
CREATE TABLE IF NOT EXISTS `profile_change_requests` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `member_id` BIGINT NOT NULL,
    `field_name` VARCHAR(100) NOT NULL,
    `old_value` TEXT,
    `new_value` TEXT,
    `status` ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    `approved_by` BIGINT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_profile_changes_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- EVENT & ATTENDANCE MANAGEMENT
-- =====================================================

-- Event Categories
CREATE TABLE IF NOT EXISTS `event_categories` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `description` TEXT,
    `color` VARCHAR(20),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_event_categories_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Events
CREATE TABLE IF NOT EXISTS `events` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `uuid` CHAR(36) UNIQUE NOT NULL,
    `parent_event_id` BIGINT,
    `category_id` BIGINT,
    `service_id` BIGINT,
    `title` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `banner_image` VARCHAR(255),
    `location` VARCHAR(255),
    `start_date` DATETIME NOT NULL,
    `end_date` DATETIME NOT NULL,
    `capacity` INT DEFAULT 0,
    `registration_required` BOOLEAN DEFAULT FALSE,
    `allow_guest_registration` BOOLEAN DEFAULT FALSE,
    `status` ENUM('draft', 'published', 'completed', 'cancelled') DEFAULT 'draft',
    `created_by` BIGINT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_events_tenant` (`tenant_id`),
    INDEX `idx_events_dates` (`start_date`, `end_date`),
    INDEX `idx_events_status` (`status`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`category_id`) REFERENCES `event_categories`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Event Recurrences
CREATE TABLE IF NOT EXISTS `event_recurrences` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `event_id` BIGINT NOT NULL,
    `frequency` ENUM('daily', 'weekly', 'monthly', 'yearly') NOT NULL,
    `interval_count` INT DEFAULT 1,
    `day_of_week` VARCHAR(20),
    `end_date` DATE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`event_id`) REFERENCES `events`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Event Registration
CREATE TABLE IF NOT EXISTS `event_registrations` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `event_id` BIGINT NOT NULL,
    `member_id` BIGINT,
    `guest_name` VARCHAR(255),
    `guest_phone` VARCHAR(50),
    `guest_count` INT DEFAULT 1,
    `status` ENUM('registered', 'confirmed', 'attended', 'cancelled') DEFAULT 'registered',
    `registered_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_event_reg_tenant` (`tenant_id`),
    INDEX `idx_event_reg_event` (`event_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`event_id`) REFERENCES `events`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`member_id`) REFERENCES `members`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Services (Recurring Services like Sunday Service, Bible Study)
CREATE TABLE IF NOT EXISTS `services` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `service_type` VARCHAR(100),
    `service_category` ENUM('adult','youth','children','general') DEFAULT 'general',
    `coordinator_id` BIGINT,
    `day_of_week` VARCHAR(20),
    `start_time` TIME,
    `end_time` TIME,
    `location` VARCHAR(255),
    `status` ENUM('active', 'inactive') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_services_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Attendance Sessions
CREATE TABLE IF NOT EXISTS `attendance_sessions` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `event_id` BIGINT,
    `service_id` BIGINT,
    `category_id` BIGINT,
    `branch_id` BIGINT,
    `name` VARCHAR(255),
    `session_date` DATE NOT NULL,
    `start_time` TIME,
    `end_time` TIME,
    `location` VARCHAR(255),
    `created_by` BIGINT,
    `status` ENUM('active', 'closed') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_att_sessions_tenant` (`tenant_id`),
    INDEX `idx_att_sessions_date` (`session_date`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`event_id`) REFERENCES `events`(`id`),
    FOREIGN KEY (`service_id`) REFERENCES `services`(`id`),
    FOREIGN KEY (`category_id`) REFERENCES `attendance_categories`(`id`),
    FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Attendance Records
CREATE TABLE IF NOT EXISTS `attendance_records` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `session_id` BIGINT NOT NULL,
    `category_id` BIGINT,
    `member_id` BIGINT,
    `visitor_id` BIGINT,
    `status` ENUM('present','late','excused','visitor','first_timer') DEFAULT 'present',
    `attendance_time` DATETIME,
    `branch_id` BIGINT,
    `remarks` TEXT,
    `checked_by` BIGINT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_att_records_tenant` (`tenant_id`),
    INDEX `idx_att_records_session` (`session_id`),
    INDEX `idx_att_records_member` (`member_id`),
    UNIQUE KEY `uk_attendance_member_session` (`tenant_id`, `member_id`, `session_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`session_id`) REFERENCES `attendance_sessions`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`category_id`) REFERENCES `attendance_categories`(`id`),
    FOREIGN KEY (`member_id`) REFERENCES `members`(`id`),
    FOREIGN KEY (`branch_id`) REFERENCES `branches`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Attendance Categories (Adult, 316, Youth, Children)
CREATE TABLE IF NOT EXISTS `attendance_categories` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `name` VARCHAR(100) 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,
    INDEX `idx_att_cat_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Attendance Counts (Headcount for large services)
CREATE TABLE IF NOT EXISTS `attendance_counts` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `session_id` BIGINT NOT NULL,
    `adults` INT DEFAULT 0,
    `children` INT DEFAULT 0,
    `youth` INT DEFAULT 0,
    `visitors` INT DEFAULT 0,
    `total` INT DEFAULT 0,
    `recorded_by` BIGINT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`session_id`) REFERENCES `attendance_sessions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Attendance Audit Logs
CREATE TABLE IF NOT EXISTS `attendance_audit_logs` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `attendance_record_id` BIGINT,
    `action` VARCHAR(50) NOT NULL,
    `old_values` JSON,
    `new_values` JSON,
    `changed_by` BIGINT,
    `ip_address` VARCHAR(45),
    `user_agent` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_audit_tenant` (`tenant_id`),
    INDEX `idx_audit_record` (`attendance_record_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Absentee Alerts
CREATE TABLE IF NOT EXISTS `absentee_alerts` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `member_id` BIGINT NOT NULL,
    `absence_count` INT DEFAULT 0,
    `last_attendance_date` DATE,
    `assigned_to` BIGINT,
    `status` ENUM('new', 'contacted', 'resolved') DEFAULT 'new',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Event Volunteers
CREATE TABLE IF NOT EXISTS `event_volunteers` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `event_id` BIGINT NOT NULL,
    `member_id` BIGINT NOT NULL,
    `role` VARCHAR(100),
    `assigned_by` BIGINT,
    `status` ENUM('assigned', 'confirmed', 'attended') DEFAULT 'assigned',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`event_id`) REFERENCES `events`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Resources
CREATE TABLE IF NOT EXISTS `resources` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `type` VARCHAR(100),
    `quantity` INT DEFAULT 1,
    `status` ENUM('available', 'in_use', 'maintenance') DEFAULT 'available',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- FINANCIAL MANAGEMENT
-- =====================================================

-- Chart of Accounts
CREATE TABLE IF NOT EXISTS `financial_accounts` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `account_code` VARCHAR(20) NOT NULL,
    `account_name` VARCHAR(255) NOT NULL,
    `account_type` ENUM('income', 'expense', 'asset', 'liability', 'equity') NOT NULL,
    `parent_id` BIGINT,
    `description` TEXT,
    `status` ENUM('active', 'inactive') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_fin_accounts_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Funds
CREATE TABLE IF NOT EXISTS `funds` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `restricted` BOOLEAN DEFAULT FALSE,
    `status` ENUM('active', 'inactive') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_funds_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Payment Categories
CREATE TABLE IF NOT EXISTS `payment_categories` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `type` ENUM('tithe', 'offering', 'donation', 'pledge', 'project', 'other') NOT NULL,
    `description` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_payment_cats_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Financial Transactions (Central Ledger)
CREATE TABLE IF NOT EXISTS `financial_transactions` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `transaction_number` VARCHAR(50) UNIQUE NOT NULL,
    `transaction_type` ENUM('tithe', 'offering', 'donation', 'pledge_payment', 'project_payment', 'expense') NOT NULL,
    `category_id` BIGINT,
    `fund_id` BIGINT,
    `member_id` BIGINT,
    `account_id` BIGINT,
    `amount` DECIMAL(12,2) NOT NULL,
    `payment_method` ENUM('cash', 'cheque', 'mobile_money', 'bank_transfer', 'card', 'online') DEFAULT 'cash',
    `reference_number` VARCHAR(255),
    `description` TEXT,
    `transaction_date` DATE NOT NULL,
    `status` ENUM('pending', 'verified', 'approved', 'rejected') DEFAULT 'pending',
    `created_by` BIGINT,
    `approved_by` BIGINT,
    `approved_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL,
    INDEX `idx_fin_trans_tenant` (`tenant_id`),
    INDEX `idx_fin_trans_date` (`transaction_date`),
    INDEX `idx_fin_trans_type` (`transaction_type`),
    INDEX `idx_fin_trans_member` (`member_id`),
    INDEX `idx_fin_trans_status` (`status`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`member_id`) REFERENCES `members`(`id`),
    FOREIGN KEY (`category_id`) REFERENCES `payment_categories`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tithe Records
CREATE TABLE IF NOT EXISTS `tithes` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `member_id` BIGINT NULL,
    `transaction_id` BIGINT,
    `month` TINYINT NOT NULL,
    `year` SMALLINT NOT NULL,
    `amount` DECIMAL(12,2) NOT NULL,
    `status` ENUM('paid', 'unpaid') DEFAULT 'paid',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_tithe_member_month` (`member_id`, `month`, `year`),
    INDEX `idx_tithes_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 (`transaction_id`) REFERENCES `financial_transactions`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Offerings
CREATE TABLE IF NOT EXISTS `offerings` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `session_id` BIGINT,
    `offering_date` DATE NOT NULL,
    `service_type` VARCHAR(100),
    `offering_category` ENUM('first','second','thanksgiving','other') DEFAULT 'other',
    `cash_amount` DECIMAL(12,2) DEFAULT 0.00,
    `cheque_amount` DECIMAL(12,2) DEFAULT 0.00,
    `mobile_money_amount` DECIMAL(12,2) DEFAULT 0.00,
    `bank_amount` DECIMAL(12,2) DEFAULT 0.00,
    `total_amount` DECIMAL(12,2) DEFAULT 0.00,
    `counted_by` BIGINT,
    `verified_by` BIGINT,
    `status` ENUM('draft', 'pending_verification', 'verified', 'posted') DEFAULT 'draft',
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_offerings_tenant` (`tenant_id`),
    INDEX `idx_offerings_date` (`offering_date`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Donations
CREATE TABLE IF NOT EXISTS `donations` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `member_id` BIGINT,
    `fund_id` BIGINT,
    `project_id` BIGINT,
    `amount` DECIMAL(12,2) NOT NULL,
    `payment_method` VARCHAR(50),
    `payment_date` DATE NOT NULL,
    `anonymous` BOOLEAN DEFAULT FALSE,
    `reference` VARCHAR(255),
    `receipt_number` VARCHAR(50),
    `notes` TEXT,
    `status` ENUM('pending', 'confirmed', 'cancelled') DEFAULT 'pending',
    `created_by` BIGINT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_donations_tenant` (`tenant_id`),
    INDEX `idx_donations_project` (`project_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`member_id`) REFERENCES `members`(`id`),
    FOREIGN KEY (`fund_id`) REFERENCES `funds`(`id`),
    FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Pledge Campaigns
CREATE TABLE IF NOT EXISTS `pledge_campaigns` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `target_amount` DECIMAL(12,2) DEFAULT 0.00,
    `start_date` DATE NOT NULL,
    `end_date` DATE,
    `status` ENUM('active', 'completed', 'cancelled') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_pledge_camp_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Member Pledges
CREATE TABLE IF NOT EXISTS `member_pledges` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `campaign_id` BIGINT NOT NULL,
    `member_id` BIGINT NOT NULL,
    `pledged_amount` DECIMAL(12,2) NOT NULL,
    `fulfilled_amount` DECIMAL(12,2) DEFAULT 0.00,
    `start_date` DATE,
    `end_date` DATE,
    `status` ENUM('active', 'completed', 'cancelled', 'expired') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_member_pledges_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`campaign_id`) REFERENCES `pledge_campaigns`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Projects
CREATE TABLE IF NOT EXISTS `projects` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `goal_amount` DECIMAL(12,2) DEFAULT 0.00,
    `raised_amount` DECIMAL(12,2) DEFAULT 0.00,
    `start_date` DATE,
    `end_date` DATE,
    `status` ENUM('planning', 'active', 'completed', 'cancelled') DEFAULT 'planning',
    `created_by` BIGINT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_projects_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Expenses
CREATE TABLE IF NOT EXISTS `expenses` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `account_id` BIGINT,
    `fund_id` BIGINT,
    `amount` DECIMAL(12,2) NOT NULL,
    `expense_date` DATE NOT NULL,
    `description` TEXT,
    `vendor` VARCHAR(255),
    `receipt_file` VARCHAR(255),
    `created_by` BIGINT,
    `approved_by` BIGINT,
    `status` ENUM('draft', 'pending_approval', 'approved', 'rejected', 'paid') DEFAULT 'draft',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_expenses_tenant` (`tenant_id`),
    INDEX `idx_expenses_status` (`status`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`account_id`) REFERENCES `financial_accounts`(`id`),
    FOREIGN KEY (`fund_id`) REFERENCES `funds`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Budgets
CREATE TABLE IF NOT EXISTS `budgets` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `account_id` BIGINT NOT NULL,
    `year` SMALLINT NOT NULL,
    `allocated_amount` DECIMAL(12,2) DEFAULT 0.00,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_budget_account_year` (`account_id`, `year`),
    INDEX `idx_budgets_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`account_id`) REFERENCES `financial_accounts`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bank Accounts
CREATE TABLE IF NOT EXISTS `bank_accounts` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `bank_name` VARCHAR(255) NOT NULL,
    `account_number` VARCHAR(255),
    `currency` VARCHAR(10) DEFAULT 'GHS',
    `status` ENUM('active', 'inactive') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_bank_accounts_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bank Transactions
CREATE TABLE IF NOT EXISTS `bank_transactions` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `bank_account_id` BIGINT NOT NULL,
    `transaction_date` DATE NOT NULL,
    `description` VARCHAR(255),
    `reference` VARCHAR(255),
    `amount` DECIMAL(12,2) NOT NULL,
    `type` ENUM('credit', 'debit') NOT NULL,
    `matched_status` ENUM('unmatched', 'matched', 'needs_review') DEFAULT 'unmatched',
    `matched_transaction_id` BIGINT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_bank_trans_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`bank_account_id`) REFERENCES `bank_accounts`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Receipts
CREATE TABLE IF NOT EXISTS `receipts` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `transaction_id` BIGINT NOT NULL,
    `receipt_number` VARCHAR(50) UNIQUE NOT NULL,
    `pdf_path` VARCHAR(255),
    `generated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_receipts_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`transaction_id`) REFERENCES `financial_transactions`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Payment Submissions (Member Portal declarations)
CREATE TABLE IF NOT EXISTS `payment_submissions` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `member_id` BIGINT NOT NULL,
    `payment_type` VARCHAR(50) NOT NULL,
    `amount` DECIMAL(12,2) NOT NULL,
    `method` VARCHAR(50),
    `reference` VARCHAR(255),
    `proof_file` VARCHAR(255),
    `status` ENUM('pending', 'verified', 'rejected') DEFAULT 'pending',
    `verified_by` BIGINT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_pay_subs_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`member_id`) REFERENCES `members`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- PASTORAL CARE
-- =====================================================

-- Prayer Requests
CREATE TABLE IF NOT EXISTS `prayer_requests` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `uuid` CHAR(36) UNIQUE NOT NULL,
    `member_id` BIGINT,
    `title` VARCHAR(255),
    `request_text` TEXT,
    `category` VARCHAR(100),
    `urgency` ENUM('normal', 'important', 'urgent') DEFAULT 'normal',
    `visibility` ENUM('private', 'pastoral_team', 'public') DEFAULT 'pastoral_team',
    `status` ENUM('new', 'assigned', 'praying', 'contacted', 'completed', 'archived') DEFAULT 'new',
    `assigned_to` BIGINT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL,
    INDEX `idx_prayer_tenant` (`tenant_id`),
    INDEX `idx_prayer_status` (`status`),
    INDEX `idx_prayer_member` (`member_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`member_id`) REFERENCES `members`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Prayer Activity Log
CREATE TABLE IF NOT EXISTS `prayer_logs` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `prayer_request_id` BIGINT NOT NULL,
    `user_id` BIGINT NOT NULL,
    `action` VARCHAR(100),
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`prayer_request_id`) REFERENCES `prayer_requests`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Counselor Availability
CREATE TABLE IF NOT EXISTS `counselor_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,
    `status` ENUM('available', 'unavailable') DEFAULT 'available',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_counselor_avail_tenant` (`tenant_id`),
    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;

-- Counseling Appointments
CREATE TABLE IF NOT EXISTS `counseling_appointments` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `member_id` BIGINT NOT NULL,
    `counselor_id` BIGINT NOT NULL,
    `appointment_date` DATE NOT NULL,
    `start_time` TIME NOT NULL,
    `end_time` TIME,
    `purpose` TEXT,
    `status` ENUM('requested', 'approved', 'completed', 'cancelled', 'no_show') DEFAULT 'requested',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_counseling_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`member_id`) REFERENCES `members`(`id`),
    FOREIGN KEY (`counselor_id`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Counseling Notes (Encrypted)
CREATE TABLE IF NOT EXISTS `counseling_notes` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `appointment_id` BIGINT NOT NULL,
    `author_id` BIGINT NOT NULL,
    `encrypted_notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`appointment_id`) REFERENCES `counseling_appointments`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`author_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Pastoral Notes (Encrypted)
CREATE TABLE IF NOT EXISTS `pastoral_notes` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `member_id` BIGINT NOT NULL,
    `created_by` BIGINT NOT NULL,
    `note_type` VARCHAR(100),
    `content_encrypted` TEXT,
    `visibility` ENUM('private', 'pastoral_team', 'assigned_only') DEFAULT 'pastoral_team',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_pastoral_notes_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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Member Visits
CREATE TABLE IF NOT EXISTS `member_visits` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `member_id` BIGINT NOT NULL,
    `visited_by` BIGINT,
    `visit_type` ENUM('home', 'hospital', 'new_member', 'follow_up', 'pastoral') DEFAULT 'pastoral',
    `visit_date` DATE NOT NULL,
    `location` VARCHAR(255),
    `summary` TEXT,
    `next_action` TEXT,
    `status` ENUM('planned', 'completed', 'cancelled') DEFAULT 'planned',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_visits_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Visitor Follow-ups
CREATE TABLE IF NOT EXISTS `visitor_followups` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `visitor_id` BIGINT NOT NULL,
    `followup_number` INT NOT NULL,
    `followup_type` ENUM('phone','sms','whatsapp','email','home_visit','church_visit','other','welcome','wednesday_encouragement','saturday_reminder','small_group','newcomers_class') DEFAULT 'phone',
    `followup_date` DATE NOT NULL,
    `outcome` ENUM('interested','not_interested','joined_cell','became_member','transferred','cannot_reach','pending') DEFAULT 'pending',
    `notes` TEXT,
    `assigned_to` BIGINT,
    `next_followup_date` DATE,
    `status` ENUM('pending','in_progress','completed','cancelled') DEFAULT 'pending',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_followup_visitor` (`visitor_id`),
    INDEX `idx_followup_assigned` (`assigned_to`, `status`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`visitor_id`) REFERENCES `visitors`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`assigned_to`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Follow-up Tasks
CREATE TABLE IF NOT EXISTS `follow_up_tasks` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `member_id` BIGINT,
    `visitor_id` BIGINT,
    `assigned_to` BIGINT,
    `task_type` VARCHAR(100),
    `title` VARCHAR(255),
    `description` TEXT,
    `priority` ENUM('low', 'normal', 'high', 'urgent') DEFAULT 'normal',
    `due_date` DATE,
    `status` ENUM('pending', 'in_progress', 'completed', 'cancelled') DEFAULT 'pending',
    `completed_at` TIMESTAMP NULL,
    `created_by` BIGINT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_tasks_tenant` (`tenant_id`),
    INDEX `idx_tasks_assigned` (`assigned_to`, `status`),
    INDEX `idx_tasks_due` (`due_date`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`member_id`) REFERENCES `members`(`id`),
    FOREIGN KEY (`visitor_id`) REFERENCES `visitors`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Care Cases (Crisis/Emergency)
CREATE TABLE IF NOT EXISTS `care_cases` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `member_id` BIGINT NOT NULL,
    `case_type` VARCHAR(100),
    `priority` ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    `assigned_to` BIGINT,
    `status` ENUM('open', 'in_progress', 'closed') DEFAULT 'open',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Member Milestones
CREATE TABLE IF NOT EXISTS `member_milestones` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `member_id` BIGINT NOT NULL,
    `type` VARCHAR(50),
    `date` DATE,
    `description` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- PASTORAL CARE MODULE EXPANSION
-- =====================================================

-- NOTE: The add-on pastoral schema below is handled by the migration files in
-- database/migrations and should not be re-run here as part of the base schema import.
-- This section was removed to keep the base schema import idempotent and compatible
-- with the project installation flow.

-- New: pastoral_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;

-- New: pastoral_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;

-- New: pastoral_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;

-- New: pastoral_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;

-- New: pastoral_followups
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;

-- New: 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;

-- New: 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;

-- =====================================================
-- COMMUNICATION ENGINE
-- =====================================================

-- Notifications
CREATE TABLE IF NOT EXISTS `notifications` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `user_id` BIGINT,
    `title` VARCHAR(255) NOT NULL,
    `message` TEXT,
    `type` VARCHAR(50),
    `link` VARCHAR(255),
    `read_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_notifications_user` (`user_id`, `read_at`),
    INDEX `idx_notifications_tenant` (`tenant_id`),
    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;

-- Announcements
CREATE TABLE IF NOT EXISTS `announcements` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `content` TEXT,
    `image` VARCHAR(255),
    `target_type` VARCHAR(50),
    `published_by` BIGINT,
    `publish_date` DATETIME,
    `status` ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_announcements_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Message Templates
CREATE TABLE IF NOT EXISTS `message_templates` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `channel` ENUM('whatsapp', 'sms', 'email', 'in_app') NOT NULL,
    `subject` VARCHAR(255),
    `body` TEXT NOT NULL,
    `variables` JSON,
    `status` ENUM('active', 'inactive') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_msg_templates_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- WhatsApp Settings
CREATE TABLE IF NOT EXISTS `whatsapp_settings` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `phone_number_id` VARCHAR(100),
    `business_account_id` VARCHAR(100),
    `access_token_encrypted` TEXT,
    `status` ENUM('active', 'inactive') DEFAULT 'inactive',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_whatsapp_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Message Queue
CREATE TABLE IF NOT EXISTS `message_queue` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `recipient` VARCHAR(255) NOT NULL,
    `channel` ENUM('whatsapp', 'sms', 'email', 'in_app') NOT NULL,
    `template_id` BIGINT,
    `payload` JSON,
    `priority` ENUM('high', 'normal', 'low') DEFAULT 'normal',
    `status` ENUM('pending', 'processing', 'sent', 'failed') DEFAULT 'pending',
    `attempts` INT DEFAULT 0,
    `max_attempts` INT DEFAULT 3,
    `scheduled_at` TIMESTAMP NULL,
    `sent_at` TIMESTAMP NULL,
    `error` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_msg_queue_tenant` (`tenant_id`),
    INDEX `idx_msg_queue_status` (`status`, `priority`),
    INDEX `idx_msg_queue_scheduled` (`scheduled_at`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Message Campaigns
CREATE TABLE IF NOT EXISTS `message_campaigns` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `channel` ENUM('whatsapp', 'sms', 'email') NOT NULL,
    `target_filter` JSON,
    `template_id` BIGINT,
    `scheduled_at` TIMESTAMP NULL,
    `status` ENUM('draft', 'scheduled', 'processing', 'completed', 'cancelled') DEFAULT 'draft',
    `created_by` BIGINT,
    `completed_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_campaigns_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Campaign Recipients
CREATE TABLE IF NOT EXISTS `campaign_recipients` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `campaign_id` BIGINT NOT NULL,
    `member_id` BIGINT,
    `contact` VARCHAR(255),
    `status` ENUM('pending', 'sent', 'failed') DEFAULT 'pending',
    `error` TEXT,
    `sent_at` TIMESTAMP NULL,
    FOREIGN KEY (`campaign_id`) REFERENCES `message_campaigns`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`member_id`) REFERENCES `members`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Communication Logs
CREATE TABLE IF NOT EXISTS `communication_logs` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `member_id` BIGINT,
    `channel` ENUM('whatsapp', 'sms', 'email', 'in_app') NOT NULL,
    `message` TEXT,
    `provider_response` TEXT,
    `status` ENUM('sent', 'delivered', 'failed') DEFAULT 'sent',
    `sent_by` BIGINT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_comm_logs_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Communication Audit
CREATE TABLE IF NOT EXISTS `communication_audit_logs` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `user_id` BIGINT,
    `action` VARCHAR(100),
    `campaign_id` BIGINT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Member Notification Preferences
CREATE TABLE IF NOT EXISTS `member_notification_preferences` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `member_id` BIGINT NOT NULL,
    `whatsapp_enabled` BOOLEAN DEFAULT TRUE,
    `sms_enabled` BOOLEAN DEFAULT FALSE,
    `email_enabled` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_member_notif_prefs` (`member_id`),
    FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- ANALYTICS & REPORTING
-- =====================================================

-- Analytics Daily Summary
CREATE TABLE IF NOT EXISTS `analytics_daily_summary` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `date` DATE NOT NULL,
    `members_count` INT DEFAULT 0,
    `new_members` INT DEFAULT 0,
    `attendance_count` INT DEFAULT 0,
    `visitors_count` INT DEFAULT 0,
    `income_total` DECIMAL(12,2) DEFAULT 0.00,
    `expense_total` DECIMAL(12,2) DEFAULT 0.00,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_analytics_date` (`tenant_id`, `date`),
    INDEX `idx_analytics_tenant` (`tenant_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dashboard Preferences
CREATE TABLE IF NOT EXISTS `dashboard_preferences` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `user_id` BIGINT NOT NULL,
    `widget_name` VARCHAR(100),
    `position` INT,
    `enabled` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    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;

-- Scheduled Reports
CREATE TABLE IF NOT EXISTS `scheduled_reports` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `user_id` BIGINT NOT NULL,
    `report_type` VARCHAR(100),
    `frequency` ENUM('daily', 'weekly', 'monthly') NOT NULL,
    `recipients` JSON,
    `last_sent` TIMESTAMP NULL,
    `status` ENUM('active', 'paused') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    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;

-- =====================================================
-- SYSTEM TABLES
-- =====================================================

-- Audit Logs
CREATE TABLE IF NOT EXISTS `audit_logs` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT,
    `user_id` BIGINT,
    `action` VARCHAR(100) NOT NULL,
    `module` VARCHAR(50) NOT NULL,
    `record_id` BIGINT,
    `old_values` JSON,
    `new_values` JSON,
    `ip_address` VARCHAR(45),
    `user_agent` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_audit_tenant` (`tenant_id`),
    INDEX `idx_audit_user` (`user_id`),
    INDEX `idx_audit_action` (`action`, `module`),
    INDEX `idx_audit_created` (`created_at`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Scheduled Jobs
CREATE TABLE IF NOT EXISTS `scheduled_jobs` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT,
    `job_name` VARCHAR(100) NOT NULL,
    `last_run` TIMESTAMP NULL,
    `next_run` TIMESTAMP NULL,
    `status` ENUM('active', 'inactive', 'failed') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_scheduled_jobs_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Database Migrations
CREATE TABLE IF NOT EXISTS `migrations` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `migration_name` VARCHAR(255) NOT NULL,
    `executed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_migration_name` (`migration_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Pastoral Care Module Tables

-- Prayer Request Categories
CREATE TABLE IF NOT EXISTS `pastoral_prayer_categories` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `code` VARCHAR(50),
    `description` TEXT,
    `sort_order` INT DEFAULT 0,
    `status` ENUM('active', 'inactive') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_pr_cat_tenant` (`tenant_id`),
    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 8,
    `appointment_duration` INT DEFAULT 30,
    `break_start` TIME,
    `break_end` TIME,
    `status` ENUM('available', 'unavailable') DEFAULT 'available',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_pastoral_avail_tenant` (`tenant_id`),
    INDEX `idx_pastoral_avail_user` (`user_id`),
    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;

-- Pastor Availability Exceptions (vacation, blocked dates, etc.)
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') DEFAULT 'blocked',
    `reason` VARCHAR(255),
    `start_time` TIME,
    `end_time` TIME,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_pastoral_except_tenant` (`tenant_id`),
    INDEX `idx_pastoral_except_user` (`user_id`),
    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;

-- 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_pastoral_audit_tenant` (`tenant_id`),
    INDEX `idx_pastoral_audit_user` (`user_id`),
    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_care_timeline_tenant` (`tenant_id`),
    INDEX `idx_care_timeline_member` (`member_id`),
    INDEX `idx_care_timeline_date` (`event_date`),
    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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Pastoral Notifications
CREATE TABLE IF NOT EXISTS `pastoral_notifications` (
    `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
    `tenant_id` BIGINT NOT NULL,
    `user_id` BIGINT,
    `member_id` BIGINT,
    `title` VARCHAR(255) NOT NULL,
    `message` TEXT NOT NULL,
    `channel` ENUM('dashboard', 'email', 'whatsapp', 'sms', 'push') DEFAULT 'dashboard',
    `is_read` BOOLEAN DEFAULT FALSE,
    `read_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_pastoral_notif_tenant` (`tenant_id`),
    INDEX `idx_pastoral_notif_user` (`user_id`),
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Extend existing tables with new columns

-- prayer_requests: already extended via schema and manual migration
-- counseling_appointments: already extended via manual migration
-- member_visits: already extended via manual migration

SET FOREIGN_KEY_CHECKS = 1;