-- ============================================================
--  PORTAL Y PUBLICACIONES — chms-iglesia v3
--  Ejecutar en phpMyAdmin (pestaña SQL)
--  Compatible con MySQL 5.7+
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;

-- Enlace usuario <-> miembro
ALTER TABLE users ADD COLUMN member_id BIGINT UNSIGNED NULL AFTER id;
ALTER TABLE users ADD COLUMN bio TEXT NULL AFTER avatar_url;

-- Publicaciones del pastor al portal
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') NOT NULL 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;

-- Registro de lecturas (para marcar como leído)
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;

SET FOREIGN_KEY_CHECKS = 1;