/**
 * Somatoscopic Annotation Backend Server
 * Hosts the HTTP API for atlas schemas, annotations, model uploads, user-contributed
 * images, update import and export flows, and static asset serving backed by PostgreSQL.
 */
require('dotenv').config();
const { Pool } = require('pg');
const express = require('express');
const crypto = require('crypto');
const fs = require('fs');
const path = require('path');
const multer = require('multer');
const cors = require('cors');

const AnnotationRepository = require('./repositories/AnnotationRepository');
const TraitRepository = require('./repositories/TraitRepository');
const SchemaService = require('./services/SchemaService');
const AnnotationService = require('./services/AnnotationService');
const AtlasService = require('./services/AtlasService');
const { uploadModelFiles: uploadModelFilesClient } = require('./modelUploadClient');

const app = express();
const PORT = parseInt(process.env.PORT, 10) || 8081;

const isValidUuid = (id) => typeof id === 'string' && /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i.test(id);
const normalizeFileName = (fileName) => path.basename(String(fileName || '').trim().replace(/["']/g, '')).toLowerCase();
const normalizeOrigin = (origin) => {
    if (!origin) return '';

    try {
        const parsedOrigin = new URL(origin);
        return `${parsedOrigin.protocol}//${parsedOrigin.hostname}${parsedOrigin.port ? `:${parsedOrigin.port}` : ''}`;
    } catch {
        return String(origin).trim().replace(/\/$/, '');
    }
};

const parseObjMtllibNames = (objContent) => {
    const refs = [];
    const regex = /^\s*mtllib\s+(.+)$/gim;
    let match;

    while ((match = regex.exec(objContent)) !== null) {
        const tokens = match[1]
            .trim()
            .split(/\s+/)
            .map(normalizeFileName)
            .filter(Boolean);
        refs.push(...tokens);
    }

    return Array.from(new Set(refs));
};

const parseMtlTextureNames = (mtlContent) => {
    const refs = [];
    const regex = /^\s*(map_Kd|map_Ka|map_Ks|map_d|bump|map_Bump|disp|decal|refl)\s+(.+)$/gim;
    let match;

    while ((match = regex.exec(mtlContent)) !== null) {
        const rhs = match[2].trim();
        if (!rhs) continue;

        const tokens = rhs.split(/\s+/).filter(Boolean);
        const candidate = tokens[tokens.length - 1];
        const normalized = normalizeFileName(candidate);
        if (normalized) refs.push(normalized);
    }

    return Array.from(new Set(refs));
};

const cleanupUploadedModelFiles = (...files) => {
    files.flat().filter(Boolean).forEach(file => {
        const filePath = path.join(UPLOADS_DIR, file.filename);
        if (fs.existsSync(filePath)) fs.unlinkSync(filePath);
    });
};

const validateUploadedModelBinding = ({ modelFile, mtlFile, textureFile }) => {
    if (!modelFile && (mtlFile || textureFile)) {
        return { valid: false, error: 'missingModelFile', message: 'Model file (.obj) is required.' };
    }

    if (!modelFile) {
        return { valid: true };
    }

    if (!mtlFile || !textureFile) {
        return { valid: false, error: 'requireCompleteModelSet', message: 'You must upload the full set: .obj + .mtl + texture.' };
    }

    if (textureFile && !mtlFile) {
        return { valid: false, error: 'textureRequiresMtl', message: 'Texture file requires a matching .mtl file.' };
    }

    if (mtlFile) {
        const objPath = path.join(UPLOADS_DIR, modelFile.filename);
        const objContent = fs.readFileSync(objPath, 'utf8');
        const objRefs = parseObjMtllibNames(objContent);
        const uploadedMtlName = normalizeFileName(mtlFile.originalname);

        if (objRefs.length === 0) {
            return { valid: false, error: 'objMissingMtlBinding', message: 'OBJ file does not contain mtllib reference for the uploaded .mtl file.' };
        }

        if (!objRefs.includes(uploadedMtlName)) {
            return { valid: false, error: 'mtlNotMatchingObj', message: `Uploaded .mtl (${mtlFile.originalname}) does not match mtllib reference in OBJ.` };
        }
    }

    if (textureFile && mtlFile) {
        const mtlPath = path.join(UPLOADS_DIR, mtlFile.filename);
        const mtlContent = fs.readFileSync(mtlPath, 'utf8');
        const mtlRefs = parseMtlTextureNames(mtlContent);
        const uploadedTextureName = normalizeFileName(textureFile.originalname);

        if (mtlRefs.length === 0) {
            return { valid: false, error: 'mtlMissingTextureBinding', message: 'MTL file does not reference any texture while a texture was uploaded.' };
        }

        if (!mtlRefs.includes(uploadedTextureName)) {
            return { valid: false, error: 'textureNotMatchingMtl', message: `Uploaded texture (${textureFile.originalname}) is not referenced in MTL.` };
        }
    }

    return { valid: true };
};

const pool = new Pool({
    user: process.env.DB_USER || 'postgres',
    host: process.env.DB_HOST || 'localhost',
    database: process.env.DB_NAME || (process.env.NODE_ENV === 'test' ? 'postgres_test' : 'postgres'),
    password: process.env.DB_PASSWORD || 'betka',
    port: parseInt(process.env.DB_PORT) || 5432,
    allowExitOnIdle: process.env.NODE_ENV === 'test',
});

const schemaReadyPromise = pool.query(`
    ALTER TABLE annotations
    ADD COLUMN IF NOT EXISTS checker_name TEXT;

    ALTER TABLE user_contributed_images
    ADD COLUMN IF NOT EXISTS image_hash TEXT
`).catch((error) => {
    console.error('Schema migration error:', error);
    throw error;
});

const computeFileHash = (filePath) => crypto.createHash('sha256').update(fs.readFileSync(filePath)).digest('hex');

const logAnnotationDebug = (message, payload) => {
    if (payload === undefined) {
        console.log(`[annotation-debug] ${message}`);
        return;
    }

    console.log(`[annotation-debug] ${message}`, payload);
};

const logDatabaseFingerprint = async () => {
    try {
        const dbInfoResult = await pool.query(`
            SELECT current_database() AS database,
                   current_user AS "user",
                   current_setting('port') AS port
        `);
        const annotationCountResult = await pool.query('SELECT COUNT(*)::int AS count FROM annotations');

        logAnnotationDebug('database connection', {
            host: process.env.DB_HOST || 'localhost',
            port: parseInt(dbInfoResult.rows[0]?.port || process.env.DB_PORT || '5432', 10),
            database: dbInfoResult.rows[0]?.database,
            user: dbInfoResult.rows[0]?.user,
            annotationCount: annotationCountResult.rows[0]?.count ?? 0,
        });
    } catch (error) {
        console.error('[annotation-debug] failed to inspect database connection', error);
    }
};

const logAnnotationStateById = async (annotationId, label) => {
    try {
        const result = await pool.query(`
            SELECT a.id AS "annotationId",
                   a.annotator_name AS "annotatorName",
                   a.is_checked AS "isChecked",
                   a.checker_name AS "checkerName",
                   a.last_updated AS "lastUpdated",
                   a.atlas_id AS "atlasId",
                   m.external_id AS "modelId"
            FROM annotations a
            LEFT JOIN models m ON a.model_id = m.id
            WHERE a.id = $1
        `, [annotationId]);

        logAnnotationDebug(label, result.rows[0] || { annotationId, found: false });
    } catch (error) {
        console.error(`[annotation-debug] failed to inspect annotation ${annotationId}`, error);
    }
};

const annotationRepo = new AnnotationRepository(pool);
const traitRepo = new TraitRepository(pool);
const schemaService = new SchemaService(traitRepo);
const annotationService = new AnnotationService(pool, annotationRepo);
const atlasService = new AtlasService(pool);

const UPLOADS_DIR = path.join(__dirname, 'uploads');
const USER_IMAGES_DIR = path.join(__dirname, 'public', 'user_added_images');

[UPLOADS_DIR, USER_IMAGES_DIR].forEach(dir => {
    if (!fs.existsSync(dir)) fs.mkdirSync(dir, { recursive: true });
});

const storage = multer.diskStorage({
    destination: (req, file, cb) => cb(null, UPLOADS_DIR),
    filename: (req, file, cb) => {
        const timestamp = Date.now();
        const random = Math.random().toString(36).substring(2, 8);
        const ext = path.extname(file.originalname).toLowerCase();
        const safeName = `${timestamp}-${random}${ext}`;
        cb(null, safeName);
    }
});

const fileFilter = (req, file, cb) => {
    const allowedTypes = {
        'modelFile': ['.obj'],
        'mtlFile': ['.mtl'], 
        'textureFile': ['.jpg', '.jpeg', '.png', '.bmp', '.tiff']
    };
    
    const fieldName = file.fieldname;
    const ext = path.extname(file.originalname).toLowerCase();
    
    if (allowedTypes[fieldName] && allowedTypes[fieldName].includes(ext)) {
        cb(null, true);
    } else {
        cb(new Error(`Invalid file type for ${fieldName}. Allowed: ${allowedTypes[fieldName].join(', ')}`), false);
    }
};

const upload = multer({ 
    storage,
    fileFilter,
    limits: {
        fileSize: 50 * 1024 * 1024, // 50MB limit
        files: 3
    }
});

const userImageStorage = multer.diskStorage({
    destination: (req, file, cb) => cb(null, USER_IMAGES_DIR),
    filename: (req, file, cb) => {
        const timestamp = Date.now();
        const random = Math.random().toString(36).substring(2, 8);
        const ext = path.extname(file.originalname).toLowerCase();
        const safeName = `user_${timestamp}-${random}${ext}`;
        cb(null, safeName);
    }
});

const userImageFilter = (req, file, cb) => {
    const allowedTypes = ['.jpg', '.jpeg', '.png', '.bmp', '.tiff', '.gif'];
    const ext = path.extname(file.originalname).toLowerCase();
    
    if (allowedTypes.includes(ext)) {
        cb(null, true);
    } else {
        cb(new Error(`Invalid file type. Allowed: ${allowedTypes.join(', ')}`), false);
    }
};

const uploadUserImage = multer({ 
    storage: userImageStorage,
    fileFilter: userImageFilter,
    limits: {
        fileSize: 10 * 1024 * 1024, // 10MB limit for images
        files: 1
    }
});

const zipUpload = multer({ storage: multer.memoryStorage() });

const removeUserImageFiles = (imagePaths = []) => {
    Array.from(new Set(imagePaths.filter(Boolean))).forEach((imagePath) => {
        const fileName = imagePath.split('/').pop();
        const fullPath = path.join(USER_IMAGES_DIR, fileName);

        if (fs.existsSync(fullPath)) {
            fs.unlinkSync(fullPath);
        }
    });
};

const multiUpload = upload.fields([
    { name: 'modelFile', maxCount: 1 },
    { name: 'mtlFile', maxCount: 1 },
    { name: 'textureFile', maxCount: 1 }
]);

app.use(cors({
    origin: true, 
    credentials: true,
    methods: 'GET,HEAD,PUT,PATCH,POST,DELETE'
}));

app.use(express.json());
app.use('/models', express.static(UPLOADS_DIR));
app.use('/public', express.static(path.join(__dirname, 'public')));

// Serve built frontend if present (production deployment)
const FRONTEND_DIR = path.join(__dirname, 'public', 'frontend');
if (fs.existsSync(path.join(FRONTEND_DIR, 'index.html'))) {
    app.use(express.static(FRONTEND_DIR, {
        maxAge: '1y',
        immutable: true,
        setHeaders: (res, filePath) => {
            // Don't cache HTML files (they contain the script references)
            if (filePath.endsWith('.html')) {
                res.setHeader('Cache-Control', 'no-cache, no-store, must-revalidate');
            }
        }
    }));
}

app.use(async (_req, _res, next) => {
    try {
        await schemaReadyPromise;
        next();
    } catch (error) {
        next(error);
    }
});

app.get('/health', (_req, res) => {
    res.status(200).json({ status: 'ok' });
});

app.use((error, req, res, next) => {
    if (error instanceof multer.MulterError) {
        if (error.code === 'LIMIT_FILE_SIZE') {
            return res.status(400).json({ error: 'File too large' });
        }
        if (error.code === 'LIMIT_FILE_COUNT') {
            return res.status(400).json({ error: 'Too many files' });
        }
        return res.status(400).json({ error: 'File upload error' });
    }
    
    if (error.message.includes('Invalid file type')) {
        return res.status(400).json({ error: error.message });
    }
    
    next(error);
});

app.get('/', (_req, res) => res.send('Somatoscopic SQL Backend is running.'));

/**
 * Get dynamic form structure (Factory + User extensions)
 */
app.get('/api/form-schema', async (req, res) => {
    try {
        const { atlasId } = req.query;

        if (!atlasId) {
            const atlases = await traitRepo.getAtlases();
            const allSchemas = await Promise.all(
                atlases.map(async (a) => {
                    const schema = await schemaService.getFullFormSchema(a.id);
                    return schema.map(category => ({ ...category, atlas_id: a.id }));
                })
            );
            return res.json(allSchemas.flat());
        }

        const fullSchema = await schemaService.getFullFormSchema(atlasId);
        res.json(fullSchema);
    } catch (err) {
        console.error("SCHEMA LOAD ERROR:", err);
        res.status(500).json({ error: "errorFormLoad" });
    }
});

app.get('/api/atlases', async (req, res) => {
    try {
        const atlases = await traitRepo.getAtlases();
        res.json(atlases);
    } catch (err) {
        res.status(500).json({ error: "errorAtlasesLoad" });
    }
});

/**
 * Add a single image to an existing trait option
 */
app.post('/api/atlases/add-image', uploadUserImage.single('image'), async (req, res) => {
    try {
        const { traitId, optionLabelCs, isDrawing } = req.body;
        const result = await atlasService.addUserImage({
            traitId,
            optionLabelCs,
            imageFile: req.file,
            isDrawing: isDrawing === 'true',
            imageHash: computeFileHash(req.file.path)
        });
        removeUserImageFiles(result.replacedImagePaths);
        res.json(result);
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});

/**
 * Export all local user contributions to a ZIP
 */
app.get('/api/updates/export', async (_req, res) => {
    try {
        const archiver = require('archiver');
        const archive = archiver('zip', { zlib: { level: 9 } });

        const { rows: updates } = await pool.query(`
            SELECT DISTINCT ON (uci.option_id, uci.is_drawing, COALESCE(uci.image_hash, uci.original_filename, uci.image_path))
                   uci.image_path, uci.is_drawing, uci.original_filename,
                   t.id as trait_id, o.label_cs as option_label_cs
            FROM user_contributed_images uci
            JOIN trait_options o ON uci.option_id = o.id
            JOIN traits t ON o.trait_id = t.id
            ORDER BY uci.option_id, uci.is_drawing, COALESCE(uci.image_hash, uci.original_filename, uci.image_path), uci.created_at DESC, uci.id DESC
        `);

        if (updates.length === 0) {
            return res.status(404).json({ error: "Žádné uživatelské změny k exportu nenalezeny." });
        }

        res.attachment(`somatoscopic_update_${Date.now()}.zip`);
        archive.pipe(res);
        archive.append(JSON.stringify(updates, null, 2), { name: 'updates.json' });
        updates.forEach(upd => {
            const fileName = upd.image_path.split('/').pop();
            const filePath = path.join(USER_IMAGES_DIR, fileName);
            
            if (fs.existsSync(filePath)) {
                archive.file(filePath, { name: `images/${fileName}` });
            } else {
                console.warn(`⚠️ File not found on disk, skipping: ${filePath}`);
            }
        });
        await archive.finalize();

    } catch (e) {
        console.error("❌ EXPORT ERROR:", e);
        res.status(500).json({ error: e.message });
    }
});

/**
 * Delete a user-contributed image
 */
app.delete('/api/atlases/user-image/:imageId', async (req, res) => {
    try {
        const { imageId } = req.params;

        const result = await pool.query(
            `SELECT option_id, image_path, is_drawing, original_filename, image_hash
             FROM user_contributed_images
             WHERE id = $1`,
            [imageId]
        );

        if (result.rows.length === 0) {
            return res.status(404).json({ error: "Obrázek nebyl v databázi nalezen." });
        }

        const { option_id: optionId, is_drawing: isDrawing, original_filename: originalFilename, image_hash: imageHash } = result.rows[0];
        const duplicateResult = await pool.query(
            `DELETE FROM user_contributed_images
             WHERE option_id = $1
               AND is_drawing = $2
               AND (
                   ($3::text IS NOT NULL AND image_hash = $3)
                   OR ($3::text IS NULL AND COALESCE(original_filename, '') = COALESCE($4, ''))
               )
             RETURNING image_path`,
            [optionId, isDrawing, imageHash || null, originalFilename]
        );

        removeUserImageFiles(duplicateResult.rows.map((row) => row.image_path));

        res.json({ message: "Obrázek úspěšně smazán." });
    } catch (err) {
        console.error("❌ DELETE IMAGE ERROR:", err);
        res.status(500).json({ error: "Interní chyba při mazání obrázku." });
    }
});

/**
 * Import a ZIP bundle of updates from another user
 */
app.post('/api/updates/import', zipUpload.single('updateZip'), async (req, res) => {
    let client;
    try {
        const unzipper = require('unzipper');
        let addedCount = 0;
        let duplicateCount = 0;

        if (!req.file) {
            return res.status(400).json({ error: "Nebyl nahrán žádný soubor." });
        }

        const directory = await unzipper.Open.buffer(req.file.buffer);
        client = await pool.connect();
        
        await client.query('BEGIN');

        const metadataFile = directory.files.find(f => f.path === 'updates.json');
        if (!metadataFile) throw new Error("V balíčku chybí soubor updates.json");
        
        const updates = JSON.parse((await metadataFile.buffer()).toString());
        console.log(`📦 Importing ${updates.length} updates...`);

        for (const update of updates) {
            const fileName = update.image_path.split('/').pop();
            const zipFile = directory.files.find(f => f.path === `images/${fileName}`);
            
            if (zipFile) {
                const targetPath = path.join(USER_IMAGES_DIR, fileName);
                
                const buffer = await zipFile.buffer();
                const result = await atlasService.registerUserImageFromUpdate(client, {
                    ...update,
                    filename: fileName,
                    imageHash: crypto.createHash('sha256').update(buffer).digest('hex')
                });

                if (result?.skippedDuplicate) {
                    duplicateCount += 1;
                } else if (result) {
                    fs.writeFileSync(targetPath, buffer);
                    removeUserImageFiles(result.replacedImagePaths || []);
                    addedCount += 1;
                }
            } else {
                console.warn(`⚠️ Image ${fileName} found in metadata but missing from the images/ folder in the ZIP.`);
            }
        }

        await client.query('COMMIT');
        res.json({
            message: "Import proběhl úspěšně",
            addedCount,
            duplicateCount,
        });

    } catch (err) {
        if (client) await client.query('ROLLBACK');
        console.error("❌ IMPORT CRASH:", err);
        res.status(500).json({ error: err.message || "Interní chyba při importu" });
    } finally {
        if (client) client.release();
    }
});

app.get('/annotations', async (req, res) => {
    try {
        const annotations = await annotationRepo.findAllAnnotations();
        res.json({ annotations });
    } catch (error) {
        res.status(500).json({ error: "errorDataLoading" });
    }
});

app.get('/annotations/:annotationId', async (req, res) => {
    try {
        const { annotationId } = req.params;
        const valueLanguage = req.query.lang === 'en' ? 'en' : 'cs';
        if (!isValidUuid(annotationId)) {
            return res.status(400).json({ error: "invalidUuid" });
        }

        const detail = await annotationService.getAnnotationDetail(annotationId, valueLanguage);
        if (!detail) return res.status(404).json({ error: "errorAnnotationNotFound" });
        res.json(detail);
    } catch (error) {
        res.status(500).json({ error: "errorDataLoading" });
    }
});

app.post('/annotations', multiUpload, async (req, res) => {
    try {
        const { annotationId, atlasId, modelId, values, fileHash, annotatorName, checkerName } = req.body;
        const modelFile = req.files?.['modelFile']?.[0];
        const mtlFile = req.files?.['mtlFile']?.[0];
        const textureFile = req.files?.['textureFile']?.[0];
        const normalizedIsChecked = typeof req.body.isChecked === 'string'
            ? req.body.isChecked === 'true'
            : typeof req.body.isChecked === 'boolean'
                ? req.body.isChecked
                : undefined;

        const relationValidation = validateUploadedModelBinding({ modelFile, mtlFile, textureFile });
        if (!relationValidation.valid) {
            cleanupUploadedModelFiles(modelFile, mtlFile, textureFile);
            return res.status(400).json({ error: relationValidation.error, message: relationValidation.message });
        }
        
        if (!atlasId || !fileHash || !annotatorName || !values) {
            return res.status(400).json({ error: "missingRequiredFields", message: "Chybí povinné údaje: atlasId, fileHash, annotatorName, values" });
        }
        
        const parsedValues = typeof values === 'string' ? JSON.parse(values) : values;

        logAnnotationDebug('save request received', {
            annotationId: annotationId || null,
            atlasId,
            modelId,
            fileHash,
            annotatorName,
            isChecked: normalizedIsChecked,
            checkerName: checkerName || null,
            traitCount: Object.keys(parsedValues || {}).length,
        });

        const result = await annotationService.saveAnnotation({
            annotationId, atlasId, modelId, fileHash, annotatorName,
            values: parsedValues,
            isChecked: normalizedIsChecked,
            checkerName,
            modelFile,
            mtlFile,
            textureFile
        });

        await logAnnotationStateById(result.annotationId, 'annotation state after save');
        
        res.status(200).json({ ...result, message: "saveSuccess" });
    } catch (error) {
        res.status(500).json({ error: "saveError", details: error.message });
    }
});

app.delete('/annotations/:annotationId', async (req, res) => {
    try {
        const { annotationId } = req.params;
        if (!isValidUuid(annotationId)) {
            return res.status(400).json({ error: "invalidUuid" });
        }

        const detail = await annotationService.getAnnotationDetail(annotationId);
        if (!detail) return res.status(200).json({ message: "deleteSuccess" }); // Idempotent delete

        await annotationRepo.deleteAnnotation(annotationId);

        // Important: deleting annotation must not delete model metadata or model files.
        // Models are managed separately via /api/models/:modelId.
        res.json({ message: "deleteSuccess" });
    } catch (error) {
        res.status(500).json({ error: "errorDeleteFailed" });
    }
});

/**
 * Get all models, optionally filter by annotated status
 */
app.get('/api/models', async (req, res) => {
    try {
        const { annotated } = req.query;
        let query;
        if (annotated === 'false') {
            query = `
                SELECT m.id, m.external_id, m.file_hash, m.model_path, m.mtl_path, m.texture_path, m.created_at, false as has_annotations
                FROM models m
                WHERE NOT EXISTS (SELECT 1 FROM annotations a WHERE a.model_id = m.id)
                ORDER BY m.created_at DESC
            `;
        } else if (annotated === 'true') {
            query = `
                SELECT DISTINCT m.id, m.external_id, m.file_hash, m.model_path, m.mtl_path, m.texture_path, m.created_at, true as has_annotations
                FROM models m
                JOIN annotations a ON m.id = a.model_id
                ORDER BY m.created_at DESC
            `;
        } else {
            query = `
                SELECT m.id, m.external_id, m.file_hash, m.model_path, m.mtl_path, m.texture_path, m.created_at,
                       CASE WHEN EXISTS (SELECT 1 FROM annotations a WHERE a.model_id = m.id) THEN true ELSE false END as has_annotations
                FROM models m
                ORDER BY m.created_at DESC
            `;
        }
        
        const result = await pool.query(query);
        res.json(result.rows);
    } catch (error) {
        console.error('Models query error:', error);
        res.status(500).json({ error: "errorModelsLoading" });
    }
});

/**
 * Upload model files (obj, mtl, texture)
 */
app.post('/api/models', multiUpload, async (req, res) => {
    try {
        const { modelId } = req.body;
        const modelFile = req.files?.['modelFile']?.[0];
        const mtlFile = req.files?.['mtlFile']?.[0];
        const textureFile = req.files?.['textureFile']?.[0];
        const normalizedModelId = String(modelId || `model_${Date.now()}`).trim();

        if (!modelFile) {
            return res.status(400).json({ error: "Model file (.obj) is required" });
        }

        const relationValidation = validateUploadedModelBinding({ modelFile, mtlFile, textureFile });
        if (!relationValidation.valid) {
            cleanupUploadedModelFiles(modelFile, mtlFile, textureFile);
            return res.status(400).json({ error: relationValidation.error, message: relationValidation.message });
        }

        const existingByName = await pool.query(
            'SELECT id, external_id FROM models WHERE LOWER(external_id) = LOWER($1) LIMIT 1',
            [normalizedModelId]
        );
        if (existingByName.rows.length > 0) {
            cleanupUploadedModelFiles(modelFile, mtlFile, textureFile);
            return res.status(409).json({
                error: "Model name already exists",
                errorCode: "modelNameAlreadyExists",
                message: `A model with the same name already exists: ${existingByName.rows[0].external_id}`,
                existingModelId: existingByName.rows[0].id,
                existingModelName: existingByName.rows[0].external_id
            });
        }

        const crypto = require('crypto');
        const fileBuffer = fs.readFileSync(path.join(UPLOADS_DIR, modelFile.filename));
        const fileHash = crypto.createHash('sha256').update(fileBuffer).digest('hex');

        const existingModel = await pool.query('SELECT id, external_id FROM models WHERE file_hash = $1', [fileHash]);
        if (existingModel.rows.length > 0) {
            cleanupUploadedModelFiles(modelFile, mtlFile, textureFile);
            return res.status(409).json({ 
                error: "Model already exists", 
                errorCode: "modelContentAlreadyExists",
                message: `A model with the same content already exists: ${existingModel.rows[0].external_id}`,
                existingModelId: existingModel.rows[0].id,
                existingModelName: existingModel.rows[0].external_id
            });
        }

        const client = await pool.connect();
        try {
            await client.query('BEGIN');

            const dbModelId = await annotationRepo.upsertModelInTransaction(client, {
                modelId: normalizedModelId,
                fileHash,
                modelPath: modelFile ? `/models/${modelFile.filename}` : null,
                mtlPath: mtlFile ? `/models/${mtlFile.filename}` : null,
                texturePath: textureFile ? `/models/${textureFile.filename}` : null
            });

            await client.query('COMMIT');
            res.status(201).json({ modelId: dbModelId, message: "Model uploaded successfully" });
        } catch (error) {
            await client.query('ROLLBACK');
            throw error;
        } finally {
            client.release();
        }
    } catch (error) {
        res.status(500).json({ error: "errorModelUpload", details: error.message });
    }
});

app.delete('/api/models/:modelId', async (req, res) => {
    try {
        const { modelId } = req.params;
        if (!isValidUuid(modelId)) {
            return res.status(400).json({ error: "invalidUuid" });
        }

        const modelQuery = await pool.query(`
            SELECT m.id, m.external_id, m.model_path, m.mtl_path, m.texture_path
            FROM models m
            WHERE m.id = $1
        `, [modelId]);
        
        if (modelQuery.rows.length === 0) {
            return res.status(404).json({ error: "Model not found" });
        }
        
        const model = modelQuery.rows[0];

        const client = await pool.connect();
        try {
            await client.query('BEGIN');
            await client.query('DELETE FROM annotations WHERE model_id = $1', [modelId]);
            await client.query('DELETE FROM models WHERE id = $1', [modelId]);
            await client.query('COMMIT');
        } catch (txError) {
            await client.query('ROLLBACK');
            throw txError;
        } finally {
            client.release();
        }

        [model.model_path, model.mtl_path, model.texture_path].forEach(filePath => {
            if (filePath) {
                const fileName = filePath.split('/').pop();
                const fullPath = path.join(UPLOADS_DIR, fileName);
                if (fs.existsSync(fullPath)) {
                    fs.unlinkSync(fullPath);
                    console.log(`🗑️ Model file deleted: ${fileName}`);
                }
            }
        });
        
        res.json({ message: "Model deleted successfully" });
    } catch (error) {
        console.error('Model deletion error:', error);
        res.status(500).json({ error: "Failed to delete model" });
    }
});

app.get('/export/full', async (req, res) => {
    try {
        const valueLanguage = req.query.lang === 'en' ? 'en' : 'cs';
        const fullData = await annotationService.getExportData(valueLanguage);
        const checkedRows = fullData.filter(item => item.isChecked);
        const checkedWithoutChecker = checkedRows.filter(item => !String(item.checkerName || '').trim());

        logAnnotationDebug('export/full summary', {
            totalRows: fullData.length,
            checkedRows: checkedRows.length,
            checkedWithoutChecker: checkedWithoutChecker.length,
            sampleMissingCheckerIds: checkedWithoutChecker.slice(0, 10).map(item => item.annotationId),
        });

        res.json(fullData);
    } catch (err) {
        res.status(500).json({ error: "exportDatabaseError" });
    }
});

app.patch('/api/annotations/:annotationId/check', async (req, res) => {
    try {
        const { annotationId } = req.params;
        const { isChecked, checkerName } = req.body;

        logAnnotationDebug('check toggle request received', {
            annotationId,
            isChecked,
            checkerName: checkerName || null,
        });

        if (!isValidUuid(annotationId)) {
            return res.status(400).json({ error: "invalidUuid" });
        }
        
        const normalizedCheckerName = isChecked
            ? (String(checkerName || '').trim() || null)
            : null;

        await pool.query(
            'UPDATE annotations SET is_checked = $1, checker_name = $2, last_updated = NOW() WHERE id = $3',
            [isChecked, normalizedCheckerName, annotationId]
        );

        await logAnnotationStateById(annotationId, 'annotation state after check toggle');
        
        res.json({ message: "Status updated" });
    } catch (error) {
        res.status(500).json({ error: "Failed to update status" });
    }
});

// SPA fallback: as there's tanstack router in the project, it will likely be used in near future
if (fs.existsSync(path.join(FRONTEND_DIR, 'index.html'))) {
    app.get('/{*splat}', (req, res) => {
        res.sendFile(path.join(FRONTEND_DIR, 'index.html'));
    });
}

if (process.env.NODE_ENV !== 'test') {
    app.listen(PORT, () => {
        console.log(`🚀 Somatoscopic Server running at http://localhost:${PORT}`);
        console.log(`📁 Uploads: ${UPLOADS_DIR}`);
        logDatabaseFingerprint();
    });
}

module.exports = app;
module.exports.uploadModelFiles = uploadModelFilesClient;