partRepository.js

import pool from '../db.js';

const PartRepository = {
  async findById(id) {
    const resp = await pool.query(
      `SELECT type, name, image_url FROM parts WHERE id = $1`,
      [id]
    );
    const [part] = resp.rows;
    return part;
  },
  async findMetaByTypeAndId(type, id) {
    const resp = await pool.query(
      `SELECT * FROM part_info_${type} WHERE part_id = $1;`,
      [id]
    );
    const [info] = resp.rows;
    return info;
  },
  async getColumnsByType(type) {
    const resp = await pool.query(
      `
      SELECT column_name 
      FROM information_schema.columns 
      WHERE table_name = $1
      ORDER BY ordinal_position;
      `,
      [`part_info_${type}`.toLowerCase()]
    );
    return resp.rows.map((row) => row.column_name);
  },

  async getFilterDataByTypeAndColumn(type, column) {
    const query = `
    SELECT DISTINCT p.${column}
    FROM relations r
    JOIN part_info_${type} p ON r.part_id = p.part_id -- id 대신 part_id 사용
    WHERE p.${column} IS NOT NULL
    ORDER BY p.${column}
  `;
    const resp = await pool.query(query);
    return resp.rows.map((row) => row[column]);
  },

  async getPartsByFilters(partType, whereClauses, queryValues) {
    const query = `
  SELECT 
    parts.id AS partId, 
    parts.name, 
    parts.image_url
  FROM part_info_${partType.toLowerCase()} 
  INNER JOIN parts 
  ON part_info_${partType.toLowerCase()}.part_id = parts.id
  ${whereClauses ? `WHERE ${whereClauses}` : ''}
  LIMIT 20;
`;
    const result = await pool.query(query, queryValues);
    return result.rows;
  },

  async getAllCombinations() {
    const query = `
    SELECT array_agg(r.part_id) AS partIds
    FROM combinations c
    LEFT JOIN relations r ON c.id = r.combination_id
    GROUP BY c.id;
  `;

    const result = await pool.query(query);
    return result.rows;
  },

  async getFilteredCombinations(queryValues) {
    const query = `
    SELECT 
      c.id AS combination_id,
      array_agg(r.part_id) AS partIds
    FROM combinations c
    LEFT JOIN relations r ON c.id = r.combination_id
    WHERE r.combination_id IN (
      SELECT DISTINCT combination_id
      FROM relations
      WHERE part_id = ANY($1::int[])
    )
    GROUP BY c.id;
  `;
    const result = await pool.query(query, queryValues);
    return result.rows;
  },
};

export default PartRepository;