package sql_db

// |@@| C

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"gardening/src/lib"
	"gardening/src/lib/config"
	"gardening/src/lib/error"
	"gardening/src/lib/filesystem"
	"gardening/src/lib/logger"
	sql2 "gardening/src/lib/sql"
	"os"
	"strconv"
	"strings"
)

type SqlDb struct {
	Sql        sql2.Sql
	Logger     *logger.Logger
	Filesystem *filesystem.Filesystem
	Config     *config.Config
	Data       struct {
		Current    *sql.DB
		DbPath     string
		SchemaPath string
	}
}

func (this *SqlDb) GetDb(ctx context.Context) *sql.DB {
	if this.Data.Current != nil {
		return this.Data.Current
	}
	this.Data.Current = this.openDb(ctx)
	return this.Data.Current
}

func (this *SqlDb) openDb(ctx context.Context) *sql.DB {
	dbPath := this.DataSourceName(ctx)
	db, err := this.Sql.Open(dbPath)
	if err != nil {
		lib.Poe(err)
	}
	this.Sql.SetMaxOpenConns(db, 1)
	return db
}

func (this *SqlDb) DataSourceName(ctx context.Context) string {
	if this.Data.DbPath != "" {
		return this.Data.DbPath
	}
	return this.Sql.DataSourceName(ctx, this.Config.GetRootDir())
}

func (this *SqlDb) SchemaPath(ctx context.Context) string {
	if this.Data.SchemaPath != "" {
		return this.Data.SchemaPath
	}
	return this.Sql.SchemaPath(ctx, this.Config.GetRootDir())
}

func (this *SqlDb) CreateEmptyDb(ctx context.Context) {
	dataSourceName := this.DataSourceName(ctx)
	this.Sql.Create(ctx, dataSourceName)
}

func (this *SqlDb) LoadSchema(ctx context.Context) {
	path := this.SchemaPath(ctx)
	b, err := os.ReadFile(path)
	lib.Poe(err)
	db := this.GetDb(ctx)
	_, err = this.Sql.Exec(db, string(b))
	lib.Poe(err)
}

func (this *SqlDb) DestroyDb(ctx context.Context) {
	dataSourceName := this.DataSourceName(ctx)
	this.Sql.Destroy(ctx, dataSourceName)
}

func (this *SqlDb) BeginTx(ctx context.Context) *sql.Tx {
	db := this.GetDb(ctx)
	tx, err := this.Sql.BeginTx(ctx, db, nil)
	if err != nil {
		panic("Cannot begin transaction")
	}
	return tx
}

func (this *SqlDb) CommitTx(tx *sql.Tx) error {
	return tx.Commit()
}

func (this *SqlDb) Query(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error) {
	sr := strings.ReplaceAll(query, "?", "\"%v\"")
	s := fmt.Sprintf(sr, args...)
	this.Logger.DebugL(ctx, this.Logger.N("Db/Query").KV("query", s).KV("log/kind", "queries"))
	db := this.GetDb(ctx)
	return this.Sql.Query(db, query, args...)
}

func (this *SqlDb) QueryRow(ctx context.Context, query string, args ...interface{}) *sql.Row {
	sr := strings.ReplaceAll(query, "?", "\"%v\"")
	s := fmt.Sprintf(sr, args...)
	this.Logger.DebugL(ctx, this.Logger.N("Db/QueryRow").KV("query", s).KV("log/kind", "queries"))
	db := this.GetDb(ctx)
	return this.Sql.QueryRow(db, query, args...)
}

func (this *SqlDb) Exec(ctx context.Context, query string, args ...interface{}) (sql.Result, error) {
	sr := strings.ReplaceAll(query, "?", "\"%v\"")
	s := fmt.Sprintf(sr, args...)
	this.Logger.DebugL(ctx, this.Logger.N("Db/Exec").KV("query", s).KV("log/kind", "queries"))
	db := this.GetDb(ctx)
	return this.Sql.Exec(db, query, args...)
}

func (this *SqlDb) Create(ctx context.Context, tx *sql.Tx, table string, fields []string, values []interface{}) (string, int, error) {
	columns, placeholders := this.columnsAndPlaceHolders(fields)
	query := "INSERT INTO " + table + "(" + columns + ") VALUES (" + placeholders + ")"

	if this.Sql.Driver() == sql2.DRIVER_POSTGRES {
		query += " RETURNING id"
	}

	fullQuery := this.makeFullQuery(query, values) // Finish that for a better logging

	db := this.GetDb(ctx)
	stmt, err := this.Sql.Prepare(db, query)
	if err != nil {
		err = erro.W("cannot prepare statement", err).
			KVT("query", query).
			KVT("data_source_name", this.DataSourceName(ctx)).
			KVT("full_query", fullQuery)
		return "", 0, err
	}
	defer stmt.Close()
	args := values

	this.Logger.Audit(ctx, this.Logger.N("Db create").KVT("query", query))
	this.Logger.DebugL(ctx, this.Logger.N(" -> Db/Update/Create: "+fullQuery))

	switch this.Sql.Driver() {
	case sql2.DRIVER_SQLITE3:
		res, err := stmt.Exec(args...)
		if err != nil {
			return "", 0, erro.W("cannot exec statement", err)
		}
		affected, err := res.RowsAffected()
		if err != nil {
			return "", 0, err
		}
		id, err := res.LastInsertId()
		if err != nil {
			return "", 0, err
		}
		return strconv.Itoa(int(id)), int(affected), nil
	case sql2.DRIVER_POSTGRES:
		var id string
		err := db.QueryRow(query, args...).Scan(&id)
		if err != nil {
			return "", 0, err
		}
		return id, 1, nil
	default:
		return "", 0, fmt.Errorf("unsupported driver: %s", this.Sql.Driver())
	}
}

func (this *SqlDb) columnsAndPlaceHolders(fields []string) (string, string) {
	columns := ""
	placeholders := ""
	for n, field := range fields {
		if columns != "" {
			columns += ", "
		}
		if placeholders != "" {
			placeholders += ", "
		}
		columns += "\"" + field + "\""
		placeholders += this.Sql.Placeholder(n + 1)
	}
	return columns, placeholders
}

func (this *SqlDb) Update(ctx context.Context, tx *sql.Tx, table string, id string, fields []string, values []interface{}) (int, error) {
	if id == "" {
		panic("Id is not set")
	}

	query := ""
	for _, field := range fields {
		if query != "" {
			query += ", "
		}
		query += "\"" + field + "\" = ?"
	}
	query = "UPDATE " + table + " set " + query + " where id = ?"

	sr := strings.ReplaceAll(query, "?", "\"%v\"")

	db := this.GetDb(ctx)
	stmt, err := this.Sql.Prepare(db, query)
	if err != nil {
		return 0, err
	}
	defer stmt.Close()
	args := append(values, id)

	s := fmt.Sprintf(sr, args...)
	this.Logger.Audit(ctx, this.Logger.N("Db Update").KVT("query", s))
	this.Logger.DebugL(ctx, this.Logger.N(" -> Db/Update/Exec: "+s))

	res, err := stmt.Exec(args...)
	if err != nil {
		return 0, err
	}
	affected, err := res.RowsAffected()
	if err != nil {
		return 0, err
	}
	return int(affected), nil
}

func (this *SqlDb) Delete(ctx context.Context, tx *sql.Tx, table string, id string) (int, error) {
	query := "DELETE from " + table + " where id = ?"
	sr := strings.ReplaceAll(query, "?", "\"%v\"")
	s := fmt.Sprintf(sr, id)
	this.Logger.Audit(ctx, this.Logger.N("Db Delete").KVT("query", s))
	this.Logger.DebugL(ctx, this.Logger.N(" -> Db/Delete/Exec: "+s))

	db := this.GetDb(ctx)
	stmt, err := this.Sql.Prepare(db, query)
	if err != nil {
		return 0, err
	}
	defer stmt.Close()
	res, err := stmt.Exec(id)
	if err != nil {
		return 0, err
	}
	affected, err := res.RowsAffected()
	if err != nil {
		return 0, err
	}
	return int(affected), nil
}

func (this *SqlDb) IsNoRow(err error) bool {
	return errors.Is(err, sql.ErrNoRows)
}

func (this *SqlDb) GetRowValues(ctx context.Context, table string, id string) map[string]any {
	m := make(map[string]any)
	var values []any
	rows, err := this.Query(ctx, "SELECT * FROM "+table+" WHERE id = ?", id)
	lib.Poe(err)
	columns, err := rows.Columns()
	lib.Poe(err)
	rows.Next()
	for _, column := range columns {
		var value any
		lib.Poe(err)
		m[column] = &value
		values = append(values, &value)
	}
	err = rows.Scan(values...)
	lib.Poe(err)
	err = rows.Close()
	lib.Poe(err)
	return m
}

func (this *SqlDb) makeFullQuery(query string, values []interface{}) string {
	// 	sr := strings.ReplaceAll(query, this.Sql.Placeholder(-1), "\"%v\"")
	// s := fmt.Sprintf(sr, args...)
	return "TODO"
}
