-- +goose Up
-- Enable pg_trgm extension for fuzzy text search
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Communities table: stores community metadata indexed from firehose
CREATE TABLE communities (
    id SERIAL PRIMARY KEY,
    did TEXT UNIQUE NOT NULL,                                       -- Community DID (did:coves:xxx or did:plc:xxx)
    handle TEXT UNIQUE NOT NULL,                                    -- Scoped handle (!gaming@coves.social)
    name TEXT NOT NULL,                                             -- Short community name (local part of handle)
    display_name TEXT,                                              -- Display name for UI
    description TEXT,                                               -- Community description
    description_facets JSONB,                                       -- Rich text annotations
    avatar_cid TEXT,                                                -- CID of avatar image blob
    banner_cid TEXT,                                                -- CID of banner image blob

    -- Ownership & hosting
    owner_did TEXT NOT NULL,                                        -- DID of community owner (instance in V1)
    created_by_did TEXT NOT NULL,                                   -- DID of user who created community
    hosted_by_did TEXT NOT NULL,                                    -- DID of hosting instance

    -- Visibility & federation
    visibility TEXT NOT NULL DEFAULT 'public' CHECK (visibility IN ('public', 'unlisted', 'private')),
    allow_external_discovery BOOLEAN NOT NULL DEFAULT true,

    -- Moderation
    moderation_type TEXT CHECK (moderation_type IN ('moderator', 'sortition')),
    content_warnings TEXT[],                                        -- Array of content warning types

    -- Statistics (cached counts)
    member_count INTEGER DEFAULT 0,
    subscriber_count INTEGER DEFAULT 0,
    post_count INTEGER DEFAULT 0,

    -- Federation metadata (for future cross-platform support)
    federated_from TEXT,                                            -- 'lemmy', 'coves', etc.
    federated_id TEXT,                                              -- Original ID on federated platform

    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    -- AT-Proto metadata
    record_uri TEXT,                                                -- AT-URI of the community profile record
    record_cid TEXT                                                 -- CID of the community profile record
);

-- Indexes for efficient queries
CREATE INDEX idx_communities_handle ON communities(handle);
CREATE INDEX idx_communities_visibility ON communities(visibility);
CREATE INDEX idx_communities_hosted_by ON communities(hosted_by_did);
CREATE INDEX idx_communities_created_by ON communities(created_by_did);
CREATE INDEX idx_communities_created_at ON communities(created_at);
CREATE INDEX idx_communities_name_trgm ON communities USING gin(name gin_trgm_ops);  -- For fuzzy search
CREATE INDEX idx_communities_description_trgm ON communities USING gin(description gin_trgm_ops);

-- Subscriptions table: lightweight feed following
CREATE TABLE community_subscriptions (
    id SERIAL PRIMARY KEY,
    user_did TEXT NOT NULL,
    community_did TEXT NOT NULL REFERENCES communities(did) ON DELETE CASCADE,
    subscribed_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    -- AT-Proto metadata (subscription is a record in user's repo)
    record_uri TEXT,                                                -- AT-URI of the subscription record
    record_cid TEXT,                                                -- CID of the subscription record

    UNIQUE(user_did, community_did)
);

-- Indexes for subscription queries
CREATE INDEX idx_subscriptions_user ON community_subscriptions(user_did);
CREATE INDEX idx_subscriptions_community ON community_subscriptions(community_did);
CREATE INDEX idx_subscriptions_user_community ON community_subscriptions(user_did, community_did); -- Composite index for GetSubscription
CREATE INDEX idx_subscriptions_subscribed_at ON community_subscriptions(subscribed_at);

-- Memberships table: active participation & reputation tracking
CREATE TABLE community_memberships (
    id SERIAL PRIMARY KEY,
    user_did TEXT NOT NULL,
    community_did TEXT NOT NULL REFERENCES communities(did) ON DELETE CASCADE,

    -- Reputation system
    reputation_score INTEGER DEFAULT 0,                            -- Gained through participation
    contribution_count INTEGER DEFAULT 0,                          -- Total posts + comments + actions

    -- Activity tracking
    joined_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_active_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    -- Moderation status
    is_banned BOOLEAN DEFAULT false,
    is_moderator BOOLEAN DEFAULT false,

    UNIQUE(user_did, community_did)
);

-- Indexes for membership queries
CREATE INDEX idx_memberships_user ON community_memberships(user_did);
CREATE INDEX idx_memberships_community ON community_memberships(community_did);
CREATE INDEX idx_memberships_reputation ON community_memberships(community_did, reputation_score DESC);
CREATE INDEX idx_memberships_last_active ON community_memberships(last_active_at);

-- Community moderation actions table (V2 feature, schema prepared)
CREATE TABLE community_moderation (
    id SERIAL PRIMARY KEY,
    community_did TEXT NOT NULL REFERENCES communities(did) ON DELETE CASCADE,
    action TEXT NOT NULL CHECK (action IN ('delist', 'quarantine', 'remove')),
    reason TEXT,
    instance_did TEXT NOT NULL,                                    -- Which instance took this action
    broadcast BOOLEAN DEFAULT false,                               -- Share moderation signal with network?
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP WITH TIME ZONE                            -- Optional: temporary moderation
);

-- Indexes for moderation queries
CREATE INDEX idx_moderation_community ON community_moderation(community_did);
CREATE INDEX idx_moderation_instance ON community_moderation(instance_did);
CREATE INDEX idx_moderation_action ON community_moderation(action);
CREATE INDEX idx_moderation_created_at ON community_moderation(created_at);

-- +goose Down
DROP INDEX IF EXISTS idx_moderation_created_at;
DROP INDEX IF EXISTS idx_moderation_action;
DROP INDEX IF EXISTS idx_moderation_instance;
DROP INDEX IF EXISTS idx_moderation_community;
DROP TABLE IF EXISTS community_moderation;

DROP INDEX IF EXISTS idx_memberships_last_active;
DROP INDEX IF EXISTS idx_memberships_reputation;
DROP INDEX IF EXISTS idx_memberships_community;
DROP INDEX IF EXISTS idx_memberships_user;
DROP TABLE IF EXISTS community_memberships;

DROP INDEX IF EXISTS idx_subscriptions_subscribed_at;
DROP INDEX IF EXISTS idx_subscriptions_user_community;
DROP INDEX IF EXISTS idx_subscriptions_community;
DROP INDEX IF EXISTS idx_subscriptions_user;
DROP TABLE IF EXISTS community_subscriptions;

DROP INDEX IF EXISTS idx_communities_description_trgm;
DROP INDEX IF EXISTS idx_communities_name_trgm;
DROP INDEX IF EXISTS idx_communities_created_at;
DROP INDEX IF EXISTS idx_communities_created_by;
DROP INDEX IF EXISTS idx_communities_hosted_by;
DROP INDEX IF EXISTS idx_communities_visibility;
DROP INDEX IF EXISTS idx_communities_handle;
DROP TABLE IF EXISTS communities;