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

namespace PIEDAL.Query.Module;

// Required indexes:
//   ix_pie_module_tenant        ON pie.MPIEMODULE(TENANTID)
//   ix_pie_module_group         ON pie.MPIEMODULE(PIEMODULEGROUP) WHERE STATUS = 1
//   ix_pie_kpi_module           ON pie.MPIEKPI(PIEMODULEID) WHERE STATUS = 1
//   ix_pie_kpi_tenant           ON pie.MPIEKPI(TENANTID)

// ── pie_module list ───────────────────────────────────────────────────────────

public sealed class PIEModuleQB : IQueryBuilder<PIEModuleListQuery, PIEModuleListDTO>
{
    private static readonly IReadOnlyDictionary<string, string> _sort =
        new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            ["modulecode"]  = "m.PIEMODULECODE",
            ["modulename"]  = "m.PIEMODULENAME",
            ["modulegroup"] = "m.PIEMODULEGROUP",
            ["displayorder"] = "m.SORTORDER"
        };

    public (string Sql, DynamicParameters Params) Build(PIEModuleListQuery query, LoginDTO login, ISqlDialect dialect)
    {
        var orderBy = SqlClauses.OrderBy(
            query.Criteria.SortBy, query.Criteria.SortDesc, _sort, "m.SORTORDER ASC, m.PIEMODULECODE ASC");
        return BuildModuleList(query.Criteria, login, dialect, orderBy);
    }

    public static (string Sql, DynamicParameters Params) BuildModuleList(
        PIEModuleListCriteria c, LoginDTO login, ISqlDialect d, string orderBy)
    {
        var ctx = new QueryContext(d).Register("module", "m", schema: "pie");

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

        var (whereSql, whereParams) = SqlClause.AsWhere(new[]
        {
            new SqlClause("m.TENANTID = @TenantId", tenantParams),
            SqlClauses.ExactString(ctx, c.ModuleGroup, "module", "PIEMODULEGROUP"),
            SqlClauses.ExactShort(ctx,  c.IsActive,    "module", "STATUS"),
            SqlClauses.CodeNameSearch(ctx, c.SearchText, "module", "PIEMODULECODE", "PIEMODULENAME"),
        });

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

        string sql = $@"
SELECT {d.TotalCountExpr()},
       m.PIEMODULEID    AS ModuleId,
       m.PIEMODULECODE  AS ModuleCode,
       m.PIEMODULENAME  AS ModuleName,
       m.PIEMODULEGROUP AS ModuleGroup,
       m.STATUS         AS IsActive,
       m.ISSTANDALONE   AS IsStandalone,
       m.SORTORDER      AS DisplayOrder
FROM   {ctx.Table("module", "MPIEMODULE")} m
{whereSql}
{pagingSql}";

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

    // ── CRUD SQL ──────────────────────────────────────────────────────────────

    public const string GET_BY_ID = @"
        SELECT m.PIEMODULEID      AS ModuleId,
               m.PIEMODULECODE    AS ModuleCode,
               m.PIEMODULENAME    AS ModuleName,
               m.PIEMODULEGROUP   AS ModuleGroup,
               m.description      AS Description,
               m.ICONKEY          AS IconKey,
               m.SORTORDER        AS DisplayOrder,
               m.STATUS           AS IsActive,
               m.ISSTANDALONE     AS IsStandalone,
               m.CMSCONTENTREF    AS CmsContentRef,
               m.CREATEDON        AS CreatedAt,
               m.CREATEDBYID      AS CreatedBy,
               m.MODIFIEDON       AS UpdatedAt,
               m.MODIFIEDBYID     AS UpdatedBy,
               m.TENANTID         AS TenantId
        FROM   pie.MPIEMODULE m
        WHERE  m.PIEMODULEID  = @ModuleId
          AND  m.TENANTID     = @TenantId";

    public const string GET_DEPENDENCIES = @"
        SELECT d.PIECONFIGDEPENDENCYID  AS DependencyId,
               d.PIEMODULEID            AS ModuleId,
               d.DEPENDSONMODULEID      AS DependsOnModuleId,
               dep.PIEMODULENAME        AS DependsOnModuleName,
               d.DEPENDENCYTYPE         AS DependencyType,
               d.TENANTID               AS TenantId
        FROM   pie.MPIEMODULEDEPENDENCY d
        JOIN   pie.MPIEMODULE dep
               ON  dep.PIEMODULEID  = d.DEPENDSONMODULEID
               AND dep.TENANTID     = d.TENANTID
        WHERE  d.PIEMODULEID  = @ModuleId
          AND  d.TENANTID     = @TenantId";

    public const string INSERT = @"
        INSERT INTO pie.MPIEMODULE
            (PIEMODULEID, PIEMODULECODE, PIEMODULENAME, PIEMODULEGROUP, description, ICONKEY,
             SORTORDER, STATUS, ISSTANDALONE, CMSCONTENTREF,
             CREATEDON, CREATEDBYID, MODIFIEDON, MODIFIEDBYID, TENANTID)
        VALUES
            (@ModuleId, @ModuleCode, @ModuleName, @ModuleGroup, @Description, @IconKey,
             @DisplayOrder, @IsActive, @IsStandalone, @CmsContentRef,
             @CreatedAt, @CreatedBy, @UpdatedAt, @UpdatedBy, @TenantId)";

    public const string UPDATE = @"
        UPDATE pie.MPIEMODULE SET
            PIEMODULECODE  = @ModuleCode,
            PIEMODULENAME  = @ModuleName,
            PIEMODULEGROUP = @ModuleGroup,
            description    = @Description,
            ICONKEY        = @IconKey,
            SORTORDER      = @DisplayOrder,
            STATUS         = @IsActive,
            ISSTANDALONE   = @IsStandalone,
            CMSCONTENTREF  = @CmsContentRef,
            MODIFIEDON     = @UpdatedAt,
            MODIFIEDBYID   = @UpdatedBy
        WHERE PIEMODULEID = @ModuleId AND TENANTID = @TenantId";

    /// <summary>Soft delete — preserves FK integrity with downstream tables.</summary>
    public const string SOFT_DELETE = @"
        UPDATE pie.MPIEMODULE SET
            STATUS       = 0,
            MODIFIEDON   = @UpdatedAt,
            MODIFIEDBYID = @UpdatedBy
        WHERE PIEMODULEID = @ModuleId AND TENANTID = @TenantId";

    public const string INSERT_DEPENDENCY = @"
        INSERT INTO pie.MPIEMODULEDEPENDENCY
            (PIECONFIGDEPENDENCYID, PIEMODULEID, DEPENDSONMODULEID, DEPENDENCYTYPE, TENANTID)
        VALUES
            (@DependencyId, @ModuleId, @DependsOnModuleId, @DependencyType, @TenantId)";

    public const string DELETE_DEPENDENCIES = @"
        DELETE FROM pie.MPIEMODULEDEPENDENCY
        WHERE PIEMODULEID = @ModuleId AND TENANTID = @TenantId";

    public const string GET_SELECT_LIST = @"
        SELECT m.PIEMODULEID    AS ModuleId,
               m.PIEMODULECODE  AS ModuleCode,
               m.PIEMODULENAME  AS ModuleName,
               m.PIEMODULEGROUP AS ModuleGroup
        FROM   pie.MPIEMODULE m
        WHERE  m.STATUS    = 1
          AND  m.TENANTID  = @TenantId
        ORDER BY m.PIEMODULEGROUP, m.SORTORDER, m.PIEMODULECODE";

    /// <summary>Returns all PIEMODULEID values for graph cycle check before inserting a dependency.</summary>
    public const string GET_ALL_DEPENDENCIES_FOR_CYCLE_CHECK = @"
        SELECT PIEMODULEID           AS ModuleId,
               DEPENDSONMODULEID     AS DependsOnModuleId
        FROM   pie.MPIEMODULEDEPENDENCY
        WHERE  TENANTID = @TenantId";
}

// ── pie_kpi list ──────────────────────────────────────────────────────────────

public sealed class PIEKpiQB : IQueryBuilder<PIEKpiListQuery, PIEKpiListDTO>
{
    private static readonly IReadOnlyDictionary<string, string> _sort =
        new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            ["kpicode"]     = "k.KPICODE",
            ["kpiname"]     = "k.KPINAME",
            ["kpicategory"] = "k.KPICATEGORY"
        };

    public (string Sql, DynamicParameters Params) Build(PIEKpiListQuery query, LoginDTO login, ISqlDialect dialect)
    {
        var orderBy = SqlClauses.OrderBy(
            query.Criteria.SortBy, query.Criteria.SortDesc, _sort, "k.KPICODE ASC");
        return BuildKpiList(query.Criteria, login, dialect, orderBy);
    }

    public static (string Sql, DynamicParameters Params) BuildKpiList(
        PIEKpiListCriteria c, LoginDTO login, ISqlDialect d, string orderBy)
    {
        var ctx = new QueryContext(d).Register("kpi", "k", schema: "pie");

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

        var (whereSql, whereParams) = SqlClause.AsWhere(new[]
        {
            new SqlClause("k.TENANTID = @TenantId", tenantParams),
            SqlClauses.ExactGuid(ctx,  c.ModuleId,    "kpi", "PIEMODULEID"),
            SqlClauses.ExactShort(ctx, c.KpiCategory, "kpi", "KPICATEGORY"),
            SqlClauses.ExactShort(ctx, c.IsActive,    "kpi", "STATUS"),
            SqlClauses.CodeNameSearch(ctx, c.SearchText, "kpi", "KPICODE", "KPINAME"),
        });

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

        string sql = $@"
SELECT {d.TotalCountExpr()},
       k.PIEKPIID     AS KpiId,
       k.PIEMODULEID  AS ModuleId,
       k.KPICODE      AS KpiCode,
       k.KPINAME      AS KpiName,
       k.KPICATEGORY  AS KpiCategory,
       k.STATUS       AS IsActive
FROM   {ctx.Table("kpi", "MPIEKPI")} k
{whereSql}
{pagingSql}";

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

    // ── KPI CRUD SQL ──────────────────────────────────────────────────────────

    public const string GET_BY_ID = @"
        SELECT k.PIEKPIID          AS KpiId,
               k.PIEMODULEID       AS ModuleId,
               m.PIEMODULENAME     AS ModuleName,
               k.KPICODE           AS KpiCode,
               k.KPINAME           AS KpiName,
               k.KPICATEGORY       AS KpiCategory,
               k.unit               AS Unit,
               k.description        AS Description,
               k.MEASUREMENTGUIDE   AS MeasurementGuide,
               k.STATUS             AS IsActive,
               k.CMSCONTENTREF      AS CmsContentRef,
               k.CREATEDON          AS CreatedAt,
               k.CREATEDBYID        AS CreatedBy,
               k.MODIFIEDON         AS UpdatedAt,
               k.MODIFIEDBYID       AS UpdatedBy,
               k.TENANTID           AS TenantId
        FROM   pie.MPIEKPI k
        JOIN   pie.MPIEMODULE m ON m.PIEMODULEID = k.PIEMODULEID AND m.TENANTID = k.TENANTID
        WHERE  k.PIEKPIID    = @KpiId
          AND  k.TENANTID    = @TenantId";

    public const string GET_BY_MODULE = @"
        SELECT k.PIEKPIID     AS KpiId,
               k.PIEMODULEID  AS ModuleId,
               k.KPICODE      AS KpiCode,
               k.KPINAME      AS KpiName,
               k.KPICATEGORY  AS KpiCategory,
               k.unit          AS Unit,
               k.STATUS        AS IsActive,
               k.TENANTID      AS TenantId
        FROM   pie.MPIEKPI k
        WHERE  k.PIEMODULEID  = @ModuleId
          AND  k.TENANTID     = @TenantId
        ORDER BY k.KPICODE";

    public const string INSERT = @"
        INSERT INTO pie.MPIEKPI
            (PIEKPIID, PIEMODULEID, KPICODE, KPINAME, KPICATEGORY, unit, description,
             MEASUREMENTGUIDE, STATUS, CMSCONTENTREF,
             CREATEDON, CREATEDBYID, MODIFIEDON, MODIFIEDBYID, TENANTID)
        VALUES
            (@KpiId, @ModuleId, @KpiCode, @KpiName, @KpiCategory, @Unit, @Description,
             @MeasurementGuide, @IsActive, @CmsContentRef,
             @CreatedAt, @CreatedBy, @UpdatedAt, @UpdatedBy, @TenantId)";

    public const string UPDATE = @"
        UPDATE pie.MPIEKPI SET
            KPICODE          = @KpiCode,
            KPINAME          = @KpiName,
            KPICATEGORY      = @KpiCategory,
            unit              = @Unit,
            description       = @Description,
            MEASUREMENTGUIDE  = @MeasurementGuide,
            STATUS            = @IsActive,
            CMSCONTENTREF     = @CmsContentRef,
            MODIFIEDON        = @UpdatedAt,
            MODIFIEDBYID      = @UpdatedBy
        WHERE PIEKPIID = @KpiId AND TENANTID = @TenantId";

    public const string SOFT_DELETE = @"
        UPDATE pie.MPIEKPI SET
            STATUS       = 0,
            MODIFIEDON   = @UpdatedAt,
            MODIFIEDBYID = @UpdatedBy
        WHERE PIEKPIID = @KpiId AND TENANTID = @TenantId";
}