package controlplane

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

	"github.com/nexuscrm/shared/pkg/constants"
)

type profilePermissionCoverageGap struct {
	ProfileID          string
	ProfileName        string
	ObjectRows         int
	ExpectedObjectRows int
	FieldRows          int
	ExpectedFieldRows  int
}

type businessObjectFilterOptions struct {
	ExcludeDerivedSelfPermissions bool
}

func businessObjectFilter(opts businessObjectFilterOptions) string {
	parts := []string{
		fmt.Sprintf("o.`%s` = 0", constants.FieldSysObject_IsDeleted),
		fmt.Sprintf("o.`%s` NOT LIKE 'system%%'", constants.FieldSysObject_TableType),
		fmt.Sprintf("o.`%s` NOT LIKE '%%\\\\_bak\\\\_%%' ESCAPE '\\\\'", constants.FieldSysObject_APIName),
	}
	if opts.ExcludeDerivedSelfPermissions {
		parts = append(parts, businessObjectOwnsSelfPermissionsFilter("o"))
	}
	return strings.Join(parts, " AND ")
}

func businessObjectOwnsSelfPermissionsFilter(alias string) string {
	return fmt.Sprintf(
		"(%s.`%s` IS NULL OR COALESCE(LOWER(JSON_UNQUOTE(JSON_EXTRACT(%s.`%s`, '$.mode'))), '') <> 'derived')",
		alias,
		constants.FieldSysObject_AccessPolicy,
		alias,
		constants.FieldSysObject_AccessPolicy,
	)
}

// VerifyProfilePermissionCoverage returns an error when any profile-owned
// PermissionSet is missing the expected business-object ObjectPermissions or
// FieldPermissions coverage implied by the current metadata. `_bak_` discovery
// leftovers are excluded so the signal stays actionable for the main profile
// convergence path.
func VerifyProfilePermissionCoverage(rawDB *sql.DB) error {
	filterOpts := businessObjectFilterOptions{ExcludeDerivedSelfPermissions: true}

	expectedObjectRows, err := countExpectedBusinessObjects(rawDB, filterOpts)
	if err != nil {
		return err
	}
	expectedFieldRows, err := countExpectedBusinessFields(rawDB, filterOpts)
	if err != nil {
		return err
	}
	if expectedObjectRows == 0 && expectedFieldRows == 0 {
		log.Println("✅ profile permission coverage check skipped: no business-object metadata found")
		return nil
	}

	gaps, err := listProfilePermissionCoverageGaps(rawDB, filterOpts, expectedObjectRows, expectedFieldRows)
	if err != nil {
		return err
	}
	if len(gaps) == 0 {
		log.Println("✅ profile permission coverage matches expected business-object metadata")
		return nil
	}

	sort.Slice(gaps, func(i, j int) bool {
		if gaps[i].ProfileName == gaps[j].ProfileName {
			return gaps[i].ProfileID < gaps[j].ProfileID
		}
		return gaps[i].ProfileName < gaps[j].ProfileName
	})

	preview := make([]string, 0, len(gaps))
	for _, gap := range gaps {
		log.Printf(
			"⚠️  profile permission coverage gap: profile=%s (%s) objectRows=%d/%d fieldRows=%d/%d",
			gap.ProfileName, gap.ProfileID, gap.ObjectRows, gap.ExpectedObjectRows, gap.FieldRows, gap.ExpectedFieldRows,
		)
		preview = append(preview, fmt.Sprintf(
			"%s (%s): objects %d/%d, fields %d/%d",
			gap.ProfileName, gap.ProfileID, gap.ObjectRows, gap.ExpectedObjectRows, gap.FieldRows, gap.ExpectedFieldRows,
		))
	}

	return fmt.Errorf(
		"profile permission coverage drift detected on %d profile(s) — run 'cmd/setup repair' or 'cmd/setup apply'; samples: %s",
		len(gaps),
		strings.Join(preview, "; "),
	)
}

func countExpectedBusinessObjects(rawDB *sql.DB, filterOpts businessObjectFilterOptions) (int, error) {
	query := fmt.Sprintf(
		"SELECT COUNT(*) FROM `%s` o WHERE %s",
		constants.TableObject,
		businessObjectFilter(filterOpts),
	)
	var count int
	if err := rawDB.QueryRow(query).Scan(&count); err != nil {
		return 0, fmt.Errorf("count expected business objects: %w", err)
	}
	return count, nil
}

func countExpectedBusinessFields(rawDB *sql.DB, filterOpts businessObjectFilterOptions) (int, error) {
	query := fmt.Sprintf(
		"SELECT COUNT(*) FROM `%s` f JOIN `%s` o ON f.`%s` = o.`%s` WHERE f.`%s` = 0 AND %s",
		constants.TableField,
		constants.TableObject,
		constants.FieldSysField_ObjectID,
		constants.FieldID,
		constants.FieldSysField_IsDeleted,
		businessObjectFilter(filterOpts),
	)
	var count int
	if err := rawDB.QueryRow(query).Scan(&count); err != nil {
		return 0, fmt.Errorf("count expected business fields: %w", err)
	}
	return count, nil
}

func listProfilePermissionCoverageGaps(rawDB *sql.DB, filterOpts businessObjectFilterOptions, expectedObjectRows, expectedFieldRows int) ([]profilePermissionCoverageGap, error) {
	profiles, err := loadProfileCoverageCounts(rawDB, filterOpts, expectedObjectRows, expectedFieldRows)
	if err != nil {
		return nil, err
	}

	var gaps []profilePermissionCoverageGap
	for _, profile := range profiles {
		if profile.ObjectRows < expectedObjectRows || profile.FieldRows < expectedFieldRows {
			gaps = append(gaps, profile)
		}
	}
	return gaps, nil
}

func loadProfileCoverageCounts(rawDB *sql.DB, filterOpts businessObjectFilterOptions, expectedObjectRows, expectedFieldRows int) ([]profilePermissionCoverageGap, error) {
	query := fmt.Sprintf(`
SELECT
	ps.%s,
	COALESCE(p.%s, ps.%s),
	COALESCE(op_counts.object_rows, 0) AS object_rows,
	COALESCE(fp_counts.field_rows, 0) AS field_rows
FROM %s ps
LEFT JOIN %s p
	ON p.%s = ps.%s
	AND p.%s = 0
LEFT JOIN (
	SELECT
		op.%s AS parent_id,
		COUNT(DISTINCT op.%s) AS object_rows
	FROM %s op
	JOIN %s o
		ON o.%s = op.%s
	WHERE op.%s = 0
	  AND %s
	GROUP BY op.%s
) op_counts
	ON op_counts.parent_id = ps.%s
LEFT JOIN (
	SELECT
		fp.%s AS parent_id,
		COUNT(fp.%s) AS field_rows
	FROM %s fp
	JOIN %s o
		ON o.%s = fp.%s
	WHERE fp.%s = 0
	  AND %s
	GROUP BY fp.%s
) fp_counts
	ON fp_counts.parent_id = ps.%s
WHERE ps.%s = 1
  AND ps.%s = 0
`,
		constants.FieldPermissionSet_ProfileID,
		constants.FieldProfile_Name,
		constants.FieldPermissionSet_ProfileID,
		constants.TablePermissionSet,
		constants.TableProfile,
		constants.FieldID,
		constants.FieldPermissionSet_ProfileID,
		constants.FieldIsDeleted,
		constants.FieldObjectPermissions_ParentID,
		constants.FieldID,
		constants.TableObjectPermissions,
		constants.TableObject,
		constants.FieldSysObject_APIName,
		constants.FieldObjectPermissions_SobjectType,
		constants.FieldObjectPermissions_IsDeleted,
		businessObjectFilter(filterOpts),
		constants.FieldObjectPermissions_ParentID,
		constants.FieldID,
		constants.FieldFieldPermissions_ParentID,
		constants.FieldID,
		constants.TableFieldPermissions,
		constants.TableObject,
		constants.FieldSysObject_APIName,
		constants.FieldFieldPermissions_SobjectType,
		constants.FieldFieldPermissions_IsDeleted,
		businessObjectFilter(filterOpts),
		constants.FieldFieldPermissions_ParentID,
		constants.FieldID,
		constants.FieldPermissionSet_IsOwnedByProfile,
		constants.FieldPermissionSet_IsDeleted,
	)

	rows, err := rawDB.Query(query)
	if err != nil {
		return nil, fmt.Errorf("query profile permission coverage counts: %w", err)
	}
	defer rows.Close()

	var gaps []profilePermissionCoverageGap
	for rows.Next() {
		var gap profilePermissionCoverageGap
		gap.ExpectedObjectRows = expectedObjectRows
		gap.ExpectedFieldRows = expectedFieldRows
		if err := rows.Scan(&gap.ProfileID, &gap.ProfileName, &gap.ObjectRows, &gap.FieldRows); err != nil {
			return nil, fmt.Errorf("scan profile permission coverage row: %w", err)
		}
		gaps = append(gaps, gap)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate profile permission coverage rows: %w", err)
	}
	return gaps, nil
}