-- +goose up
CREATE TABLE IF NOT EXISTS analytics_posts (
    id BIGSERIAL PRIMARY KEY,
    user_id TEXT NOT NULL,
    platform TEXT NOT NULL,
    post_id TEXT NOT NULL,
    post_text TEXT NOT NULL,
    post_timestamp TIMESTAMPTZ NOT NULL,
    likes BIGINT NOT NULL DEFAULT 0,
    reposts BIGINT NOT NULL DEFAULT 0,
    replies BIGINT NOT NULL DEFAULT 0,
    quotes BIGINT NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (user_id, platform, post_id)
);

CREATE INDEX IF NOT EXISTS idx_analytics_posts_user_platform_time
    ON analytics_posts (user_id, platform, post_timestamp);

-- +goose down
DROP INDEX IF EXISTS idx_analytics_posts_user_platform_time;
DROP TABLE IF EXISTS analytics_posts;