/**
 * AnnotationRepository
 * Encapsulates database queries for annotation lists, annotation detail records,
 * model upserts, and lightweight annotation maintenance helpers.
 */
class AnnotationRepository {
    constructor(pool) {
        this.pool = pool;
    }

    async findAllAnnotations() {
        const query = `
        SELECT 
            a.id as "annotationId",
            a.atlas_id as "atlasId",
            atl.nazev_cs as "atlasNameCs", 
            atl.nazev_en as "atlasNameEn", 
            m.external_id as "modelLabel",
            m.file_hash as "fileHash",
            a.annotator_name as "annotator",
            a.last_updated as "lastUpdated",
            a.is_checked as "isChecked",
            a.checker_name as "checkerName",
            (SELECT COUNT(*) FROM model_trait_selections WHERE annotation_id = a.id)::int as "filledTraitsCount",
            (SELECT COUNT(*) FROM traits t 
            JOIN trait_categories tc ON t.category_id = tc.id 
            WHERE tc.atlas_id = a.atlas_id)::int as "totalTraitsInAtlas"
        FROM annotations a
        LEFT JOIN models m ON a.model_id = m.id
        LEFT JOIN atlases atl ON a.atlas_id = atl.id
        GROUP BY 
            a.id, m.id, atl.id, atl.nazev_cs, atl.nazev_en, 
            m.external_id, m.file_hash, a.annotator_name, a.last_updated, a.is_checked, a.checker_name
        ORDER BY a.last_updated DESC;
        `;
        const result = await this.pool.query(query);
        return result.rows;
    }

    async findAnnotationDetail(annotationId) {
        const query = `
        SELECT 
            a.id AS "annotationId",
            a.atlas_id AS "atlasId",
            atl.nazev_cs as "atlasNameCs",
            atl.nazev_en as "atlasNameEn",
            m.external_id AS "modelId",
            m.model_path AS "modelPath",
            m.mtl_path AS "mtlPath",
            m.texture_path AS "texturePath",
            m.file_hash AS "fileHash",
            a.annotator_name AS "annotator",
            a.last_updated AS "lastUpdated",
            a.is_checked AS "isChecked",
            a.checker_name AS "checkerName",
            t.id AS "trait_id", 
            o.label_cs AS "selected_label_cs",
            o.label_en AS "selected_label_en"
        FROM annotations a
        JOIN models m ON a.model_id = m.id
        LEFT JOIN atlases atl ON a.atlas_id = atl.id
        LEFT JOIN model_trait_selections mts ON a.id = mts.annotation_id
        LEFT JOIN traits t ON mts.trait_id = t.id
        LEFT JOIN trait_options o ON mts.option_id = o.id
        WHERE a.id = $1;
    `;
        const result = await this.pool.query(query, [annotationId]);
        return result.rows;
    }

    async upsertModelInTransaction(client, { modelId, fileHash, modelPath, mtlPath, texturePath }) {
        const query = `
        INSERT INTO models (external_id, file_hash, model_path, mtl_path, texture_path)
        VALUES ($1, $2, $3, $4, $5)
        ON CONFLICT (file_hash) 
        DO UPDATE SET 
            external_id = EXCLUDED.external_id,
            model_path = COALESCE(EXCLUDED.model_path, models.model_path),
            mtl_path = COALESCE(EXCLUDED.mtl_path, models.mtl_path),
            texture_path = COALESCE(EXCLUDED.texture_path, models.texture_path)
        RETURNING id;
    `;
        const result = await client.query(query, [modelId, fileHash, modelPath, mtlPath, texturePath]);
        return result.rows[0].id;
    }

    async deleteAnnotation(id) {
        await this.pool.query('DELETE FROM annotations WHERE id = $1', [id]);
    }

    async countModelReferences(modelId) {
        const res = await this.pool.query(
            'SELECT COUNT(*) FROM annotations WHERE model_id = $1',
            [modelId]
        );
        return parseInt(res.rows[0].count, 10);
    }
}

module.exports = AnnotationRepository;