import { query } from '../config/database.js';

export class ProcessingJob {
  static async create(jobData) {
    const {
      id,
      userId,
      batchId,
      originalFilename,
      originalSize,
      originalFormat,
      outputFilename,
      outputFormat,
      qualitySetting
    } = jobData;

    const sql = `
      INSERT INTO processing_jobs (
        id, user_id, batch_id, original_filename, original_size, 
        original_format, output_filename, output_format, quality_setting
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `;

    await query(sql, [
      id, userId, batchId, originalFilename, originalSize,
      originalFormat, outputFilename, outputFormat, qualitySetting
    ]);

    return this.findById(id);
  }

  static async findById(id) {
    const sql = 'SELECT * FROM processing_jobs WHERE id = ?';
    const results = await query(sql, [id]);
    return results[0] || null;
  }

  static async findByBatchId(batchId) {
    const sql = 'SELECT * FROM processing_jobs WHERE batch_id = ? ORDER BY created_at';
    return await query(sql, [batchId]);
  }

  static async findByUserId(userId, limit = 50) {
    const sql = `
      SELECT * FROM processing_jobs 
      WHERE user_id = ? 
      ORDER BY created_at DESC 
      LIMIT ?
    `;
    return await query(sql, [userId, limit]);
  }

  static async updateStatus(id, status, progress = null, errorMessage = null) {
    let sql = 'UPDATE processing_jobs SET status = ?';
    const params = [status];

    if (progress !== null) {
      sql += ', progress = ?';
      params.push(progress);
    }

    if (status === 'processing' && !errorMessage) {
      sql += ', processing_started_at = NOW()';
    }

    if (status === 'completed' || status === 'failed') {
      sql += ', processing_completed_at = NOW()';
    }

    if (errorMessage) {
      sql += ', error_message = ?';
      params.push(errorMessage);
    }

    sql += ' WHERE id = ?';
    params.push(id);

    await query(sql, params);
  }

  static async updateOptimizedSize(id, optimizedSize) {
    const sql = `
      UPDATE processing_jobs 
      SET output_size = ?, compression_ratio = ROUND((1 - (? / original_size)) * 100, 2)
      WHERE id = ?
    `;
    await query(sql, [optimizedSize, optimizedSize, id]);
  }

  static async getCompletedJobsByBatch(batchId) {
    const sql = `
      SELECT * FROM processing_jobs 
      WHERE batch_id = ? AND status = 'completed'
      ORDER BY created_at
    `;
    return await query(sql, [batchId]);
  }

  static async deleteOldJobs(daysOld = 30) {
    const sql = `
      DELETE FROM processing_jobs 
      WHERE created_at < DATE_SUB(NOW(), INTERVAL ? DAY)
    `;
    await query(sql, [daysOld]);
  }

  static async getUserStats(userId) {
    const sql = `
      SELECT 
        COUNT(*) as total_jobs,
        COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed_jobs,
        COUNT(CASE WHEN status = 'failed' THEN 1 END) as failed_jobs,
        AVG(CASE WHEN compression_ratio IS NOT NULL THEN compression_ratio END) as avg_compression,
        SUM(CASE WHEN status = 'completed' THEN original_size - output_size ELSE 0 END) as total_bytes_saved
      FROM processing_jobs 
      WHERE user_id = ?
    `;
    const results = await query(sql, [userId]);
    return results[0] || null;
  }
}