func (db *Database) ListResourceNames() ([]string, error) {
	return db.listField("resources", "name")
}
ListResourceNames lists all Resource names from the Database
package db
import (
	"database/sql"
	"encoding/json"
	"fmt"
	"net/url"
	"strconv"
	"strings"
	"sync"
	"github.com/camptocamp/terraboard/config"
	"github.com/camptocamp/terraboard/internal/terraform/addrs"
	"github.com/camptocamp/terraboard/internal/terraform/states"
	"github.com/camptocamp/terraboard/internal/terraform/states/statefile"
	"github.com/camptocamp/terraboard/state"
	"github.com/camptocamp/terraboard/types"
	log "github.com/sirupsen/logrus"
	ctyJson "github.com/zclconf/go-cty/cty/json"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)
type Database struct {
	*gorm.DB
	lock sync.Mutex
}
var pageSize = 20
func Init(config config.DBConfig, debug bool) *Database {
	var err error
	connString := fmt.Sprintf(
		"host=%s port=%d user=%s dbname=%s sslmode=%s password=%s",
		config.Host,
		config.Port,
		config.User,
		config.Name,
		config.SSLMode,
		config.Password,
	)
	db, err := gorm.Open(postgres.Open(connString), &gorm.Config{
		Logger: &LogrusGormLogger,
	})
	if err != nil {
		log.Fatal(err)
	}
	log.Infof("Automigrate")
	err = db.AutoMigrate(
		&types.Lineage{},
		&types.Version{},
		&types.State{},
		&types.Module{},
		&types.Resource{},
		&types.Attribute{},
		&types.OutputValue{},
		&types.Plan{},
		&types.PlanModel{},
		&types.PlanModelVariable{},
		&types.PlanOutput{},
		&types.PlanResourceChange{},
		&types.PlanState{},
		&types.PlanStateModule{},
		&types.PlanStateOutput{},
		&types.PlanStateResource{},
		&types.PlanStateResourceAttribute{},
		&types.PlanStateValue{},
		&types.Change{},
	)
	if err != nil {
		log.Fatalf("Migration failed: %v\n", err)
	}
	if debug {
		db.Config.Logger.LogMode(logger.Info)
	}
	d := &Database{DB: db}
	if err = d.MigrateLineage(); err != nil {
		log.Fatalf("Lineage migration failed: %v\n", err)
	}
	return d
}
func (db *Database) MigrateLineage() error {
	if db.Migrator().HasColumn(&types.State{}, "lineage") {
		var states []types.State
		if err := db.Find(&states).Error; err != nil {
			return err
		}
		for _, st := range states {
			if err := db.UpdateState(st); err != nil {
				return fmt.Errorf("Failed to update %s state during lineage migration: %v", st.Path, err)
			}
		}
		if err := db.Migrator().DropColumn(&types.State{}, "lineage"); err != nil {
			return fmt.Errorf("Failed to drop lineage column during migration: %v", err)
		}
	}
	return nil
}
type attributeValues map[string]interface{}
func (db *Database) stateS3toDB(sf *statefile.File, path string, versionID string) (st types.State, err error) {
	var version types.Version
	db.First(&version, types.Version{VersionID: versionID})
	var lineage types.Lineage
	db.lock.Lock()
	err = db.FirstOrCreate(&lineage, types.Lineage{Value: sf.Lineage}).Error
	if err != nil || lineage.ID == 0 {
		log.WithField("error", err).
			Error("Unknown error in stateS3toDB during lineage finding")
		return types.State{}, err
	}
	db.lock.Unlock()
	st = types.State{
		Path:      path,
		Version:   version,
		TFVersion: sf.TerraformVersion.String(),
		Serial:    int64(sf.Serial),
		LineageID: sql.NullInt64{Int64: int64(lineage.ID), Valid: true},
	}
	for _, m := range sf.State.Modules {
		mod := types.Module{
			Path: m.Addr.String(),
		}
		for _, r := range m.Resources {
			for index, i := range r.Instances {
				res := types.Resource{
					Type:       r.Addr.Resource.Type,
					Name:       r.Addr.Resource.Name,
					Index:      getResourceIndex(index),
					Attributes: marshalAttributeValues(i.Current),
				}
				mod.Resources = append(mod.Resources, res)
			}
		}
		for n, r := range m.OutputValues {
			jsonVal, err := ctyJson.Marshal(r.Value, r.Value.Type())
			if err != nil {
				log.WithError(err).Errorf("failed to load output for %s", r.Addr.String())
			}
			out := types.OutputValue{
				Sensitive: r.Sensitive,
				Name:      n,
				Value:     string(jsonVal),
			}
			mod.OutputValues = append(mod.OutputValues, out)
		}
		st.Modules = append(st.Modules, mod)
	}
	return
}
func getResourceIndex(index addrs.InstanceKey) string {
	switch index.(type) {
	case addrs.IntKey, addrs.StringKey:
		return index.String()
	}
	return ""
}
func marshalAttributeValues(src *states.ResourceInstanceObjectSrc) (attrs []types.Attribute) {
	vals := make(attributeValues)
	if src == nil {
		return
	}
	if src.AttrsFlat != nil {
		for k, v := range src.AttrsFlat {
			vals[k] = v
		}
	} else if err := json.Unmarshal(src.AttrsJSON, &vals); err != nil {
		log.Error(err.Error())
	}
	log.Debug(vals)
	for k, v := range vals {
		vJSON, _ := json.Marshal(v)
		attr := types.Attribute{
			Key:   k,
			Value: string(vJSON),
		}
		log.Debug(attrs)
		attrs = append(attrs, attr)
	}
	return attrs
}
func (db *Database) InsertState(path string, versionID string, sf *statefile.File) error {
	st, err := db.stateS3toDB(sf, path, versionID)
	if err == nil {
		db.Create(&st)
	}
	return nil
}
func (db *Database) UpdateState(st types.State) error {
	var lineageValue sql.NullString
	if err := db.Raw("SELECT lineage FROM states WHERE id = ?", st.ID).Scan(&lineageValue).Error; err != nil {
		return fmt.Errorf("Error on %s lineage recovering during migration: %v", st.Path, err)
	}
	if lineageValue.String == "" || !lineageValue.Valid {
		log.Warnf("Missing lineage for '%s' state, attempt to recover lineage from other states...", st.Path)
		var lineages []string
		db.Table("states").
			Distinct("lineage").
			Order("lineage desc").
			Where("path = ?", st.Path).
			Scan(&lineages)
		for _, l := range lineages {
			if l != "" {
				lineageValue.String = l
				lineageValue.Valid = true
				log.Infof("Missing lineage for '%s' state solved!", st.Path)
				break
			}
		}
		if lineageValue.String == "" || !lineageValue.Valid {
			log.Warnf("Failed to recover '%s' lineage from others states. Orphan state", st.Path)
			return nil
		}
	}
	lineage := types.Lineage{
		Value: lineageValue.String,
	}
	tx := db.FirstOrCreate(&lineage, lineage)
	if tx.Error != nil || lineage.ID == 0 {
		return tx.Error
	}
	st.LineageID = sql.NullInt64{Int64: int64(lineage.ID), Valid: true}
	return db.Save(&st).Error
}
func (db *Database) InsertVersion(version *state.Version) error {
	var v types.Version
	db.lock.Lock()
	db.FirstOrCreate(&v, types.Version{
		VersionID:    version.ID,
		LastModified: version.LastModified,
	})
	db.lock.Unlock()
	return nil
}
func (db *Database) GetState(lineage, versionID string) (state types.State) {
	db.Joins("JOIN lineages on states.lineage_id=lineages.id").
		Joins("JOIN versions on states.version_id=versions.id").
		Preload("Version").Preload("Modules").Preload("Modules.Resources").Preload("Modules.Resources.Attributes").
		Preload("Modules.OutputValues").
		Find(&state, "lineages.value = ? AND versions.version_id = ?", lineage, versionID)
	return
}
func (db *Database) GetLineageActivity(lineage string) (states []types.StateStat) {
	sql := "SELECT t.path, t.serial, t.tf_version, t.version_id, t.last_modified, count(resources.*) as resource_count" +
		" FROM (SELECT states.id, states.path, states.serial, states.tf_version, versions.version_id, versions.last_modified FROM states JOIN lineages ON lineages.id = states.lineage_id JOIN versions ON versions.id = states.version_id WHERE lineages.value = ? ORDER BY states.path, versions.last_modified ASC) t" +
		" JOIN modules ON modules.state_id = t.id" +
		" JOIN resources ON resources.module_id = modules.id" +
		" GROUP BY t.path, t.serial, t.tf_version, t.version_id, t.last_modified" +
		" ORDER BY last_modified ASC"
	db.Raw(sql, lineage).Find(&states)
	return
}
func (db *Database) KnownVersions() (versions []string) {
	rows, _ := db.Table("versions").Select("DISTINCT version_id").Rows()
	defer rows.Close()
	for rows.Next() {
		var version string
		if err := rows.Scan(&version); err != nil {
			log.Error(err.Error())
		}
		versions = append(versions, version)
	}
	return
}
func (db *Database) SearchAttribute(query url.Values) (results []types.SearchResult, page int, total int) {
	log.WithFields(log.Fields{
		"query": query,
	}).Info("Searching for attribute with query")
	targetVersion := string(query.Get("versionid"))
	sqlQuery := ""
	if targetVersion == "" {
		sqlQuery += " FROM (SELECT states.path, max(states.serial) as mx FROM states GROUP BY states.path) t" +
			" JOIN states ON t.path = states.path AND t.mx = states.serial"
	} else {
		sqlQuery += " FROM states"
	}
	sqlQuery += " JOIN modules ON states.id = modules.state_id" +
		" JOIN resources ON modules.id = resources.module_id" +
		" JOIN attributes ON resources.id = attributes.resource_id" +
		" JOIN lineages ON lineages.id = states.lineage_id" +
		" JOIN versions ON states.version_id = versions.id"
	var where []string
	var params []interface{}
	if targetVersion != "" && targetVersion != "*" {
		where = append(where, "states.version_id = ?")
		params = append(params, targetVersion)
	}
	if v := string(query.Get("type")); v != "" {
		where = append(where, "resources.type LIKE ?")
		params = append(params, fmt.Sprintf("%%%s%%", v))
	}
	if v := string(query.Get("name")); v != "" {
		where = append(where, "resources.name LIKE ?")
		params = append(params, fmt.Sprintf("%%%s%%", v))
	}
	if v := string(query.Get("key")); v != "" {
		where = append(where, "attributes.key LIKE ?")
		params = append(params, fmt.Sprintf("%%%s%%", v))
	}
	if v := string(query.Get("value")); v != "" {
		where = append(where, "attributes.value LIKE ?")
		params = append(params, fmt.Sprintf("%%%s%%", v))
	}
	if v := query.Get("tf_version"); string(v) != "" {
		where = append(where, fmt.Sprintf("states.tf_version LIKE '%s'", fmt.Sprintf("%%%s%%", v)))
	}
	if v := query.Get("lineage_value"); string(v) != "" {
		where = append(where, fmt.Sprintf("lineages.value LIKE '%s'", fmt.Sprintf("%%%s%%", v)))
	}
	if len(where) > 0 {
		sqlQuery += " WHERE " + strings.Join(where, " AND ")
	}
	row := db.Raw("SELECT count(*)"+sqlQuery, params...).Row()
	if err := row.Scan(&total); err != nil {
		log.Error(err.Error())
	}
	sql := "SELECT states.path, versions.version_id, states.tf_version, states.serial, lineages.value as lineage_value, modules.path as module_path, resources.type, resources.name, resources.index, attributes.key, attributes.value" +
		sqlQuery +
		" ORDER BY states.path, states.serial, lineage_value, modules.path, resources.type, resources.name, resources.index, attributes.key" +
		" LIMIT ?"
	params = append(params, pageSize)
	if v := string(query.Get("page")); v != "" {
		page, _ = strconv.Atoi(v)  
		o := (page - 1) * pageSize
		sql += " OFFSET ?"
		params = append(params, o)
	} else {
		page = 1
	}
	db.Raw(sql, params...).Find(&results)
	return
}
func (db *Database) ListStatesVersions() (statesVersions map[string][]string) {
	rows, _ := db.Table("states").
		Joins("JOIN versions ON versions.id = states.version_id").
		Select("states.path, versions.version_id").Rows()
	defer rows.Close()
	statesVersions = make(map[string][]string)
	for rows.Next() {
		var path string
		var versionID string
		if err := rows.Scan(&path, &versionID); err != nil {
			log.Error(err.Error())
		}
		statesVersions[versionID] = append(statesVersions[versionID], path)
	}
	return
}
func (db *Database) ListTerraformVersionsWithCount(query url.Values) (results []map[string]string, err error) {
	orderBy := string(query.Get("orderBy"))
	sql := "SELECT t.tf_version, COUNT(*)" +
		" FROM (SELECT DISTINCT ON(states.path) states.id, states.path, states.serial, states.tf_version, versions.version_id, versions.last_modified" +
		" FROM states JOIN versions ON versions.id = states.version_id ORDER BY states.path, versions.last_modified DESC) t" +
		" GROUP BY t.tf_version ORDER BY "
	if orderBy == "version" {
		sql += "string_to_array(t.tf_version, '.')::int[] DESC"
	} else {
		sql += "count DESC"
	}
	rows, err := db.Raw(sql).Rows()
	if err != nil {
		return results, err
	}
	defer rows.Close()
	for rows.Next() {
		var name string
		var count string
		r := make(map[string]string)
		if err = rows.Scan(&name, &count); err != nil {
			return
		}
		r["name"] = name
		r["count"] = count
		results = append(results, r)
	}
	return
}
func (db *Database) ListStateStats(query url.Values) (states []types.StateStat, page int, total int) {
	row := db.Raw("SELECT count(*) FROM (SELECT DISTINCT lineage_id FROM states) AS t").Row()
	if err := row.Scan(&total); err != nil {
		log.Error(err.Error())
	}
	var paginationQuery string
	var params []interface{}
	page = 1
	if v := string(query.Get("page")); v != "" {
		page, _ = strconv.Atoi(v)  
		offset := (page - 1) * pageSize
		params = append(params, offset)
		paginationQuery = " LIMIT 20 OFFSET ?"
	} else {
		page = -1
	}
	sql := "SELECT t.path, lineages.value as lineage_value, t.serial, t.tf_version, t.version_id, t.last_modified, count(resources.*) as resource_count" +
		" FROM (SELECT DISTINCT ON(states.lineage_id) states.id, states.lineage_id, states.path, states.serial, states.tf_version, versions.version_id, versions.last_modified FROM states JOIN versions ON versions.id = states.version_id ORDER BY states.lineage_id, versions.last_modified DESC) t" +
		" JOIN modules ON modules.state_id = t.id" +
		" JOIN resources ON resources.module_id = modules.id" +
		" JOIN lineages ON lineages.id = t.lineage_id" +
		" GROUP BY t.path, lineages.value, t.serial, t.tf_version, t.version_id, t.last_modified" +
		" ORDER BY last_modified DESC" +
		paginationQuery
	db.Raw(sql, params...).Find(&states)
	return
}
func (db *Database) listField(table, field string) (results []string, err error) {
	rows, err := db.Table(table).Select(fmt.Sprintf("DISTINCT %s", field)).Rows()
	if err != nil {
		return results, err
	}
	defer rows.Close()
	for rows.Next() {
		var t string
		if err = rows.Scan(&t); err != nil {
			return
		}
		results = append(results, t)
	}
	return
}
func (db *Database) ListResourceTypes() ([]string, error) {
	return db.listField("resources", "type")
}
func (db *Database) ListResourceTypesWithCount() (results []map[string]string, err error) {
	sql := "SELECT resources.type, COUNT(*)" +
		" FROM (SELECT DISTINCT ON(states.path) states.id, states.path, states.serial, states.tf_version, versions.version_id, versions.last_modified" +
		" FROM states" +
		" JOIN versions ON versions.id = states.version_id" +
		" ORDER BY states.path, versions.last_modified DESC) t" +
		" JOIN modules ON modules.state_id = t.id" +
		" JOIN resources ON resources.module_id = modules.id" +
		" GROUP BY resources.type" +
		" ORDER BY count DESC"
	rows, err := db.Raw(sql).Rows()
	if err != nil {
		return results, err
	}
	defer rows.Close()
	for rows.Next() {
		var name string
		var count string
		r := make(map[string]string)
		if err = rows.Scan(&name, &count); err != nil {
			return
		}
		r["name"] = name
		r["count"] = count
		results = append(results, r)
	}
	return
}