namespace MarketingDAL.Query.BusinessAspirationGoal;

/// <summary>SQL constants for MBUSINESSASPIRATIONGOAL — MarketMind AI BusinessAspirationGoal data.
/// No inline SQL elsewhere.</summary>
public static class BusinessAspirationGoalQB
{
    public const string GET_BUSINESSASPIRATIONGOAL = @"
SELECT
    B.BUSINESSASPIRATIONGOALID     AS BusinessAspirationGoalId,
    B.BUSINESSASPIRATIONGOALCODE   AS BusinessAspirationGoalCode,
    B.BUSINESSASPIRATIONGOALNAME   AS BusinessAspirationGoalName,
    B.ANNUALREVENUETARGET          AS AnnualRevenueTarget,
    B.EFFECTIVEYEAR                AS EffectiveYear,
    B.REMARKS                      AS Remarks,

    B.CREATEDBYID                  AS CreatedById,
    B.CREATEDON                    AS CreatedOn,
    CB.USERNAME                    AS CreatedByName,
    B.MODIFIEDBYID                 AS ModifiedById,
    B.MODIFIEDON                   AS ModifiedOn,
    MB.USERNAME                    AS ModifiedByName,

    B.SORTORDER                    AS SortOrder,
    B.STATUS                       AS Status,
    B.VERSION                      AS Version,
    B.TENANTID                     AS TenantId
FROM MBUSINESSASPIRATIONGOAL B
LEFT JOIN MUSER CB ON CB.USERID = B.CREATEDBYID
LEFT JOIN MUSER MB ON MB.USERID = B.MODIFIEDBYID
WHERE B.BUSINESSASPIRATIONGOALID = @BusinessAspirationGoalId;
";

    /// <summary>Latest active row by EffectiveYear — used by CustomerProductMatrixCellBLL to
    /// compute RevenueTargetAmount. Returns null (no row) if none exists yet.</summary>
    public const string GET_LATEST_BUSINESSASPIRATIONGOAL = @"
SELECT TOP 1
    B.BUSINESSASPIRATIONGOALID     AS BusinessAspirationGoalId,
    B.BUSINESSASPIRATIONGOALCODE   AS BusinessAspirationGoalCode,
    B.BUSINESSASPIRATIONGOALNAME   AS BusinessAspirationGoalName,
    B.ANNUALREVENUETARGET          AS AnnualRevenueTarget,
    B.EFFECTIVEYEAR                AS EffectiveYear,
    B.REMARKS                      AS Remarks,

    B.CREATEDBYID                  AS CreatedById,
    B.CREATEDON                    AS CreatedOn,
    CB.USERNAME                    AS CreatedByName,
    B.MODIFIEDBYID                 AS ModifiedById,
    B.MODIFIEDON                   AS ModifiedOn,
    MB.USERNAME                    AS ModifiedByName,

    B.SORTORDER                    AS SortOrder,
    B.STATUS                       AS Status,
    B.VERSION                      AS Version,
    B.TENANTID                     AS TenantId
FROM MBUSINESSASPIRATIONGOAL B
LEFT JOIN MUSER CB ON CB.USERID = B.CREATEDBYID
LEFT JOIN MUSER MB ON MB.USERID = B.MODIFIEDBYID
WHERE B.STATUS = 1
ORDER BY B.EFFECTIVEYEAR DESC, B.BUSINESSASPIRATIONGOALID DESC;
";

    public const string SAVE_BUSINESSASPIRATIONGOAL = @"
INSERT INTO MBUSINESSASPIRATIONGOAL (
    BUSINESSASPIRATIONGOALID, BUSINESSASPIRATIONGOALCODE, BUSINESSASPIRATIONGOALNAME,
    ANNUALREVENUETARGET, EFFECTIVEYEAR, REMARKS,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SORTORDER, STATUS, VERSION, TENANTID
)
VALUES (
    @BusinessAspirationGoalId, @BusinessAspirationGoalCode, @BusinessAspirationGoalName,
    @AnnualRevenueTarget, @EffectiveYear, @Remarks,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SortOrder, @Status, @Version, @TenantId
);
";

    public const string UPDATE_BUSINESSASPIRATIONGOAL = @"
UPDATE MBUSINESSASPIRATIONGOAL
SET
    BUSINESSASPIRATIONGOALCODE   = @BusinessAspirationGoalCode,
    BUSINESSASPIRATIONGOALNAME   = @BusinessAspirationGoalName,
    ANNUALREVENUETARGET          = @AnnualRevenueTarget,
    EFFECTIVEYEAR                = @EffectiveYear,
    REMARKS                      = @Remarks,
    MODIFIEDBYID                 = @ModifiedById,
    MODIFIEDON                   = @ModifiedOn,
    SORTORDER                    = @SortOrder,
    STATUS                       = @Status,
    VERSION                      = @Version
WHERE BUSINESSASPIRATIONGOALID = @BusinessAspirationGoalId;
";

    public const string DELETE_BUSINESSASPIRATIONGOAL = @"
DELETE FROM MBUSINESSASPIRATIONGOAL
WHERE BUSINESSASPIRATIONGOALID = @BusinessAspirationGoalId;
";

    public const string GET_SELECTLIST_BUSINESSASPIRATIONGOAL = @"
SELECT
    BUSINESSASPIRATIONGOALID    AS Id,
    BUSINESSASPIRATIONGOALCODE  AS Code,
    BUSINESSASPIRATIONGOALNAME  AS Name
FROM MBUSINESSASPIRATIONGOAL
WHERE STATUS = 1
ORDER BY EFFECTIVEYEAR DESC;
";
}