using System;

namespace RecruitmentDAL.Query.JobProfileLibrary
{
    // SQL for MJOBPROFILELIBRARY (industry/role JD template reference library). Deliberately
    // separate from MJOBPROFILE (a real employee-position record). See
    // DB/Migrations/20260901_Recruitment_Phase1_CoreATS_Schema_SqlServer.sql section
    // "7. MJOBPROFILELIBRARY". Covering indexes: PK_MJOBPROFILELIBRARY (JOBPROFILELIBRARYID),
    // IX_MJOBPROFILELIBRARY_TENANTID (TENANTID).
    public static class JobProfileLibraryQB
    {
        #region GET

        public const string GET_JOBPROFILELIBRARY = @"
SELECT
    JL.JOBPROFILELIBRARYID   AS JobProfileLibraryId,
    JL.ROLETITLE             AS RoleTitle,
    JL.INDUSTRYTAG           AS IndustryTag,
    JL.LEVELTAG              AS LevelTag,

    JL.JOBDESCRIPTION        AS JobDescription,
    JL.REQUIREDSKILLS        AS RequiredSkills,
    JL.RESPONSIBILITIES      AS Responsibilities,
    JL.QUALIFICATIONS        AS Qualifications,

    JL.CREATEDBYID           AS CreatedById,
    JL.CREATEDON             AS CreatedOn,
    CU.USERNAME              AS CreatedByName,

    JL.MODIFIEDBYID          AS ModifiedById,
    JL.MODIFIEDON            AS ModifiedOn,
    MU.USERNAME              AS ModifiedByName,

    JL.SORTORDER             AS SortOrder,
    JL.STATUS                AS Status,
    JL.VERSION               AS Version,
    JL.TENANTID              AS TenantId
FROM MJOBPROFILELIBRARY JL
LEFT JOIN MUSER CU ON CU.USERID = JL.CREATEDBYID
LEFT JOIN MUSER MU ON MU.USERID = JL.MODIFIEDBYID
WHERE JL.JOBPROFILELIBRARYID = @JobProfileLibraryId
  AND JL.TENANTID = @TenantId;
";

        #endregion


        #region SAVE

        // JOBPROFILELIBRARYID is application-generated via AutoNumber (entity code
        // "JOBPROFILELIBRARY") — unlike ProfileGenerator's IDENTITY column, the id is already
        // populated on the DTO before this INSERT runs, so it is listed explicitly here and the
        // call site uses ExecuteAsync (rows-affected), not ExecuteIdentityAsync.
        public const string SAVE_JOBPROFILELIBRARY = @"
INSERT INTO MJOBPROFILELIBRARY
(
    JOBPROFILELIBRARYID,
    ROLETITLE,
    INDUSTRYTAG,
    LEVELTAG,

    JOBDESCRIPTION,
    REQUIREDSKILLS,
    RESPONSIBILITIES,
    QUALIFICATIONS,

    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,

    SORTORDER,
    STATUS,
    VERSION,
    TENANTID
)
VALUES
(
    @JobProfileLibraryId,
    @RoleTitle,
    @IndustryTag,
    @LevelTag,

    @JobDescription,
    @RequiredSkills,
    @Responsibilities,
    @Qualifications,

    @CreatedById,
    @CreatedOn,
    @ModifiedById,
    @ModifiedOn,

    @SortOrder,
    @Status,
    @Version,
    @TenantId
);
";

        #endregion


        #region UPDATE

        public const string UPDATE_JOBPROFILELIBRARY = @"
UPDATE MJOBPROFILELIBRARY SET
    ROLETITLE        = @RoleTitle,
    INDUSTRYTAG      = @IndustryTag,
    LEVELTAG         = @LevelTag,

    JOBDESCRIPTION   = @JobDescription,
    REQUIREDSKILLS   = @RequiredSkills,
    RESPONSIBILITIES = @Responsibilities,
    QUALIFICATIONS   = @Qualifications,

    MODIFIEDBYID     = @ModifiedById,
    MODIFIEDON       = @ModifiedOn,

    SORTORDER        = @SortOrder,
    STATUS           = @Status,
    VERSION          = @Version
WHERE JOBPROFILELIBRARYID = @JobProfileLibraryId
  AND TENANTID            = @TenantId;
";

        #endregion


        #region DELETE

        // Soft delete — STATUS 2 = Deleted (repo-standard: 0=Pending 1=Active 2=Deleted
        // 3=Amended 4=Inactive 5=Archived). This is reference/template content, not a
        // transactional record, so rows are marked deleted rather than physically removed.
        public const string DELETE_JOBPROFILELIBRARY = @"
UPDATE MJOBPROFILELIBRARY
SET STATUS       = 2,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = @ModifiedOn
WHERE JOBPROFILELIBRARYID = @JobProfileLibraryId
  AND TENANTID             = @TenantId;
";

        #endregion


        #region SELECT LIST

        // Browse-screen list — filters by STATUS=1 (Active) and TENANTID, with an optional
        // SearchKeyword matched against ROLETITLE and INDUSTRYTAG (repo-standard prefix-LIKE
        // idiom — see AccountQB.GET_ACCOUNT_PICKLIST_FOR_POPUP).
        public const string GET_SELECTLIST_JOBPROFILELIBRARY = @"
SELECT
    JL.JOBPROFILELIBRARYID   AS JobProfileLibraryId,
    JL.ROLETITLE             AS RoleTitle,
    JL.INDUSTRYTAG           AS IndustryTag,
    JL.LEVELTAG              AS LevelTag,

    JL.JOBDESCRIPTION        AS JobDescription,
    JL.REQUIREDSKILLS        AS RequiredSkills,
    JL.RESPONSIBILITIES      AS Responsibilities,
    JL.QUALIFICATIONS        AS Qualifications,

    JL.CREATEDBYID           AS CreatedById,
    JL.CREATEDON             AS CreatedOn,
    CU.USERNAME              AS CreatedByName,

    JL.MODIFIEDBYID          AS ModifiedById,
    JL.MODIFIEDON            AS ModifiedOn,
    MU.USERNAME              AS ModifiedByName,

    JL.SORTORDER             AS SortOrder,
    JL.STATUS                AS Status,
    JL.VERSION               AS Version,
    JL.TENANTID              AS TenantId
FROM MJOBPROFILELIBRARY JL
LEFT JOIN MUSER CU ON CU.USERID = JL.CREATEDBYID
LEFT JOIN MUSER MU ON MU.USERID = JL.MODIFIEDBYID
WHERE JL.STATUS = 1
  AND JL.TENANTID = @TenantId
  AND (@SearchKeyword IS NULL OR @SearchKeyword = ''
       OR JL.ROLETITLE   LIKE @SearchKeyword + '%'
       OR JL.INDUSTRYTAG LIKE @SearchKeyword + '%')
ORDER BY JL.SORTORDER, JL.ROLETITLE;
";

        #endregion
    }
}