using System;
using System.Collections.Generic;
using System.IO;
using System.Runtime.InteropServices;
using System.Text.Json;
using System.Threading;
using System.Threading.Tasks;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace AxCopilot.Services.Agent;

public class ExcelSkill : IAgentTool
{
	public string Name => "excel_create";

	public string Description => "Create a styled Excel (.xlsx) file. Supports: header styling (bold white text on blue background), striped rows, column auto-width, formulas (=SUM, =AVERAGE, etc), cell merge, freeze panes (freeze header row), and number formatting.";

	public ToolParameterSchema Parameters
	{
		get
		{
			ToolParameterSchema obj = new ToolParameterSchema
			{
				Properties = new Dictionary<string, ToolProperty>
				{
					["path"] = new ToolProperty
					{
						Type = "string",
						Description = "Output file path (.xlsx). Relative to work folder."
					},
					["sheet_name"] = new ToolProperty
					{
						Type = "string",
						Description = "Sheet name. Default: 'Sheet1'."
					},
					["headers"] = new ToolProperty
					{
						Type = "array",
						Description = "Column headers as JSON array of strings.",
						Items = new ToolProperty
						{
							Type = "string"
						}
					},
					["rows"] = new ToolProperty
					{
						Type = "array",
						Description = "Data rows as JSON array of arrays. Use string starting with '=' for formulas (e.g. '=SUM(A2:A10)').",
						Items = new ToolProperty
						{
							Type = "array",
							Items = new ToolProperty
							{
								Type = "string"
							}
						}
					},
					["style"] = new ToolProperty
					{
						Type = "string",
						Description = "Table style: 'styled' (blue header, striped rows, borders) or 'plain'. Default: 'styled'"
					},
					["col_widths"] = new ToolProperty
					{
						Type = "array",
						Description = "Column widths as JSON array of numbers (in characters). e.g. [15, 10, 20]. Auto-fit if omitted.",
						Items = new ToolProperty
						{
							Type = "number"
						}
					},
					["freeze_header"] = new ToolProperty
					{
						Type = "boolean",
						Description = "Freeze the header row. Default: true for styled."
					},
					["merges"] = new ToolProperty
					{
						Type = "array",
						Description = "Cell merge ranges. e.g. [\"A1:C1\", \"D5:D8\"]",
						Items = new ToolProperty
						{
							Type = "string"
						}
					},
					["summary_row"] = new ToolProperty
					{
						Type = "object",
						Description = "Auto-generate summary row. {\"label\": \"합계\", \"columns\": {\"B\": \"SUM\", \"C\": \"AVERAGE\"}}. Adds formulas at bottom."
					}
				}
			};
			int num = 3;
			List<string> list = new List<string>(num);
			CollectionsMarshal.SetCount(list, num);
			Span<string> span = CollectionsMarshal.AsSpan(list);
			span[0] = "path";
			span[1] = "headers";
			span[2] = "rows";
			obj.Required = list;
			return obj;
		}
	}

	public async Task<ToolResult> ExecuteAsync(JsonElement args, AgentContext context, CancellationToken ct)
	{
		JsonElement value = args.GetProperty("path");
		string path = value.GetString() ?? "";
		JsonElement sn;
		string sheetName = (args.TryGetProperty("sheet_name", out sn) ? (sn.GetString() ?? "Sheet1") : "Sheet1");
		JsonElement st;
		string tableStyle = (args.TryGetProperty("style", out st) ? (st.GetString() ?? "styled") : "styled");
		bool isStyled = tableStyle != "plain";
		JsonElement fh;
		bool freezeHeader = (args.TryGetProperty("freeze_header", out fh) ? fh.GetBoolean() : isStyled);
		string fullPath = FileReadTool.ResolvePath(path, context.WorkFolder);
		if (context.ActiveTab == "Cowork")
		{
			fullPath = AgentContext.EnsureTimestampedPath(fullPath);
		}
		if (!fullPath.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase))
		{
			fullPath += ".xlsx";
		}
		if (!context.IsPathAllowed(fullPath))
		{
			return ToolResult.Fail("경로 접근 차단: " + fullPath);
		}
		if (!(await context.CheckWritePermissionAsync(Name, fullPath)))
		{
			return ToolResult.Fail("쓰기 권한 거부: " + fullPath);
		}
		try
		{
			JsonElement headers = args.GetProperty("headers");
			JsonElement rows = args.GetProperty("rows");
			string dir = Path.GetDirectoryName(fullPath);
			if (!string.IsNullOrEmpty(dir))
			{
				Directory.CreateDirectory(dir);
			}
			using SpreadsheetDocument spreadsheet = SpreadsheetDocument.Create(fullPath, SpreadsheetDocumentType.Workbook);
			WorkbookPart workbookPart = spreadsheet.AddWorkbookPart();
			workbookPart.Workbook = new Workbook();
			WorkbookStylesPart stylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
			stylesPart.Stylesheet = CreateStylesheet(isStyled);
			stylesPart.Stylesheet.Save();
			WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
			worksheetPart.Worksheet = new Worksheet();
			int colCount = headers.GetArrayLength();
			Columns columns = CreateColumns(args, colCount);
			if (columns != null)
			{
				worksheetPart.Worksheet.Append(columns);
			}
			SheetData sheetData = new SheetData();
			worksheetPart.Worksheet.Append(sheetData);
			Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
			sheets.Append(new Sheet
			{
				Id = workbookPart.GetIdOfPart(worksheetPart),
				SheetId = 1u,
				Name = sheetName
			});
			Row headerRow = new Row
			{
				RowIndex = 1u
			};
			int colIdx = 0;
			foreach (JsonElement h in headers.EnumerateArray())
			{
				string cellRef = GetCellReference(colIdx, 0);
				Cell cell = new Cell
				{
					CellReference = cellRef,
					DataType = CellValues.String,
					CellValue = new CellValue(h.GetString() ?? ""),
					StyleIndex = (isStyled ? 1u : 0u)
				};
				headerRow.Append(cell);
				colIdx++;
			}
			sheetData.Append(headerRow);
			int rowCount = 0;
			uint rowNum = 2u;
			foreach (JsonElement row in rows.EnumerateArray())
			{
				Row dataRow = new Row
				{
					RowIndex = rowNum
				};
				int ci = 0;
				foreach (JsonElement cellVal in row.EnumerateArray())
				{
					string cellRef2 = GetCellReference(ci, (int)(rowNum - 1));
					Cell cell2 = new Cell
					{
						CellReference = cellRef2
					};
					if (isStyled && rowCount % 2 == 0)
					{
						cell2.StyleIndex = 2u;
					}
					string strVal = cellVal.ToString();
					if (strVal.StartsWith('='))
					{
						cell2.CellFormula = new CellFormula(strVal);
						cell2.DataType = null;
					}
					else if (cellVal.ValueKind == JsonValueKind.Number)
					{
						cell2.DataType = CellValues.Number;
						cell2.CellValue = new CellValue(cellVal.GetDouble().ToString());
					}
					else
					{
						cell2.DataType = CellValues.String;
						cell2.CellValue = new CellValue(strVal);
					}
					dataRow.Append(cell2);
					ci++;
				}
				sheetData.Append(dataRow);
				rowCount++;
				rowNum++;
			}
			if (args.TryGetProperty("summary_row", out var summary))
			{
				AddSummaryRow(sheetData, summary, rowNum, colCount, rowCount, isStyled);
			}
			if (args.TryGetProperty("merges", out var merges) && merges.ValueKind == JsonValueKind.Array)
			{
				MergeCells mergeCells = new MergeCells();
				foreach (JsonElement item in merges.EnumerateArray())
				{
					string range = item.GetString();
					if (!string.IsNullOrEmpty(range))
					{
						mergeCells.Append(new MergeCell
						{
							Reference = range
						});
					}
				}
				if (mergeCells.HasChildren)
				{
					worksheetPart.Worksheet.InsertAfter(mergeCells, sheetData);
				}
			}
			if (freezeHeader)
			{
				SheetViews sheetViews = new SheetViews(new SheetView(new Pane
				{
					VerticalSplit = 1.0,
					TopLeftCell = "A2",
					ActivePane = PaneValues.BottomLeft,
					State = PaneStateValues.Frozen
				}, new Selection
				{
					Pane = PaneValues.BottomLeft,
					ActiveCell = "A2",
					SequenceOfReferences = new ListValue<StringValue>
					{
						InnerText = "A2"
					}
				})
				{
					TabSelected = true,
					WorkbookViewId = 0u
				});
				OpenXmlElement insertBefore = (OpenXmlElement)(((object)worksheetPart.Worksheet.GetFirstChild<Columns>()) ?? ((object)worksheetPart.Worksheet.GetFirstChild<SheetData>()));
				worksheetPart.Worksheet.InsertBefore(sheetViews, insertBefore);
			}
			workbookPart.Workbook.Save();
			List<string> features = new List<string>();
			if (isStyled)
			{
				features.Add("스타일 적용");
			}
			if (freezeHeader)
			{
				features.Add("틀 고정");
			}
			if (args.TryGetProperty("merges", out value))
			{
				features.Add("셀 병합");
			}
			if (args.TryGetProperty("summary_row", out value))
			{
				features.Add("요약행");
			}
			string featureStr = ((features.Count > 0) ? (" [" + string.Join(", ", features) + "]") : "");
			return ToolResult.Ok($"Excel 파일 생성 완료: {fullPath}\n시트: {sheetName}, 열: {colCount}, 행: {rowCount}{featureStr}", fullPath);
		}
		catch (Exception ex)
		{
			return ToolResult.Fail("Excel 생성 실패: " + ex.Message);
		}
	}

	private static Stylesheet CreateStylesheet(bool isStyled)
	{
		Stylesheet stylesheet = new Stylesheet();
		Fonts fonts = new Fonts(new Font(new FontSize
		{
			Val = 11.0
		}, new FontName
		{
			Val = "맑은 고딕"
		}), new Font(new Bold(), new FontSize
		{
			Val = 11.0
		}, new Color
		{
			Rgb = "FFFFFFFF"
		}, new FontName
		{
			Val = "맑은 고딕"
		}), new Font(new Bold(), new FontSize
		{
			Val = 11.0
		}, new FontName
		{
			Val = "맑은 고딕"
		}));
		stylesheet.Append(fonts);
		Fills fills = new Fills(new Fill(new PatternFill
		{
			PatternType = PatternValues.None
		}), new Fill(new PatternFill
		{
			PatternType = PatternValues.Gray125
		}), new Fill(new PatternFill
		{
			PatternType = PatternValues.Solid,
			ForegroundColor = new ForegroundColor
			{
				Rgb = "FF2E74B5"
			},
			BackgroundColor = new BackgroundColor
			{
				Indexed = 64u
			}
		}), new Fill(new PatternFill
		{
			PatternType = PatternValues.Solid,
			ForegroundColor = new ForegroundColor
			{
				Rgb = "FFF2F7FB"
			},
			BackgroundColor = new BackgroundColor
			{
				Indexed = 64u
			}
		}), new Fill(new PatternFill
		{
			PatternType = PatternValues.Solid,
			ForegroundColor = new ForegroundColor
			{
				Rgb = "FFE8E8E8"
			},
			BackgroundColor = new BackgroundColor
			{
				Indexed = 64u
			}
		}));
		stylesheet.Append(fills);
		Borders borders = new Borders(new Border(new LeftBorder(), new RightBorder(), new TopBorder(), new BottomBorder(), new DiagonalBorder()), new Border(new LeftBorder(new Color
		{
			Rgb = "FFD9D9D9"
		})
		{
			Style = BorderStyleValues.Thin
		}, new RightBorder(new Color
		{
			Rgb = "FFD9D9D9"
		})
		{
			Style = BorderStyleValues.Thin
		}, new TopBorder(new Color
		{
			Rgb = "FFD9D9D9"
		})
		{
			Style = BorderStyleValues.Thin
		}, new BottomBorder(new Color
		{
			Rgb = "FFD9D9D9"
		})
		{
			Style = BorderStyleValues.Thin
		}, new DiagonalBorder()));
		stylesheet.Append(borders);
		CellFormats cellFormats = new CellFormats(new CellFormat
		{
			FontId = 0u,
			FillId = 0u,
			BorderId = (isStyled ? 1u : 0u),
			ApplyBorder = isStyled
		}, new CellFormat
		{
			FontId = 1u,
			FillId = 2u,
			BorderId = 1u,
			ApplyFont = true,
			ApplyFill = true,
			ApplyBorder = true,
			Alignment = new Alignment
			{
				Horizontal = HorizontalAlignmentValues.Center,
				Vertical = VerticalAlignmentValues.Center
			}
		}, new CellFormat
		{
			FontId = 0u,
			FillId = 3u,
			BorderId = 1u,
			ApplyFill = true,
			ApplyBorder = true
		}, new CellFormat
		{
			FontId = 2u,
			FillId = 4u,
			BorderId = 1u,
			ApplyFont = true,
			ApplyFill = true,
			ApplyBorder = true
		});
		stylesheet.Append(cellFormats);
		return stylesheet;
	}

	private static Columns? CreateColumns(JsonElement args, int colCount)
	{
		JsonElement value;
		bool flag = args.TryGetProperty("col_widths", out value) && value.ValueKind == JsonValueKind.Array;
		Columns columns = new Columns();
		for (int i = 0; i < colCount; i++)
		{
			double num = 15.0;
			if (flag && i < value.GetArrayLength())
			{
				num = value[i].GetDouble();
			}
			columns.Append(new Column
			{
				Min = (uint)(i + 1),
				Max = (uint)(i + 1),
				Width = num,
				CustomWidth = true
			});
		}
		return columns;
	}

	private static void AddSummaryRow(SheetData sheetData, JsonElement summary, uint rowNum, int colCount, int dataRowCount, bool isStyled)
	{
		JsonElement value;
		string text = (summary.TryGetProperty("label", out value) ? (value.GetString() ?? "합계") : "합계");
		JsonElement value2;
		JsonElement jsonElement = (summary.TryGetProperty("columns", out value2) ? value2 : default(JsonElement));
		Row row = new Row
		{
			RowIndex = rowNum
		};
		Cell cell = new Cell
		{
			CellReference = GetCellReference(0, (int)(rowNum - 1)),
			DataType = CellValues.String,
			CellValue = new CellValue(text),
			StyleIndex = (isStyled ? 3u : 0u)
		};
		row.Append(cell);
		for (int i = 1; i < colCount; i++)
		{
			string columnLetter = GetColumnLetter(i);
			Cell cell2 = new Cell
			{
				CellReference = GetCellReference(i, (int)(rowNum - 1)),
				StyleIndex = (isStyled ? 3u : 0u)
			};
			if (jsonElement.ValueKind == JsonValueKind.Object && jsonElement.TryGetProperty(columnLetter, out var value3))
			{
				string value4 = value3.GetString()?.ToUpper() ?? "SUM";
				int num = 2;
				int value5 = num + dataRowCount - 1;
				cell2.CellFormula = new CellFormula($"={value4}({columnLetter}{num}:{columnLetter}{value5})");
			}
			row.Append(cell2);
		}
		sheetData.Append(row);
	}

	private static string GetColumnLetter(int colIndex)
	{
		string text = "";
		while (colIndex >= 0)
		{
			text = (char)(65 + colIndex % 26) + text;
			colIndex = colIndex / 26 - 1;
		}
		return text;
	}

	private static string GetCellReference(int colIndex, int rowIndex)
	{
		return $"{GetColumnLetter(colIndex)}{rowIndex + 1}";
	}
}