ALTER TABLE push_notifications
    ADD COLUMN context_type VARCHAR(80) NULL AFTER tag_mode,
    ADD COLUMN context_id BIGINT UNSIGNED NULL AFTER context_type,
    ADD INDEX idx_push_notifications_context (context_type, context_id);

CREATE TABLE internal_wall_post_events (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    post_id INT UNSIGNED NOT NULL,
    lead_id INT UNSIGNED NULL,
    user_id INT UNSIGNED NULL,
    subscription_id BIGINT UNSIGNED NULL,
    event_type ENUM('delivered','clicked','view','scroll','cta','interaction','message') NOT NULL,
    source VARCHAR(40) NOT NULL DEFAULT 'portal',
    value DECIMAL(6,2) NULL,
    metadata JSON NULL,
    recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_wall_post_events_post (post_id, event_type, recorded_at),
    INDEX idx_wall_post_events_lead (lead_id, event_type),
    INDEX idx_wall_post_events_subscription (subscription_id),
    CONSTRAINT fk_wall_post_events_post FOREIGN KEY (post_id) REFERENCES internal_wall_posts (id) ON DELETE CASCADE,
    CONSTRAINT fk_wall_post_events_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE SET NULL,
    CONSTRAINT fk_wall_post_events_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_wall_post_events_subscription FOREIGN KEY (subscription_id) REFERENCES push_subscriptions (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE internal_wall_engagement_summaries (
    lead_id INT UNSIGNED NOT NULL PRIMARY KEY,
    last_delivery_at DATETIME NULL,
    last_click_at DATETIME NULL,
    last_view_at DATETIME NULL,
    last_scroll_at DATETIME NULL,
    last_interaction_at DATETIME NULL,
    last_message_at DATETIME NULL,
    delivery_count INT UNSIGNED NOT NULL DEFAULT 0,
    click_count INT UNSIGNED NOT NULL DEFAULT 0,
    view_count INT UNSIGNED NOT NULL DEFAULT 0,
    scroll_events INT UNSIGNED NOT NULL DEFAULT 0,
    max_scroll_depth DECIMAL(6,2) NOT NULL DEFAULT 0,
    interaction_count INT UNSIGNED NOT NULL DEFAULT 0,
    message_count INT UNSIGNED NOT NULL DEFAULT 0,
    engagement_score INT NOT NULL DEFAULT 0,
    engagement_level ENUM('no_notifications','silent','viewer','reader','engaged','advocate') NOT NULL DEFAULT 'no_notifications',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_wall_engagement_summaries_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO internal_wall_engagement_summaries (lead_id, updated_at)
SELECT id, NOW()
FROM leads;