package layoutownership

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"strings"

	"github.com/nexuscrm/backend/internal/layoutrouting"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

// PageLayoutRow is the persisted ownership/config state for one live
// _System_PageLayout row.
type PageLayoutRow struct {
	ID           string
	ConfigJSON   string
	Source       string
	IsCustomized bool
	IsDefault    bool
}

type WildcardLayoutTarget = layoutrouting.WildcardTarget

const PageLayoutSourceTenantOverlay = "tenant_overlay"

// IsAdminOwnedPageLayout is the single ownership predicate for page-layout
// writers. Admin saves, clones, and custom rows are all L2 authority and must
// be preserved by L1 pack/overlay reconciliation.
func IsAdminOwnedPageLayout(isCustomized bool, source string) bool {
	return isCustomized ||
		strings.EqualFold(source, "admin_created") ||
		strings.EqualFold(source, "cloned") ||
		strings.EqualFold(source, "custom")
}

// IsTenantOverlayManagedPageLayout identifies page-layout rows whose current
// config was last composed by the tenant overlay path. These rows are still
// managed/L1+tenant composed state, not admin-authored L2 customization, so
// platform baseline seeding must preserve them until apply-tenant recomposes.
func IsTenantOverlayManagedPageLayout(isCustomized bool, source string) bool {
	return !isCustomized && strings.EqualFold(source, PageLayoutSourceTenantOverlay)
}

// IsAdminOwnedLayoutAssignment is the single ownership predicate for layout
// assignment rows authored via admin UI surfaces. RouteSource carries intent;
// CreatedById remains audit only.
func IsAdminOwnedLayoutAssignment(routeSource *string) bool {
	return routeSource != nil &&
		strings.EqualFold(strings.TrimSpace(*routeSource), models.LayoutAssignmentRouteSourceAdminAuthored)
}

// LoadPageLayoutRow returns one live _System_PageLayout row by Id.
func LoadPageLayoutRow(ctx context.Context, db *sql.DB, layoutID string) (*PageLayoutRow, error) {
	query := fmt.Sprintf(
		"SELECT `%s`, COALESCE(`%s`, ''), COALESCE(`%s`, ''), COALESCE(`%s`, 0), COALESCE(`%s`, 0) "+
			"FROM `%s` WHERE `%s` = ? AND (`%s` = 0 OR `%s` IS NULL)",
		constants.ColID,
		constants.FieldSysPageLayout_Config,
		constants.FieldSysPageLayout_Source,
		constants.FieldSysPageLayout_IsCustomized,
		constants.FieldSysPageLayout_IsDefault,
		constants.TablePageLayout,
		constants.ColID,
		constants.FieldIsDeleted,
		constants.FieldIsDeleted,
	)

	var row PageLayoutRow
	if err := db.QueryRowContext(ctx, query, layoutID).Scan(
		&row.ID,
		&row.ConfigJSON,
		&row.Source,
		&row.IsCustomized,
		&row.IsDefault,
	); err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}
	return &row, nil
}

// LoadWildcardLayoutTarget returns the oldest wildcard layout assignment that
// still points at a live target layout for an object.
func LoadWildcardLayoutTarget(ctx context.Context, db *sql.DB, objectAPIName string) (*WildcardLayoutTarget, error) {
	return layoutrouting.LoadLiveWildcardTarget(ctx, db, objectAPIName)
}

func CountLivePageLayoutsForObject(ctx context.Context, db *sql.DB, objectAPIName string) (int, error) {
	var count int
	if err := db.QueryRowContext(ctx, `
		SELECT COUNT(*)
		FROM _System_PageLayout
		WHERE LOWER(ObjectApiName) = LOWER(?)
		  AND (IsDeleted = 0 OR IsDeleted IS NULL)`, objectAPIName).Scan(&count); err != nil {
		return 0, err
	}
	return count, nil
}

func CountWildcardLayoutAssignments(ctx context.Context, db *sql.DB, objectAPIName string) (int, error) {
	var count int
	if err := db.QueryRowContext(ctx, `
		SELECT COUNT(*)
		FROM _System_LayoutAssignment
		WHERE LOWER(ObjectApiName) = LOWER(?)
		  AND RoleId IS NULL
		  AND ProfileId IS NULL
		  AND RecordTypeId IS NULL`, objectAPIName).Scan(&count); err != nil {
		return 0, err
	}
	return count, nil
}

func LoadSingleLivePageLayoutRowForObject(ctx context.Context, db *sql.DB, objectAPIName string) (*PageLayoutRow, error) {
	query := fmt.Sprintf(
		"SELECT `%s`, COALESCE(`%s`, ''), COALESCE(`%s`, ''), COALESCE(`%s`, 0), COALESCE(`%s`, 0) "+
			"FROM `%s` WHERE LOWER(`%s`) = LOWER(?) AND (`%s` = 0 OR `%s` IS NULL) ORDER BY `%s` ASC LIMIT 1",
		constants.ColID,
		constants.FieldSysPageLayout_Config,
		constants.FieldSysPageLayout_Source,
		constants.FieldSysPageLayout_IsCustomized,
		constants.FieldSysPageLayout_IsDefault,
		constants.TablePageLayout,
		constants.FieldObjectAPIName,
		constants.FieldIsDeleted,
		constants.FieldIsDeleted,
		constants.ColID,
	)

	var row PageLayoutRow
	if err := db.QueryRowContext(ctx, query, objectAPIName).Scan(
		&row.ID,
		&row.ConfigJSON,
		&row.Source,
		&row.IsCustomized,
		&row.IsDefault,
	); err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}
	return &row, nil
}

func UpsertManagedPageLayout(ctx context.Context, db *sql.DB, layout models.PageLayout, source string) error {
	configJSON, err := json.Marshal(layout)
	if err != nil {
		return err
	}

	_, err = db.ExecContext(ctx, `
		INSERT INTO _System_PageLayout (
			Id, ObjectApiName, LayoutName, Type, IsDefault, IsDeleted, Config,
			Source, GenerationVersion, IsCustomized, LayoutVersion,
			CreatedDate, LastModifiedDate
		) VALUES (?, ?, ?, ?, ?, 0, ?, ?, 0, 0, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
		ON DUPLICATE KEY UPDATE
			Config           = VALUES(Config),
			Source           = VALUES(Source),
			Type             = VALUES(Type),
			GenerationVersion = VALUES(GenerationVersion),
			IsCustomized     = VALUES(IsCustomized),
			LayoutVersion    = VALUES(LayoutVersion),
			IsDefault        = VALUES(IsDefault),
			IsDeleted        = 0,
			LastModifiedDate = CURRENT_TIMESTAMP`,
		layout.ID, layout.ObjectAPIName, layout.LayoutName, layout.Type, layout.IsDefault, string(configJSON), source,
	)
	return err
}

func ClearPageLayoutDefaultFlagByID(ctx context.Context, db *sql.DB, layoutID string) error {
	_, err := db.ExecContext(ctx, `
		UPDATE _System_PageLayout
		SET IsDefault = 0,
		    LastModifiedDate = CURRENT_TIMESTAMP
		WHERE Id = ?
		  AND IsDefault = 1`, layoutID)
	return err
}

func ClearOtherPageLayoutDefaultFlags(ctx context.Context, db *sql.DB, objectAPIName, keepLayoutID string) error {
	_, err := db.ExecContext(ctx, `
		UPDATE _System_PageLayout
		SET IsDefault = 0,
		    LastModifiedDate = CURRENT_TIMESTAMP
		WHERE LOWER(ObjectApiName) = LOWER(?)
		  AND Id <> ?
		  AND IsDefault = 1`, objectAPIName, keepLayoutID)
	return err
}

func SetPageLayoutDefaultFlagByID(ctx context.Context, db *sql.DB, layoutID string) error {
	_, err := db.ExecContext(ctx, `
		UPDATE _System_PageLayout
		SET IsDefault = 1,
		    LastModifiedDate = CURRENT_TIMESTAMP
		WHERE Id = ?`, layoutID)
	return err
}

func InsertWildcardLayoutAssignment(ctx context.Context, db *sql.DB, assignmentID, objectAPIName, layoutID, routeSource string) error {
	_, err := db.ExecContext(ctx, `
		INSERT INTO _System_LayoutAssignment (
			Id, ObjectApiName, LayoutId, RoleId, ProfileId, RecordTypeId,
			Specificity, CreatedDate, LastModifiedDate, RouteSource
		) VALUES (?, ?, ?, NULL, NULL, NULL, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?)`,
		assignmentID, objectAPIName, layoutID, routeSource,
	)
	return err
}