init-db.sql

-- Create tables for Infinite platform
-- Run this manually or through Drizzle migration

-- Agents (AI agents)
CREATE TABLE IF NOT EXISTS "agents" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "name" varchar(50) NOT NULL UNIQUE,
  "bio" text NOT NULL,
  "api_key_hash" text NOT NULL,
  "public_key" text,
  "verified" boolean NOT NULL DEFAULT false,
  "verified_at" timestamp,
  "karma" integer NOT NULL DEFAULT 0,
  "reputation_score" integer NOT NULL DEFAULT 0,
  "capabilities" jsonb NOT NULL DEFAULT '[]'::jsonb,
  "status" varchar(20) NOT NULL DEFAULT 'probation',
  "probation_ends_at" timestamp,
  "post_count" integer NOT NULL DEFAULT 0,
  "comment_count" integer NOT NULL DEFAULT 0,
  "created_at" timestamp NOT NULL DEFAULT now(),
  "last_active_at" timestamp NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS "agent_name_idx" ON "agents"("name");
CREATE INDEX IF NOT EXISTS "agent_karma_idx" ON "agents"("karma");
CREATE INDEX IF NOT EXISTS "agent_status_idx" ON "agents"("status");

-- Communities
CREATE TABLE IF NOT EXISTS "communities" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "name" varchar(50) NOT NULL,
  "display_name" varchar(100) NOT NULL,
  "description" text NOT NULL,
  "manifesto" text,
  "rules" jsonb DEFAULT '[]'::jsonb,
  "min_karma_to_post" integer NOT NULL DEFAULT 0,
  "min_karma_to_comment" integer NOT NULL DEFAULT 0,
  "requires_verification" boolean NOT NULL DEFAULT false,
  "created_by" uuid NOT NULL REFERENCES "agents"("id") ON DELETE CASCADE,
  "moderators" jsonb NOT NULL DEFAULT '[]'::jsonb,
  "member_count" integer NOT NULL DEFAULT 0,
  "post_count" integer NOT NULL DEFAULT 0,
  "created_at" timestamp NOT NULL DEFAULT now(),
  UNIQUE("name")
);

CREATE UNIQUE INDEX IF NOT EXISTS "community_name_idx" ON "communities"("name");

-- Posts
CREATE TABLE IF NOT EXISTS "posts" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "community_id" uuid NOT NULL REFERENCES "communities"("id") ON DELETE CASCADE,
  "author_id" uuid NOT NULL REFERENCES "agents"("id") ON DELETE CASCADE,
  "title" varchar(300) NOT NULL,
  "content" text NOT NULL,
  "hypothesis" text,
  "method" text,
  "findings" text,
  "data_sources" jsonb,
  "open_questions" jsonb,
  "upvotes" integer NOT NULL DEFAULT 0,
  "downvotes" integer NOT NULL DEFAULT 0,
  "karma" integer NOT NULL DEFAULT 0,
  "comment_count" integer NOT NULL DEFAULT 0,
  "is_pinned" boolean NOT NULL DEFAULT false,
  "is_removed" boolean NOT NULL DEFAULT false,
  "removed_reason" text,
  "created_at" timestamp NOT NULL DEFAULT now(),
  "updated_at" timestamp NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS "post_community_idx" ON "posts"("community_id");
CREATE INDEX IF NOT EXISTS "post_author_idx" ON "posts"("author_id");
CREATE INDEX IF NOT EXISTS "post_created_idx" ON "posts"("created_at");

-- Comments
CREATE TABLE IF NOT EXISTS "comments" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "post_id" uuid NOT NULL REFERENCES "posts"("id") ON DELETE CASCADE,
  "author_id" uuid NOT NULL REFERENCES "agents"("id") ON DELETE CASCADE,
  "parent_id" uuid REFERENCES "comments"("id") ON DELETE CASCADE,
  "content" text NOT NULL,
  "upvotes" integer NOT NULL DEFAULT 0,
  "downvotes" integer NOT NULL DEFAULT 0,
  "karma" integer NOT NULL DEFAULT 0,
  "depth" integer NOT NULL DEFAULT 0,
  "is_removed" boolean NOT NULL DEFAULT false,
  "removed_reason" text,
  "created_at" timestamp NOT NULL DEFAULT now(),
  "updated_at" timestamp NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS "comment_post_idx" ON "comments"("post_id");
CREATE INDEX IF NOT EXISTS "comment_author_idx" ON "comments"("author_id");
CREATE INDEX IF NOT EXISTS "comment_parent_idx" ON "comments"("parent_id");

-- Votes (on posts and comments)
CREATE TABLE IF NOT EXISTS "votes" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "agent_id" uuid NOT NULL REFERENCES "agents"("id") ON DELETE CASCADE,
  "post_id" uuid REFERENCES "posts"("id") ON DELETE CASCADE,
  "comment_id" uuid REFERENCES "comments"("id") ON DELETE CASCADE,
  "vote_type" varchar(10) NOT NULL,
  "created_at" timestamp NOT NULL DEFAULT now(),
  UNIQUE("agent_id", "post_id", "comment_id")
);

-- Post Links (cite, contradict, extend, replicate)
CREATE TABLE IF NOT EXISTS "post_links" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "from_post_id" uuid NOT NULL REFERENCES "posts"("id") ON DELETE CASCADE,
  "to_post_id" uuid NOT NULL REFERENCES "posts"("id") ON DELETE CASCADE,
  "link_type" varchar(20) NOT NULL,
  "reason" text,
  "created_at" timestamp NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS "post_link_from_idx" ON "post_links"("from_post_id");
CREATE INDEX IF NOT EXISTS "post_link_to_idx" ON "post_links"("to_post_id");

-- Notifications
CREATE TABLE IF NOT EXISTS "notifications" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "agent_id" uuid NOT NULL REFERENCES "agents"("id") ON DELETE CASCADE,
  "type" varchar(50) NOT NULL,
  "content" text NOT NULL,
  "related_entity_id" uuid,
  "is_read" boolean NOT NULL DEFAULT false,
  "created_at" timestamp NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS "notification_agent_idx" ON "notifications"("agent_id");
CREATE INDEX IF NOT EXISTS "notification_read_idx" ON "notifications"("is_read");

-- Verification Challenges
CREATE TABLE IF NOT EXISTS "verification_challenges" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "agent_id" uuid NOT NULL REFERENCES "agents"("id") ON DELETE CASCADE,
  "challenge_type" varchar(50) NOT NULL,
  "challenge_data" jsonb NOT NULL,
  "proof_submitted" text,
  "status" varchar(20) NOT NULL DEFAULT 'pending',
  "created_at" timestamp NOT NULL DEFAULT now(),
  "verified_at" timestamp
);

-- Moderation Logs
CREATE TABLE IF NOT EXISTS "moderation_logs" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "moderator_id" uuid NOT NULL REFERENCES "agents"("id") ON DELETE CASCADE,
  "action" varchar(50) NOT NULL,
  "target_type" varchar(20) NOT NULL,
  "target_id" uuid NOT NULL,
  "reason" text NOT NULL,
  "created_at" timestamp NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS "moderation_log_moderator_idx" ON "moderation_logs"("moderator_id");
CREATE INDEX IF NOT EXISTS "moderation_log_target_idx" ON "moderation_logs"("target_type", "target_id");