package service

import (
	"bytes"
	"fmt"
	"github.com/linmadan/egglib-go/core/application"
	"gitlab.fjmaimaimai.com/allied-creation/character-library-metadata-bastion/pkg/application/factory"
	"gitlab.fjmaimaimai.com/allied-creation/character-library-metadata-bastion/pkg/application/table/command"
	"gitlab.fjmaimaimai.com/allied-creation/character-library-metadata-bastion/pkg/application/table/query"
	"gitlab.fjmaimaimai.com/allied-creation/character-library-metadata-bastion/pkg/constant"
	"gitlab.fjmaimaimai.com/allied-creation/character-library-metadata-bastion/pkg/domain"
	"gitlab.fjmaimaimai.com/allied-creation/character-library-metadata-bastion/pkg/infrastructure/api/bytelib"
	"gitlab.fjmaimaimai.com/allied-creation/character-library-metadata-bastion/pkg/infrastructure/domainService"
	"gorm.io/driver/mysql"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"log"
	"os"
	"strings"
)

func (tableService *TableService) ShowBusinessDatabases(ctx *domain.Context, cmd *query.ShowBusinessDatabasesRequest) (interface{}, error) {
	byteCoreService := domainService.ByteCoreService{}
	response, err := byteCoreService.ShowBusinessDatabases(bytelib.ReqShowBusinessDatabases{})
	if err != nil {
		return nil, factory.FastError(err)
	}
	return response, err
}

var GlobalDB []command.DBTablesRequest = []command.DBTablesRequest{
	{
		ByteBankDBName:   "tl_mysql.suplus_salary",
		ByteBankDBZhName: "销导报表系统",
		GenerateBusinessTableViewRequest: command.GenerateBusinessTableViewRequest{
			Host:       "114.55.200.59",
			Port:       "32306",
			User:       "root",
			Password:   "eagle1010",
			DBName:     "suplus_salary_test",
			SchemaName: "public",
			DBType:     "mysql",
		},
	},
	{
		ByteBankDBName:   "allied_creation_reporting_system_test.public",
		ByteBankDBZhName: "销导报表系统",
		GenerateBusinessTableViewRequest: command.GenerateBusinessTableViewRequest{
			Host:       "114.55.200.59",
			Port:       "31543",
			User:       "postgres",
			Password:   "eagle1010",
			DBName:     "allied-creation-reporting-system_test",
			SchemaName: "public",
			DBType:     "postgresql",
		},
	},
	{
		ByteBankDBName:   "allied_creation_reporting_system_prd.public",
		ByteBankDBZhName: "销导报表系统(正式)",
		GenerateBusinessTableViewRequest: command.GenerateBusinessTableViewRequest{
			Host:       "114.55.200.59",
			Port:       "31544",
			User:       "postgres",
			Password:   "eagle1010",
			DBName:     "allied-creation-reporting-system_prd",
			SchemaName: "public",
			DBType:     "postgresql",
		},
	},
}

func getDB(databaseEnName string) (*command.DBTablesRequest, bool) {
	for i := range GlobalDB {
		if GlobalDB[i].ByteBankDBName == databaseEnName {
			return &GlobalDB[i], true
		}
	}
	return nil, false
}
func (tableService *TableService) ShowBusinessTables(ctx *domain.Context, cmd *query.ShowTablesRequest) (interface{}, error) {
	req, ok := getDB(cmd.DatabaseEnName)
	if !ok {
		return nil, factory.FastError(fmt.Errorf("db " + cmd.DatabaseEnName + "未配置"))
	}
	return DBTables(req)
}

func (tableService *TableService) ShowBusinessTables1(ctx *domain.Context, cmd *query.ShowTablesRequest) (interface{}, error) {
	byteCoreService := domainService.ByteCoreService{}
	response, err := byteCoreService.ShowBusinessTables(bytelib.ReqShowBusinessTables{
		DatabaseEnName: cmd.DatabaseEnName,
		DatabaseType:   cmd.DatabaseType,
	})
	if err != nil {
		return nil, factory.FastError(err)
	}
	result := make([]map[string]interface{}, 0)
	for _, t := range response.TableFullNames {
		simpleName := strings.Split(t, ".")
		result = append(result, map[string]interface{}{
			"name":       t,
			"simpleName": simpleName[len(simpleName)-1],
		})
	}
	return map[string]interface{}{
		"list": result,
	}, err
}

func (tableService *TableService) QueryBusinessTable(ctx *domain.Context, cmd *query.ShowTableDataRequest) (interface{}, error) {
	byteCoreService := domainService.ByteCoreService{}
	response, err := byteCoreService.QueryBusinessTable(bytelib.ReqQueryBusinessTable{
		TableFullName: cmd.TableFullName,
		PageNumber:    cmd.PageNumber - 1, // 分页从0开始
		PageSize:      cmd.PageSize,
	})
	if err != nil {
		return nil, factory.FastError(err)
	}
	transactionContext, err := factory.CreateTransactionContext(nil)
	if err != nil {
		return nil, application.ThrowError(application.TRANSACTION_ERROR, err.Error())
	}
	tableRepository, _, _ := factory.FastPgTable(transactionContext, 0)
	table, _ := tableRepository.FindBusinessTable(constant.COMPANY_SU_TIAN_XIA, response.TableFullName)
	if table != nil {
		response.TableRemarkName = table.Name
		response.ShowTableNameBy = table.TableInfo.BusinessTableShowTableNameBy
		response.ShowTableFieldNameBy = table.TableInfo.BusinessTableShowTableFieldNameBy
		for i, f := range response.FieldSchemas {
			for j, jf := range table.DataFields {
				if jf.SQLName == f.FieldEnName {
					response.FieldSchemas[i].FieldZhName = table.DataFields[j].Name
				}
			}
		}

	}
	return response, err
}

func (tableService *TableService) UpdateBusinessTable(ctx *domain.Context, cmd *command.UpdateBusinessTableRequest) (interface{}, error) {
	transactionContext, err := factory.CreateTransactionContext(nil)
	if err != nil {
		return nil, application.ThrowError(application.TRANSACTION_ERROR, err.Error())
	}
	if err := transactionContext.StartTransaction(); err != nil {
		return nil, application.ThrowError(application.TRANSACTION_ERROR, err.Error())
	}
	defer func() {
		transactionContext.RollbackTransaction()
	}()
	var tableRepository domain.TableRepository
	if value, err := factory.CreateTableRepository(map[string]interface{}{
		"transactionContext": transactionContext,
	}); err != nil {
		return nil, application.ThrowError(application.INTERNAL_SERVER_ERROR, err.Error())
	} else {
		tableRepository = value
	}

	ctx.CompanyId = int(constant.COMPANY_SU_TIAN_XIA)

	var (
		fields    = make([]*domain.Field, 0)
		mainTable *domain.Table
	)
	for i, f := range cmd.Fields {
		fields = append(fields, &domain.Field{
			Index:   i + 1,
			Name:    f.FieldZhName,
			SQLName: f.FieldEnName,
			SQLType: f.FieldType, //TODO:类型转换
			Flag:    domain.MainTableField,
		})
	}
	table, _ := tableRepository.FindBusinessTable(constant.COMPANY_SU_TIAN_XIA, cmd.TableFullName)
	if table == nil {
		mainTable = domainService.NewTable(domain.BusinessTable, cmd.TableName, fields, 0).
			WithContext(ctx).
			WithPrefix(domain.BusinessTable.ToString())
		mainTable.SQLName = cmd.TableFullName
		mainTable.DataFields = fields
		mainTable.TableInfo.BusinessTableShowTableNameBy = cmd.ShowTableNameBy
		mainTable.TableInfo.BusinessTableShowTableFieldNameBy = cmd.ShowTableFieldNameBy
		mainTable.TableInfo.TableFrom = 1

		if mainTable, err = tableRepository.Save(mainTable); err != nil {
			return nil, application.ThrowError(application.INTERNAL_SERVER_ERROR, err.Error())
		}
	} else {
		table.Name = cmd.TableName
		table.DataFields = fields
		table.TableInfo.BusinessTableShowTableNameBy = cmd.ShowTableNameBy
		table.TableInfo.BusinessTableShowTableFieldNameBy = cmd.ShowTableFieldNameBy
		if table, err = tableRepository.Save(table); err != nil {
			return nil, application.ThrowError(application.INTERNAL_SERVER_ERROR, err.Error())
		}
	}

	if err = transactionContext.CommitTransaction(); err != nil {
		return nil, application.ThrowError(application.TRANSACTION_ERROR, err.Error())
	}
	return mainTable, nil
}

func (tableService *TableService) GenerateBusinessTable(ctx *domain.Context, cmd *command.GenerateBusinessTableRequest) (interface{}, error) {
	transactionContext, err := factory.CreateTransactionContext(nil)
	if err != nil {
		return nil, application.ThrowError(application.TRANSACTION_ERROR, err.Error())
	}
	if err := transactionContext.StartTransaction(); err != nil {
		return nil, application.ThrowError(application.TRANSACTION_ERROR, err.Error())
	}
	defer func() {
		transactionContext.RollbackTransaction()
	}()
	var tableRepository domain.TableRepository
	if value, err := factory.CreateTableRepository(map[string]interface{}{
		"transactionContext": transactionContext,
	}); err != nil {
		return nil, application.ThrowError(application.INTERNAL_SERVER_ERROR, err.Error())
	} else {
		tableRepository = value
	}
	ctx.CompanyId = int(constant.COMPANY_SU_TIAN_XIA)

	duplicateTable, err := tableRepository.FindOne(map[string]interface{}{"context": ctx, "tableName": cmd.TableName,
		"tableTypes": []string{string(domain.MainTable), string(domain.SubTable), string(domain.SideTable)}})
	if err == nil && duplicateTable != nil {
		return nil, factory.FastError(fmt.Errorf("表名称重复"))
	}

	var (
		fields     = make([]*domain.Field, 0)
		mainTable  *domain.Table
		hasPkField bool
	)
	for i, f := range cmd.Fields {
		if strings.ToLower(f.FieldEnName) == "id" {
			hasPkField = true
			continue
		}
		var filedName = f.FieldZhName
		// 使用备注名
		if cmd.ShowTableFieldNameBy == 0 {
			filedName = f.FieldEnName
		}
		// 字段为空时显示原字段名
		if cmd.ShowTableFieldNameBy == 1 && filedName == "" {
			filedName = f.FieldEnName
		}
		fields = append(fields, &domain.Field{
			Index:   i + 1,
			Name:    filedName,
			SQLName: f.FieldEnName,
			SQLType: f.FieldType, //TODO:类型转换
			Flag:    domain.MainTableField,
		})
	}
	if !hasPkField {
		return nil, factory.FastError(fmt.Errorf("业务表未包含字段 `id`"))
	}
	var tableName = cmd.TableName
	if cmd.ShowTableNameBy == 0 {
		tableName = cmd.TableFullName
	}
	mainTable = domainService.NewTable(domain.MainTable, tableName, fields, 0).
		WithContext(ctx).
		WithPrefix(domain.MainTable.ToString()).ApplyDefaultModule()
	mainTable.SQLName = cmd.TableFullName
	mainTable.TableInfo.TableFrom = 1
	mainTable.DataFields = fields
	if mainTable, err = tableRepository.Save(mainTable); err != nil {
		return nil, application.ThrowError(application.INTERNAL_SERVER_ERROR, err.Error())
	}

	if err = transactionContext.CommitTransaction(); err != nil {
		return nil, application.ThrowError(application.TRANSACTION_ERROR, err.Error())
	}
	return nil, nil
}

type Field struct {
	Field string `json:"field"`
	Type  string `json:"type"`
}

func (tableService *TableService) GenerateBusinessTablesView(ctx *domain.Context, cmd *command.GenerateBusinessTableViewRequest) (interface{}, error) {
	var (
		db  *gorm.DB
		err error
	)
	if cmd.DBType == "" {
		cmd.SchemaName = "postgresql"
	}
	if cmd.SchemaName == "" {
		cmd.SchemaName = "public"
	}

	buf := bytes.NewBuffer(nil)
	if cmd.DBType == "postgresql" {
		db, err = gorm.Open(postgres.Open(fmt.Sprintf("user=%v password=%v host=%v  port=%v dbname=%v sslmode=disable TimeZone=Asia/Shanghai",
			cmd.User, cmd.Password, cmd.Host, cmd.Port, cmd.DBName)), &gorm.Config{PrepareStmt: false})
		if err != nil {
			return nil, factory.FastError(err)
		}
		var tables []string
		if tx := db.Raw(fmt.Sprintf(`select tablename from pg_tables where schemaname='%v';`, cmd.SchemaName)).Scan(&tables); tx.Error != nil {
			return nil, factory.FastError(tx.Error)
		}
		var fields = make([]Field, 0)
		for _, t := range tables {
			if tx := db.Raw(fmt.Sprintf(`SELECT a.attnum,a.attname AS field,t.typname AS type,a.attlen AS length,a.atttypmod AS lengthvar,a.attnotnull AS notnull
FROM pg_class c,pg_attribute a,pg_type t
WHERE c.relname = '%v' and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid
ORDER BY a.attnum;`, t)).Scan(&fields); tx.Error != nil {
				return nil, factory.FastError(tx.Error)
			}
			var commonFields []string
			var jsonbFields []string
			var pkFields []string
			var containPkId = false
			for _, f := range fields {
				if f.Field == "id" {
					containPkId = true
				}
			}
			for index, f := range fields {
				if index == 0 && strings.HasSuffix(f.Field, "id") && !containPkId {
					commonFields = append(commonFields, fmt.Sprintf("%v as id", f.Field))
					pkFields = append(pkFields, f.Field)
				}
				if f.Type == "jsonb" || f.Type == "json" {
					jsonbFields = append(jsonbFields, f.Field)
				} else {
					commonFields = append(commonFields, f.Field)
				}
			}
			buf.WriteString(fmt.Sprintf("-- == 表【%v】建视图语句 ==\n", t))
			buf.WriteString(fmt.Sprintf("-- drop view view_%v;\n", t))
			buf.WriteString(fmt.Sprintf(`DO $$ 
BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.views WHERE table_name = 'view_%s') THEN
    -- 如果存在就删除视图
    DROP VIEW view_%s;
    RAISE NOTICE '视图已删除';
  ELSE
    RAISE NOTICE '视图不存在';
  END IF;
END $$;`, t, t))
			buf.WriteString("\n")
			buf.WriteString(fmt.Sprintf("create view view_%v as select %v from %v;\n", t, strings.Join(commonFields, ","), t))
			buf.WriteString(fmt.Sprintf("-- pk %v\n", strings.Join(pkFields, ",")))
			buf.WriteString(fmt.Sprintf("-- jsonb %v\n", strings.Join(jsonbFields, ",")))
			buf.WriteString("\n\n")
		}
		generalWrite(cmd.DBName+"_tables_view.sql", buf)
	} else if cmd.DBType == "mysql" {
		db, err = gorm.Open(mysql.Open(fmt.Sprintf("%v:%v@tcp(%v:%v)/%v?charset=utf8&parseTime=True&loc=Local",
			cmd.User, cmd.Password, cmd.Host, cmd.Port, cmd.DBName)), &gorm.Config{PrepareStmt: false})
		if err != nil {
			return nil, factory.FastError(err)
		}
		var tables []string
		if tx := db.Raw(fmt.Sprintf(`show tables;`)).Scan(&tables); tx.Error != nil {
			return nil, factory.FastError(tx.Error)
		}
		var fields = make([]Field, 0)
		for _, t := range tables {
			if tx := db.Raw(fmt.Sprintf(`SHOW COLUMNS FROM %s;`, t)).Scan(&fields); tx.Error != nil {
				return nil, factory.FastError(tx.Error)
			}
			writeToBuf(buf, t, fields)
		}
		generalWrite(cmd.DBName+"_tables_view.sql", buf)
	}

	return nil, nil
}

func DBTables(cmd *command.DBTablesRequest) (interface{}, error) {
	var (
		result = make([]map[string]interface{}, 0)
		tables []string
		db     *gorm.DB
		err    error
	)
	if cmd.DBType == "postgresql" {
		db, err = gorm.Open(postgres.Open(fmt.Sprintf("user=%v password=%v host=%v  port=%v dbname=%v sslmode=disable TimeZone=Asia/Shanghai",
			cmd.User, cmd.Password, cmd.Host, cmd.Port, cmd.DBName)), &gorm.Config{PrepareStmt: false})
		if err != nil {
			return nil, factory.FastError(err)
		}
		if tx := db.Raw(fmt.Sprintf(`select tablename from pg_tables where schemaname='%v';`, cmd.SchemaName)).Scan(&tables); tx.Error != nil {
			return nil, factory.FastError(tx.Error)
		}
	} else if cmd.DBType == "mysql" {
		db, err = gorm.Open(mysql.Open(fmt.Sprintf("%v:%v@tcp(%v:%v)/%v?charset=utf8&parseTime=True&loc=Local",
			cmd.User, cmd.Password, cmd.Host, cmd.Port, cmd.DBName)), &gorm.Config{PrepareStmt: false})
		if err != nil {
			return nil, factory.FastError(err)
		}
		if tx := db.Raw(fmt.Sprintf(`show tables;`)).Scan(&tables); tx.Error != nil {
			return nil, factory.FastError(tx.Error)
		}
	}
	for _, t := range tables {
		viewT := "view_" + t
		result = append(result, map[string]interface{}{
			"name":       fmt.Sprintf("%s.%s", cmd.ByteBankDBName, viewT), //返回所有视图 view开头
			"simpleName": viewT,
		})
	}
	return map[string]interface{}{
		"list": result,
	}, nil
}

func writeToBuf(buf *bytes.Buffer, t string, fields []Field) {
	var commonFields []string
	var jsonbFields []string
	var pkFields []string
	var containPkId = false
	for _, f := range fields {
		if f.Field == "id" {
			containPkId = true
		}
	}
	for index, f := range fields {
		if index == 0 && strings.HasSuffix(f.Field, "id") && !containPkId {
			commonFields = append(commonFields, fmt.Sprintf("%v as id", f.Field))
			pkFields = append(pkFields, f.Field)
		}
		if f.Type == "jsonb" || f.Type == "json" {
			jsonbFields = append(jsonbFields, f.Field)
		} else {
			commonFields = append(commonFields, f.Field)
		}
	}
	buf.WriteString(fmt.Sprintf("-- == 表【%v】建视图语句 ==\n", t))
	buf.WriteString(fmt.Sprintf("-- drop view view_%v;\n", t))
	buf.WriteString(fmt.Sprintf("DROP VIEW IF EXISTS %s;", t))
	buf.WriteString("\n")
	buf.WriteString(fmt.Sprintf("create view view_%v as select %v from %v;\n", t, strings.Join(commonFields, ","), t))
	buf.WriteString(fmt.Sprintf("-- pk %v\n", strings.Join(pkFields, ",")))
	buf.WriteString(fmt.Sprintf("-- jsonb %v\n", strings.Join(jsonbFields, ",")))
	buf.WriteString("\n\n")
}

func generalWrite(filename string, buf *bytes.Buffer) {
	f, err := os.OpenFile(filename, os.O_RDONLY|os.O_CREATE|os.O_APPEND, 0666)
	if err != nil {
		log.Println("open file error :", err)
		return
	}
	// 关闭文件
	defer f.Close()
	// 字符串写入
	_, err = f.WriteString(buf.String())
	if err != nil {
		log.Println(err)
		return
	}
}