CREATE TABLE internal_wall_posts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(180) NOT NULL,
    summary VARCHAR(255) NULL,
    body TEXT NOT NULL,
    type VARCHAR(40) NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'draft',
    audience ENUM('internal','clients','both') NOT NULL DEFAULT 'internal',
    is_pinned TINYINT(1) NOT NULL DEFAULT 0,
    starts_at DATETIME NULL,
    expires_at DATETIME NULL,
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_internal_wall_posts_status (status),
    INDEX idx_internal_wall_posts_type (type),
    INDEX idx_internal_wall_posts_schedule (starts_at, expires_at),
    INDEX idx_internal_wall_posts_pinned (is_pinned),
    CONSTRAINT fk_internal_wall_posts_created_by FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_internal_wall_posts_updated_by FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE internal_wall_attachments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id INT UNSIGNED NOT NULL,
    label VARCHAR(160) NOT NULL,
    storage_path VARCHAR(255) NOT NULL,
    original_name VARCHAR(190) NULL,
    mime_type VARCHAR(120) NULL,
    file_size BIGINT UNSIGNED NOT NULL DEFAULT 0,
    download_count INT UNSIGNED NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_internal_wall_attachments_post (post_id),
    CONSTRAINT fk_internal_wall_attachments_post FOREIGN KEY (post_id) REFERENCES internal_wall_posts (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;