-- ============================================
-- KanisaIQ Database Schema
-- Church Communication Platform
-- Run this in phpMyAdmin or MySQL CLI
-- ============================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET NAMES utf8mb4;

-- -------------------------------------------
-- 1. CHURCHES (multi-tenant)
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS `churches` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `short_code` VARCHAR(20) NOT NULL UNIQUE,
  `branch` VARCHAR(100) DEFAULT 'Main Campus',
  `address` TEXT,
  `phone` VARCHAR(20),
  `email` VARCHAR(100),
  `pastor_name` VARCHAR(100),
  `logo_path` VARCHAR(255),
  `timezone` VARCHAR(50) DEFAULT 'Africa/Nairobi',
  `language_default` ENUM('en','sw') DEFAULT 'en',
  `mpesa_shortcode` VARCHAR(20),
  `mpesa_passkey` VARCHAR(255),
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `churches` (`name`, `short_code`, `branch`, `pastor_name`) VALUES
('Victory Faith Church in the Garden', 'VFC', 'Main Campus', 'Pastor');

-- -------------------------------------------
-- 2. MEMBERS
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS `members` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `church_id` INT NOT NULL DEFAULT 1,
  `member_number` VARCHAR(30) UNIQUE,
  `national_id` VARCHAR(30),
  `name` VARCHAR(150) NOT NULL,
  `whatsapp_number` VARCHAR(20),
  `email` VARCHAR(100),
  `area` VARCHAR(100),
  `branch` VARCHAR(100) DEFAULT 'Main Campus',
  `cell_group_id` INT DEFAULT NULL,
  `role` ENUM('member','leader','deacon','elder','pastor','admin','super_admin') DEFAULT 'member',
  `language_pref` ENUM('en','sw') DEFAULT 'en',
  `joined_date` DATE,
  `joined_via` ENUM('whatsapp','web','manual','walk_in') DEFAULT 'whatsapp',
  `how_found` VARCHAR(100),
  `is_active` TINYINT(1) DEFAULT 1,
  `is_new_member` TINYINT(1) DEFAULT 1,
  `is_demo` TINYINT(1) DEFAULT 0,
  `points` INT DEFAULT 0,
  `last_interaction` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_church` (`church_id`),
  INDEX `idx_whatsapp` (`whatsapp_number`),
  INDEX `idx_national_id` (`national_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------
-- 3. PRAYER REQUESTS
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS `prayer_requests` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `request_number` VARCHAR(30) NOT NULL UNIQUE,
  `church_id` INT NOT NULL DEFAULT 1,
  `member_id` INT,
  `whatsapp_number` VARCHAR(20),
  `member_name` VARCHAR(150),
  `request_text` TEXT NOT NULL,
  `type` ENUM('personal','congregational','urgent') DEFAULT 'personal',
  `sentiment_flag` ENUM('neutral','distress','medical','financial','thanksgiving','grief') DEFAULT 'neutral',
  `status` ENUM('received','prayed','assigned','closed') DEFAULT 'received',
  `assigned_to` INT DEFAULT NULL,
  `prayed_at` TIMESTAMP NULL,
  `follow_up_sent` TINYINT(1) DEFAULT 0,
  `follow_up_at` TIMESTAMP NULL,
  `notes` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_church` (`church_id`),
  INDEX `idx_member` (`member_id`),
  INDEX `idx_status` (`status`),
  INDEX `idx_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------
-- 4. APPOINTMENTS
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS `appointments` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `appt_number` VARCHAR(30) NOT NULL UNIQUE,
  `church_id` INT NOT NULL DEFAULT 1,
  `member_id` INT,
  `member_name` VARCHAR(150),
  `assignee_id` INT,
  `assignee_role` VARCHAR(50),
  `slot_date` DATE NOT NULL,
  `slot_time` TIME NOT NULL,
  `type` VARCHAR(100),
  `notes` TEXT,
  `status` ENUM('booked','confirmed','completed','cancelled','noshow') DEFAULT 'booked',
  `reminder_24h_sent` TINYINT(1) DEFAULT 0,
  `reminder_1h_sent` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_church` (`church_id`),
  INDEX `idx_member` (`member_id`),
  INDEX `idx_date` (`slot_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------
-- 5. OFFERINGS / GIVING
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS `offerings` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `transaction_ref` VARCHAR(30) NOT NULL UNIQUE,
  `church_id` INT NOT NULL DEFAULT 1,
  `member_id` INT,
  `member_name` VARCHAR(150),
  `whatsapp_number` VARCHAR(20),
  `type` ENUM('tithe','offering','building','missions','benevolence','special','pledge') NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `mpesa_receipt` VARCHAR(30),
  `mpesa_phone` VARCHAR(20),
  `status` ENUM('pending','completed','failed','reversed') DEFAULT 'pending',
  `transaction_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_church` (`church_id`),
  INDEX `idx_member` (`member_id`),
  INDEX `idx_type` (`type`),
  INDEX `idx_date` (`transaction_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------
-- 6. SERMONS
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS `sermons` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `church_id` INT NOT NULL DEFAULT 1,
  `title` VARCHAR(255) NOT NULL,
  `pastor_name` VARCHAR(100),
  `sermon_date` DATE NOT NULL,
  `audio_url` VARCHAR(500),
  `video_url` VARCHAR(500),
  `notes_pdf_path` VARCHAR(255),
  `transcript_text` LONGTEXT,
  `ai_summary` TEXT,
  `ai_key_points` JSON,
  `scripture_tags` JSON,
  `tags` VARCHAR(500),
  `is_published` TINYINT(1) DEFAULT 1,
  `broadcast_sent_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FULLTEXT KEY `ft_sermon` (`title`, `transcript_text`, `ai_summary`, `tags`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------
-- 7. EVENTS
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS `events` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `church_id` INT NOT NULL DEFAULT 1,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `event_date` DATE NOT NULL,
  `event_time` TIME,
  `end_date` DATE,
  `venue` VARCHAR(255),
  `zoom_link` VARCHAR(500),
  `branch` VARCHAR(100),
  `max_capacity` INT DEFAULT NULL,
  `rsvp_count` INT DEFAULT 0,
  `volunteer_count` INT DEFAULT 0,
  `reminder_sent` TINYINT(1) DEFAULT 0,
  `status` ENUM('upcoming','ongoing','past','cancelled') DEFAULT 'upcoming',
  `created_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_church` (`church_id`),
  INDEX `idx_date` (`event_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `event_rsvps` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `event_id` INT NOT NULL,
  `member_id` INT NOT NULL,
  `type` ENUM('attendee','volunteer') DEFAULT 'attendee',
  `status` ENUM('confirmed','cancelled') DEFAULT 'confirmed',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_event_member` (`event_id`, `member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------
-- 8. SPECIAL OCCASIONS
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS `special_occasions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `church_id` INT NOT NULL DEFAULT 1,
  `member_id` INT,
  `member_name` VARCHAR(150),
  `type` ENUM('testimony','baby_dedication','wedding','baptism','bereavement','birthday','hospital_visit','graduation') NOT NULL,
  `details` JSON,
  `status` ENUM('received','assigned','scheduled','completed','cancelled') DEFAULT 'received',
  `assigned_to` INT DEFAULT NULL,
  `scheduled_date` DATE,
  `share_sunday` TINYINT(1) DEFAULT 0,
  `notes` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_church` (`church_id`),
  INDEX `idx_type` (`type`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------
-- 9. SUNDAY SCHOOL
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS `ss_children` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `church_id` INT NOT NULL DEFAULT 1,
  `parent_member_id` INT,
  `child_name` VARCHAR(150) NOT NULL,
  `age` INT,
  `class_level` ENUM('tots','primary','junior','senior','youth') DEFAULT 'primary',
  `parent_whatsapp` VARCHAR(20),
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `ss_quizzes` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `church_id` INT NOT NULL DEFAULT 1,
  `lesson_title` VARCHAR(255) NOT NULL,
  `week_date` DATE NOT NULL,
  `class_level` ENUM('tots','primary','junior','senior','youth') DEFAULT 'primary',
  `is_active` TINYINT(1) DEFAULT 1,
  `created_by` INT,
  `created_by_ai` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `ss_questions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `quiz_id` INT NOT NULL,
  `question_number` INT NOT NULL,
  `question_text` TEXT NOT NULL,
  `option_a` VARCHAR(255) NOT NULL,
  `option_b` VARCHAR(255) NOT NULL,
  `option_c` VARCHAR(255) NOT NULL,
  `option_d` VARCHAR(255) NOT NULL,
  `correct_option` CHAR(1) NOT NULL,
  `explanation` TEXT,
  INDEX `idx_quiz` (`quiz_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `ss_responses` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `question_id` INT NOT NULL,
  `child_id` INT NOT NULL,
  `whatsapp_number` VARCHAR(20),
  `selected_option` CHAR(1),
  `is_correct` TINYINT(1) DEFAULT 0,
  `responded_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_question_child` (`question_id`, `child_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------
-- 10. KNOWLEDGE BASE
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS `knowledge_base` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `church_id` INT NOT NULL DEFAULT 1,
  `entry_type` ENUM('qa','bible','sermon_excerpt','doctrine','devotional','faq','resource') DEFAULT 'qa',
  `category` VARCHAR(100) DEFAULT 'General',
  `title` VARCHAR(255) NOT NULL,
  `content` LONGTEXT NOT NULL,
  `summary` TEXT,
  `tags` VARCHAR(500),
  `scripture_refs` VARCHAR(500),
  `is_active` TINYINT(1) DEFAULT 1,
  `created_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FULLTEXT KEY `ft_kb` (`title`, `content`, `summary`, `tags`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------
-- 11. AI CHAT LOG
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS `ai_chat_log` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `church_id` INT NOT NULL DEFAULT 1,
  `member_id` INT,
  `whatsapp_number` VARCHAR(20),
  `question` TEXT NOT NULL,
  `answer` TEXT,
  `approach` VARCHAR(20) DEFAULT 'hybrid',
  `kb_entries_used` VARCHAR(100),
  `response_time_ms` INT,
  `tokens_in` INT,
  `tokens_out` INT,
  `confidence` DECIMAL(3,2) DEFAULT 0.00,
  `answered_from_kb` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_church` (`church_id`),
  INDEX `idx_member` (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------
-- 12. UNANSWERED QUESTIONS (Knowledge Gaps)
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS `unanswered_questions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `church_id` INT NOT NULL DEFAULT 1,
  `member_id` INT,
  `whatsapp_number` VARCHAR(20),
  `question` TEXT NOT NULL,
  `answer` TEXT,
  `answered_by` INT,
  `answered_at` TIMESTAMP NULL,
  `notified_at` TIMESTAMP NULL,
  `status` ENUM('pending','answered','sent') DEFAULT 'pending',
  `kb_entry_id` INT DEFAULT NULL,
  `asked_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------
-- 13. CONVERSATION STATE (WhatsApp flow)
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS `conversation_state` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `whatsapp_number` VARCHAR(20) NOT NULL,
  `church_id` INT NOT NULL DEFAULT 1,
  `current_step` VARCHAR(50) DEFAULT 'main_menu',
  `flow_data` JSON,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_phone_church` (`whatsapp_number`, `church_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------
-- 14. BROADCASTS
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS `broadcasts` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `church_id` INT NOT NULL DEFAULT 1,
  `title` VARCHAR(255),
  `message_body` TEXT NOT NULL,
  `segment` JSON,
  `scheduled_at` TIMESTAMP NULL,
  `sent_at` TIMESTAMP NULL,
  `total_recipients` INT DEFAULT 0,
  `delivered_count` INT DEFAULT 0,
  `created_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------
-- 15. RESOURCES
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS `resources` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `church_id` INT NOT NULL DEFAULT 1,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `type` ENUM('document','link','audio','video','form') DEFAULT 'document',
  `category` VARCHAR(100) DEFAULT 'General',
  `file_path` VARCHAR(500),
  `external_url` VARCHAR(500),
  `is_active` TINYINT(1) DEFAULT 1,
  `download_count` INT DEFAULT 0,
  `created_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------
-- 16. CELL GROUPS
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS `cell_groups` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `church_id` INT NOT NULL DEFAULT 1,
  `name` VARCHAR(100) NOT NULL,
  `area` VARCHAR(100),
  `leader_id` INT,
  `meeting_day` VARCHAR(20),
  `meeting_time` TIME,
  `meeting_venue` VARCHAR(255),
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------
-- 17. ADMIN USERS (portal login)
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS `admin_users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `church_id` INT NOT NULL DEFAULT 1,
  `name` VARCHAR(150) NOT NULL,
  `email` VARCHAR(100) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `role` ENUM('super_admin','pastor','associate_pastor','secretary','ss_coordinator','finance_officer','cell_leader') NOT NULL DEFAULT 'secretary',
  `is_active` TINYINT(1) DEFAULT 1,
  `last_login` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Default admin (change password immediately)
INSERT INTO `admin_users` (`name`, `email`, `password_hash`, `role`) VALUES
('Admin', 'admin@kanisaiq.co.ke', '$2y$10$placeholder_hash_change_this', 'super_admin');

-- -------------------------------------------
-- 18. NOTIFICATIONS
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS `notifications` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `church_id` INT NOT NULL DEFAULT 1,
  `type` ENUM('critical','action','info') DEFAULT 'info',
  `category` VARCHAR(50),
  `title` VARCHAR(255) NOT NULL,
  `message` TEXT,
  `link` VARCHAR(255),
  `is_read` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_type` (`type`),
  INDEX `idx_read` (`is_read`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------
-- 19. DEVOTIONALS
-- -------------------------------------------
CREATE TABLE IF NOT EXISTS `devotionals` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `church_id` INT NOT NULL DEFAULT 1,
  `title` VARCHAR(255) NOT NULL,
  `content` TEXT NOT NULL,
  `scripture_ref` VARCHAR(255),
  `publish_date` DATE NOT NULL,
  `is_ai_generated` TINYINT(1) DEFAULT 0,
  `created_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_date` (`publish_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;