-- ============================================================
-- KOINONIA CHMS — Parche v6
-- Ejecutar en phpMyAdmin (pestaña SQL), UNA LÍNEA A LA VEZ
-- si alguna ya existe, omitir esa línea y continuar
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;

-- ── 1. Columnas faltantes en USERS ──────────────────────────
ALTER TABLE `users` ADD COLUMN `member_id` BIGINT UNSIGNED NULL AFTER `id`;
ALTER TABLE `users` ADD COLUMN `bio` TEXT NULL AFTER `avatar_url`;
ALTER TABLE `users` MODIFY COLUMN `role` ENUM('admin','pastor','co_pastor','leader','member') NOT NULL DEFAULT 'member';

-- ── 2. Columnas faltantes en MEMBERS ────────────────────────
ALTER TABLE `members` ADD COLUMN `whatsapp`          VARCHAR(30)  NULL AFTER `phone`;
ALTER TABLE `members` ADD COLUMN `emergency_contact` VARCHAR(200) NULL;
ALTER TABLE `members` ADD COLUMN `emergency_phone`   VARCHAR(30)  NULL;
ALTER TABLE `members` ADD COLUMN `spiritual_gifts`   TEXT         NULL;
ALTER TABLE `members` ADD COLUMN `skills`            TEXT         NULL;

-- ── 3. Corrección enum GROUPS.visibility ────────────────────
ALTER TABLE `groups` MODIFY COLUMN `visibility` ENUM('public','members','private') NOT NULL DEFAULT 'members';

-- ── 4. Tabla ATTENDANCE ─────────────────────────────────────
CREATE TABLE IF NOT EXISTS `attendance` (
    `id`          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `event_id`    BIGINT UNSIGNED NOT NULL,
    `member_id`   BIGINT UNSIGNED NULL,
    `status`      ENUM('present','absent','late','excused') DEFAULT 'present',
    `notes`       TEXT NULL,
    `recorded_by` BIGINT UNSIGNED NULL,
    `created_at`  TIMESTAMP NULL,
    `updated_at`  TIMESTAMP NULL,
    UNIQUE KEY `uq_event_member` (`event_id`,`member_id`),
    FOREIGN KEY (`event_id`)    REFERENCES `events`(`id`)   ON DELETE CASCADE,
    FOREIGN KEY (`member_id`)   REFERENCES `members`(`id`)  ON DELETE SET NULL,
    FOREIGN KEY (`recorded_by`) REFERENCES `users`(`id`)    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 5. Tabla PRAYER_REQUESTS ─────────────────────────────────
CREATE TABLE IF NOT EXISTS `prayer_requests` (
    `id`             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `member_id`      BIGINT UNSIGNED NULL,
    `title`          VARCHAR(200) NOT NULL,
    `content`        TEXT NULL,
    `is_anonymous`   TINYINT(1) DEFAULT 0,
    `status`         ENUM('active','answered','archived') DEFAULT 'active',
    `answered_notes` TEXT NULL,
    `answered_at`    TIMESTAMP NULL,
    `created_by`     BIGINT UNSIGNED NULL,
    `created_at`     TIMESTAMP NULL,
    `updated_at`     TIMESTAMP NULL,
    FOREIGN KEY (`member_id`)  REFERENCES `members`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)   ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 6. Tabla PASTORAL_NOTES ──────────────────────────────────
CREATE TABLE IF NOT EXISTS `pastoral_notes` (
    `id`         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `member_id`  BIGINT UNSIGNED NOT NULL,
    `content`    TEXT NOT NULL,
    `type`       ENUM('general','visit','call','concern','praise') DEFAULT 'general',
    `is_private` TINYINT(1) DEFAULT 1,
    `created_by` BIGINT UNSIGNED NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    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;

-- ── 7. Tabla PUBLICATIONS ────────────────────────────────────
CREATE TABLE IF NOT EXISTS `publications` (
    `id`           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `created_by`   BIGINT UNSIGNED NULL,
    `type`         ENUM('announcement','devotional','sermon_notes','resource') DEFAULT 'announcement',
    `title`        VARCHAR(300) NOT NULL,
    `content`      LONGTEXT NOT NULL,
    `is_published` TINYINT(1) DEFAULT 1,
    `published_at` TIMESTAMP NULL,
    `created_at`   TIMESTAMP NULL,
    `updated_at`   TIMESTAMP NULL,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 8. Tabla PUBLICATION_READS ───────────────────────────────
CREATE TABLE IF NOT EXISTS `publication_reads` (
    `id`             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `publication_id` BIGINT UNSIGNED NOT NULL,
    `user_id`        BIGINT UNSIGNED NOT NULL,
    `read_at`        TIMESTAMP NULL,
    UNIQUE KEY `uq_pub_user` (`publication_id`,`user_id`),
    FOREIGN KEY (`publication_id`) REFERENCES `publications`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`)        REFERENCES `users`(`id`)        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 9. Tabla AI_GENERATIONS ──────────────────────────────────
CREATE TABLE IF NOT EXISTS `ai_generations` (
    `id`         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `created_by` BIGINT UNSIGNED NULL,
    `tool`       VARCHAR(50) NOT NULL,
    `title`      VARCHAR(300) NULL,
    `input`      TEXT NOT NULL,
    `extra`      TEXT NULL,
    `result`     LONGTEXT NOT NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- FIN DEL PARCHE v6
-- ============================================================