-- ============================================================
--  ADICIONES DE SCHEMA — chms-iglesia v2
--  Ejecutar en phpMyAdmin (pestaña SQL)
--  Compatible con MySQL 5.7+
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;

-- Nuevas columnas en members
-- (si ya las ejecutaste y da error "Duplicate column", ignóralo — ya están)
ALTER TABLE members ADD COLUMN emergency_contact VARCHAR(200) NULL AFTER occupation;
ALTER TABLE members ADD COLUMN emergency_phone   VARCHAR(30)  NULL AFTER emergency_contact;
ALTER TABLE members ADD COLUMN spiritual_gifts   TEXT         NULL AFTER emergency_phone;
ALTER TABLE members ADD COLUMN skills            TEXT         NULL AFTER spiritual_gifts;
ALTER TABLE members ADD COLUMN whatsapp          VARCHAR(30)  NULL AFTER phone;

-- Peticiones de oración
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
);

-- Asistencia a eventos
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       VARCHAR(255) 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 CASCADE,
    FOREIGN KEY (recorded_by) REFERENCES users(id)     ON DELETE SET NULL
);

-- Notas pastorales en miembros
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
);

-- Creaciones de IA (historial)
CREATE TABLE IF NOT EXISTS ai_generations (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    created_by BIGINT UNSIGNED NULL,
    tool       ENUM('outline','summary','series','devotional') NOT NULL,
    title      VARCHAR(300) NOT NULL,
    input      TEXT NOT NULL,
    extra      VARCHAR(500) NULL,
    result     LONGTEXT NOT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Agregar co_pastor al enum de roles
ALTER TABLE users
    MODIFY COLUMN role ENUM('admin','pastor','co_pastor','leader','member') NOT NULL DEFAULT 'member';

SET FOREIGN_KEY_CHECKS = 1;