package packs

import (
	"database/sql"
	"fmt"
	"strings"

	apperrors "github.com/nexuscrm/kernel/pkg/errors"
)

// bootstrapStateTable mirrors the canonical bootstrap constant (byte-identical
// literal). The relocated pack-hash gate (isPackHashApplied / markPackHashApplied)
// and the standard-flow source-migration gates read/write _System_BootstrapState;
// the bootstrap copy stays for any read paths that remain in that package.
const bootstrapStateTable = "_System_BootstrapState"

// PackTable describes one L1 table whose rows are driven by a shipped
// pack. The tuple {Name, ScopeColumn, KeyColumns, IdColumn} is the
// minimum the reconcile utility needs to:
//
//   - find "live" rows within an optional scope (`WHERE ScopeColumn = ? AND IsCustomized = 0`,
//     or just `WHERE IsCustomized = 0` when ScopeColumn is empty). L1-only
//     tables opt out of the IsCustomized predicate so every non-pack row is
//     reconciled away.
//   - match them against the pack's expected Id set,
//   - deactivate orphans in one batched UPDATE.
//
// KeyColumns here is informational + drift-guard material: the reconcile
// itself keys by IdColumn (deterministic Ids synthesized elsewhere), but
// TestPackTableDescriptors_MatchSystemTablesJSON asserts KeyColumns equals
// the `uq_*` unique-index column list declared in system_tables.json. If
// the DDL unique key ever drifts from the descriptor the drift-guard test
// fails at CI time — preventing Id derivation and the unique index from
// referencing different tuples, which otherwise silently lets stale rows
// sneak past ON DUPLICATE KEY UPDATE.
//
// ScopeColumn == "" means "no scope predicate" — the reconcile scans the
// whole table. Used for parent tables like _System_StandardAction whose
// unique key is a single column (Name) with no parent-scope to restrict
// by. Callers for global reconciles MUST build `keep` from persisted row
// Ids (SELECT after upsert), not from Id-derivation of the pack key,
// because ON DUPLICATE KEY UPDATE matches on the unique key and preserves
// pre-existing Ids (legacy, migrated, or manually-loaded rows).
type PackTable struct {
	Name        string   // e.g. "_System_ActionFieldDefault"
	ScopeColumn string   // column used in the reconcile WHERE clause ("" = global)
	KeyColumns  []string // must match the uq_* columns in system_tables.json
	IdColumn    string   // "Id" in every current satellite; future-proof knob
	L1Only      bool     // true means shipped core with no admin customization author
}

// ReconcileReport is the observability output of one ReconcilePackTable call.
// Structured so the caller can emit one log line per reconcile.
type ReconcileReport struct {
	Table       string
	Scope       string
	Deactivated int
}

// ReconcilePackTable soft-deletes rows in tbl (scoped by tbl.ScopeColumn =
// scopeVal when ScopeColumn is non-empty; whole-table when empty) whose Id
// is not present in keep. By default, IsCustomized=1 rows are filtered at the
// DB level so they're never deactivation candidates — same customization
// contract as the upsert path's `IF(IsCustomized=0, VALUES(x), x)` guards.
// L1-only tables set PackTable.L1Only, which removes that filter so there is
// no second admin authoring site for shipped-core facts. scopeVal is ignored
// when ScopeColumn is empty.
//
// Returns (zero-report, error) on SQL failures; (non-zero-report, nil) on
// success whether or not any rows were deactivated. The caller decides how
// to log; this function logs nothing so it stays test-hermetic.
func ReconcilePackTable(
	db *sql.DB,
	tbl PackTable,
	scopeVal string,
	keep map[string]struct{},
	now string,
) (ReconcileReport, error) {
	report := ReconcileReport{Table: tbl.Name, Scope: scopeVal}
	unscopedWhere := "IsCustomized = 0 AND IsDeleted = 0"
	scopedWhere := fmt.Sprintf("%s = ? AND IsCustomized = 0 AND IsDeleted = 0", tbl.ScopeColumn)
	if tbl.L1Only {
		unscopedWhere = "IsDeleted = 0"
		scopedWhere = fmt.Sprintf("%s = ? AND IsDeleted = 0", tbl.ScopeColumn)
	}

	var (
		rows *sql.Rows
		err  error
	)
	if tbl.ScopeColumn == "" {
		rows, err = db.Query(
			fmt.Sprintf(
				"SELECT %s FROM `%s` WHERE %s",
				tbl.IdColumn, tbl.Name, unscopedWhere),
		)
	} else {
		rows, err = db.Query(
			fmt.Sprintf(
				"SELECT %s FROM `%s` WHERE %s",
				tbl.IdColumn, tbl.Name, scopedWhere),
			scopeVal,
		)
	}
	if err != nil {
		return report, apperrors.NewInternalError(
			fmt.Sprintf("reconcile %s: SELECT live rows", tbl.Name), err)
	}
	defer rows.Close()

	var orphans []string
	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err != nil {
			return report, apperrors.NewInternalError(
				fmt.Sprintf("reconcile %s: scan Id", tbl.Name), err)
		}
		if _, kept := keep[id]; !kept {
			orphans = append(orphans, id)
		}
	}
	if err := rows.Err(); err != nil {
		return report, apperrors.NewInternalError(
			fmt.Sprintf("reconcile %s: iterate rows", tbl.Name), err)
	}
	if len(orphans) == 0 {
		return report, nil
	}

	placeholders := strings.TrimRight(strings.Repeat("?,", len(orphans)), ",")
	args := make([]interface{}, 0, len(orphans)+1)
	args = append(args, now)
	for _, id := range orphans {
		args = append(args, id)
	}
	if _, err := db.Exec(
		fmt.Sprintf(
			"UPDATE `%s` SET IsActive=0, IsDeleted=1, LastModifiedDate=? WHERE %s IN (%s)",
			tbl.Name, tbl.IdColumn, placeholders),
		args...,
	); err != nil {
		return report, apperrors.NewInternalError(
			fmt.Sprintf("reconcile %s: batch deactivate %d orphan rows", tbl.Name, len(orphans)), err)
	}
	report.Deactivated = len(orphans)
	return report, nil
}

// isPackHashApplied returns true when a prior successful seed stamped the
// given sha256 hash for stateKey in _System_BootstrapState. Empty state or
// a different hash returns false (caller runs the full seed + reconcile
// + re-stamps on success).
func isPackHashApplied(db *sql.DB, stateKey, hash string) (bool, error) {
	var storedPhase, status string
	err := db.QueryRow(
		fmt.Sprintf(
			"SELECT `Phase`, `Status` FROM `%s` WHERE `StateKey` = ?",
			bootstrapStateTable),
		stateKey,
	).Scan(&storedPhase, &status)
	switch {
	case err == sql.ErrNoRows:
		return false, nil
	case err != nil:
		return false, apperrors.NewInternalError(
			fmt.Sprintf("check pack hash gate for %q", stateKey), err)
	}
	return status == "applied" && storedPhase == hash, nil
}

// markPackHashApplied upserts the given sha256 hash under stateKey with
// Status="applied". Stores the hash in the Phase column (the column is free-
// form text; avoiding a DDL migration for the one extra field we need).
func markPackHashApplied(db *sql.DB, stateKey, hash, now string) error {
	if _, err := db.Exec(
		fmt.Sprintf(
			"INSERT INTO `%s` (`StateKey`,`RunID`,`Phase`,`Status`,`LastError`,`CreatedDate`,`LastModifiedDate`) "+
				"VALUES (?,?,?,?,?,?,?) "+
				"ON DUPLICATE KEY UPDATE `Phase`=VALUES(`Phase`),`Status`=VALUES(`Status`),`LastModifiedDate`=VALUES(`LastModifiedDate`)",
			bootstrapStateTable),
		stateKey, now, hash, "applied", "", now, now,
	); err != nil {
		return apperrors.NewInternalError(
			fmt.Sprintf("stamp pack hash gate for %q", stateKey), err)
	}
	return nil
}

// sha256Hex is defined once for this package in pack_component.go.