using Dapper;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.ListQuery;
using PIEDAL.DTO.Profile;

namespace PIEDAL.Query.Profile;

// Required indexes:
//   ix_pie_profile_tenant       ON pie.MPIEPROFILE(TENANTID)
//   ix_pie_profile_match        ON pie.MPIEPROFILE(TENANTID, STATUS, INDUSTRY, GEOGRAPHY, COMPANYSIZEMIN)
//                                  WHERE STATUS = 2
//   ix_pie_deviation_q_tenant   ON pie.MPIEDEVIATIONQUESTION(TENANTID)
//   ix_pie_deviation_impl_tenant ON pie.MPIEDEVIATIONIMPLICATION(TENANTID)

public sealed class PIEProfileQB : IQueryBuilder<PIEProfileListQuery, PIEProfileListDTO>
{
    private static readonly IReadOnlyDictionary<string, string> _sort =
        new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            ["profilecode"]  = "p.PIEPROFILECODE",
            ["profilename"]  = "p.PIEPROFILENAME",
            ["industry"]     = "p.industry",
            ["geography"]    = "p.geography",
            ["status"]       = "p.STATUS",
            ["usagecount"]   = "p.USAGECOUNT",
            ["version"]      = "p.PROFILEVERSION"
        };

    public (string Sql, DynamicParameters Params) Build(PIEProfileListQuery query, LoginDTO login, ISqlDialect dialect)
    {
        var orderBy = SqlClauses.OrderBy(
            query.Criteria.SortBy, query.Criteria.SortDesc, _sort,
            "p.STATUS ASC, p.PIEPROFILECODE ASC");
        return BuildProfileList(query.Criteria, login, dialect, orderBy);
    }

    public static (string Sql, DynamicParameters Params) BuildProfileList(
        PIEProfileListCriteria c, LoginDTO login, ISqlDialect d, string orderBy)
    {
        var ctx = new QueryContext(d).Register("profile", "p", schema: "pie");

        var tenantParams = new DynamicParameters();
        tenantParams.Add("TenantId", login.ClientId);

        var (whereSql, whereParams) = SqlClause.AsWhere(new[]
        {
            new SqlClause("p.TENANTID = @TenantId", tenantParams),
            SqlClauses.ExactString(ctx, c.Industry,  "profile", "INDUSTRY"),
            SqlClauses.ExactString(ctx, c.Geography, "profile", "GEOGRAPHY"),
            SqlClauses.ExactShort(ctx,  c.Status,    "profile", "STATUS"),
            SqlClauses.CodeNameSearch(ctx, c.SearchText, "profile", "PIEPROFILECODE", "PIEPROFILENAME"),
        });

        var (pagingSql, pagingParams) = SqlClauses.Paging(ctx, c, orderBy);

        string sql = $@"
SELECT {d.TotalCountExpr()},
       p.PIEPROFILEID    AS ProfileId,
       p.PIEPROFILECODE  AS ProfileCode,
       p.PIEPROFILENAME  AS ProfileName,
       p.INDUSTRY        AS Industry,
       p.GEOGRAPHY       AS Geography,
       p.STATUS          AS Status,
       p.PROFILEVERSION  AS Version,
       p.USAGECOUNT      AS UsageCount
FROM   {ctx.Table("profile", "MPIEPROFILE")} p
{whereSql}
{pagingSql}";

        return (sql, SqlClause.Merge(whereParams, pagingParams));
    }

    // ── Profile CRUD ──────────────────────────────────────────────────────────

    public const string GET_BY_ID = @"
        SELECT p.PIEPROFILEID           AS ProfileId,
               p.PIEPROFILECODE         AS ProfileCode,
               p.PIEPROFILENAME         AS ProfileName,
               p.INDUSTRY               AS Industry,
               p.COMPANYSIZEMIN         AS CompanySizeMin,
               p.COMPANYSIZEMAX         AS CompanySizeMax,
               p.GEOGRAPHY              AS Geography,
               p.PROFILEVERSION         AS Version,
               p.STATUS                 AS Status,
               p.USAGECOUNT             AS UsageCount,
               p.AVGCOMPLETIONDAYS      AS AvgCompletionDays,
               p.AVGREWORKRATE          AS AvgReworkRate,
               p.PUBLISHEDON            AS PublishedAt,
               p.PUBLISHEDBYID          AS PublishedBy,
               p.PREVIOUSVERSIONID      AS PreviousVersionId,
               p.MATCHKEYWORDS          AS MatchKeywords,
               p.CREATEDON              AS CreatedAt,
               p.CREATEDBYID            AS CreatedBy,
               p.MODIFIEDON             AS UpdatedAt,
               p.MODIFIEDBYID           AS UpdatedBy,
               p.TENANTID               AS TenantId
        FROM   pie.MPIEPROFILE p
        WHERE  p.PIEPROFILEID = @ProfileId
          AND  p.TENANTID     = @TenantId";

    public const string GET_PROFILE_MODULES = @"
        SELECT pm.PIEPROFILEMODULEID AS ProfileModuleId,
               pm.PIEPROFILEID       AS ProfileId,
               pm.PIEMODULEID        AS ModuleId,
               m.PIEMODULENAME       AS ModuleName,
               m.PIEMODULEGROUP      AS ModuleGroup,
               pm.ISPRIMARY          AS IsPrimary,
               pm.SEQUENCEORDER      AS SequenceOrder,
               pm.TENANTID           AS TenantId
        FROM   pie.MPIEPROFILEMODULE pm
        JOIN   pie.MPIEMODULE m ON m.PIEMODULEID = pm.PIEMODULEID AND m.TENANTID = pm.TENANTID
        WHERE  pm.PIEPROFILEID = @ProfileId
          AND  pm.TENANTID     = @TenantId
        ORDER BY pm.ISPRIMARY DESC, pm.SEQUENCEORDER";

    public const string GET_VARIANT_DEFAULTS = @"
        SELECT pvd.PIEPROFILEVARIANTDEFAULTID AS PvdId,
               pvd.PIEPROFILEID               AS ProfileId,
               pvd.PIEVARIANTID               AS VariantId,
               v.PIEVARIANTCODE               AS VariantCode,
               v.PIEVARIANTNAME               AS VariantName,
               v.PIEMODULEID                  AS ModuleId,
               pvd.TENANTID                   AS TenantId
        FROM   pie.MPIEPROFILEVARIANTDEFAULT pvd
        JOIN   pie.MPIEVARIANT v ON v.PIEVARIANTID = pvd.PIEVARIANTID AND v.TENANTID = pvd.TENANTID
        WHERE  pvd.PIEPROFILEID = @ProfileId
          AND  pvd.TENANTID     = @TenantId";

    public const string GET_PROFILE_KPIS = @"
        SELECT pk.PIEPROFILEKPIID AS ProfileKpiId,
               pk.PIEPROFILEID    AS ProfileId,
               pk.PIEKPIID        AS KpiId,
               k.KPICODE          AS KpiCode,
               k.KPINAME          AS KpiName,
               pk.ISPRIMARY       AS IsPrimary,
               pk.SEQUENCEORDER   AS SequenceOrder,
               pk.TENANTID        AS TenantId
        FROM   pie.MPIEPROFILEKPI pk
        JOIN   pie.MPIEKPI k ON k.PIEKPIID = pk.PIEKPIID AND k.TENANTID = pk.TENANTID
        WHERE  pk.PIEPROFILEID = @ProfileId
          AND  pk.TENANTID     = @TenantId
        ORDER BY pk.ISPRIMARY DESC, pk.SEQUENCEORDER";

    public const string GET_DEVIATION_QUESTIONS = @"
        SELECT dq.PIEDEVIATIONQUESTIONID AS QuestionId,
               dq.PIEPROFILEID            AS ProfileId,
               dq.QUESTIONCODE            AS QuestionCode,
               dq.QUESTIONTEXT            AS QuestionText,
               dq.HELPTEXT                AS HelpText,
               dq.ANSWERTYPE              AS AnswerType,
               dq.OPTIONS                 AS Options,
               dq.SEQUENCEORDER           AS SequenceOrder,
               dq.STATUS                  AS IsActive,
               dq.CREATEDON               AS CreatedAt,
               dq.CREATEDBYID             AS CreatedBy,
               dq.TENANTID                AS TenantId
        FROM   pie.MPIEDEVIATIONQUESTION dq
        WHERE  dq.PIEPROFILEID = @ProfileId
          AND  dq.STATUS       = 1
          AND  dq.TENANTID     = @TenantId
        ORDER BY dq.SEQUENCEORDER";

    /// <summary>
    /// Batch version of GET_DEVIATION_QUESTIONS — fetches all questions for a set of profiles
    /// in a single round-trip. Used by MatchProfilesAsync to avoid N+1.
    /// Requires index: ix_pie_deviation_q_profile ON pie.MPIEDEVIATIONQUESTION(PIEPROFILEID, STATUS, TENANTID)
    /// </summary>
    public const string GET_DEVIATION_QUESTIONS_BATCH = @"
        SELECT dq.PIEDEVIATIONQUESTIONID AS QuestionId,
               dq.PIEPROFILEID            AS ProfileId,
               dq.QUESTIONCODE            AS QuestionCode,
               dq.QUESTIONTEXT            AS QuestionText,
               dq.HELPTEXT                AS HelpText,
               dq.ANSWERTYPE              AS AnswerType,
               dq.OPTIONS                 AS Options,
               dq.SEQUENCEORDER           AS SequenceOrder,
               dq.STATUS                  AS IsActive,
               dq.CREATEDON               AS CreatedAt,
               dq.CREATEDBYID             AS CreatedBy,
               dq.TENANTID                AS TenantId
        FROM   pie.MPIEDEVIATIONQUESTION dq
        WHERE  dq.PIEPROFILEID = ANY(@ProfileIds)
          AND  dq.STATUS       = 1
          AND  dq.TENANTID     = @TenantId
        ORDER BY dq.PIEPROFILEID, dq.SEQUENCEORDER";

    public const string GET_DEVIATION_IMPLICATIONS = @"
        SELECT di.PIEDEVIATIONIMPLICATIONID AS ImplicationId,
               di.PIEDEVIATIONQUESTIONID     AS QuestionId,
               di.ANSWERVALUE                AS AnswerValue,
               di.IMPLICATIONTYPE            AS ImplicationType,
               di.REFID                      AS RefId,
               di.REFCODE                    AS RefCode,
               di.EFFORTDELTAHOURS           AS EffortDeltaHours,
               di.DESCRIPTION                AS Description,
               di.TENANTID                   AS TenantId
        FROM   pie.MPIEDEVIATIONIMPLICATION di
        WHERE  di.PIEDEVIATIONQUESTIONID = ANY(@QuestionIds)
          AND  di.TENANTID              = @TenantId";

    public const string INSERT = @"
        INSERT INTO pie.MPIEPROFILE
            (PIEPROFILEID, PIEPROFILECODE, PIEPROFILENAME, INDUSTRY, COMPANYSIZEMIN, COMPANYSIZEMAX,
             GEOGRAPHY, PROFILEVERSION, STATUS, MATCHKEYWORDS,
             CREATEDON, CREATEDBYID, MODIFIEDON, MODIFIEDBYID, TENANTID)
        VALUES
            (@ProfileId, @ProfileCode, @ProfileName, @Industry, @CompanySizeMin, @CompanySizeMax,
             @Geography, @Version, @Status, @MatchKeywords,
             @CreatedAt, @CreatedBy, @UpdatedAt, @UpdatedBy, @TenantId)";

    public const string UPDATE = @"
        UPDATE pie.MPIEPROFILE SET
            PIEPROFILECODE  = @ProfileCode,
            PIEPROFILENAME  = @ProfileName,
            INDUSTRY        = @Industry,
            COMPANYSIZEMIN  = @CompanySizeMin,
            COMPANYSIZEMAX  = @CompanySizeMax,
            GEOGRAPHY       = @Geography,
            STATUS          = @Status,
            MATCHKEYWORDS   = @MatchKeywords,
            MODIFIEDON      = @UpdatedAt,
            MODIFIEDBYID    = @UpdatedBy
        WHERE PIEPROFILEID = @ProfileId AND TENANTID = @TenantId";

    /// <summary>Sets status=2 (Active) and records published_at/by.</summary>
    public const string PUBLISH = @"
        UPDATE pie.MPIEPROFILE SET
            STATUS       = 2,
            PUBLISHEDON  = @PublishedAt,
            PUBLISHEDBYID = @PublishedBy,
            MODIFIEDON   = @UpdatedAt,
            MODIFIEDBYID = @UpdatedBy
        WHERE PIEPROFILEID = @ProfileId AND TENANTID = @TenantId AND STATUS = 1";

    /// <summary>Sets status=3 (Deprecated).</summary>
    public const string DEPRECATE = @"
        UPDATE pie.MPIEPROFILE SET
            STATUS       = 3,
            MODIFIEDON   = @UpdatedAt,
            MODIFIEDBYID = @UpdatedBy
        WHERE PIEPROFILEID = @ProfileId AND TENANTID = @TenantId AND STATUS = 2";

    // ── Profile child inserts / replaces ──────────────────────────────────────

    public const string DELETE_PROFILE_MODULES = @"
        DELETE FROM pie.MPIEPROFILEMODULE WHERE PIEPROFILEID = @ProfileId AND TENANTID = @TenantId";

    public const string INSERT_PROFILE_MODULE = @"
        INSERT INTO pie.MPIEPROFILEMODULE
            (PIEPROFILEMODULEID, PIEPROFILEID, PIEMODULEID, ISPRIMARY, SEQUENCEORDER, TENANTID)
        VALUES
            (@ProfileModuleId, @ProfileId, @ModuleId, @IsPrimary, @SequenceOrder, @TenantId)";

    public const string DELETE_VARIANT_DEFAULTS = @"
        DELETE FROM pie.MPIEPROFILEVARIANTDEFAULT WHERE PIEPROFILEID = @ProfileId AND TENANTID = @TenantId";

    public const string INSERT_VARIANT_DEFAULT = @"
        INSERT INTO pie.MPIEPROFILEVARIANTDEFAULT (PIEPROFILEVARIANTDEFAULTID, PIEPROFILEID, PIEVARIANTID, TENANTID)
        VALUES (@PvdId, @ProfileId, @VariantId, @TenantId)";

    public const string DELETE_PROFILE_KPIS = @"
        DELETE FROM pie.MPIEPROFILEKPI WHERE PIEPROFILEID = @ProfileId AND TENANTID = @TenantId";

    public const string INSERT_PROFILE_KPI = @"
        INSERT INTO pie.MPIEPROFILEKPI
            (PIEPROFILEKPIID, PIEPROFILEID, PIEKPIID, ISPRIMARY, SEQUENCEORDER, TENANTID)
        VALUES
            (@ProfileKpiId, @ProfileId, @KpiId, @IsPrimary, @SequenceOrder, @TenantId)";

    public const string INSERT_DEVIATION_QUESTION = @"
        INSERT INTO pie.MPIEDEVIATIONQUESTION
            (PIEDEVIATIONQUESTIONID, PIEPROFILEID, QUESTIONCODE, QUESTIONTEXT, HELPTEXT,
             ANSWERTYPE, OPTIONS, SEQUENCEORDER, STATUS,
             CREATEDON, CREATEDBYID, TENANTID)
        VALUES
            (@QuestionId, @ProfileId, @QuestionCode, @QuestionText, @HelpText,
             @AnswerType, @Options, @SequenceOrder, @IsActive,
             @CreatedAt, @CreatedBy, @TenantId)";

    public const string INSERT_DEVIATION_IMPLICATION = @"
        INSERT INTO pie.MPIEDEVIATIONIMPLICATION
            (PIEDEVIATIONIMPLICATIONID, PIEDEVIATIONQUESTIONID, ANSWERVALUE, IMPLICATIONTYPE, REFID, REFCODE,
             EFFORTDELTAHOURS, DESCRIPTION, TENANTID)
        VALUES
            (@ImplicationId, @QuestionId, @AnswerValue, @ImplicationType, @RefId, @RefCode,
             @EffortDeltaHours, @Description, @TenantId)";

    public const string DELETE_DEVIATION_QUESTIONS = @"
        DELETE FROM pie.MPIEDEVIATIONQUESTION WHERE PIEPROFILEID = @ProfileId AND TENANTID = @TenantId";

    public const string DELETE_DEVIATION_IMPLICATIONS_BY_QUESTION = @"
        DELETE FROM pie.MPIEDEVIATIONIMPLICATION WHERE PIEDEVIATIONQUESTIONID = @QuestionId AND TENANTID = @TenantId";

    // ── Profile match query (QuickStart <500ms SLA) ───────────────────────────
    // Uses ix_pie_profile_match partial index (STATUS=2, TENANTID, INDUSTRY, GEOGRAPHY, COMPANYSIZEMIN)

    public const string MATCH_PROFILES = @"
        SELECT p.PIEPROFILEID       AS ProfileId,
               p.PIEPROFILECODE     AS ProfileCode,
               p.PIEPROFILENAME     AS ProfileName,
               p.INDUSTRY           AS Industry,
               p.GEOGRAPHY          AS Geography,
               p.COMPANYSIZEMIN     AS CompanySizeMin,
               p.COMPANYSIZEMAX     AS CompanySizeMax,
               p.MATCHKEYWORDS      AS MatchKeywords
        FROM   pie.MPIEPROFILE p
        WHERE  p.STATUS          = 2
          AND  p.TENANTID        = @TenantId
          AND  p.INDUSTRY        = @Industry
          AND  p.GEOGRAPHY       = @Geography
          AND  p.COMPANYSIZEMIN <= @CompanySize
          AND  (p.COMPANYSIZEMAX IS NULL OR p.COMPANYSIZEMAX >= @CompanySize)
        ORDER BY p.PIEPROFILECODE";

    // ── Select list ───────────────────────────────────────────────────────────

    public const string GET_SELECT_LIST = @"
        SELECT p.PIEPROFILEID    AS ProfileId,
               p.PIEPROFILECODE  AS ProfileCode,
               p.PIEPROFILENAME  AS ProfileName,
               p.PROFILEVERSION  AS Version
        FROM   pie.MPIEPROFILE p
        WHERE  p.STATUS    = 2
          AND  p.TENANTID  = @TenantId
        ORDER BY p.PIEPROFILECODE";

    public const string INCREMENT_USAGE_COUNT = @"
        UPDATE pie.MPIEPROFILE SET USAGECOUNT = USAGECOUNT + 1
        WHERE  PIEPROFILEID = @ProfileId AND TENANTID = @TenantId";
}