-- =====================================================================
-- TechAge MissionSuite - Foundation Schema (Milestone 1)
-- Enterprise Missionary & Ministry Management Platform
-- Engine: InnoDB, Charset: utf8mb4 / utf8mb4_unicode_ci throughout.
--
-- This covers only the Phase 1 foundation tables (auth, RBAC, branches,
-- settings, modules, notifications, audit trail). Feature-module tables
-- (missionaries, mission_fields, projects, events, sermons, donations,
-- volunteers, CMS content types, etc.) are added in their own migrations
-- as each module is built.
-- =====================================================================

SET FOREIGN_KEY_CHECKS = 0;
SET NAMES utf8mb4;

-- ---------------------------------------------------------------------
-- Branches (Enterprise Multi-Branch / Multi-Country scoping — see
-- App\Core\Model::$scopeColumns. A Basic/Professional install just has
-- the single default row seeded below and never surfaces this in the UI.)
-- ---------------------------------------------------------------------
CREATE TABLE `branches` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `country` VARCHAR(100) NULL,
  `city` VARCHAR(100) NULL,
  `is_default` TINYINT(1) NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- RBAC: roles / permissions / role_permissions
-- ---------------------------------------------------------------------
CREATE TABLE `roles` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL,
  `display_name` VARCHAR(100) NOT NULL,
  `description` VARCHAR(255) NULL,
  `is_system` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Seeded role -- cannot be deleted. super_administrator specifically also cannot have its permissions edited (it bypasses the permission table entirely in code).',
  `is_enabled` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Disabled roles are hidden from the user-creation role picker but existing holders keep the role.',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_roles_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `permissions` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `slug` VARCHAR(100) NOT NULL,
  `module` VARCHAR(50) NOT NULL,
  `description` VARCHAR(255) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_permissions_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `role_permissions` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `role_id` BIGINT UNSIGNED NOT NULL,
  `permission_id` BIGINT UNSIGNED NOT NULL,
  UNIQUE KEY `uq_role_permissions` (`role_id`, `permission_id`),
  CONSTRAINT `fk_role_permissions_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_role_permissions_permission` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Users
-- ---------------------------------------------------------------------
CREATE TABLE `users` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `role_id` BIGINT UNSIGNED NOT NULL,
  `branch_id` BIGINT UNSIGNED NULL,
  `name` VARCHAR(150) NOT NULL,
  `email` VARCHAR(150) NOT NULL,
  `phone` VARCHAR(30) NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `photo_path` VARCHAR(255) NULL,
  `status` ENUM('active','inactive','suspended') NOT NULL DEFAULT 'active',
  `failed_login_attempts` INT UNSIGNED NOT NULL DEFAULT 0,
  `locked_until` DATETIME NULL,
  `last_login_at` DATETIME NULL,
  `two_factor_enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `two_factor_secret` VARCHAR(100) NULL,
  `password_reset_token` VARCHAR(100) NULL,
  `password_reset_expires` DATETIME NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `updated_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL,
  UNIQUE KEY `uq_users_email` (`email`),
  KEY `idx_users_role` (`role_id`),
  KEY `idx_users_branch` (`branch_id`),
  KEY `idx_users_status` (`status`),
  CONSTRAINT `fk_users_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_users_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Settings (ministry profile, branding, SMTP, gateways -- key/value)
-- ---------------------------------------------------------------------
CREATE TABLE `settings` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `key` VARCHAR(100) NOT NULL,
  `value` TEXT NULL,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_settings_key` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Modules (edition feature-toggle manifest -- Basic/Professional/Enterprise)
-- ---------------------------------------------------------------------
CREATE TABLE `modules` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `slug` VARCHAR(60) NOT NULL,
  `name` VARCHAR(120) NOT NULL,
  `tier` ENUM('basic','professional','enterprise') NOT NULL DEFAULT 'basic',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_modules_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Newsletter (Communication Centre is a later phase; the public homepage's
-- subscribe form needs somewhere real to write to now rather than faking success)
-- ---------------------------------------------------------------------
CREATE TABLE `newsletter_subscribers` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `email` VARCHAR(150) NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `subscribed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_newsletter_subscribers_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Notifications
-- ---------------------------------------------------------------------
CREATE TABLE `notifications` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `title` VARCHAR(150) NOT NULL,
  `message` VARCHAR(500) NULL,
  `type` VARCHAR(30) NOT NULL DEFAULT 'info',
  `link` VARCHAR(255) NULL,
  `is_read` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_notifications_user` (`user_id`, `is_read`),
  CONSTRAINT `fk_notifications_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Audit / security trail
-- ---------------------------------------------------------------------
CREATE TABLE `security_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NULL,
  `identifier` VARCHAR(150) NOT NULL,
  `status` ENUM('success','failed','locked') NOT NULL,
  `reason` VARCHAR(255) NULL,
  `ip_address` VARCHAR(45) NULL,
  `user_agent` VARCHAR(255) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_security_logs_user` (`user_id`),
  KEY `idx_security_logs_created` (`created_at`),
  CONSTRAINT `fk_security_logs_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `audit_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NULL,
  `user_label` VARCHAR(200) NULL,
  `action` VARCHAR(50) NOT NULL,
  `module` VARCHAR(50) NOT NULL,
  `record_id` BIGINT UNSIGNED NULL,
  `description` VARCHAR(500) NULL,
  `old_values` JSON NULL,
  `new_values` JSON NULL,
  `ip_address` VARCHAR(45) NULL,
  `user_agent` VARCHAR(255) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_audit_logs_module` (`module`),
  KEY `idx_audit_logs_user` (`user_id`),
  KEY `idx_audit_logs_created` (`created_at`),
  CONSTRAINT `fk_audit_logs_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `error_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `error_code` VARCHAR(20) NULL,
  `exception_class` VARCHAR(150) NULL,
  `message` TEXT NULL,
  `file` VARCHAR(500) NULL,
  `line` INT NULL,
  `url` VARCHAR(500) NULL,
  `http_method` VARCHAR(10) NULL,
  `ip_address` VARCHAR(45) NULL,
  `user_id` BIGINT UNSIGNED NULL,
  `user_label` VARCHAR(200) NULL,
  `trace` LONGTEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- Milestone 2 -- Public Website + CMS
-- =====================================================================

-- ---------------------------------------------------------------------
-- Pages (admin-editable rich-content pages -- About, Vision, etc.)
-- ---------------------------------------------------------------------
CREATE TABLE `pages` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `slug` VARCHAR(120) NOT NULL,
  `title` VARCHAR(200) NOT NULL,
  `content` LONGTEXT NULL,
  `meta_title` VARCHAR(200) NULL,
  `meta_description` VARCHAR(300) NULL,
  `is_published` TINYINT(1) NOT NULL DEFAULT 1,
  `created_by` BIGINT UNSIGNED NULL,
  `updated_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_pages_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Sliders (homepage hero carousel)
-- ---------------------------------------------------------------------
CREATE TABLE `sliders` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(200) NOT NULL,
  `subtitle` VARCHAR(300) NULL,
  `image_path` VARCHAR(255) NULL,
  `link_url` VARCHAR(255) NULL,
  `link_text` VARCHAR(100) NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Testimonials (moderated: pending -> published/featured, or rejected)
-- ---------------------------------------------------------------------
CREATE TABLE `testimonials` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `type` ENUM('donor','missionary','partner_church','volunteer') NOT NULL DEFAULT 'donor',
  `role_title` VARCHAR(200) NULL,
  `quote` TEXT NOT NULL,
  `status` ENUM('pending','published','featured','rejected') NOT NULL DEFAULT 'pending',
  `email` VARCHAR(150) NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- FAQs (grouped by category, shown as an accordion on the public FAQ page)
-- ---------------------------------------------------------------------
CREATE TABLE `faq_categories` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(120) NOT NULL,
  `icon` VARCHAR(60) NOT NULL DEFAULT 'fa-circle-info',
  `sort_order` INT NOT NULL DEFAULT 0,
  UNIQUE KEY `uq_faq_categories_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `faqs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `category_id` BIGINT UNSIGNED NOT NULL,
  `question` VARCHAR(300) NOT NULL,
  `answer` TEXT NOT NULL,
  `is_published` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_faqs_category` (`category_id`),
  CONSTRAINT `fk_faqs_category` FOREIGN KEY (`category_id`) REFERENCES `faq_categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Leadership / team members (About page)
-- ---------------------------------------------------------------------
CREATE TABLE `team_members` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `role_title` VARCHAR(200) NOT NULL,
  `bio` VARCHAR(500) NULL,
  `photo_path` VARCHAR(255) NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Contact: office locations + inbound messages
-- ---------------------------------------------------------------------
CREATE TABLE `contact_offices` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `description` VARCHAR(300) NULL,
  `icon` VARCHAR(60) NOT NULL DEFAULT 'fa-building',
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `contact_messages` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `email` VARCHAR(150) NOT NULL,
  `phone` VARCHAR(30) NULL,
  `department` VARCHAR(100) NULL,
  `subject` VARCHAR(200) NOT NULL,
  `message` TEXT NOT NULL,
  `status` ENUM('new','read','replied','archived') NOT NULL DEFAULT 'new',
  `replied_message` TEXT NULL,
  `replied_by` BIGINT UNSIGNED NULL,
  `replied_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_contact_messages_status` (`status`),
  CONSTRAINT `fk_contact_messages_replied_by` FOREIGN KEY (`replied_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- Milestone 3 -- Missionary + Mission Field Management
-- mission_fields.coordinator_id and missionaries.mission_field_id reference
-- each other -- safe only because FOREIGN_KEY_CHECKS is disabled for the
-- whole of this import (see top of file) and both tables exist by the time
-- it's re-enabled below.
-- =====================================================================

CREATE TABLE `mission_fields` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `slug` VARCHAR(160) NOT NULL,
  `country` VARCHAR(100) NOT NULL,
  `region` VARCHAR(100) NULL,
  `icon` VARCHAR(60) NOT NULL DEFAULT 'fa-earth-africa',
  `latitude` VARCHAR(30) NULL,
  `longitude` VARCHAR(30) NULL,
  `coordinator_id` BIGINT UNSIGNED NULL,
  `description` TEXT NULL,
  `photo_path` VARCHAR(255) NULL,
  `status` ENUM('new','active','closed') NOT NULL DEFAULT 'new',
  `is_published` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_by` BIGINT UNSIGNED NULL,
  `updated_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_mission_fields_slug` (`slug`),
  KEY `idx_mission_fields_coordinator` (`coordinator_id`),
  CONSTRAINT `fk_mission_fields_coordinator` FOREIGN KEY (`coordinator_id`) REFERENCES `missionaries` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `missionaries` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `slug` VARCHAR(160) NOT NULL,
  `dispatch_code` VARCHAR(30) NOT NULL,
  `email` VARCHAR(150) NULL,
  `phone` VARCHAR(30) NULL,
  `role_title` VARCHAR(100) NOT NULL DEFAULT 'Missionary',
  `mission_field_id` BIGINT UNSIGNED NULL,
  `photo_path` VARCHAR(255) NULL,
  `bio` TEXT NULL,
  `status` ENUM('pending','active','furlough','closed') NOT NULL DEFAULT 'pending',
  `deployment_date` DATE NULL,
  `supervising_coordinator` VARCHAR(150) NULL,
  `monthly_support_need` DECIMAL(12,2) NULL,
  `support_percentage` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `support_notes` TEXT NULL,
  `last_report_date` DATE NULL,
  `is_published` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_by` BIGINT UNSIGNED NULL,
  `updated_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME NULL,
  UNIQUE KEY `uq_missionaries_slug` (`slug`),
  UNIQUE KEY `uq_missionaries_dispatch_code` (`dispatch_code`),
  KEY `idx_missionaries_field` (`mission_field_id`),
  CONSTRAINT `fk_missionaries_field` FOREIGN KEY (`mission_field_id`) REFERENCES `mission_fields` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- Milestone 4 -- Outreach & Project Management + Events Management
-- =====================================================================

CREATE TABLE `projects` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(200) NOT NULL,
  `slug` VARCHAR(210) NOT NULL,
  `category` VARCHAR(100) NOT NULL DEFAULT 'Community Development',
  `mission_field_id` BIGINT UNSIGNED NULL,
  `project_lead` VARCHAR(150) NULL,
  `description` TEXT NULL,
  `photo_path` VARCHAR(255) NULL,
  `budget_total` DECIMAL(14,2) NOT NULL DEFAULT 0,
  `amount_raised` DECIMAL(14,2) NOT NULL DEFAULT 0,
  `start_date` DATE NULL,
  `target_completion_date` DATE NULL,
  `status` ENUM('planning','active','completed') NOT NULL DEFAULT 'planning',
  `is_urgent` TINYINT(1) NOT NULL DEFAULT 0,
  `is_published` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_by` BIGINT UNSIGNED NULL,
  `updated_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_projects_slug` (`slug`),
  KEY `idx_projects_field` (`mission_field_id`),
  CONSTRAINT `fk_projects_field` FOREIGN KEY (`mission_field_id`) REFERENCES `mission_fields` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `project_milestones` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `project_id` BIGINT UNSIGNED NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `is_complete` TINYINT(1) NOT NULL DEFAULT 0,
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_project_milestones_project` (`project_id`),
  CONSTRAINT `fk_project_milestones_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `events` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(200) NOT NULL,
  `slug` VARCHAR(210) NOT NULL,
  `event_type` VARCHAR(100) NOT NULL DEFAULT 'Conference',
  `description` TEXT NULL,
  `photo_path` VARCHAR(255) NULL,
  `location` VARCHAR(200) NULL,
  `format` ENUM('in_person','online','hybrid') NOT NULL DEFAULT 'in_person',
  `start_at` DATETIME NOT NULL,
  `end_at` DATETIME NULL,
  `capacity` INT UNSIGNED NULL,
  `fee` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `registration_deadline` DATE NULL,
  `qr_checkin_enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `schedule_notes` TEXT NULL,
  `status` ENUM('upcoming','open','closed','cancelled') NOT NULL DEFAULT 'upcoming',
  `is_published` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_by` BIGINT UNSIGNED NULL,
  `updated_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_events_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `event_registrations` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `event_id` BIGINT UNSIGNED NOT NULL,
  `registration_code` VARCHAR(20) NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `email` VARCHAR(150) NOT NULL,
  `phone` VARCHAR(30) NULL,
  `attendees_count` INT UNSIGNED NOT NULL DEFAULT 1,
  `status` ENUM('registered','checked_in','cancelled') NOT NULL DEFAULT 'registered',
  `checked_in_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_event_registrations_code` (`registration_code`),
  KEY `idx_event_registrations_event` (`event_id`),
  CONSTRAINT `fk_event_registrations_event` FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Milestone 5 -- Sermons & Media Centre + Publications + Prayer Centre
CREATE TABLE `sermons` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(200) NOT NULL,
  `slug` VARCHAR(220) NOT NULL,
  `media_type` ENUM('video','audio','pdf') NOT NULL DEFAULT 'audio',
  `category` VARCHAR(60) NOT NULL DEFAULT 'Sermon',
  `speaker` VARCHAR(150) NULL,
  `description` TEXT NULL,
  `file_path` VARCHAR(255) NULL,
  `duration` VARCHAR(20) NULL,
  `publish_date` DATE NULL,
  `plays_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `is_published` TINYINT(1) NOT NULL DEFAULT 1,
  `created_by` BIGINT UNSIGNED NULL,
  `updated_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_sermons_slug` (`slug`),
  KEY `idx_sermons_type` (`media_type`),
  KEY `idx_sermons_category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `publications` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(200) NOT NULL,
  `slug` VARCHAR(220) NOT NULL,
  `category` VARCHAR(60) NOT NULL DEFAULT 'Newsletter',
  `author` VARCHAR(150) NULL,
  `summary` TEXT NULL,
  `file_path` VARCHAR(255) NULL,
  `cover_path` VARCHAR(255) NULL,
  `publish_date` DATE NULL,
  `downloads_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `status` ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
  `notify_subscribers` TINYINT(1) NOT NULL DEFAULT 0,
  `created_by` BIGINT UNSIGNED NULL,
  `updated_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_publications_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `prayer_requests` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `email` VARCHAR(150) NULL,
  `category` VARCHAR(60) NOT NULL DEFAULT 'Family',
  `message` TEXT NOT NULL,
  `status` ENUM('open','praying','answered','archived') NOT NULL DEFAULT 'open',
  `is_private` TINYINT(1) NOT NULL DEFAULT 0,
  `is_urgent` TINYINT(1) NOT NULL DEFAULT 0,
  `prayer_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `assigned_to` BIGINT UNSIGNED NULL,
  `response_note` TEXT NULL,
  `answered_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_prayer_status` (`status`),
  KEY `idx_prayer_category` (`category`),
  CONSTRAINT `fk_prayer_assigned_to` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Milestone 6 -- Donations & Partnership + Volunteer Management
CREATE TABLE `donations` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `donor_name` VARCHAR(150) NOT NULL,
  `donor_email` VARCHAR(150) NOT NULL,
  `is_anonymous` TINYINT(1) NOT NULL DEFAULT 0,
  `amount` DECIMAL(14,2) NOT NULL,
  `frequency` ENUM('one_time','monthly') NOT NULL DEFAULT 'one_time',
  `campaign` VARCHAR(150) NOT NULL DEFAULT 'General Fund',
  `project_id` BIGINT UNSIGNED NULL,
  `gateway` ENUM('paystack','manual') NOT NULL DEFAULT 'paystack',
  `payment_method` VARCHAR(30) NULL COMMENT 'card, bank_transfer, ussd, cash, cheque -- populated once known',
  `reference` VARCHAR(100) NOT NULL,
  `status` ENUM('pending','completed','failed','refunded') NOT NULL DEFAULT 'pending',
  `notes` VARCHAR(255) NULL,
  `received_at` DATE NULL,
  `created_by` BIGINT UNSIGNED NULL COMMENT 'set when logged manually by an admin',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_donations_reference` (`reference`),
  KEY `idx_donations_status` (`status`),
  KEY `idx_donations_email` (`donor_email`),
  CONSTRAINT `fk_donations_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `volunteers` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `email` VARCHAR(150) NOT NULL,
  `phone` VARCHAR(30) NULL,
  `home_church` VARCHAR(150) NULL,
  `department` VARCHAR(60) NOT NULL DEFAULT 'Church Planting',
  `primary_skill` VARCHAR(150) NULL,
  `additional_skills` TEXT NULL,
  `availability` ENUM('trips','local','both') NOT NULL DEFAULT 'both',
  `project_id` BIGINT UNSIGNED NULL,
  `event_id` BIGINT UNSIGNED NULL,
  `status` ENUM('pending','active','inactive') NOT NULL DEFAULT 'pending',
  `created_by` BIGINT UNSIGNED NULL,
  `updated_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_volunteers_status` (`status`),
  KEY `idx_volunteers_dept` (`department`),
  CONSTRAINT `fk_volunteers_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_volunteers_event` FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Milestone 7 -- Communication Centre + Reports & Analytics
CREATE TABLE `announcements` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(200) NOT NULL,
  `body` TEXT NOT NULL,
  `audience` ENUM('public','staff','both') NOT NULL DEFAULT 'both',
  `is_published` TINYINT(1) NOT NULL DEFAULT 1,
  `starts_at` DATE NULL,
  `ends_at` DATE NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_announcements_published` (`is_published`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `email_campaigns` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `subject` VARCHAR(200) NOT NULL,
  `body` TEXT NOT NULL,
  `audience` ENUM('newsletter','donors','volunteers') NOT NULL DEFAULT 'newsletter',
  `status` ENUM('draft','sending','sent','failed') NOT NULL DEFAULT 'draft',
  `recipient_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `sent_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `sent_at` DATETIME NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Lightweight real visitor analytics for the public site (Reports > Engagement tab).
-- Logged once per session per path per day from views/layouts/public.php.
CREATE TABLE `page_views` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `path` VARCHAR(255) NOT NULL,
  `referrer_host` VARCHAR(150) NULL,
  `session_id` VARCHAR(64) NOT NULL,
  `viewed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_page_views_path` (`path`),
  KEY `idx_page_views_session_date` (`session_id`, `viewed_at`),
  KEY `idx_page_views_date` (`viewed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Gallery: albums + images (public gallery page with lightbox)
CREATE TABLE `gallery_albums` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(150) NOT NULL,
  `slug` VARCHAR(160) NOT NULL,
  `description` TEXT NULL,
  `is_published` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_by` BIGINT UNSIGNED NULL,
  `updated_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_gallery_albums_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `gallery_images` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `album_id` BIGINT UNSIGNED NULL,
  `caption` VARCHAR(200) NULL,
  `image_path` VARCHAR(255) NOT NULL,
  `is_published` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_gallery_images_album` (`album_id`),
  CONSTRAINT `fk_gallery_images_album` FOREIGN KEY (`album_id`) REFERENCES `gallery_albums` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- About page: structured content so non-technical admins never touch raw HTML.
-- Hero/Mission/Vision text lives in `settings` (about_*); these three tables hold the
-- repeatable list sections (Core Values, Statement of Faith, Our Journey timeline).
CREATE TABLE `about_core_values` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(120) NOT NULL,
  `description` VARCHAR(400) NOT NULL,
  `icon` VARCHAR(60) NOT NULL DEFAULT 'fa-star',
  `sort_order` INT NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `about_faith_statements` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(120) NOT NULL,
  `description` VARCHAR(400) NOT NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `about_journey_milestones` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `year` VARCHAR(10) NOT NULL,
  `title` VARCHAR(150) NOT NULL,
  `description` VARCHAR(400) NOT NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- File Manager (shared document/media library, folder tree)
-- ---------------------------------------------------------------------
CREATE TABLE `file_manager_folders` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `parent_id` BIGINT UNSIGNED NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `idx_fmf_parent` (`parent_id`),
  CONSTRAINT `fk_fmf_parent` FOREIGN KEY (`parent_id`) REFERENCES `file_manager_folders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `file_manager_files` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `folder_id` BIGINT UNSIGNED NULL,
  `original_name` VARCHAR(255) NOT NULL,
  `file_path` VARCHAR(255) NOT NULL,
  `size` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `mime_type` VARCHAR(120) NULL,
  `uploaded_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_fmfile_folder` (`folder_id`),
  CONSTRAINT `fk_fmfile_folder` FOREIGN KEY (`folder_id`) REFERENCES `file_manager_folders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Live Streaming (embed-based -- admin pastes a YouTube/Vimeo URL or a
-- direct embed URL; MissionSuite does not host video itself)
-- ---------------------------------------------------------------------
CREATE TABLE `live_streams` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(200) NOT NULL,
  `description` TEXT NULL,
  `platform` ENUM('youtube','vimeo','custom') NOT NULL DEFAULT 'youtube',
  `embed_url` VARCHAR(500) NOT NULL,
  `scheduled_at` DATETIME NULL,
  `status` ENUM('scheduled','live','ended') NOT NULL DEFAULT 'scheduled',
  `is_published` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- REST API tokens (Bearer auth for /api/v1/*)
-- ---------------------------------------------------------------------
CREATE TABLE `api_tokens` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `token_hash` VARCHAR(64) NOT NULL COMMENT 'SHA-256 of the plaintext token -- plaintext is only ever shown once, at creation',
  `last_used_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_api_tokens_hash` (`token_hash`),
  KEY `idx_api_tokens_user` (`user_id`),
  CONSTRAINT `fk_api_tokens_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Multi-Branch scoping -- nullable everywhere so a single-branch install
-- (or any user not assigned to a branch) sees every row unscoped, per
-- App\Core\Model::activeScope()'s existing null-safe behavior.
-- ---------------------------------------------------------------------
ALTER TABLE `missionaries` ADD COLUMN `branch_id` BIGINT UNSIGNED NULL AFTER `mission_field_id`,
  ADD KEY `idx_missionaries_branch` (`branch_id`),
  ADD CONSTRAINT `fk_missionaries_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL;

ALTER TABLE `mission_fields` ADD COLUMN `branch_id` BIGINT UNSIGNED NULL AFTER `coordinator_id`,
  ADD KEY `idx_mission_fields_branch` (`branch_id`),
  ADD CONSTRAINT `fk_mission_fields_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL;

ALTER TABLE `projects` ADD COLUMN `branch_id` BIGINT UNSIGNED NULL AFTER `mission_field_id`,
  ADD KEY `idx_projects_branch` (`branch_id`),
  ADD CONSTRAINT `fk_projects_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL;

ALTER TABLE `events` ADD COLUMN `branch_id` BIGINT UNSIGNED NULL AFTER `event_type`,
  ADD KEY `idx_events_branch` (`branch_id`),
  ADD CONSTRAINT `fk_events_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL;

ALTER TABLE `donations` ADD COLUMN `branch_id` BIGINT UNSIGNED NULL AFTER `campaign`,
  ADD KEY `idx_donations_branch` (`branch_id`),
  ADD CONSTRAINT `fk_donations_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL;

ALTER TABLE `volunteers` ADD COLUMN `branch_id` BIGINT UNSIGNED NULL AFTER `department`,
  ADD KEY `idx_volunteers_branch` (`branch_id`),
  ADD CONSTRAINT `fk_volunteers_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL;

-- ---------------------------------------------------------------------
-- Sermons: let an admin paste a video/audio URL (YouTube, Vimeo, or a
-- directly-hosted file link) instead of uploading straight to server disk.
-- ---------------------------------------------------------------------
ALTER TABLE `sermons`
  ADD COLUMN `source_type` ENUM('file','url') NOT NULL DEFAULT 'file' AFTER `media_type`,
  ADD COLUMN `external_url` VARCHAR(500) NULL AFTER `file_path`;

SET FOREIGN_KEY_CHECKS = 1;