package analytics

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

	pkgerrors "github.com/nexuscrm/backend/pkg/errors"
	"github.com/nexuscrm/backend/pkg/logging"
	"github.com/nexuscrm/shared/pkg/models"
)

// crtSystemTable is the physical name of the _System_ReportType table.
// This table is not registered in system_tables.json (the metadata SSoT) so
// QueryService cannot see it — we go straight to the DB like
// SystemRepository.GetConnectedAppByClientID does for _System_ConnectedApp.
const crtSystemTable = "_System_ReportType"

// CRTRepository is the read-only port for loading Custom Report Type rows.
type CRTRepository interface {
	LoadByFullName(ctx context.Context, fullName string, user *models.UserSession) (*models.SystemReportType, error)
	LoadByID(ctx context.Context, id string, user *models.UserSession) (*models.SystemReportType, error)
}

// CRTDBQuerier is the narrow database interface the loader needs. Both *sql.DB
// and *sql.Tx satisfy it, so tests can inject an in-memory driver.
type CRTDBQuerier interface {
	QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
}

type crtLoader struct {
	db     CRTDBQuerier
	logger *logging.Logger
}

// NewCRTLoader returns the default CRTRepository implementation backed by a
// raw SQL handle. Bypasses QueryService because _System_ReportType is not
// registered in the metadata SSoT.
func NewCRTLoader(db CRTDBQuerier, logger *logging.Logger) CRTRepository {
	return &crtLoader{db: db, logger: logger}
}

// LoadByFullName first tries the verbatim FullName, then — for callers that
// pass custom API names with the trailing "__c" still attached — retries after
// trimming the suffix. Mirrors the ~39% hit-rate-with-trim pattern observed
// in staging.
func (l *crtLoader) LoadByFullName(ctx context.Context, fullName string, _ *models.UserSession) (*models.SystemReportType, error) {
	fullName = strings.TrimSpace(fullName)
	if fullName == "" {
		return nil, pkgerrors.NewNotFoundError("CRT", fullName)
	}

	rt, err := l.queryOneBy(ctx, "FullName", fullName)
	if err != nil && err != sql.ErrNoRows {
		return nil, fmt.Errorf("load CRT by full name %q: %w", fullName, err)
	}
	if rt != nil {
		return rt, nil
	}

	if trimmed := strings.TrimSuffix(fullName, "__c"); trimmed != fullName {
		rt, err = l.queryOneBy(ctx, "FullName", trimmed)
		if err != nil && err != sql.ErrNoRows {
			return nil, fmt.Errorf("load CRT by full name %q: %w", trimmed, err)
		}
		if rt != nil {
			return rt, nil
		}
	}

	return nil, pkgerrors.NewNotFoundError("CRT", fullName)
}

// LoadByID loads a single deployed CRT by its Id column.
func (l *crtLoader) LoadByID(ctx context.Context, id string, _ *models.UserSession) (*models.SystemReportType, error) {
	id = strings.TrimSpace(id)
	if id == "" {
		return nil, pkgerrors.NewNotFoundError("CRT", id)
	}
	rt, err := l.queryOneBy(ctx, "Id", id)
	if err != nil && err != sql.ErrNoRows {
		return nil, fmt.Errorf("load CRT by id %q: %w", id, err)
	}
	if rt == nil {
		return nil, pkgerrors.NewNotFoundError("CRT", id)
	}
	return rt, nil
}

// queryOneBy issues a single-row SELECT against _System_ReportType, filtering
// on the given column plus Deployed = 1. Returns (nil, nil) on not-found.
// Column name is from a closed set ("Id", "FullName") so string interpolation
// is safe here.
func (l *crtLoader) queryOneBy(ctx context.Context, column, value string) (*models.SystemReportType, error) {
	const cols = "`Id`, `FullName`, `Label`, `BaseObject`, `Deployed`, `Autogenerated`, `Sections`, `Join`, `Metadata`, `Description`"
	sqlStr := "SELECT " + cols + " FROM `" + crtSystemTable + "` WHERE `" + column + "` = ? AND `Deployed` = 1 LIMIT 1"

	var (
		out           models.SystemReportType
		deployed      int
		autogenerated sql.NullInt64
		sections      sql.NullString
		joinJSON      sql.NullString
		metaJSON      sql.NullString
		description   sql.NullString
	)
	err := l.db.QueryRowContext(ctx, sqlStr, value).Scan(
		&out.ID,
		&out.FullName,
		&out.Label,
		&out.BaseObject,
		&deployed,
		&autogenerated,
		&sections,
		&joinJSON,
		&metaJSON,
		&description,
	)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	out.Deployed = deployed != 0
	out.Autogenerated = autogenerated.Valid && autogenerated.Int64 != 0
	if sections.Valid {
		out.Sections = sections.String
	}
	if joinJSON.Valid {
		out.Join = joinJSON.String
	}
	if metaJSON.Valid {
		out.Metadata = metaJSON.String
	}
	if description.Valid {
		out.Description = description.String
	}
	return &out, nil
}