""" Database-backed configuration storage (MySQL). Implements the same interface as config_storage.ConfigStorage. Used when DATABASE_URL is MySQL. """ import copy import logging from typing import Dict, Optional from app.database import SessionLocal from app.models import RevenueTarget, GPThreshold, KPITargetOverride from app.config.config_storage import DEFAULT_GP_THRESHOLDS, DEFAULT_KPI_TARGETS logger = logging.getLogger(__name__) class DBConfigStorage: """Config storage backed by MySQL tables. Same interface as ConfigStorage.""" def _get_db(self): return SessionLocal() def get_targets_breakevens(self) -> Dict: """Get all targets and breakevens as { branch_id_company_id: { target, breakeven } }.""" db = self._get_db() try: rows = db.query(RevenueTarget).all() out = {} for r in rows: key = f"{r.branch_id}_{r.company_id}" out[key] = {} if r.target is not None: out[key]["target"] = r.target if r.breakeven is not None: out[key]["breakeven"] = r.breakeven return out finally: db.close() def update_target_breakeven( self, branch_id: str, company_id: int, target: Optional[int] = None, breakeven: Optional[int] = None, ): db = self._get_db() try: row = db.query(RevenueTarget).filter( RevenueTarget.branch_id == branch_id, RevenueTarget.company_id == company_id, ).first() if not row: row = RevenueTarget(branch_id=branch_id, company_id=company_id) db.add(row) if target is not None: row.target = target if breakeven is not None: row.breakeven = breakeven db.commit() logger.info(f"Updated target/breakeven for {branch_id} company {company_id}") finally: db.close() def get_gp_thresholds(self) -> Dict: """Get all GP color thresholds. Merge DB rows with defaults.""" result = copy.deepcopy(DEFAULT_GP_THRESHOLDS) db = self._get_db() try: rows = db.query(GPThreshold).all() for r in rows: result[r.department_type] = { "green_min": r.green_min, "yellow_min": r.yellow_min, "yellow_max": r.yellow_max, "red_max": r.red_max, } return result finally: db.close() def update_gp_thresholds(self, department_type: str, thresholds: Dict): dept = department_type.lower() db = self._get_db() try: row = db.query(GPThreshold).filter(GPThreshold.department_type == dept).first() if not row: row = GPThreshold(department_type=dept) db.add(row) row.green_min = thresholds["green_min"] row.yellow_min = thresholds["yellow_min"] row.yellow_max = thresholds["yellow_max"] row.red_max = thresholds["red_max"] db.commit() logger.info(f"Updated GP thresholds for {department_type}") finally: db.close() @staticmethod def _deep_merge(base: Dict, overrides: Dict) -> Dict: result = copy.deepcopy(base) for key, value in overrides.items(): if key in result and isinstance(result[key], dict) and isinstance(value, dict): result[key] = DBConfigStorage._deep_merge(result[key], value) else: result[key] = copy.deepcopy(value) return result def get_kpi_targets(self) -> Dict: """Defaults deep-merged with DB overrides (target, weight only in DB).""" db = self._get_db() try: rows = db.query(KPITargetOverride).all() overrides = {} for r in rows: if r.branch not in overrides: overrides[r.branch] = {} if r.department not in overrides[r.branch]: overrides[r.branch][r.department] = {} overrides[r.branch][r.department][r.metric_key] = {} if r.target is not None: overrides[r.branch][r.department][r.metric_key]["target"] = r.target if r.weight is not None: overrides[r.branch][r.department][r.metric_key]["weight"] = r.weight return self._deep_merge(DEFAULT_KPI_TARGETS, overrides) finally: db.close() def update_kpi_targets(self, branch: str, department: str, metric_key: str, updates: Dict): db = self._get_db() try: row = db.query(KPITargetOverride).filter( KPITargetOverride.branch == branch, KPITargetOverride.department == department, KPITargetOverride.metric_key == metric_key, ).first() if not row: row = KPITargetOverride(branch=branch, department=department, metric_key=metric_key) db.add(row) if "target" in updates: row.target = updates["target"] if "weight" in updates: row.weight = updates["weight"] db.commit() logger.info(f"Updated KPI target {branch}/{department}/{metric_key}: {updates}") finally: db.close() def update_all_kpi_targets(self, targets: Dict): """Bulk-replace: clear existing overrides and insert from dict (target/weight only).""" db = self._get_db() try: db.query(KPITargetOverride).delete() for branch, depts in targets.items(): for department, metrics in depts.items(): for metric_key, vals in metrics.items(): if isinstance(vals, dict) and (vals.get("target") is not None or vals.get("weight") is not None): row = KPITargetOverride( branch=branch, department=department, metric_key=metric_key, target=vals.get("target"), weight=vals.get("weight"), ) db.add(row) db.commit() logger.info("Bulk-updated all KPI targets") finally: db.close() def get_gp_color(self, department_name: str, gp_margin: float) -> str: thresholds = self.get_gp_thresholds() dept_lower = department_name.lower() if "electrical" in dept_lower or "residential" in dept_lower: dept_type = "electrical" if "electrical" in dept_lower else "residential" elif "commercial" in dept_lower: dept_type = "commercial" elif "air" in dept_lower: dept_type = "air" elif "solar" in dept_lower: dept_type = "solar" else: dept_type = "electrical" dept_thresholds = thresholds.get(dept_type, DEFAULT_GP_THRESHOLDS.get(dept_type, {})) green_min = dept_thresholds.get("green_min", 55.0) yellow_min = dept_thresholds.get("yellow_min", 40.0) yellow_max = dept_thresholds.get("yellow_max", 55.0) if gp_margin >= green_min: return "green" elif yellow_min <= gp_margin < yellow_max: return "yellow" return "red" def migrate_json_config_to_db_if_present(): """ One-time migration: if admin_config.json exists, seed revenue_targets, gp_thresholds, and kpi_target_overrides so existing admin-set values are not lost. Call after init_db() when using MySQL. """ import json from app.config.config_storage import CONFIG_STORAGE_FILE if not CONFIG_STORAGE_FILE.exists(): return try: with open(CONFIG_STORAGE_FILE, "r") as f: config = json.load(f) except (json.JSONDecodeError, IOError) as e: logger.warning(f"Could not read config file for migration: {e}") return db = SessionLocal() try: # Revenue targets for key, vals in config.get("targets_breakevens", {}).items(): if "_" in key: parts = key.split("_", 1) try: branch_id, company_id = parts[0], int(parts[1]) row = db.query(RevenueTarget).filter( RevenueTarget.branch_id == branch_id, RevenueTarget.company_id == company_id, ).first() if not row: row = RevenueTarget(branch_id=branch_id, company_id=company_id) db.add(row) if vals.get("target") is not None: row.target = vals["target"] if vals.get("breakeven") is not None: row.breakeven = vals["breakeven"] except (ValueError, IndexError): pass # GP thresholds for dept, th in config.get("gp_thresholds", {}).items(): if dept in DEFAULT_GP_THRESHOLDS: row = db.query(GPThreshold).filter(GPThreshold.department_type == dept).first() if not row: row = GPThreshold(department_type=dept) db.add(row) row.green_min = th.get("green_min", 55.0) row.yellow_min = th.get("yellow_min", 40.0) row.yellow_max = th.get("yellow_max", 55.0) row.red_max = th.get("red_max", 40.0) # KPI overrides kpi = config.get("kpi_targets", {}) for branch, depts in kpi.items(): for department, metrics in depts.items(): for metric_key, m in metrics.items(): if isinstance(m, dict): row = db.query(KPITargetOverride).filter( KPITargetOverride.branch == branch, KPITargetOverride.department == department, KPITargetOverride.metric_key == metric_key, ).first() if not row: row = KPITargetOverride( branch=branch, department=department, metric_key=metric_key ) db.add(row) if m.get("target") is not None: row.target = m["target"] if m.get("weight") is not None: row.weight = m["weight"] db.commit() logger.info("Migrated admin_config.json to database config tables") except Exception as e: logger.warning(f"Config migration failed: {e}") db.rollback() finally: db.close()