package system

import (
	"context"

	"gorm.io/gorm"
)

// AutoCodeOracle Oracle适配器
type AutoCodeOracle struct {
	db     *gorm.DB
	dbList map[string]*gorm.DB
}

// NewAutoCodeOracle 创建Oracle适配器
func NewAutoCodeOracle(db *gorm.DB, dbList map[string]*gorm.DB) *AutoCodeOracle {
	return &AutoCodeOracle{
		db:     db,
		dbList: dbList,
	}
}

// GetDB 获取数据库的所有数据库名
func (s *AutoCodeOracle) GetDB(ctx context.Context) ([]Db, error) {
	var entities []Db
	sql := `SELECT lower(username) AS "database" FROM all_users`
	err := s.db.WithContext(ctx).Raw(sql).Scan(&entities).Error
	return entities, err
}

// GetTables 获取数据库的所有表名
func (s *AutoCodeOracle) GetTables(ctx context.Context, dbName string) ([]Table, error) {
	var entities []Table
	sql := `select lower(table_name) as "table_name" from all_tables where lower(owner) = ?`
	err := s.db.WithContext(ctx).Raw(sql, dbName).Scan(&entities).Error
	return entities, err
}

// GetColumn 获取指定数据库和指定数据表的所有字段名,类型值等
func (s *AutoCodeOracle) GetColumn(ctx context.Context, tableName, dbName string) ([]Column, error) {
	var entities []Column
	sql := `
	SELECT
    lower(a.COLUMN_NAME) as "column_name",
    (CASE WHEN a.DATA_TYPE = 'NUMBER' AND a.DATA_SCALE=0 THEN 'int' else lower(a.DATA_TYPE) end)  as "data_type",
    (CASE WHEN a.DATA_TYPE = 'NUMBER' THEN a.DATA_PRECISION else a.DATA_LENGTH end) as "data_type_long",
    b.COMMENTS as "column_comment",
    (CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END) as "primary_key",
    a.COLUMN_ID
FROM
    all_tab_columns a
JOIN
    all_col_comments b ON a.OWNER = b.OWNER AND a.TABLE_NAME = b.TABLE_NAME AND a.COLUMN_NAME = b.COLUMN_NAME
LEFT JOIN
    (
        SELECT
            acc.OWNER,
            acc.TABLE_NAME,
            acc.COLUMN_NAME
        FROM
            all_cons_columns acc
        JOIN
            all_constraints ac ON acc.OWNER = ac.OWNER AND acc.CONSTRAINT_NAME = ac.CONSTRAINT_NAME
        WHERE
            ac.CONSTRAINT_TYPE = 'P'
    ) pk ON a.OWNER = pk.OWNER AND a.TABLE_NAME = pk.TABLE_NAME AND a.COLUMN_NAME = pk.COLUMN_NAME
WHERE
    lower(a.table_name) = ?
    AND lower(a.OWNER) = ?
ORDER BY
    a.COLUMN_ID
`
	err := s.db.WithContext(ctx).Raw(sql, tableName, dbName).Scan(&entities).Error
	return entities, err
}