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

/**
 * Проверка прав доступа пользователя к сценарию
 */
export async function getUserScenarioPermission(userId, scenarioId) {
  const query = `
    SELECT can_start, can_execute, can_view_results
    FROM uno_bff.scenario_permissions
    WHERE user_id = $1 AND scenario_id = $2
  `;
  const result = await pool.query(query, [userId, scenarioId]);
  return result.rows[0] || null;
}

/**
 * Получить сценарий по ID
 */
export async function getScenarioById(scenarioId) {
  const query = `
    SELECT scenario_id, name, description, status, is_public, start_workflow_key, created_at, updated_at
    FROM uno_bff.scenarios
    WHERE scenario_id = $1
  `;
  const result = await pool.query(query, [scenarioId]);
  return result.rows[0] || null;
}

/**
 * Получить шаг сценария по ID
 */
export async function getScenarioStep(scenarioId, stepId) {
  const query = `
    SELECT scenario_id, step_id, name, description, step_order, status, step_workflow_key, is_terminal, input_schema, output_schema, created_at, updated_at
    FROM uno_bff.scenario_steps
    WHERE scenario_id = $1 AND step_id = $2
  `;
  const result = await pool.query(query, [scenarioId, stepId]);
  return result.rows[0] || null;
}

/**
 * Получить первый шаг сценария
 */
export async function getFirstScenarioStep(scenarioId) {
  const query = `
    SELECT scenario_id, step_id, name, description, step_order, status, step_workflow_key, is_terminal, input_schema, output_schema
    FROM uno_bff.scenario_steps
    WHERE scenario_id = $1 AND status = 'active'
    ORDER BY step_order ASC
    LIMIT 1
  `;
  const result = await pool.query(query, [scenarioId]);
  return result.rows[0] || null;
}

/**
 * Создать запись generation
 */
export async function createGeneration({ userId, scenarioId, authSessionId, requestPayload, currentStepId }) {
  const query = `
    INSERT INTO uno_bff.generations (user_id, scenario_id, auth_session_id, status, request_payload, current_step_id)
    VALUES ($1, $2, $3, 'running', $4, $5)
    RETURNING generation_uuid, user_id, scenario_id, auth_session_id, status, current_step_id, request_payload, started_at
  `;
  const result = await pool.query(query, [userId, scenarioId, authSessionId, JSON.stringify(requestPayload), currentStepId]);
  return result.rows[0];
}

/**
 * Получить generation по UUID
 */
export async function getGenerationByUuid(generationUuid) {
  const query = `
    SELECT generation_uuid, user_id, scenario_id, auth_session_id, status, current_step_id, request_payload, result_payload, last_error_payload, external_run_id, started_at, finished_at
    FROM uno_bff.generations
    WHERE generation_uuid = $1
  `;
  const result = await pool.query(query, [generationUuid]);
  return result.rows[0] || null;
}

/**
 * Обновить generation (после выполнения шага)
 */
export async function updateGeneration(generationUuid, updates) {
  const fields = [];
  const values = [];
  let idx = 1;

  if (updates.status !== undefined) {
    fields.push(`status = $${idx++}`);
    values.push(updates.status);
  }
  if (updates.currentStepId !== undefined) {
    fields.push(`current_step_id = $${idx++}`);
    values.push(updates.currentStepId);
  }
  if (updates.resultPayload !== undefined) {
    fields.push(`result_payload = $${idx++}`);
    values.push(JSON.stringify(updates.resultPayload));
  }
  if (updates.lastErrorPayload !== undefined) {
    fields.push(`last_error_payload = $${idx++}`);
    values.push(JSON.stringify(updates.lastErrorPayload));
  }
  if (updates.externalRunId !== undefined) {
    fields.push(`external_run_id = $${idx++}`);
    values.push(updates.externalRunId);
  }
  if (updates.finishedAt !== undefined) {
    fields.push(`finished_at = $${idx++}`);
    values.push(updates.finishedAt);
  }

  if (fields.length === 0) {
    return getGenerationByUuid(generationUuid);
  }

  values.push(generationUuid);
  const query = `
    UPDATE uno_bff.generations
    SET ${fields.join(', ')}, updated_at = now()
    WHERE generation_uuid = $${idx}
    RETURNING generation_uuid, user_id, scenario_id, status, current_step_id, request_payload, result_payload, external_run_id
  `;
  const result = await pool.query(query, values);
  return result.rows[0];
}

/**
 * Обновить generation_step
 * Используется n8n workflow для обновления статуса шага
 */
export async function updateGenerationStepByUuid(generationUuid, stepId, updates) {
  const fields = [];
  const values = [];
  let idx = 1;

  if (updates.status !== undefined) {
    fields.push(`status = $${idx++}`);
    values.push(updates.status);
  }
  if (updates.responsePayload !== undefined) {
    fields.push(`response_payload = $${idx++}`);
    values.push(JSON.stringify(updates.responsePayload));
  }
  if (updates.errorPayload !== undefined) {
    fields.push(`error_payload = $${idx++}`);
    values.push(JSON.stringify(updates.errorPayload));
  }
  if (updates.startedAt !== undefined) {
    fields.push(`started_at = $${idx++}`);
    values.push(updates.startedAt);
  }
  if (updates.finishedAt !== undefined) {
    fields.push(`finished_at = $${idx++}`);
    values.push(updates.finishedAt);
  }

  if (fields.length === 0) {
    return getGenerationStep(generationUuid, stepId);
  }

  values.push(generationUuid, stepId);
  const query = `
    UPDATE uno_bff.generation_steps
    SET ${fields.join(', ')}, updated_at = now()
    WHERE generation_uuid = $${idx} AND step_id = $${idx + 1}
    RETURNING id, generation_uuid, scenario_id, step_id, status, request_payload, response_payload
  `;
  const result = await pool.query(query, values);
  return result.rows[0];
}

/**
 * Получить generation_step
 */
export async function getGenerationStep(generationUuid, stepId) {
  const query = `
    SELECT id, generation_uuid, scenario_id, step_id, step_order, status, request_payload, response_payload, error_payload
    FROM uno_bff.generation_steps
    WHERE generation_uuid = $1 AND step_id = $2
  `;
  const result = await pool.query(query, [generationUuid, stepId]);
  return result.rows[0] || null;
}

/**
 * Получить generation по UUID с проверкой принадлежности пользователю
 */
export async function getGenerationByUuidForUser(generationUuid, userId) {
  const query = `
    SELECT g.generation_uuid, g.user_id, g.scenario_id, g.auth_session_id, g.status,
           g.current_step_id, g.request_payload, g.result_payload, g.last_error_payload,
           g.external_run_id, g.started_at, g.finished_at,
           s.name as scenario_name
    FROM uno_bff.generations g
    LEFT JOIN uno_bff.scenarios s ON g.scenario_id = s.scenario_id
    WHERE g.generation_uuid = $1 AND g.user_id = $2
  `;
  const result = await pool.query(query, [generationUuid, userId]);
  return result.rows[0] || null;
}

/**
 * Получить метаданные generation по UUID с проверкой принадлежности пользователю
 */
export async function getGenerationMetaByUuid(generationUuid, userId) {
  const query = `
    SELECT g.scenario_id, s.name as scenario_name
    FROM uno_bff.generations g
    LEFT JOIN uno_bff.scenarios s ON g.scenario_id = s.scenario_id
    WHERE g.generation_uuid = $1 AND g.user_id = $2
  `;
  const result = await pool.query(query, [generationUuid, userId]);
  return result.rows[0] || null;
}

/**
 * Обновить generation result_payload и статус
 */
export async function updateGenerationResult(generationUuid, resultPayload, status = 'completed') {
  const query = `
    UPDATE uno_bff.generations
    SET result_payload = $2, status = $3, finished_at = COALESCE(finished_at, now()), updated_at = now()
    WHERE generation_uuid = $1
    RETURNING generation_uuid, user_id, scenario_id, status, result_payload
  `;
  const result = await pool.query(query, [generationUuid, JSON.stringify(resultPayload), status]);
  return result.rows[0] || null;
}

/**
 * Получить все шаги для generation
 */
export async function getGenerationSteps(generationUuid) {
  const query = `
    SELECT step_id, step_order, status, request_payload, response_payload, error_payload, started_at, finished_at
    FROM uno_bff.generation_steps
    WHERE generation_uuid = $1
    ORDER BY step_order ASC
  `;
  const result = await pool.query(query, [generationUuid]);
  return result.rows;
}