package persistence

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

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

// CreateDashboard creates a new dashboard
func (r *MetadataRepository) CreateDashboard(ctx context.Context, dashboard *models.DashboardConfig) error {

	desc := ""
	if dashboard.Description != nil {
		desc = *dashboard.Description
	}

	tx, err := r.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback()

	qr := query.Insert(constants.TableDashboard, map[string]interface{}{
		constants.FieldID:                      dashboard.ID,
		constants.FieldDashboard_Name:          dashboard.Label,
		constants.FieldDashboard_DeveloperName: dashboard.DeveloperName,
		constants.FieldDashboard_Description:   desc,
		constants.FieldDashboard_FilterMode:    dashboard.FilterMode,
		constants.FieldDashboard_LayoutMode:    dashboard.LayoutMode,
		constants.FieldDashboard_StartDate:     dashboard.StartDate,
		constants.FieldDashboard_EndDate:       dashboard.EndDate,
		constants.FieldDashboard_DateRangeType: dashboard.DateRangeType,
	}).Build()

	_, err = tx.ExecContext(ctx, qr.SQL, qr.Params...)
	if err != nil {
		return err
	}

	// Save Components
	if err := r.saveDashboardComponents(ctx, tx, dashboard.ID, dashboard.Widgets); err != nil {
		return err
	}

	return tx.Commit()
}

// UpdateDashboard updates a dashboard
func (r *MetadataRepository) UpdateDashboard(ctx context.Context, id string, dashboard *models.DashboardConfig) error {

	desc := ""
	if dashboard.Description != nil {
		desc = *dashboard.Description
	}

	tx, err := r.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback()

	qr := query.Update(constants.TableDashboard).
		Set(map[string]interface{}{
			constants.FieldDashboard_Name:          dashboard.Label,
			constants.FieldDashboard_DeveloperName: dashboard.DeveloperName,
			constants.FieldDashboard_Description:   desc,
			constants.FieldDashboard_FilterMode:    dashboard.FilterMode,
			constants.FieldDashboard_LayoutMode:    dashboard.LayoutMode,
			constants.FieldDashboard_StartDate:     dashboard.StartDate,
			constants.FieldDashboard_EndDate:       dashboard.EndDate,
			constants.FieldDashboard_DateRangeType: dashboard.DateRangeType,
		}).
		Where(constants.FieldID, id).
		Build()

	_, err = tx.ExecContext(ctx, qr.SQL, qr.Params...)
	if err != nil {
		return err
	}

	// Delete existing components
	_, err = tx.ExecContext(ctx, "DELETE FROM "+constants.TableDashboardComponent+" WHERE DashboardId = ?", id)
	if err != nil {
		return err
	}

	// Save New Components
	if err := r.saveDashboardComponents(ctx, tx, id, dashboard.Widgets); err != nil {
		return err
	}

	return tx.Commit()
}

// DeleteDashboard deletes a dashboard
func (r *MetadataRepository) DeleteDashboard(ctx context.Context, id string) error {
	qr := query.Delete(constants.TableDashboard).
		Where(constants.FieldID, id).
		Build()
	_, err := r.db.ExecContext(ctx, qr.SQL, qr.Params...)
	return err
}

// GetAllDashboards queries all dashboards
func (r *MetadataRepository) GetAllDashboards(ctx context.Context) ([]*models.DashboardConfig, error) {
	qr := query.From(constants.TableDashboard).
		Select(constants.ColsDashboard).
		Build()

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

	dashboards := make([]*models.DashboardConfig, 0)
	for rows.Next() {
		db, err := r.scanDashboard(rows)
		if err != nil {
			log.Warn().Err(err).Msg("Failed to scan dashboard")
			continue
		}
		dashboards = append(dashboards, db)
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating dashboards", err)
	}
	return dashboards, nil
}

// GetDashboard queries a single dashboard
func (r *MetadataRepository) GetDashboard(ctx context.Context, id string) (*models.DashboardConfig, error) {
	qr := query.From(constants.TableDashboard).
		Select(constants.ColsDashboard).
		WhereRaw(fmt.Sprintf("%s = ? OR %s = ?", constants.ColDashboard_ID, constants.FieldDashboard_DeveloperName), []interface{}{id, id}).
		Build()

	db, err := r.scanDashboard(r.db.QueryRowContext(ctx, qr.SQL, qr.Params...))
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}

	// Fetch Components (Use resolved db.ID, not input id which might be developer_name)
	componentsQuery := "SELECT Id, DashboardId, ReportId, Type, Title, Config FROM "+constants.TableDashboardComponent+" WHERE DashboardId = ?"
	compRows, err := r.db.QueryContext(ctx, componentsQuery, db.ID)
	if err != nil {
		// Log error but return dashboard? Or fail? Fail safe.
		return nil, err
	}
	defer compRows.Close()

	widgets := make([]models.WidgetConfig, 0)
	for compRows.Next() {
		var compID, dashID, reportID, compType, title, configJSON string
		if err := compRows.Scan(&compID, &dashID, &reportID, &compType, &title, &configJSON); err != nil {
			log.Warn().Err(err).Msg("Failed to scan dashboard component")
			continue
		}

		var widget models.WidgetConfig
		if err := json.Unmarshal([]byte(configJSON), &widget); err != nil {
			log.Warn().Err(err).Msg("Failed to unmarshal widget config")
			continue
		}
		// Ensure core fields match DB (source of truth is the DB columns)
		widget.ID = compID
		widget.ReportID = reportID
		widget.Type = compType
		widget.Title = title
		widgets = append(widgets, widget)
	}
	if err := compRows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating dashboard components", err)
	}
	db.Widgets = widgets

	return db, nil
}

// saveDashboardComponents helper to transactionally save widgets
func (r *MetadataRepository) saveDashboardComponents(ctx context.Context, tx *sql.Tx, dashboardID string, widgets []models.WidgetConfig) error {
	// Helper to get dummy report
	getDummyReportID := func() (string, error) {
		const dummyDevName = "ad_hoc_placeholder"
		var id string

		// Try to find existing placeholder by Unique DeveloperName
		err := tx.QueryRowContext(ctx, "SELECT Id FROM Report WHERE DeveloperName = ?", dummyDevName).Scan(&id)
		if err == nil {
			return id, nil
		}

		// Create new
		id = utils.GenerateID()
		// Use INSERT IGNORE to handle race conditions on developer_name unique constraint
		// Note: SourceObject is empty for placeholder - actual value set on real report creation
		_, err = tx.ExecContext(ctx,
			"INSERT IGNORE INTO Report (Id, Name, DeveloperName, SourceObject, ReportType, CreatedDate, LastModifiedDate) VALUES (?, 'Ad-Hoc Widget Placeholder', ?, '', 'Tabular', NOW(), NOW())",
			id, dummyDevName)
		if err != nil {
			return "", err
		}

		// If race condition occurred, the previous INSERT didn't insert. We must fetch the existing ID.
		// (Or if it did insert, the SELECT acts as confirmation).
		err = tx.QueryRowContext(ctx, "SELECT Id FROM Report WHERE DeveloperName = ?", dummyDevName).Scan(&id)
		if err != nil {
			return "", apperrors.NewInternalError("failed to resolve dummy report id", err)
		}
		return id, nil
	}

	for _, widget := range widgets {
		configBytes, err := json.Marshal(widget)
		if err != nil {
			return err
		}

		compID := utils.GenerateID()

		var reportIDVal interface{}
		if widget.ReportID != "" {
			reportIDVal = widget.ReportID
		} else {
			// Generate/Find dummy report
			dummyID, err := getDummyReportID()
			if err != nil {
				return apperrors.NewInternalError(fmt.Sprintf("failed to get dummy report for widget %s", widget.Title), err)
			}
			reportIDVal = dummyID
		}

		_, err = tx.ExecContext(ctx,
			"INSERT INTO "+constants.TableDashboardComponent+" (Id, DashboardId, ReportId, Type, Title, Config) VALUES (?, ?, ?, ?, ?, ?)",
			compID, dashboardID, reportIDVal, widget.Type, widget.Title, string(configBytes))
		if err != nil {
			return err
		}
	}
	return nil
}