import json
import sys
import pandas as pd
import openpyxl
from openpyxl.styles import Font

from pathlib import Path

sys.path.insert(0, str(Path(__file__).parent.parent))
from config.config import (resource_order, method_order, resource_unit_map, resource_groups)
from utils.data_utils import strip_unit_suffix

# Data
with open("data/cost_sliders.json") as f:
    cost_sliders = json.load(f)

with open("data/secondary_resources.json") as _f:
    secondary_resources = {tuple(p) for p in json.load(_f)}

wb = openpyxl.Workbook()

resource_to_unit = {
    r: resource_unit_map[group]
    for group, resources in resource_groups.items()
    for r in resources
}

# instructions
ws0 = wb.active
ws0.title = "instructions"
ws0.column_dimensions["A"].width = 25
ws0.column_dimensions["B"].width = 80
header_font = Font(bold=True)
ws0.append(["Sheet", "Description", "Columns to edit", "Do not modify"])
ws0["A1"].font = header_font
ws0["B1"].font = header_font
ws0["C1"].font = header_font
ws0["D1"].font = header_font
ws0.column_dimensions["C"].width = 35
ws0.column_dimensions["D"].width = 30
ws0.append(["resource_caps",
    "Total available quantities of each resource for 1 year of optimization. Leave blank to exclude a resource.",
    "Available Amount",
    "Resource, Unit"])
ws0.append(["constraints",
    "Which CDR methods are active and their maximum deployment cap (percent of total potential, or absolute value in MtCO2/yr).",
    "active (TRUE/FALSE), cap_type (percent or absolute), cap_value",
    "method"])
ws0.append(["coefficients",
    "Resource usage coefficients for each CDR method. Adjust to reflect your country conditions.",
    "value",
    "method, resource, secondary"])
ws0.append(["custom_resources",
    "User-defined resource batches: sub-types of an existing resource group (e.g. 'Forest biomass - Eucalyptus' within 'Forest biomass'), with their own availability and per-method coefficients. Add one row per method linked to the resource.",
    "All columns (add new rows)",
    "—"])
ws0.append(["enabled_methods",
    "Which CDR methods are allowed to use each standard resource. Set to FALSE to prevent a method from using a resource.",
    "enabled (TRUE/FALSE)",
    "resource, method"])

# resource_caps
ws1 = wb.create_sheet("resource_caps")
ws1.append(["Resource", "Unit", "Available Amount"])
ws1["A1"].font = Font(bold=True)
ws1["B1"].font = Font(bold=True)
ws1["C1"].font = Font(bold=True)
for r in resource_order:
    ws1.append([r, resource_to_unit.get(r, ""), float("nan")])
    
# constraints
ws2 = wb.create_sheet("constraints")
ws2.append(["method", "active", "cap_type (percent or absolute)", "cap_value"])
ws2["A1"].font = Font(bold=True)
ws2["B1"].font = Font(bold=True)
ws2["C1"].font = Font(bold=True)
ws2["D1"].font = Font(bold=True)
for m in method_order:
    ws2.append([m, True, "percent", 100])

ws3 = wb.create_sheet("coefficients")
ws3.append(["method", "resource", "value", "secondary"])
for cell in ws3[1]:
    cell.font = Font(bold=True)
for m in method_order:
    for r, conf in cost_sliders.get(m, {}).items():
        is_secondary = (m, strip_unit_suffix(r)) in secondary_resources
        ws3.append([m, strip_unit_suffix(r), conf["median"], True if is_secondary else False])

ws4 = wb.create_sheet("custom_resources")
ws4.append(["name", "group", "amount", "unit", "method", "min", "median", "max"])
for cell in ws4[1]:
    cell.font = Font(bold=True)
ws4.append(["(Example) Forest biomass - Eucalyptus", "Forest biomass", 5.0, "Mt", "Bio-char - Forest biomass", 0.33, 0.37, 0.82])

ws5 = wb.create_sheet("enabled_methods")
ws5.append(["resource", "method", "enabled"])
for cell in ws5[1]:
    cell.font = Font(bold=True)
for m in method_order:
    for r_with_unit in cost_sliders.get(m, {}):
        r = strip_unit_suffix(r_with_unit)
        ws5.append([r, m, True])

wb.save("data/inputs_template.xlsx")
print("Template generated: data/inputs_template.xlsx")