package egovframework.com.mtdata.schema.service.impl;

import java.sql.*;
import java.util.HashMap;
import java.util.Map;

import javax.annotation.Resource;

import egovframework.com.mtdata.comtsrcdata.service.ComtsrcdataService;
import egovframework.com.mtdata.comtsrcdata.vo.ComtColVO;
import egovframework.com.mtdata.comtsrcdata.vo.ComtExtrtVO;
import egovframework.com.mtdata.comtsrcdata.vo.ComtTableVO;
import egovframework.com.mtdata.comtsrcdata.vo.ComtsrcdataVO;
import egovframework.com.mtdata.database.vo.ColumnInfoVO;
import egovframework.com.mtdata.database.vo.TableInfoVO;
import org.json.simple.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import egovframework.com.mtdata.database.service.DatabaseService;
import egovframework.com.mtdata.database.vo.DatabaseInfoVO;
import egovframework.com.mtdata.schema.service.DatabaseSchemaViewer;

@Service("mysqlSchemaViewer")
public class MySQLSchemaViewerImpl implements DatabaseSchemaViewer {

	private static final Logger log = LoggerFactory.getLogger(MySQLSchemaViewerImpl.class);
	private static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";

	@Resource(name = "DatabaseService")
	private DatabaseService dbService;

	@Resource(name = "ComtsrcdataService")
	private ComtsrcdataService comtsrcdataService;

	private String url;
	private String user;
	private String password;
	private int dbConnId;


	static {
		try {
			Class.forName(JDBC_DRIVER);
		} catch (ClassNotFoundException e) {
			log.error("JDBC Driver loading error", e);
		}
	}

	@Override
	public void viewSchema(DatabaseInfoVO dbInfoVO) {
		url = "jdbc:mysql://" + dbInfoVO.getDbConnIp() + ":" + dbInfoVO.getDbConnPort() + "/";
		user = dbInfoVO.getDbConnUser();
		password = dbInfoVO.getDbConnPw();
		dbConnId = dbInfoVO.getDbConnId();

		try (Connection conn = DriverManager.getConnection(url, user, password)) {
			processDatabases(conn, dbInfoVO);
		} catch (Exception e) {
			log.error("Error while connecting to the database: ", e);
		}

// [테이블 및 컬럼명 가지고 오는 소스]
//		try (Connection conn = DriverManager.getConnection(url, user, password)){
//			// MySQL database의 이름 추출 - 스키마 조회시 databases catalog에 해당하는 데이터만 출력하기 위함
//			Statement stmt = conn.createStatement();
//			// MySQL 시스템 데이터베이스는 제외함
//			ResultSet databases = stmt.executeQuery("SELECT SCHEMA_NAME AS `Database` FROM information_schema.SCHEMATA WHERE SCHEMA_NAME NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')");
//			String dbNm = null;
//			while (databases.next()) {
//				dbNm = databases.getString(1);
//				System.out.println("dbName : " + dbNm);
//				dbInfoVO.setDbNm(dbNm);
//				int insertDbList = dbService.insertDbInfo(dbInfoVO);
//
//				DatabaseMetaData metaData = conn.getMetaData();
//				ResultSet tables = metaData.getTables(dbNm, null, "%", null); //catalog, schemaPattern, tableNamePattern, types
//				// 여기서 부터 필요한 테이블의 정보를 출력함
//				while(tables.next()) {
//					String tableName = tables.getString(3);
//					System.out.println("Table : " + tableName);
//
//					ResultSet columns = metaData.getColumns(dbNm, null, tableName, "%");
//					while (columns.next()) {
//						String columnName = columns.getString(4);
//						String columnType = columns.getString(6);
//						System.out.println("\tColumn: " + columnName+ " (" + columnType + ")");
//					}
//
//				}
//			}
//		}  catch (Exception e) {
//			e.printStackTrace();
//		}
	}

//	기관정보 스키마
	private void processDatabases(Connection conn, DatabaseInfoVO dbConnInfoVO) {
		String selectDbSql = "SELECT SCHEMA_NAME AS `Database` FROM information_schema.SCHEMATA WHERE SCHEMA_NAME NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')";
		try (Statement stmt = conn.createStatement(); ResultSet databases = stmt.executeQuery(selectDbSql)) {

			// 스키마
			while (databases.next()) {
				String dbNm = databases.getString("Database");
				log.info("dbName : {}", dbNm);

				DatabaseInfoVO dbVo = new DatabaseInfoVO();
				dbVo.setDbConnId(dbConnId);
				dbVo.setDbNm(dbNm);

				if (dbService == null) {
					log.error("dbService is null");
				}
				int insertDbList = dbService.insertDbInfo(dbVo);

				if (insertDbList > 0) {
					dbVo.setSysId(dbConnInfoVO.getSysId());
					tableProcess(conn, dbVo);

					log.info("Database info successfully inserted for: {}", dbNm);
				} else {
					log.warn("No database info was inserted for: {}", dbNm);
				}
			}
		} catch (Exception e) {
			log.error("Error processing databases", e);
		}
	}


	//	기관정보 테이블+컬럼
	private void tableProcess(Connection conn, DatabaseInfoVO dbVo) throws SQLException {
//		String selectTableSql = "SELECT TABLE_NAME AS `TableName` FROM information_schema.tables WHERE table_schema="+dbNm+"and table_type=`BASE TABLE`";
		DatabaseMetaData metaData = conn.getMetaData();
		try (ResultSet tables = metaData.getTables(null, dbVo.getDbNm(), null, new String[]{"TABLE"})){

			int checkSysId = dbVo.getSysId();
			log.info("comtExInfo insert success : ", checkSysId);

			// 테이블
			while (tables.next()) {
				String tbNm = tables.getString("TABLE_NAME");

				TableInfoVO tbVo = new TableInfoVO();
				tbVo.setDbLinkId(dbVo.getDbLinkId());
				if(tbNm.equals("sys_config")){
					break;
				}
				tbVo.setTableNm(tbNm);
				log.info("TableName :", tbNm);
				int insertTbCnt = dbService.insertDbTblInfo(tbVo);
				comtsrcProcess(conn, dbVo, tbVo);

				if (insertTbCnt > 0) {
					log.info("TABLE info successfully inserted for: {}", tbNm);
					ResultSet columns = metaData.getColumns(null, dbVo.getDbNm(), tbNm, "%");
					ResultSet pk = metaData.getPrimaryKeys(null, dbVo.getDbNm(), tbNm);

					// 컬럼
					while (columns.next()) {
						String columnName = columns.getString(4);
						String columnType = columns.getString(6);
						boolean nullable  = columns.getInt(11) == 0 ? false : true;;

						ColumnInfoVO colVo = new ColumnInfoVO();
						colVo.setTableId(tbVo.getTableId());
						colVo.setColumnNm(columnName);
						colVo.setColumnType(columnType);
						colVo.setNullable(nullable);
						colVo.setPkey(false);
						colVo.setFkey(false);
//							if(pk==columnName){
//								colVo.setIsPkey(false);
//							}else{
//								colVo.setIsPkey(false);
//							}

						dbService.insertTblColInfo(colVo);
						System.out.println("\tColumn: " + columnName+ " (" + columnType + ")");
					}

				} else {
					log.warn("No TABLE info was inserted for: {}", tbNm);
				}
			}

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



	// 원천데이터 처리
	private void comtsrcProcess(Connection conn, DatabaseInfoVO dbVo, TableInfoVO tbVo) throws SQLException {
		try {
			// [ comtextrtblinfo ] 원천데이터 추출 정보 저장
			ComtExtrtVO comExVo = new ComtExtrtVO();
			comExVo.setSysId(dbVo.getSysId());
			int extCnt = comtsrcdataService.insertComtExtrtInfo(comExVo);

			if (extCnt > 0) {
				String dbNm = dbVo.getDbNm();
				String tbNm = tbVo.getTableNm();

				//	[ comtsrcdatameta_new ] 원천테이블의 테이블 정보 저장
					ComtTableVO comtTableVo = new ComtTableVO();
					comtTableVo.setExtrId(comExVo.getExtrId());
					comtTableVo.setSrcDataTblNm(tbNm);
					int insertTbCnt = comtsrcdataService.insertComtTabledataInfo(comtTableVo);

				    if (insertTbCnt > 0) {
						String selectTableSql = "SELECT * FROM "+dbNm+"."+tbNm;
						Statement stmt = conn.createStatement();
						ResultSet datas = stmt.executeQuery(selectTableSql);
						ResultSetMetaData rsmd = datas.getMetaData();
						int colCnt = rsmd.getColumnCount();
						JSONObject obj = new JSONObject();
						int insertDataCnt = 0;
						int insertColCnt = 0;

						//	[ comtsrcdatacol_new ] 원천테이블의 컬럼 정보 저장
						for (int i=0; i<colCnt; i++) {
							ComtColVO colVo = new ComtColVO();
							colVo.setSrcDataColNm(rsmd.getColumnLabel(i+1));
							colVo.setSrcDataMetaId(comtTableVo.getSrcDataMetaId());
							insertColCnt = comtsrcdataService.insertComtSrcCol(colVo);
						}

						//  [ comtsrcdata_new ] 원천데이터 저장
						if(insertColCnt > 0){
							while (datas.next()){
								Map<String, String> dataVlMap = new HashMap<>();
								for (int i=0; i<colCnt; i++) {
									String columnName = rsmd.getColumnLabel(i+1);
									String value = datas.getString(i+1);
									dataVlMap.put(columnName, value);
								}
								obj.putAll(dataVlMap);
								ComtsrcdataVO srcVo = new ComtsrcdataVO();
								srcVo.setSrcDataVl(obj.toString());
								srcVo.setSrcDataMetaId(comtTableVo.getSrcDataMetaId());
								insertDataCnt = comtsrcdataService.insertComtSrcdata(srcVo);

								if(insertDataCnt > 0){
									log.info("all data insert SUCCESS!!!!!!");
								}else{
									log.warn("No DATA was inserted for: {}", tbNm);
								}
							}
						}else{
							log.warn("No COLUMN info was inserted for: {}", tbNm);
						}

					}else{
						log.warn("No Table info was inserted for: {}", tbNm);
					}
			}

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


	// 원천데이터 -> 공유데이터 INSERT
	private void comtrlsProcess(Connection conn, DatabaseInfoVO dbVo, TableInfoVO tbVo) throws SQLException {
		try {

			ComtExtrtVO comExVo = new ComtExtrtVO();
			comExVo.setSysId(dbVo.getSysId());
			int extCnt = comtsrcdataService.insertComtExtrtInfo(comExVo);

			if (extCnt > 0) {
				String dbNm = dbVo.getDbNm();
				String tbNm = tbVo.getTableNm();

				// [ comtrlstrgtmetadata ] 공유데이터 메타정보 테이블
				ComtTableVO comtTableVo = new ComtTableVO();
				comtTableVo.setExtrId(comExVo.getExtrId());
				comtTableVo.setSrcDataTblNm(tbNm);
				int insertTbCnt = comtsrcdataService.insertComtTabledataInfo(comtTableVo);

				if (insertTbCnt > 0) {
					String selectTableSql = "SELECT * FROM "+dbNm+"."+tbNm;
					Statement stmt = conn.createStatement();
					ResultSet datas = stmt.executeQuery(selectTableSql);
					ResultSetMetaData rsmd = datas.getMetaData();
					int colCnt = rsmd.getColumnCount();
					JSONObject obj = new JSONObject();
					int insertDataCnt = 0;
					int insertColCnt = 0;

					//	[ comtrlsdatacol ]  공유데이터 컬럼 정보 테이블
					for (int i=0; i<colCnt; i++) {
						ComtColVO colVo = new ComtColVO();
						colVo.setSrcDataColNm(rsmd.getColumnLabel(i+1));
						colVo.setSrcDataMetaId(comtTableVo.getSrcDataMetaId());
						insertColCnt = comtsrcdataService.insertComtSrcCol(colVo);
					}

					// [ comtrlsdata ]   공유데이터 테이블
					if(insertColCnt > 0){
						while (datas.next()){
							Map<String, String> dataVlMap = new HashMap<>();
							for (int i=0; i<colCnt; i++) {
								String columnName = rsmd.getColumnLabel(i+1);
								String value = datas.getString(i+1);
								dataVlMap.put(columnName, value);
							}
							obj.putAll(dataVlMap);
							ComtsrcdataVO srcVo = new ComtsrcdataVO();
							srcVo.setSrcDataVl(obj.toString());
							srcVo.setSrcDataMetaId(comtTableVo.getSrcDataMetaId());
							insertDataCnt = comtsrcdataService.insertComtSrcdata(srcVo);

							if(insertDataCnt > 0){
								log.info("all data insert SUCCESS!!!!!!");
							}else{
								log.warn("No DATA was inserted for: {}", tbNm);
							}
						}
					}else{
						log.warn("No COLUMN info was inserted for: {}", tbNm);
					}

				}else{
					log.warn("No Table info was inserted for: {}", tbNm);
				}
			}

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


}
