// src/services/cell_change_service.rs - Полностью обновленная версия
// изменение ячеек, пересчет формул, дельта

use std::collections::HashMap;
use serde::Deserialize;
use uuid::Uuid;

use crate::services::calc_service::ChangedCell;
use crate::domain::dto::{CellUpsertItem, ReportCellDto,SheetWithData};
use crate::domain::formulas;
use crate::domain::utils::{a1_to_rc, rc_to_a1};
use crate::infrastructure::report_registry::ReportRegistry;
use crate::infrastructure::repository::Repo;
use crate::infrastructure::user_ctx::UserCtx;
use crate::services::errors::ServiceError;
use crate::services::adapters::{fetch_cells, fetch_report, load_sheet_from_report};

#[derive(Clone, Debug)]
pub struct CellChangeService {
    repo: Repo,
    excel: ReportRegistry,
}

#[derive(Deserialize, Clone, Debug)]
pub struct CellChangeInput {
    pub report_id: Uuid,
    pub sheet_name: String,
    pub r: u32,
    pub c: u32,
    pub value: String,
    pub region_id: Option<i16>,
}

type CellValues = HashMap<(u32, u32), String>;
type Coord = (u32, u32);

impl CellChangeService {
    pub fn new(repo: Repo, excel: ReportRegistry) -> Self {
        Self { repo, excel }
    }

    pub async fn change_cell(
        &self,
        input: CellChangeInput,
        user: UserCtx,
    ) -> Result<Vec<ChangedCell>, ServiceError> {
        user.require_auth()?;
        let filled_sheet = self.load_sheet_data(&input).await?;
        let before = self.compute_full_state(&filled_sheet);
        self.save_change(&input).await?;
        let after = self.compute_state_with_change(&filled_sheet, &input);
        Ok(self.find_formula_changes(&before, &after, &input))
    }

    /// Возвращает структуру листа и пользовательские данные
    async fn load_sheet_data(
        &self,
        input: &CellChangeInput
    ) -> Result<SheetWithData, ServiceError> {
        let report = fetch_report(&self.repo, input.report_id).await?;
        let sheet_structure = load_sheet_from_report(&self.excel, &report, &input.sheet_name)?;
        let db_cells = fetch_cells(&self.repo, input.report_id, &input.sheet_name).await?;

        Ok(SheetWithData {
            sheet_structure,
            db_cells,
        })
    }

    /// Сохраним изменения
    async fn save_change(&self, input: &CellChangeInput) -> Result<(), ServiceError> {
        let (value_num, value_text) = normalize_value(&input.value);
        let item = CellUpsertItem {
            address: rc_to_a1(input.r, input.c),
            value_num,
            value_text,
            region_id: input.region_id,
            row_index: None,
        };
        self.repo.upsert_cells(input.report_id, &input.sheet_name, &[item]).await?;
        Ok(())
    }

    // --- Методы вычислений теперь принимают `FilledSheet` ---

    /// Пересчитаем весь лист
    fn compute_full_state(&self, filled_sheet: &SheetWithData) -> CellValues {
        let mut values = self.merge_template_and_db(
            &filled_sheet.sheet_structure.values,
            &filled_sheet.db_cells,
        );
        self.apply_formulas(&mut values, &filled_sheet.sheet_structure.formulas);
        values
    }

    /// Пересчитаем лист после изменения ячейки
    fn compute_state_with_change(
        &self,
        filled_sheet: &SheetWithData,
        input: &CellChangeInput,
    ) -> CellValues {
        let mut values = self.merge_template_and_db_except(
            &filled_sheet.sheet_structure.values,
            &filled_sheet.db_cells,
            (input.r, input.c),
        );
        values.insert((input.r, input.c), input.value.clone());
        self.apply_formulas(&mut values, &filled_sheet.sheet_structure.formulas);
        values
    }

    // --- Вспомогательные методы ---

    /// Зальем пользовательские данные из БД в шаблон
    fn merge_template_and_db(&self, template: &CellValues, db_cells: &[ReportCellDto]) -> CellValues {
        let mut values = template.clone();
        for cell in db_cells {
            if let Ok(coord) = a1_to_rc(&cell.address) {
                values.insert(coord, cell_to_string(cell));
            }
        }
        values
    }

    /// Зальем пользовательские данные из БД в шаблон кроме указанной ячейки
    fn merge_template_and_db_except(
        &self,
        template: &CellValues,
        db_cells: &[ReportCellDto],
        except: Coord
    ) -> CellValues {
        let mut values = template.clone();
        for cell in db_cells {
            if let Ok(coord) = a1_to_rc(&cell.address) {
                if coord != except {
                    values.insert(coord, cell_to_string(cell));
                }
            }
        }
        values
    }

    fn apply_formulas(&self, values: &mut CellValues, formulas: &HashMap<Coord, String>) {
        let computed = formulas::compute_formulas(values, formulas);
        values.extend(computed);
    }

    /// найдем изменения вычисляемых ячеек
    fn find_formula_changes(
        &self,
        before: &CellValues,
        after: &CellValues,
        input: &CellChangeInput,
    ) -> Vec<ChangedCell> {
        let changed = (input.r, input.c);
        let mut res = Vec::new();
        for (coord, new_val) in after {
            if *coord == changed { continue; }
            let old = before.get(coord).cloned().unwrap_or_default();
            if old != *new_val {
                res.push(create_change_record(coord, new_val, &input.sheet_name));
            }
        }
        res
    }
}

// --- Чистые функции-помощники ---

fn cell_to_string(cell: &ReportCellDto) -> String {
    cell.value_text.clone()
        .or_else(|| cell.value_num.map(|n| n.to_string()))
        .unwrap_or_default()
}

fn create_change_record(coord: &Coord, value: &str, sheet_name: &str) -> ChangedCell {
    let (value_num, value_text) = normalize_value(value);
    ChangedCell {
        sheet_name: sheet_name.to_string(),
        r: coord.0,
        c: coord.1,
        address: rc_to_a1(coord.0, coord.1),
        value_num,
        value_text,
        is_formula: true,
        display_value: value.to_string(),
    }
}

pub fn normalize_value(s: &str) -> (Option<f64>, Option<String>) {
    let trimmed = s.trim();
    if trimmed.is_empty() { return (None, None); }
    let normalized = trimmed.replace(' ', "").replace(',', ".");
    match normalized.parse::<f64>() {
        Ok(n) => (Some(n), None),
        Err(_) => (None, Some(trimmed.to_string())),
    }
}