package persistence

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

	apperrors "github.com/nexuscrm/kernel/pkg/errors"
	"github.com/nexuscrm/kernel/pkg/query"
	"github.com/nexuscrm/kernel/pkg/utils"
	"github.com/nexuscrm/kernel/pkg/ports"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
	"github.com/rs/zerolog/log"
)

// UpsertUIComponent inserts or updates a UI component definition
func (r *MetadataRepository) UpsertUIComponent(ctx context.Context, component *models.UIComponent) error {
	// Check if exists by Name
	var existingID string

	qrCheck := query.From(constants.TableUIComponent).
		Select([]string{constants.FieldID}).
		Where(constants.FieldSysUIComponent_Name, component.Name).
		Build()

	err := r.db.QueryRowContext(ctx, qrCheck.SQL, qrCheck.Params...).Scan(&existingID)

	if err == nil {
		// Found, update it
		component.ID = existingID
		qrUpdate := query.Update(constants.TableUIComponent).
			Set(map[string]interface{}{
				constants.FieldSysUIComponent_Description:   component.Description,
				constants.FieldSysUIComponent_Type:          component.Type,
				constants.FieldSysUIComponent_IsEmbeddable:  component.IsEmbeddable,
				constants.FieldSysUIComponent_ComponentPath: component.ComponentPath,
				constants.FieldLastModifiedDate:             query.Raw("CURRENT_TIMESTAMP"),
			}).
			Where(constants.FieldID, existingID).
			Build()

		_, err = r.db.ExecContext(ctx, qrUpdate.SQL, qrUpdate.Params...)
		return err
	}

	if err != sql.ErrNoRows {
		return err // Real error
	}

	// Not found, Insert
	if component.ID == "" {
		component.ID = utils.GenerateID()
	}

	qrInsert := query.Insert(constants.TableUIComponent, map[string]interface{}{
		constants.FieldID:                           component.ID,
		constants.FieldSysUIComponent_Name:          component.Name,
		constants.FieldSysUIComponent_Type:          component.Type,
		constants.FieldSysUIComponent_IsEmbeddable:  component.IsEmbeddable,
		constants.FieldSysUIComponent_Description:   component.Description,
		constants.FieldSysUIComponent_ComponentPath: component.ComponentPath,
		constants.FieldCreatedDate:                  query.Raw("CURRENT_TIMESTAMP"),
		constants.FieldLastModifiedDate:             query.Raw("CURRENT_TIMESTAMP"),
	}).Build()

	_, err = r.db.ExecContext(ctx, qrInsert.SQL, qrInsert.Params...)
	return err
}

// UpsertSetupPage inserts or updates a setup page definition
// Uses atomic INSERT ON DUPLICATE KEY UPDATE to ensure idempotency
func (r *MetadataRepository) UpsertSetupPage(ctx context.Context, page *models.SetupPage) error {
	// Generate ID if not set (for new inserts)
	if page.ID == "" {
		page.ID = utils.GenerateID()
	}

	// Use atomic upsert pattern keyed on component_name (which has UNIQUE constraint)
	qr := query.Insert(constants.TableSetupPage, map[string]interface{}{
		constants.FieldID:                              page.ID,
		constants.FieldSysSetupPage_Label:              page.Label,
		constants.FieldSysSetupPage_Icon:               page.Icon,
		constants.FieldSysSetupPage_ComponentName:      page.ComponentName,
		constants.FieldSysSetupPage_Category:           page.Category,
		constants.FieldSysSetupPage_PageOrder:          page.PageOrder,
		constants.FieldSysSetupPage_PermissionRequired: page.PermissionRequired,
		constants.FieldSysSetupPage_IsEnabled:          page.IsEnabled,
		constants.FieldSysSetupPage_Description:        page.Description,
		constants.FieldSysSetupPage_Path:               page.Path,
		constants.FieldCreatedDate:                     query.Raw("CURRENT_TIMESTAMP"),
		constants.FieldLastModifiedDate:                query.Raw("CURRENT_TIMESTAMP"),
	}).OnDuplicateKeyUpdate(map[string]interface{}{
		constants.FieldSysSetupPage_Label:              page.Label,
		constants.FieldSysSetupPage_Icon:               page.Icon,
		constants.FieldSysSetupPage_Category:           page.Category,
		constants.FieldSysSetupPage_PageOrder:          page.PageOrder,
		constants.FieldSysSetupPage_PermissionRequired: page.PermissionRequired,
		constants.FieldSysSetupPage_IsEnabled:          page.IsEnabled,
		constants.FieldSysSetupPage_Description:        page.Description,
		constants.FieldSysSetupPage_Path:               page.Path,
		constants.FieldLastModifiedDate:                query.Raw("CURRENT_TIMESTAMP"),
	}).Build()

	_, err := r.db.ExecContext(ctx, qr.SQL, qr.Params...)
	return err
}

// GetSetupPages returns all setup pages
func (r *MetadataRepository) GetSetupPages(ctx context.Context) ([]*models.SetupPage, error) {
	qr := query.From(constants.TableSetupPage).
		Select([]string{
			constants.FieldID, constants.FieldSysSetupPage_Label, constants.FieldSysSetupPage_Icon,
			constants.FieldSysSetupPage_ComponentName, constants.FieldSysSetupPage_Category,
			constants.FieldSysSetupPage_PageOrder, constants.FieldSysSetupPage_PermissionRequired,
			constants.FieldSysSetupPage_IsEnabled, constants.FieldSysSetupPage_Description,
			constants.FieldCreatedDate, constants.FieldLastModifiedDate,
		}).
		OrderBy(constants.FieldSysSetupPage_PageOrder, "ASC").
		Build()

	rows, err := r.db.QueryContext(ctx, qr.SQL, qr.Params...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var pages []*models.SetupPage
	for rows.Next() {
		var p models.SetupPage
		var createdDateVal, lastModifiedDateVal interface{}

		if err := rows.Scan(
			&p.ID, &p.Label, &p.Icon, &p.ComponentName, &p.Category,
			&p.PageOrder, &p.PermissionRequired, &p.IsEnabled, &p.Description,
			&createdDateVal, &lastModifiedDateVal,
		); err != nil {
			return nil, err
		}

		p.CreatedDate = parseTime(createdDateVal)
		p.LastModifiedDate = parseTime(lastModifiedDateVal)
		pages = append(pages, &p)
	}

	return pages, rows.Err()
}

// GetRelatedListConfigs queries related list configs for relationship
func (r *MetadataRepository) GetRelatedListConfigs(ctx context.Context, layoutObjectAPIName string) ([]ports.RelatedListConfig, error) {

	// Use AddSelectRaw to ensure precise aliasing with backticks
	qr := query.From(constants.TableField).
		AddSelectRaw(fmt.Sprintf("`%s`.`%s`", constants.TableField, constants.FieldID), fmt.Sprintf("%s.%s", constants.TableField, constants.FieldID)).
		AddSelectRaw(fmt.Sprintf("`%s`.`%s`", constants.TableField, constants.FieldAPIName), fmt.Sprintf("%s.%s", constants.TableField, constants.FieldAPIName)).
		AddSelectRaw(fmt.Sprintf("`%s`.`%s`", constants.TableField, constants.FieldLabel), fmt.Sprintf("%s.%s", constants.TableField, constants.FieldLabel)).
		AddSelectRaw(fmt.Sprintf("`%s`.`%s`", "o", constants.FieldAPIName), fmt.Sprintf("%s.%s", constants.TableObject, constants.FieldObjectAPIName)).
		AddSelectRaw(fmt.Sprintf("`%s`.`%s`", "o", constants.FieldPluralLabel), fmt.Sprintf("%s.%s", constants.TableObject, constants.FieldPluralLabel)).
		AddSelectRaw(fmt.Sprintf("`%s`.`%s`", "rel", constants.FieldSysRelationship_RelatedListLabel), fmt.Sprintf("%s.%s", "rel", constants.FieldSysRelationship_RelatedListLabel)).
		AddSelectRaw(fmt.Sprintf("`%s`.`%s`", "rel", constants.FieldSysRelationship_RelatedListFields), fmt.Sprintf("%s.%s", "rel", constants.FieldSysRelationship_RelatedListFields)).
		Join("JOIN", constants.TableObject, "o",
			fmt.Sprintf("`%s`.`%s` = `o`.`%s`", constants.TableField, constants.FieldObjectID, constants.FieldID)).
		Join("LEFT JOIN", constants.TableRelationship, "rel",
			fmt.Sprintf("`rel`.`%s` = `o`.`%s` AND LOWER(`rel`.`%s`) = LOWER('%s') AND `rel`.`%s` = `%s`.`%s`",
				constants.FieldSysRelationship_ChildObjectAPIName, constants.FieldAPIName,
				constants.FieldSysRelationship_ParentObjectAPIName, layoutObjectAPIName,
				constants.FieldSysRelationship_FieldAPIName, constants.TableField, constants.FieldAPIName)).
		WhereRaw(fmt.Sprintf("JSON_CONTAINS(LOWER(`%s`.`%s`), LOWER('\"%s\"'))", constants.TableField, constants.FieldReferenceTo, layoutObjectAPIName), nil).
		WhereRaw(fmt.Sprintf("`%s`.`%s` IN ('reference', 'masterdetail')", constants.TableField, constants.FieldType), nil).
		OrderBy(fmt.Sprintf("`o`.`%s`", constants.FieldPluralLabel), "ASC").
		Build()

	log.Debug().Str("sql", qr.SQL).Interface("params", qr.Params).Msg("GetRelatedListConfigs query")

	rows, err := r.db.QueryContext(ctx, qr.SQL, qr.Params...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var result []ports.RelatedListConfig
	seen := make(map[string]bool)

	for rows.Next() {
		var lookupFieldAPI, lookupFieldLabel, childObjectAPI, childPluralLabel string
		var relatedListLabel sql.NullString
		var relatedListFields sql.NullString
		var fieldID string
		if err := rows.Scan(&fieldID, &lookupFieldAPI, &lookupFieldLabel, &childObjectAPI, &childPluralLabel, &relatedListLabel, &relatedListFields); err != nil {
			return nil, err
		}
		// Deduplicate by canonical key (childObject + lookupField)
		canonicalKey := strings.ToLower(childObjectAPI + "-" + lookupFieldAPI)
		if seen[canonicalKey] {
			continue
		}
		seen[canonicalKey] = true

		item := ports.RelatedListConfig{
			LookupFieldAPI:   lookupFieldAPI,
			LookupFieldLabel: lookupFieldLabel,
			ChildObjectAPI:   childObjectAPI,
			ChildPluralLabel: childPluralLabel,
		}
		if relatedListLabel.Valid {
			item.RelatedListLabel = &relatedListLabel.String
		}
		if relatedListFields.Valid {
			item.RelatedListFields = &relatedListFields.String
		}
		result = append(result, item)
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating lookup fields", err)
	}
	return result, nil
}