/**
 * TraitRepository
 * Provides data access for atlas categories, traits, options, atlas metadata,
 * and user-contributed images attached to trait options.
 */
class TraitRepository {
    constructor(pool) {
        this.pool = pool;
    }

    async getAllCategories(atlasId) {
        const res = await this.pool.query(
            'SELECT id, nazev_cs, nazev_en, poradi FROM trait_categories WHERE atlas_id = $1 ORDER BY poradi',
            [atlasId]
        );
        return res.rows;
    }

    async getTraitsByCategory(categoryId) {
        const res = await this.pool.query(
            'SELECT id, label_cs, label_en, popis_cs, popis_en FROM traits WHERE category_id = $1 ORDER BY poradi',
            [categoryId]
        );
        return res.rows;
    }

    async getOptionsByTrait(traitId) {
        const res = await this.pool.query(
            'SELECT id, label_cs, label_en, images, drawings FROM trait_options WHERE trait_id = $1 ORDER BY poradi',
            [traitId]
        );
        return res.rows;
    }

    async getAtlases() {
        const res = await this.pool.query(
            'SELECT id, nazev_cs, nazev_en, popis_cs, popis_en, je_vychozi FROM atlases ORDER BY id'
        );
        return res.rows;
    }

    async getUserImagesByOption(optionId) {
        const res = await this.pool.query(
            `SELECT DISTINCT ON (is_drawing, COALESCE(image_hash, original_filename, image_path))
                id, image_path, is_drawing
             FROM user_contributed_images
             WHERE option_id = $1
             ORDER BY is_drawing, COALESCE(image_hash, original_filename, image_path), created_at DESC, id DESC`,
            [optionId]
        );
        return res.rows;
    }

    async findOptionIdByNaturalKeys(traitId, labelValue, alternateLabelValue = null) {
        const res = await this.pool.query(
            `SELECT id
             FROM trait_options
             WHERE trait_id = $1
               AND (
                   label_cs = $2
                   OR label_en = $2
                                     OR ($3::text IS NOT NULL AND (label_cs = $3::text OR label_en = $3::text))
               )
             LIMIT 1`,
            [traitId, labelValue, alternateLabelValue]
        );
        return res.rows[0]?.id || null;
    }
}

module.exports = TraitRepository;