// services/productTeamService.ts
import database from "../../config/db";
import { QueryResult } from "pg";

export interface ProductTeamCreateData {
  product_id: string;
  creator_id: string;
  role:
    | "founder"
    | "co-founder"
    | "admin"
    | "member"
    | "contributor"
    | "adviser";
  title?: string;
  department?: string;
  is_public?: boolean;
  is_active?: boolean;
  display_order?: number;
  bio?: string;
  responsibilities?: string[];
  expertise?: string[];
  available_for_contact?: boolean;
  contact_email?: string;
  commitment_level?: "full-time" | "part-time" | "contractor" | "advisor";
  badges?: any[];
  is_featured?: boolean;
  is_verified?: boolean;
  verification_note?: string;
}

export interface ProductTeamUpdateData extends Partial<ProductTeamCreateData> {
  contributed_at?: Date;
  last_active_at?: Date;
  left_at?: Date;
}

export interface ProductTeamFilters {
  page?: number;
  limit?: number;
  product_id?: string;
  creator_id?: string;
  role?: string;
  department?: string;
  is_public?: boolean;
  is_active?: boolean;
  is_featured?: boolean;
  is_verified?: boolean;
  commitment_level?: string;
  search?: string;
  sort_by?: string;
  order?: "asc" | "desc";
}

export const productTeamService = {
  // Add team member to product
  async addTeamMember(data: ProductTeamCreateData) {
    const {
      product_id,
      creator_id,
      role = "member",
      title,
      department,
      is_public = true,
      is_active = true,
      display_order = 0,
      bio,
      responsibilities = [],
      expertise = [],
      available_for_contact = false,
      contact_email,
      commitment_level = "full-time",
      badges = [],
      is_featured = false,
      is_verified = false,
      verification_note,
    } = data;

    // Validation
    if (!product_id) {
      throw new Error("Product ID is required");
    }

    if (!creator_id) {
      throw new Error("Creator ID is required");
    }

    // Check if product exists and is active
    const productCheck = await database.query(
      `SELECT id, name, status FROM products WHERE id = $1`,
      [product_id],
    );

    if (productCheck.rows.length === 0) {
      throw new Error("Product not found");
    }

    if (productCheck.rows[0].status !== "active") {
      throw new Error("Product is not active");
    }

    // Check if creator exists
    const creatorCheck = await database.query(
      `SELECT id, user_full_name, display_name FROM accounts WHERE id = $1 AND is_active = true`,
      [creator_id],
    );

    if (creatorCheck.rows.length === 0) {
      throw new Error("Creator not found");
    }

    // Check if team member already exists for this product
    const existingMember = await database.query(
      `SELECT id FROM products_teams 
       WHERE product_id = $1 AND creator_id = $2`,
      [product_id, creator_id],
    );

    if (existingMember.rows.length > 0) {
      throw new Error("User is already a member of this product team");
    }

    // Get next display order
    const orderResult = await database.query(
      `SELECT COALESCE(MAX(display_order), 0) + 1 as next_order 
       FROM products_teams 
       WHERE product_id = $1`,
      [product_id],
    );
    const calculatedDisplayOrder =
      display_order || orderResult.rows[0].next_order;

    const query = `
      INSERT INTO products_teams (
        product_id, creator_id, role, title, department, is_public, is_active,
        display_order, bio, responsibilities, expertise, available_for_contact,
        contact_email, commitment_level, badges, is_featured, is_verified,
        verification_note, joined_at
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, CURRENT_TIMESTAMP)
      RETURNING *;
    `;

    const values = [
      product_id,
      creator_id,
      role,
      title?.trim(),
      department?.trim(),
      is_public,
      is_active,
      calculatedDisplayOrder,
      bio?.trim(),
      responsibilities,
      expertise,
      available_for_contact,
      contact_email,
      commitment_level,
      badges,
      is_featured,
      is_verified,
      verification_note?.trim(),
    ];

    const result = await database.query(query, values);
    return result.rows[0];
  },

  // Get team member by ID
  async getTeamMemberById(id: string) {
    const query = `
      SELECT 
        pt.*,
        a.user_full_name as user_name,
        a.username as user_username,
        a.email as user_email,
        a.avatar as user_avatar,
        a.bio as user_bio,
        a.location_city as user_city,
        a.location_country as user_country,
        a.user_job_title as user_job_title,
        a.user_current_company as user_current_company,
        a.user_github_url as user_github_url,
        a.user_linkedin_url as user_linkedin_url,
        a.user_twitter_url as user_twitter_url,
        p.name as product_name,
        p.slug as product_slug,
        p.logo as product_logo,
        p.description as product_description,
        pc.name as product_category_name,
        (
          SELECT JSON_AGG(
            JSON_BUILD_OBJECT(
              'id', other_pt.id,
              'product_name', other_p.name,
              'product_slug', other_p.slug,
              'role', other_pt.role,
              'joined_at', other_pt.joined_at
            )
          )
          FROM products_teams other_pt
          JOIN products other_p ON other_pt.product_id = other_p.id
          WHERE other_pt.creator_id = pt.creator_id 
            AND other_pt.id != pt.id
            AND other_pt.is_active = true
          ORDER BY other_pt.joined_at DESC
          LIMIT 5
        ) as other_products,
        (
          SELECT COUNT(*)
          FROM products_teams
          WHERE creator_id = pt.creator_id AND is_active = true
        ) as total_active_products,
        (
          SELECT COUNT(*)
          FROM products_teams
          WHERE product_id = pt.product_id AND is_active = true
        ) as team_size
      FROM products_teams pt
      LEFT JOIN accounts a ON pt.creator_id = a.id
      LEFT JOIN products p ON pt.product_id = p.id
      LEFT JOIN products_categories pc ON p.category_id = pc.id
      WHERE pt.id = $1;
    `;

    const result = await database.query(query, [id]);
    return result.rows[0] || null;
  },

  // Get all team members with filters
  async getAllTeamMembers(filters: ProductTeamFilters = {}) {
    const {
      page = 1,
      limit = 20,
      product_id,
      creator_id,
      role,
      department,
      is_public,
      is_active = true,
      is_featured,
      is_verified,
      commitment_level,
      search,
      sort_by = "display_order",
      order = "asc",
    } = filters;

    const offset = (page - 1) * limit;
    const whereClauses: string[] = [];
    const values: any[] = [];
    let paramCount = 0;

    // Active filter
    paramCount++;
    whereClauses.push(`pt.is_active = $${paramCount}`);
    values.push(is_active);

    // Product filter
    if (product_id) {
      paramCount++;
      whereClauses.push(`pt.product_id = $${paramCount}`);
      values.push(product_id);
    }

    // Creator filter
    if (creator_id) {
      paramCount++;
      whereClauses.push(`pt.creator_id = $${paramCount}`);
      values.push(creator_id);
    }

    // Role filter
    if (role) {
      paramCount++;
      whereClauses.push(`pt.role = $${paramCount}`);
      values.push(role);
    }

    // Department filter
    if (department) {
      paramCount++;
      whereClauses.push(`pt.department = $${paramCount}`);
      values.push(department);
    }

    // Public filter
    if (is_public !== undefined) {
      paramCount++;
      whereClauses.push(`pt.is_public = $${paramCount}`);
      values.push(is_public);
    }

    // Featured filter
    if (is_featured !== undefined) {
      paramCount++;
      whereClauses.push(`pt.is_featured = $${paramCount}`);
      values.push(is_featured);
    }

    // Verified filter
    if (is_verified !== undefined) {
      paramCount++;
      whereClauses.push(`pt.is_verified = $${paramCount}`);
      values.push(is_verified);
    }

    // Commitment level filter
    if (commitment_level) {
      paramCount++;
      whereClauses.push(`pt.commitment_level = $${paramCount}`);
      values.push(commitment_level);
    }

    // Search filter
    if (search) {
      paramCount++;
      whereClauses.push(`
        (a.user_full_name ILIKE $${paramCount} OR 
         a.username ILIKE $${paramCount} OR 
         pt.title ILIKE $${paramCount} OR 
         pt.bio ILIKE $${paramCount})
      `);
      values.push(`%${search}%`);
    }

    const whereClause =
      whereClauses.length > 0 ? `WHERE ${whereClauses.join(" AND ")}` : "";

    // Validate sort field
    const validSortFields = [
      "display_order",
      "joined_at",
      "created_at",
      "updated_at",
      "role",
      "title",
      "department",
      "last_active_at",
    ];
    const sortField = validSortFields.includes(sort_by)
      ? sort_by
      : "display_order";
    const sortOrder = order === "asc" ? "ASC" : "DESC";

    // Count total records
    const countQuery = `
      SELECT COUNT(*) as total
      FROM products_teams pt
      LEFT JOIN accounts a ON pt.creator_id = a.id
      ${whereClause};
    `;

    const countResult = await database.query(countQuery, values);
    const total = parseInt(countResult.rows[0].total);

    // Get paginated data
    paramCount = values.length;
    const dataQuery = `
      SELECT 
        pt.*,
        a.user_full_name as user_name,
        a.username as user_username,
        a.email as user_email,
        a.avatar as user_avatar,
        a.user_job_title as user_job_title,
        p.name as product_name,
        p.slug as product_slug,
        p.logo as product_logo,
        ROW_NUMBER() OVER (ORDER BY 
          CASE WHEN pt.role = 'founder' THEN 1
               WHEN pt.role = 'co-founder' THEN 2
               WHEN pt.role = 'admin' THEN 3
               WHEN pt.role = 'member' THEN 4
               ELSE 5
          END, pt.display_order ASC
        ) as role_order
      FROM products_teams pt
      LEFT JOIN accounts a ON pt.creator_id = a.id
      LEFT JOIN products p ON pt.product_id = p.id
      ${whereClause}
      ORDER BY 
        CASE WHEN pt.role = 'founder' THEN 1
             WHEN pt.role = 'co-founder' THEN 2
             WHEN pt.role = 'admin' THEN 3
             WHEN pt.role = 'member' THEN 4
             ELSE 5
        END ASC,
        pt.${sortField} ${sortOrder}
      LIMIT $${paramCount + 1} OFFSET $${paramCount + 2};
    `;

    const dataValues = [...values, limit, offset];
    const dataResult = await database.query(dataQuery, dataValues);

    return {
      data: dataResult.rows,
      pagination: {
        page,
        limit,
        total,
        total_pages: Math.ceil(total / limit),
        has_next: page * limit < total,
        has_prev: page > 1,
      },
    };
  },

  // Update team member
  async updateTeamMember(
    id: string,
    data: ProductTeamUpdateData,
    userId?: string,
  ) {
    // Check if team member exists
    const existingMember = await this.getTeamMemberById(id);
    if (!existingMember) {
      throw new Error("Team member not found");
    }

    // Check permissions if userId is provided
    if (userId) {
      const hasPermission = await this.checkTeamMemberPermission(id, userId);
      if (!hasPermission) {
        throw new Error("You don't have permission to update this team member");
      }
    }

    const setClauses: string[] = [];
    const values: any[] = [];
    let paramCount = 0;

    const fields = [
      "role",
      "title",
      "department",
      "is_public",
      "is_active",
      "display_order",
      "bio",
      "responsibilities",
      "expertise",
      "available_for_contact",
      "contact_email",
      "commitment_level",
      "badges",
      "is_featured",
      "is_verified",
      "verification_note",
      "contributed_at",
      "last_active_at",
      "left_at",
    ];

    fields.forEach((field) => {
      if (data[field as keyof ProductTeamUpdateData] !== undefined) {
        paramCount++;
        setClauses.push(`${field} = $${paramCount}`);
        values.push(data[field as keyof ProductTeamUpdateData]);
      }
    });

    // Handle left_at for deactivation
    if (
      data.is_active === false &&
      existingMember.is_active === true &&
      !data.left_at
    ) {
      paramCount++;
      setClauses.push(`left_at = CURRENT_TIMESTAMP`);
    }

    // Handle reactivation
    if (data.is_active === true && existingMember.is_active === false) {
      paramCount++;
      setClauses.push(`left_at = NULL`);
    }

    if (setClauses.length === 0) {
      throw new Error("No fields to update");
    }

    paramCount++;
    setClauses.push(`updated_at = CURRENT_TIMESTAMP`);
    values.push(id);

    const query = `
      UPDATE products_teams
      SET ${setClauses.join(", ")}
      WHERE id = $${paramCount}
      RETURNING *;
    `;

    const result = await database.query(query, values);
    return result.rows[0];
  },

  // Remove team member (soft delete by setting is_active = false)
  async removeTeamMember(id: string, userId?: string) {
    // Check if team member exists
    const existingMember = await this.getTeamMemberById(id);
    if (!existingMember) {
      throw new Error("Team member not found");
    }

    // Check permissions if userId is provided
    if (userId) {
      const hasPermission = await this.checkTeamMemberPermission(id, userId);
      if (!hasPermission) {
        throw new Error("You don't have permission to remove this team member");
      }
    }

    const query = `
      UPDATE products_teams
      SET 
        is_active = false,
        left_at = CURRENT_TIMESTAMP,
        updated_at = CURRENT_TIMESTAMP
      WHERE id = $1
      RETURNING *;
    `;

    const result = await database.query(query, [id]);
    return result.rows[0];
  },

  // Get team summary for a product
  async getProductTeamSummary(productId: string) {
    const query = `
      SELECT * FROM get_product_team_summary($1);
    `;

    const result = await database.query(query, [productId]);
    return result.rows[0];
  },

  // Get user's product portfolio
  async getUserProductPortfolio(userId: string) {
    const query = `
      SELECT * FROM user_product_portfolio WHERE creator_id = $1;
    `;

    const result = await database.query(query, [userId]);
    return result.rows;
  },

  // Get team member stats
  async getTeamMemberStats(userId: string) {
    const query = `
      SELECT * FROM get_team_member_stats($1);
    `;

    const result = await database.query(query, [userId]);
    return result.rows[0];
  },

  // Check if user is in product team
  async isUserInProductTeam(productId: string, userId: string) {
    const query = `
      SELECT id, role, is_active
      FROM products_teams
      WHERE product_id = $1 AND creator_id = $2 AND is_active = true;
    `;

    const result = await database.query(query, [productId, userId]);
    return result.rows[0] || null;
  },

  // Get product team by role
  async getTeamByRole(productId: string, role: string) {
    const query = `
      SELECT 
        pt.*,
        a.user_full_name as user_name,
        a.username as user_username,
        a.avatar as user_avatar,
        a.user_job_title as user_job_title
      FROM products_teams pt
      LEFT JOIN accounts a ON pt.creator_id = a.id
      WHERE pt.product_id = $1 
        AND pt.role = $2
        AND pt.is_active = true
        AND pt.is_public = true
      ORDER BY pt.display_order ASC;
    `;

    const result = await database.query(query, [productId, role]);
    return result.rows;
  },

  // Reorder team members
  async reorderTeamMembers(productId: string, teamMemberIds: string[]) {
    if (!Array.isArray(teamMemberIds) || teamMemberIds.length === 0) {
      throw new Error("Team member IDs array is required");
    }

    const updates = [];
    for (let i = 0; i < teamMemberIds.length; i++) {
      const updateQuery = `
        UPDATE products_teams
        SET display_order = $1, updated_at = CURRENT_TIMESTAMP
        WHERE id = $2 AND product_id = $3
        RETURNING id, creator_id, role, display_order;
      `;

      const result = await database.query(updateQuery, [
        i,
        teamMemberIds[i],
        productId,
      ]);
      if (result.rows.length > 0) {
        updates.push(result.rows[0]);
      }
    }

    return updates;
  },

  // Search team members with advanced filters
  async searchTeamMembers(searchParams: any) {
    const {
      product_id,
      query,
      roles = [],
      departments = [],
      commitment_levels = [],
      limit = 10,
    } = searchParams;

    let whereClauses: string[] = ["pt.is_active = true", "pt.is_public = true"];
    const values: any[] = [];
    let paramCount = 0;

    if (product_id) {
      paramCount++;
      whereClauses.push(`pt.product_id = $${paramCount}`);
      values.push(product_id);
    }

    if (query) {
      paramCount++;
      whereClauses.push(`
        (a.user_full_name ILIKE $${paramCount} OR 
         pt.title ILIKE $${paramCount} OR 
         pt.bio ILIKE $${paramCount} OR 
         a.user_job_title ILIKE $${paramCount})
      `);
      values.push(`%${query}%`);
    }

    if (roles.length > 0) {
      const rolePlaceholders = roles
        .map((_: any, idx: number) => `$${paramCount + idx + 1}`)
        .join(", ");
      whereClauses.push(`pt.role IN (${rolePlaceholders})`);
      values.push(...roles);
      paramCount += roles.length;
    }

    if (departments.length > 0) {
      const deptPlaceholders = departments
        .map((_: any, idx: number) => `$${paramCount + idx + 1}`)
        .join(", ");
      whereClauses.push(`pt.department IN (${deptPlaceholders})`);
      values.push(...departments);
      paramCount += departments.length;
    }

    if (commitment_levels.length > 0) {
      const levelPlaceholders = commitment_levels
        .map((_: any, idx: number) => `$${paramCount + idx + 1}`)
        .join(", ");
      whereClauses.push(`pt.commitment_level IN (${levelPlaceholders})`);
      values.push(...commitment_levels);
      paramCount += commitment_levels.length;
    }

    const whereClause =
      whereClauses.length > 0 ? `WHERE ${whereClauses.join(" AND ")}` : "";

    const searchQuery = `
      SELECT 
        pt.*,
        a.user_full_name as user_name,
        a.username as user_username,
        a.avatar as user_avatar,
        a.user_job_title as user_job_title,
        p.name as product_name,
        p.slug as product_slug,
        p.logo as product_logo,
        ts_rank(
          setweight(to_tsvector('english', COALESCE(a.user_full_name, '')), 'A') ||
          setweight(to_tsvector('english', COALESCE(pt.title, '')), 'B') ||
          setweight(to_tsvector('english', COALESCE(pt.bio, '')), 'C'),
          plainto_tsquery('english', $${paramCount + 1})
        ) as rank
      FROM products_teams pt
      LEFT JOIN accounts a ON pt.creator_id = a.id
      LEFT JOIN products p ON pt.product_id = p.id
      ${whereClause}
      ORDER BY rank DESC NULLS LAST, pt.display_order ASC
      LIMIT $${paramCount + 2};
    `;

    if (query) {
      values.push(query);
    }
    values.push(limit);

    const result = await database.query(searchQuery, values);
    return result.rows;
  },

  // Check team member permission
  async checkTeamMemberPermission(
    teamMemberId: string,
    userId: string,
  ): Promise<boolean> {
    // Check if user is the team member
    const teamMemberQuery = await database.query(
      `SELECT creator_id, product_id FROM products_teams WHERE id = $1`,
      [teamMemberId],
    );

    if (teamMemberQuery.rows.length === 0) {
      return false;
    }

    const teamMember = teamMemberQuery.rows[0];

    // User can modify their own team membership
    if (teamMember.creator_id === userId) {
      return true;
    }

    // Check if user is product creator
    const productQuery = await database.query(
      `SELECT creator_id FROM products WHERE id = $1`,
      [teamMember.product_id],
    );

    if (
      productQuery.rows.length > 0 &&
      productQuery.rows[0].creator_id === userId
    ) {
      return true;
    }

    // Check if user is admin of the product team
    const adminCheck = await database.query(
      `SELECT EXISTS(
        SELECT 1 FROM products_teams
        WHERE product_id = $1 
          AND creator_id = $2 
          AND role IN ('founder', 'co-founder', 'admin')
          AND is_active = true
      ) as is_admin`,
      [teamMember.product_id, userId],
    );

    if (adminCheck.rows[0]?.is_admin) {
      return true;
    }

    // Check if user is global admin
    const globalAdminCheck = await database.query(
      `SELECT EXISTS(
        SELECT 1 FROM role_assignments ra
        INNER JOIN roles r ON ra.role_id = r.id
        WHERE ra.account_id = $1 
          AND ra.is_active = true
          AND (r.name = 'admin' OR r.name = 'super_admin')
      ) as is_global_admin`,
      [userId],
    );

    return globalAdminCheck.rows[0]?.is_global_admin || false;
  },

  // Bulk add team members
  async bulkAddTeamMembers(
    productId: string,
    members: ProductTeamCreateData[],
    userId: string,
  ) {
    if (!Array.isArray(members) || members.length === 0) {
      throw new Error("Members array is required");
    }

    if (members.length > 50) {
      throw new Error("Cannot add more than 50 members at once");
    }

    // Check product ownership
    const productCheck = await database.query(
      `SELECT creator_id FROM products WHERE id = $1`,
      [productId],
    );

    if (productCheck.rows.length === 0) {
      throw new Error("Product not found");
    }

    const productCreator = productCheck.rows[0].creator_id;
    if (productCreator !== userId) {
      // Check admin permission
      const hasPermission = await this.checkProductAdminPermission(
        productId,
        userId,
      );
      if (!hasPermission) {
        throw new Error("You don't have permission to add team members");
      }
    }

    const addedMembers = [];
    const errors = [];

    for (const member of members) {
      try {
        // Ensure product_id matches
        const memberData = {
          ...member,
          product_id: productId,
        };

        const addedMember = await this.addTeamMember(memberData);
        addedMembers.push(addedMember);
      } catch (error: any) {
        errors.push({
          creator_id: member.creator_id,
          error: error.message,
        });
      }
    }

    return {
      success: addedMembers.length,
      failed: errors.length,
      added_members: addedMembers,
      errors: errors,
    };
  },

  // Check if user can administer product team
  async checkProductAdminPermission(
    productId: string,
    userId: string,
  ): Promise<boolean> {
    // Check if user is product creator
    const productQuery = await database.query(
      `SELECT creator_id FROM products WHERE id = $1`,
      [productId],
    );

    if (
      productQuery.rows.length > 0 &&
      productQuery.rows[0].creator_id === userId
    ) {
      return true;
    }

    // Check if user is admin of the product team
    const adminCheck = await database.query(
      `SELECT EXISTS(
        SELECT 1 FROM products_teams
        WHERE product_id = $1 
          AND creator_id = $2 
          AND role IN ('founder', 'co-founder', 'admin')
          AND is_active = true
      ) as is_admin`,
      [productId, userId],
    );

    if (adminCheck.rows[0]?.is_admin) {
      return true;
    }

    // Check if user is global admin
    const globalAdminCheck = await database.query(
      `SELECT EXISTS(
        SELECT 1 FROM role_assignments ra
        INNER JOIN roles r ON ra.role_id = r.id
        WHERE ra.account_id = $1 
          AND ra.is_active = true
          AND (r.name = 'admin' OR r.name = 'super_admin')
      ) as is_global_admin`,
      [userId],
    );

    return globalAdminCheck.rows[0]?.is_global_admin || false;
  },

  // Update last active time
  async updateLastActive(teamMemberId: string, userId: string) {
    const query = `
      UPDATE products_teams
      SET last_active_at = CURRENT_TIMESTAMP
      WHERE id = $1 AND creator_id = $2
      RETURNING id, last_active_at;
    `;

    const result = await database.query(query, [teamMemberId, userId]);
    return result.rows[0];
  },

  // Get enriched team data view
  async getEnrichedTeamData(productId: string) {
    const query = `
      SELECT * FROM products_teams_enriched WHERE product_id = $1;
    `;

    const result = await database.query(query, [productId]);
    return result.rows;
  },

  // Verify team member (admin only)
  async verifyTeamMember(
    teamMemberId: string,
    verified: boolean = true,
    verificationNote?: string,
  ) {
    const query = `
      UPDATE products_teams
      SET 
        is_verified = $1,
        verification_note = COALESCE($2, verification_note),
        updated_at = CURRENT_TIMESTAMP
      WHERE id = $3
      RETURNING *;
    `;

    const result = await database.query(query, [
      verified,
      verificationNote,
      teamMemberId,
    ]);
    return result.rows[0];
  },

  // Feature team member
  async featureTeamMember(teamMemberId: string, featured: boolean = true) {
    const query = `
      UPDATE products_teams
      SET 
        is_featured = $1,
        updated_at = CURRENT_TIMESTAMP
      WHERE id = $2
      RETURNING *;
    `;

    const result = await database.query(query, [featured, teamMemberId]);
    return result.rows[0];
  },

  // Get team analytics
  async getTeamAnalytics(productId: string) {
    const query = `
      SELECT 
        COUNT(*) as total_members,
        COUNT(CASE WHEN role = 'founder' THEN 1 END) as founders_count,
        COUNT(CASE WHEN role = 'co-founder' THEN 1 END) as co_founders_count,
        COUNT(CASE WHEN role = 'admin' THEN 1 END) as admins_count,
        COUNT(CASE WHEN role = 'member' THEN 1 END) as members_count,
        COUNT(CASE WHEN is_verified = true THEN 1 END) as verified_count,
        COUNT(CASE WHEN is_featured = true THEN 1 END) as featured_count,
        COUNT(CASE WHEN commitment_level = 'full-time' THEN 1 END) as full_time_count,
        COUNT(CASE WHEN commitment_level = 'part-time' THEN 1 END) as part_time_count,
        COUNT(DISTINCT department) as departments_count,
        MIN(joined_at) as first_join_date,
        MAX(joined_at) as last_join_date,
        AVG(EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - joined_at))/86400)::INTEGER as avg_days_in_team
      FROM products_teams
      WHERE product_id = $1 AND is_active = true;
    `;

    const result = await database.query(query, [productId]);
    return result.rows[0];
  },
};
