namespace MarketingDAL.Query.Persona;

/// <summary>SQL constants for MPERSONA — MarketMind AI Persona master data.</summary>
public static class PersonaQB
{
    public const string GET_PERSONA = @"
SELECT
    P.PERSONAID           AS PersonaId,
    P.PERSONACODE         AS PersonaCode,
    P.PERSONANAME         AS PersonaName,
    P.SEGMENTID           AS SegmentId,
    S.SEGMENTCODE         AS SegmentCode,
    S.SEGMENTNAME         AS SegmentName,
    P.ICPID               AS ICPId,
    I.ICPCODE             AS ICPCode,
    I.ICPNAME             AS ICPName,
    P.DEMOGRAPHICS        AS Demographics,
    P.ROLECONTEXT         AS RoleContext,
    P.DECISIONAUTHORITY   AS DecisionAuthority,
    P.PAINPOINTS          AS PainPoints,
    P.MOTIVATIONS         AS Motivations,

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

    P.SORTORDER           AS SortOrder,
    P.STATUS              AS Status,
    P.VERSION             AS Version,
    P.TENANTID            AS TenantId
FROM MPERSONA P
LEFT JOIN MSEGMENT S ON S.SEGMENTID = P.SEGMENTID
LEFT JOIN MICP I ON I.ICPID = P.ICPID
LEFT JOIN MUSER CB ON CB.USERID = P.CREATEDBYID
LEFT JOIN MUSER MB ON MB.USERID = P.MODIFIEDBYID
WHERE P.PERSONAID = @PersonaId;
";

    public const string SAVE_PERSONA = @"
INSERT INTO MPERSONA (
    PERSONAID, PERSONACODE, PERSONANAME, SEGMENTID, ICPID, DEMOGRAPHICS,
    ROLECONTEXT, DECISIONAUTHORITY, PAINPOINTS, MOTIVATIONS,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SORTORDER, STATUS, VERSION, TENANTID
)
VALUES (
    @PersonaId, @PersonaCode, @PersonaName, @SegmentId, @ICPId, @Demographics,
    @RoleContext, @DecisionAuthority, @PainPoints, @Motivations,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SortOrder, @Status, @Version, @TenantId
);
";

    public const string UPDATE_PERSONA = @"
UPDATE MPERSONA
SET
    PERSONACODE         = @PersonaCode,
    PERSONANAME         = @PersonaName,
    SEGMENTID           = @SegmentId,
    ICPID               = @ICPId,
    DEMOGRAPHICS        = @Demographics,
    ROLECONTEXT         = @RoleContext,
    DECISIONAUTHORITY   = @DecisionAuthority,
    PAINPOINTS          = @PainPoints,
    MOTIVATIONS         = @Motivations,
    MODIFIEDBYID        = @ModifiedById,
    MODIFIEDON          = @ModifiedOn,
    SORTORDER           = @SortOrder,
    STATUS              = @Status,
    VERSION             = @Version
WHERE PERSONAID = @PersonaId;
";

    public const string DELETE_PERSONA = @"
DELETE FROM MPERSONA
WHERE PERSONAID = @PersonaId;
";

    public const string GET_SELECTLIST_PERSONA = @"
SELECT
    PERSONAID    AS Id,
    PERSONACODE  AS Code,
    PERSONANAME  AS Name
FROM MPERSONA
WHERE STATUS = 1
ORDER BY PERSONANAME;
";
}