import database from "../config/db";

export async function createActivitiesTable() {
  try {
    const query = `
      CREATE TABLE IF NOT EXISTS activities (
        -- Primary Key
        id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
        
        -- Activity Source
        actor_id UUID NOT NULL,
        target_id UUID,
        object_id UUID,
        object_type VARCHAR(50),
        
        -- Activity Content
        verb VARCHAR(50) NOT NULL 
          CHECK (verb IN (
            'followed',
            'unfollowed',
            'posted',
            'liked',
            'commented',
            'shared',
            'joined',
            'updated_profile',
            'added_project',
            'launched_product',
            'achieved_milestone',
            'hired',
            'funded',
            'partnered'
          )),
        
        title VARCHAR(200),
        summary TEXT,
        content JSONB DEFAULT '{}'::JSONB,
        
        -- Visibility & Privacy
        visibility VARCHAR(20) DEFAULT 'public' 
          CHECK (visibility IN ('public', 'followers', 'private', 'custom')),
        audience JSONB DEFAULT '[]'::JSONB,
        
        -- Engagement Metrics
        likes_count INTEGER DEFAULT 0,
        comments_count INTEGER DEFAULT 0,
        shares_count INTEGER DEFAULT 0,
        views_count INTEGER DEFAULT 0,
        
        -- Metadata
        location JSONB,
        tags VARCHAR(100)[] DEFAULT '{}',
        mentions UUID[] DEFAULT '{}',
        
        -- Timestamps
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        expires_at TIMESTAMP,
        
        -- Constraints
        CONSTRAINT fk_actor FOREIGN KEY (actor_id) 
          REFERENCES accounts(id) ON DELETE CASCADE,
        CONSTRAINT fk_target FOREIGN KEY (target_id) 
          REFERENCES accounts(id) ON DELETE SET NULL
          );`;

    await database.query(query);

    // Create indexes
    const indexes = [
      `CREATE INDEX IF NOT EXISTS idx_activities_actor_id ON activities(actor_id);`,
      `CREATE INDEX IF NOT EXISTS idx_activities_created_at ON activities(created_at DESC);`,
      `CREATE INDEX IF NOT EXISTS idx_activities_verb ON activities(verb);`,
      `CREATE INDEX IF NOT EXISTS idx_activities_visibility ON activities(visibility) WHERE visibility = 'public';`,
      `CREATE INDEX IF NOT EXISTS idx_activities_tags ON activities USING GIN(tags);`,
      `CREATE INDEX IF NOT EXISTS idx_activities_mentions ON activities USING GIN(mentions);`,
      `CREATE INDEX IF NOT EXISTS idx_activities_feed ON activities(actor_id, created_at DESC) WHERE visibility IN ('public', 'followers');`,
    ];

    for (const indexQuery of indexes) {
      try {
        await database.query(indexQuery);
      } catch (err) {
        if (!(err instanceof Error) || !err.message.includes("already exists")) {
          throw err;
        }
      }
    }

    // Function to generate activity feed
    const generateFeedFunction = `
      CREATE OR REPLACE FUNCTION generate_activity_feed(
        user_id UUID,
        feed_type VARCHAR DEFAULT 'following',
        page INTEGER DEFAULT 1,
        page_size INTEGER DEFAULT 20
      )
      RETURNS JSON AS $$
      DECLARE
        offset_val INTEGER;
        activities JSON;
        total_count INTEGER;
        feed_items JSON[];
        feed_item JSON;
        activity_record RECORD;
        feed_cursor CURSOR FOR
          SELECT DISTINCT ON (a.id, a.created_at) 
            a.*,
            act.username as actor_username,
            act.display_name as actor_display_name,
            act.avatar as actor_avatar,
            act.account_type as actor_account_type,
            tgt.username as target_username,
            tgt.display_name as target_display_name
          FROM activities a
          INNER JOIN accounts act ON a.actor_id = act.id
          LEFT JOIN accounts tgt ON a.target_id = tgt.id
          WHERE 
            CASE feed_type
              WHEN 'following' THEN
                a.actor_id IN (
                  SELECT following_id 
                  FROM follows 
                  WHERE follower_id = user_id AND status = 'accepted'
                )
                AND a.visibility IN ('public', 'followers')
              WHEN 'public' THEN
                a.visibility = 'public'
              WHEN 'profile' THEN
                a.actor_id = user_id
                AND a.visibility IN ('public', 'followers', 'private')
              ELSE
                a.visibility = 'public'
            END
            AND (a.expires_at IS NULL OR a.expires_at > CURRENT_TIMESTAMP)
          ORDER BY a.created_at DESC, a.id
          LIMIT page_size
          OFFSET offset_val;
      BEGIN
        offset_val := (page - 1) * page_size;
        
        -- Get total count
        SELECT COUNT(*) INTO total_count
        FROM activities a
        WHERE 
          CASE feed_type
            WHEN 'following' THEN
              a.actor_id IN (
                SELECT following_id 
                FROM follows 
                WHERE follower_id = user_id AND status = 'accepted'
              )
              AND a.visibility IN ('public', 'followers')
            WHEN 'public' THEN
              a.visibility = 'public'
            WHEN 'profile' THEN
              a.actor_id = user_id
              AND a.visibility IN ('public', 'followers', 'private')
            ELSE
              a.visibility = 'public'
          END
          AND (a.expires_at IS NULL OR a.expires_at > CURRENT_TIMESTAMP);
        
        -- Get feed items
        feed_items := '{}';
        FOR activity_record IN feed_cursor LOOP
          feed_item := json_build_object(
            'id', activity_record.id,
            'verb', activity_record.verb,
            'title', activity_record.title,
            'summary', activity_record.summary,
            'content', activity_record.content,
            'visibility', activity_record.visibility,
            'created_at', activity_record.created_at,
            'metrics', json_build_object(
              'likes', activity_record.likes_count,
              'comments', activity_record.comments_count,
              'shares', activity_record.shares_count,
              'views', activity_record.views_count
            ),
            'actor', json_build_object(
              'id', activity_record.actor_id,
              'username', activity_record.actor_username,
              'display_name', activity_record.actor_display_name,
              'avatar', activity_record.actor_avatar,
              'account_type', activity_record.actor_account_type
            ),
            'target', CASE 
              WHEN activity_record.target_id IS NOT NULL THEN
                json_build_object(
                  'id', activity_record.target_id,
                  'username', activity_record.target_username,
                  'display_name', activity_record.target_display_name
                )
              ELSE NULL
            END,
            'tags', activity_record.tags
          );
          
          feed_items := array_append(feed_items, feed_item);
        END LOOP;
        
        activities := json_build_object(
          'items', feed_items,
          'pagination', json_build_object(
            'page', page,
            'page_size', page_size,
            'total', total_count,
            'total_pages', CEIL(total_count::DECIMAL / page_size)
          ),
          'feed_type', feed_type,
          'generated_at', CURRENT_TIMESTAMP
        );
        
        RETURN activities;
      END;
      $$ LANGUAGE plpgsql;
    `;

    await database.query(generateFeedFunction);

    // Function to create activity
    const createActivityFunction = `
      CREATE OR REPLACE FUNCTION create_activity(
        p_actor_id UUID,
        p_verb VARCHAR,
        p_title VARCHAR DEFAULT NULL,
        p_summary TEXT DEFAULT NULL,
        p_content JSONB DEFAULT '{}'::JSONB,
        p_target_id UUID DEFAULT NULL,
        p_object_id UUID DEFAULT NULL,
        p_object_type VARCHAR DEFAULT NULL,
        p_visibility VARCHAR DEFAULT 'public',
        p_tags VARCHAR[] DEFAULT '{}',
        p_mentions UUID[] DEFAULT '{}'
      )
      RETURNS UUID AS $$
      DECLARE
        activity_id UUID;
        actor_account_type VARCHAR;
        target_account_type VARCHAR;
        activity_title VARCHAR;
        activity_summary TEXT;
      BEGIN
        -- Get account types
        SELECT account_type INTO actor_account_type 
        FROM accounts WHERE id = p_actor_id;
        
        IF p_target_id IS NOT NULL THEN
          SELECT account_type INTO target_account_type 
          FROM accounts WHERE id = p_target_id;
        END IF;
        
        -- Generate title and summary if not provided
        IF p_title IS NULL THEN
          CASE p_verb
            WHEN 'followed' THEN
              IF actor_account_type = 'user' AND target_account_type = 'user' THEN
                activity_title := 'Started following a user';
              ELSIF actor_account_type = 'user' AND target_account_type = 'company' THEN
                activity_title := 'Started following a company';
              ELSIF actor_account_type = 'company' AND target_account_type = 'user' THEN
                activity_title := 'Company started following a user';
              ELSE
                activity_title := 'Company started following another company';
              END IF;
              
            WHEN 'posted' THEN
              activity_title := 'Shared a new post';
              
            WHEN 'updated_profile' THEN
              activity_title := 'Updated profile';
              
            WHEN 'added_project' THEN
              activity_title := 'Added a new project';
              
            WHEN 'launched_product' THEN
              activity_title := 'Launched a new product';
              
            ELSE
              activity_title := 'New activity';
          END CASE;
        ELSE
          activity_title := p_title;
        END IF;
        
        -- Insert activity
        INSERT INTO activities (
          actor_id,
          target_id,
          object_id,
          object_type,
          verb,
          title,
          summary,
          content,
          visibility,
          tags,
          mentions
        ) VALUES (
          p_actor_id,
          p_target_id,
          p_object_id,
          p_object_type,
          p_verb,
          COALESCE(p_title, activity_title),
          p_summary,
          p_content,
          p_visibility,
          p_tags,
          p_mentions
        ) RETURNING id INTO activity_id;
        
        -- Update activity metrics for the actor
        UPDATE accounts 
        SET updated_at = CURRENT_TIMESTAMP
        WHERE id = p_actor_id;
        
        RETURN activity_id;
      END;
      $$ LANGUAGE plpgsql;
    `;

    await database.query(createActivityFunction);

    // Trigger for follow activities
    const followActivityTriggerFunction = `
      CREATE OR REPLACE FUNCTION trigger_follow_activity()
      RETURNS TRIGGER AS $$
      BEGIN
        -- Create activity for accepted follow
        IF NEW.status = 'accepted' THEN
          PERFORM create_activity(
            NEW.follower_id,
            'followed',
            NULL,
            NULL,
            json_build_object(
              'follow_id', NEW.id,
              'follow_type', NEW.follow_type
            ),
            NEW.following_id,
            NEW.id,
            'follow',
            'followers',
            '{}',
            ARRAY[NEW.following_id]
          );
        END IF;
        
        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;
    `;

    await database.query(followActivityTriggerFunction);

    // Create trigger for follow activities
    await database.query(`
      DROP TRIGGER IF EXISTS trigger_create_follow_activity ON follows;
    `);

    await database.query(`
      CREATE TRIGGER trigger_create_follow_activity
      AFTER UPDATE OF status ON follows
      FOR EACH ROW
      WHEN (NEW.status = 'accepted' AND OLD.status != 'accepted')
      EXECUTE FUNCTION trigger_follow_activity();
    `);

    console.log("✅ Activities table created successfully");
  } catch (err) {
    console.error("❌ Failed To Create Activities Table.", err);
    process.exit(1);
  }
}