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
}

// permissionKind discriminates ObjectPermissions-scope filtering from
// FieldPermissions-scope filtering. Object scope additionally excludes
// parent-controlled children (delegation makes child OP rows advisory);
// field scope includes them so child FLS drift is still detected.
type permissionKind int

const (
	permKindObject permissionKind = iota
	permKindField
)

// businessObjectFilter renders the WHERE-fragment that scopes the verifier's
// expected business-object set. `kind` controls whether parent-controlled
// children are excluded (ObjectPermissions scope) or kept (FieldPermissions
// scope). The parent-controlled exclusion matches the runtime predicate
// `MetadataService.GetParentControlledObject` byte-for-byte: SharingModel =
// 'ControlledByParent' AND either a live `_System_Field` master-detail row or
// an explicit `_System_Relationship.IsSharingParent` overlay that still
// resolves an effective relationship edge for the child object. The overlay
// branch additionally requires that the overlay row's FieldApiName still
// corresponds to a live relationship field on the child object that is still
// a relationship type (reference/masterdetail or IsMasterDetail=1) and still
// targets the overlay's ParentObjectApiName — matching the resolver's behavior
// of ignoring overlay rows whose backing field-derived edge no longer exists
// (field retargeted, type changed away from reference/masterdetail, or
// ReferenceTo no longer contains the parent). Malformed schemas
// (ControlledByParent without either a matching master-detail field target or
// a sharing-parent overlay with a live backing relationship field still
// targeting the same parent) stay INCLUDED in OP scope, identical to the
// runtime helper's "" return.
func businessObjectFilter(opts businessObjectFilterOptions, kind permissionKind) 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"))
	}
	if kind == permKindObject {
		// The parent-controlled exclusion matches the runtime predicate
		// MetadataService.GetParentControlledObject byte-for-byte:
		// SharingModel = 'ControlledByParent' AND either a live
		// _System_Field master-detail edge or an explicit
		// _System_Relationship.IsSharingParent overlay that still
		// resolves an effective relationship edge for the child object.
		// The overlay branch additionally requires that the overlay row's
		// FieldApiName still corresponds to a live relationship field on the
		// child object that is still a relationship type (reference/masterdetail
		// or IsMasterDetail=1) and still targets r.ParentObjectApiName — matching
		// the resolver's behavior of ignoring overlay rows whose backing
		// field-derived edge no longer exists (field retargeted, type changed
		// away from reference/masterdetail, or ReferenceTo no longer contains
		// the parent).
		parts = append(parts, fmt.Sprintf(
			"NOT (o.`%s` = '%s' AND EXISTS (SELECT 1 FROM `%s` f LEFT JOIN `%s` r ON r.`%s` = 1 AND r.`%s` = o.`%s` WHERE ((f.`%s` = o.`%s` AND f.`%s` = 0 AND COALESCE(f.`%s`, '') NOT IN ('', '[]') AND (LOWER(COALESCE(f.`%s`, '')) = '%s' OR COALESCE(f.`%s`, 0) = 1)) OR (r.`%s` IS NOT NULL AND EXISTS (SELECT 1 FROM `%s` f2 WHERE f2.`%s` = o.`%s` AND f2.`%s` = r.`%s` AND f2.`%s` = 0 AND COALESCE(f2.`%s`, '') NOT IN ('', '[]') AND JSON_CONTAINS(LOWER(f2.`%s`), LOWER(CONCAT('\"', r.`%s`, '\"'))) AND (LOWER(COALESCE(f2.`%s`, '')) IN ('%s', '%s') OR COALESCE(f2.`%s`, 0) = 1))))))",
			constants.FieldSysObject_SharingModel,
			string(constants.SharingModelControlledByParent),
			constants.TableField,
			constants.TableRelationship,
			constants.FieldSysRelationship_IsSharingParent,
			constants.FieldSysRelationship_ChildObjectAPIName,
			constants.FieldSysObject_APIName,
			constants.FieldSysField_ObjectID,
			constants.FieldID,
			constants.FieldSysField_IsDeleted,
			constants.FieldReferenceTo,
			constants.FieldType,
			string(constants.FieldTypeMasterDetail),
			constants.FieldSysField_IsMasterDetail,
			constants.FieldID,
			constants.TableField,
			constants.FieldSysField_ObjectID,
			constants.FieldID,
			constants.FieldSysField_APIName,
			constants.FieldSysRelationship_FieldAPIName,
			constants.FieldSysField_IsDeleted,
			constants.FieldReferenceTo,
			constants.FieldReferenceTo,
			constants.FieldSysRelationship_ParentObjectAPIName,
			constants.FieldType,
			string(constants.FieldTypeReference),
			string(constants.FieldTypeMasterDetail),
			constants.FieldSysField_IsMasterDetail,
		))
	}
	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
// materializable 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, permKindObject),
	)
	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, permKindField),
	)
	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 {
		// Source-limited (#1374): the platform only seeds, and therefore only
		// expects full business-object coverage for, its built-in default
		// profiles. Imported / admin-created profiles own their own access —
		// partial coverage is their authored state, not platform drift — so they
		// are excluded from this gate. This mirrors the source-limited seeding in
		// permissions.resolvePermRule and the reconcile candidate scope; a proper
		// "imported profile matches its import source" audit is separate future
		// work that requires the authoritative source.
		if !constants.IsPlatformDefaultProfile(profile.ProfileID) {
			continue
		}
		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 := profilePermissionCoverageCountsQuery(filterOpts)

	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
}

func profilePermissionCoverageCountsQuery(filterOpts businessObjectFilterOptions) string {
	return 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, permKindObject),
		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, permKindField),
		constants.FieldFieldPermissions_ParentID,
		constants.FieldID,
		constants.FieldPermissionSet_IsOwnedByProfile,
		constants.FieldPermissionSet_IsDeleted,
	)
}