package mytest;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.json.JSONArray;
import org.json.JSONObject;
import org.springframework.http.HttpEntity;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpMethod;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.client.RestTemplate;

import com.fasterxml.jackson.core.exc.StreamWriteException;
import com.fasterxml.jackson.databind.DatabindException;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ObjectNode;

public class GrafanaETL {

	private static ObjectMapper objectMapper = new ObjectMapper();
	private static Connection conn;

	private static final String DB_URL = "jdbc:mysql://192.168.10.209:3306/dev_grafana";
	private static final String DB_USERNAME = "grafana";
	private static final String DB_PASSWORD = "grafana16313302";

	private static final String GRAFANA_A_URL = "https://gfn.veri-id-prd.com";
	private static final String GRAFANA_B_URL = "http://192.168.10.209:3001";
	private static final String API_KEY_A = "Bearer glsa_JBbC5bQZ0Jpj1N0B5cW89oIf6zckwH52_8c600c43"; // Grafana A 的 API
																										// Key
	private static final String API_KEY_B = "Basic YWRtaW46YWRtaW4xNjMxMzMwMg=="; // Grafana B 的 API Key
	private static final RestTemplate restTemplate = new RestTemplate();

	private static final String BACKUP_FOLDER = "D:\\TEMP\\PRD\\";

	public static void main(String args[]) {

		try {
			conn = getConnection();
//			backupAllFolders();
//			backupAllDashboardAndLibs();
//			backupDataSource();

//			importAllFolders();
//			importAllDataSource();

//			importAllLibrary();
			importAllDashboard();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}

	}

	public static void backupAllFolders() throws Exception {

		List<Map<String, Object>> folders = getAllFoldersFromGrafanaA();
		ObjectMapper objectMapper = new ObjectMapper();
		String json = objectMapper.writeValueAsString(folders);
		writeToFile(BACKUP_FOLDER + "//folders.json", json);

	}

	public static void importAllFolders() {

		try {
			
			String deleteSQL = "delete from dashboard le where is_folder = 1";
			Statement stat = conn.createStatement();
			stat.execute(deleteSQL);
			stat.close();
			// 1️⃣ 取得 Grafana A 的所有資料夾
			List<Map<String, Object>> folders = getAllFoldersFromGrafanaA();

			// 2️⃣ 匯入資料夾到 Grafana B
			for (Map<String, Object> folder : folders) {
				createFolderInGrafanaB(folder);
			}

			System.out.println("✅ 資料夾同步完成！");

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static List getAllFoldersFromGrafanaA() throws Exception {

		String url = GRAFANA_A_URL + "/api/folders";
		HttpHeaders headers = new HttpHeaders();
		headers.set("Authorization", API_KEY_A);
		headers.setContentType(MediaType.APPLICATION_JSON);
		HttpEntity<String> entity = new HttpEntity<>(headers);

		ResponseEntity<List> response = restTemplate.exchange(url, HttpMethod.GET, entity, List.class);
		if (response.getStatusCode() == HttpStatus.OK) {
			return (response.getBody());
			// writeToFile(BACKUP_FOLDER + "//folders.json", new
			// JSONArray(response.getBody()).toString());
		}
		return Collections.emptyList();
	}

	public static void createFolderInGrafanaB(Map<String, Object> folder) throws Exception {

		String url = GRAFANA_B_URL + "/api/folders";
		HttpHeaders headers = new HttpHeaders();
		headers.set("Authorization", API_KEY_B);
		headers.setContentType(MediaType.APPLICATION_JSON);

		// 準備要傳遞的 JSON 資料
		Map<String, Object> requestBody = new HashMap<>();
		requestBody.put("title", folder.get("title")); // 設定資料夾名稱
		requestBody.put("uid", folder.get("uid")); // 保留原始 UID

		HttpEntity<Map<String, Object>> entity = new HttpEntity<>(requestBody, headers);
		ResponseEntity<String> response = restTemplate.exchange(url, HttpMethod.POST, entity, String.class);

		if (response.getStatusCode() == HttpStatus.OK || response.getStatusCode() == HttpStatus.CONFLICT) {
			System.out.println("✅ 匯入資料夾: " + folder.get("title"));
			String deletePermission = "delete from permission p where "
					+ "`scope` like '%"+folder.get("uid")+"%' "
					+ " and (role_id = 2 or role_id = 3)";
			Statement stat = conn.createStatement();
			stat.execute(deletePermission);
			stat.close();
		} else {
			System.out.println("❌ 無法匯入資料夾: " + folder.get("title") + "，錯誤：" + response.getBody());
		}

	}

	public static void backupAllDashboardAndLibs() {
		try {

			RestTemplate restTemplate = new RestTemplate();
			HttpHeaders headers = new HttpHeaders();
			headers.set("Authorization", API_KEY_A);
			headers.setContentType(MediaType.APPLICATION_JSON);

			String searchUrl = GRAFANA_A_URL + "/api/search?type=dash-db";
			HttpEntity<String> request = new HttpEntity<>(headers);
			ResponseEntity<String> searchResponse = restTemplate.exchange(searchUrl, HttpMethod.GET, request,
					String.class);

			ObjectMapper objectMapper = new ObjectMapper();
			JsonNode searchResult = objectMapper.readTree(searchResponse.getBody());

			for (JsonNode dashboard : searchResult) {

				String dashboardUid = dashboard.get("uid").asText();

				String dashboardTitle = dashboard.get("title").asText();

				String dashboardUrl = GRAFANA_A_URL + "/api/dashboards/uid/" + dashboardUid;
				ResponseEntity<String> dashboardResponse = restTemplate.exchange(dashboardUrl, HttpMethod.GET, request,
						String.class);
				JsonNode dashboardJson = objectMapper.readTree(dashboardResponse.getBody());

				writeToFile(BACKUP_FOLDER + "dashboard\\" + dashboardTitle + ".json", dashboardJson);

				JsonNode panels = dashboardJson.path("dashboard").path("panels");
				for (JsonNode panel : panels) {

					if (panel.has("libraryPanel")) {

						String libraryPanelUid = panel.get("libraryPanel").get("uid").asText();
						System.out.println("Found library panel with UID: " + libraryPanelUid);

						String libraryElementsUrl = GRAFANA_A_URL + "/api/library-elements/" + libraryPanelUid;

						HttpEntity<String> libraryRequest = new HttpEntity<>(headers);
						ResponseEntity<String> libraryResponse = restTemplate.exchange(libraryElementsUrl,
								HttpMethod.GET, libraryRequest, String.class);

						String resBody = libraryResponse.getBody();

						JsonNode libraryElementsJson = objectMapper.readTree(resBody);

						writeToFile(BACKUP_FOLDER + "library\\"
								+ libraryElementsJson.path("result").get("name").asText() + ".json",
								libraryElementsJson);

					}
				}

			}

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private static void backupDataSource() throws Exception {

		String urlA = GRAFANA_A_URL + "/api/datasources";
		HttpHeaders headers = new HttpHeaders();
		headers.set("Authorization", API_KEY_A);
		headers.setContentType(MediaType.APPLICATION_JSON);
		HttpEntity<String> entity = new HttpEntity<>(headers);

		ResponseEntity<String> response = restTemplate.exchange(urlA, HttpMethod.GET, entity, String.class);
		if (response.getStatusCode() == HttpStatus.OK) {
			System.out.println(response.getBody());
			writeToFile(BACKUP_FOLDER + "//datasource.json", new JSONArray(response.getBody()).toString());
		}

	}

	public static void importAllDataSource() {

		try {

			String deleteSQL = "delete from data_source";
			Statement stat = conn.createStatement();
			stat.execute(deleteSQL);
			stat.close();
			
			String datasourceList = getAllDataSourceFromGrafanaA();

			JsonNode dataSources = objectMapper.readTree(datasourceList);

			for (JsonNode datasource : dataSources) {
				createDataSourceInGrafanaB(datasource);
			}

			System.out.println("✅ DataSource同步完成！");

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static String getAllDataSourceFromGrafanaA() throws Exception {

		String url = GRAFANA_A_URL + "/api/datasources";
		HttpHeaders headers = new HttpHeaders();
		headers.set("Authorization", API_KEY_A);
		headers.setContentType(MediaType.APPLICATION_JSON);
		HttpEntity<String> entity = new HttpEntity<>(headers);

		ResponseEntity<String> response = restTemplate.exchange(url, HttpMethod.GET, entity, String.class);
		if (response.getStatusCode() == HttpStatus.OK) {
			return (response.getBody());
		}
		return "";
	}

	public static void createDataSourceInGrafanaB(JsonNode datasource) throws Exception {

		String url = GRAFANA_B_URL + "/api/datasources";
		HttpHeaders headers = new HttpHeaders();
		headers.set("Authorization", API_KEY_B);
		headers.setContentType(MediaType.APPLICATION_JSON);

		((ObjectNode) datasource).remove("id");
		String dataSourceJson = objectMapper.writerWithDefaultPrettyPrinter().writeValueAsString(datasource);

		HttpEntity<String> entity = new HttpEntity<>(dataSourceJson, headers);
		ResponseEntity<String> response = restTemplate.exchange(url, HttpMethod.POST, entity, String.class);

		if (response.getStatusCode() == HttpStatus.OK || response.getStatusCode() == HttpStatus.CONFLICT) {
			System.out.println("✅ 匯入DataSource: " + datasource.get("name").asText());
		} else {
			System.out.println("❌ 無法匯入DataSource: " + datasource.get("name").asText() + "，錯誤：" + response.getBody());
		}

	}

	public static void importAllLibrary() throws Exception {

		String folderPath = BACKUP_FOLDER + "library\\";
		System.out.println(folderPath);
		File dir = new File(folderPath);
		if (dir.isDirectory()) {
			File[] fs = dir.listFiles();
			for (int i = 0; i < fs.length; i++) {
				importLibrary(fs[i]);
			}
		}

	}

	public static void importLibrary(File f) throws Exception {

		String titleName = "";
		try {

			String jsonStr = readFile(new FileInputStream(f), "UTF-8");
			JSONObject jsonObject = new JSONObject(jsonStr).getJSONObject("result");

			titleName = jsonObject.getString("name");

			ObjectMapper objectMapper = new ObjectMapper();
			Map<String, Object> jsonMap = jsonObject.getJSONObject("model").toMap();
			String jsonModel = objectMapper.writeValueAsString(jsonMap);

			String deleteSQL = "DELETE FROM library_element WHERE uid = ?";
			PreparedStatement pstmt = conn.prepareStatement(deleteSQL);
			pstmt.setString(1, jsonObject.getString("uid"));
			pstmt.executeUpdate();
			pstmt.close();

			String insertSQL = "INSERT INTO library_element "
					+ "(org_id, folder_id, uid, name, kind, type, description, model, created, created_by, updated, updated_by, version) "
					+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, ?, CURRENT_TIMESTAMP, ?, ?)";

			pstmt = conn.prepareStatement(insertSQL);
			pstmt.setInt(1, jsonObject.getInt("orgId"));
			pstmt.setInt(2, 0);
			pstmt.setString(3, jsonObject.getString("uid"));
			pstmt.setString(4, jsonObject.getString("name"));
			pstmt.setInt(5, jsonObject.getInt("kind"));
			pstmt.setString(6, jsonObject.getString("type"));
			pstmt.setString(7, ""); // description 預設為空
			pstmt.setString(8, jsonModel); // **正確存入 `MEDIUMTEXT` 欄位**
			pstmt.setInt(9, 1); // created_by 預設為 1
			pstmt.setInt(10, 1); // updated_by 預設為 1
			pstmt.setInt(11, 1); // version 預設為 1

			pstmt.executeUpdate();
			pstmt.close();

			String updateSQL = "UPDATE library_element SET folder_id = (SELECT id FROM dashboard WHERE uid = ?) WHERE uid = ?";
			pstmt = conn.prepareStatement(updateSQL);
			pstmt.setString(1, jsonObject.getString("folderUid"));
			pstmt.setString(2, jsonObject.getString("uid"));
			pstmt.executeUpdate();
			pstmt.close();

			System.out.println("✅ 匯入 Library: " + titleName);

		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("insert " + titleName + " failed.");
		}
	}

	public static void importAllDashboard() throws Exception {

		String deleteSQL = "delete from dashboard le where is_folder = 0";
		Statement stat = conn.createStatement();
		stat.execute(deleteSQL);
		stat.close();
		
		String folderPath = BACKUP_FOLDER + "dashboard\\";
		System.out.println(folderPath);
		File dir = new File(folderPath);
		if (dir.isDirectory()) {
			File[] fs = dir.listFiles();
			for (int i = 0; i < fs.length; i++) {
				importDashboard(fs[i]);
			}
		}

	}
	
	private static void importDashboard(File f) {

		String title = "";
		
		try {

			String dashboardString = readFile(new FileInputStream(f), "UTF-8");
			JSONObject metaObj = new JSONObject(dashboardString).getJSONObject("meta");
			JSONObject dashboardObj = new JSONObject(dashboardString).getJSONObject("dashboard");
			
			dashboardObj.remove("id");
			title = dashboardObj.getString("title");
			
			JSONObject importObj = new JSONObject();

			importObj.put("dashboard", dashboardObj);
			importObj.put("folderUid", metaObj.getString("folderUid"));
			importObj.put("overwrite", true);
			importObj.put("inputs", new JSONArray());
			importObj.put("version", 1);

			String urlB = GRAFANA_B_URL + "/api/dashboards/import";
			HttpHeaders headersB = new HttpHeaders();
			headersB.set("Authorization", API_KEY_B);
			headersB.setContentType(MediaType.APPLICATION_JSON);

			String importString = importObj.toString();
			//String importString = importObj.toString().replaceAll("\"collapsed\" : false", "\"collapsed\" : true");
			
			HttpEntity<String> entityB = new HttpEntity<>(importString, headersB);

			ResponseEntity<String> responseB = restTemplate.exchange(urlB, HttpMethod.POST, entityB, String.class);

			if (responseB.getStatusCode() == HttpStatus.OK || responseB.getStatusCode() == HttpStatus.CREATED) {
				System.out.println("✅ 匯入 Dashboard: " + dashboardObj.get("title"));
				
				String deletePermission = "delete from permission p where "
						+ "`scope` like '%"+dashboardObj.getString("uid")+"%' "
						+ " and (role_id = 2 or role_id = 3)";
				Statement stat = conn.createStatement();
				stat.execute(deletePermission);
				stat.close();
				
			} else {
				System.out.println(
						"❌ 無法匯入 Dashboard: " + dashboardObj.get("title") + "，錯誤：" + responseB.getBody());
			}

		} catch (Exception e) {
			System.out.println(
					"❌ 無法匯入 Dashboard: " + title + "，錯誤：");
			e.printStackTrace();
		}

	}

	private static void writeToFile(String fileName, String data)
			throws StreamWriteException, DatabindException, IOException {

		ObjectMapper objectMapper = new ObjectMapper();
		File outputFile = new File(fileName);
		if (!outputFile.getParentFile().exists()) {
			outputFile.getParentFile().mkdirs();
		}
		BufferedWriter writer = new BufferedWriter(new FileWriter(fileName));
		writer.write(data);
		writer.flush();
		writer.close();

	}

	private static void writeToFile(String fileName, JsonNode data)
			throws StreamWriteException, DatabindException, IOException {

		ObjectMapper objectMapper = new ObjectMapper();
		File outputFile = new File(fileName);
		if (!outputFile.getParentFile().exists()) {
			outputFile.getParentFile().mkdirs();
		}
		objectMapper.writerWithDefaultPrettyPrinter().writeValue(outputFile, data);
		System.out.println("Dashboard saved to file: " + outputFile.getName());

	}

	public static String readFile(InputStream fis, String encoding) {

		StringBuffer sb = new StringBuffer();

		try {

			BufferedReader in = new BufferedReader(new InputStreamReader(fis, encoding));
			try {
				String data = null;
				while ((data = in.readLine()) != null) {
					sb.append(data);
				}
			} finally {
				in.close();
				fis.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return sb.toString();
	}

	private static Connection getConnection() throws Exception {

		Class.forName("com.mysql.cj.jdbc.Driver");
		return DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);

	}
}