namespace FrameworkDAL.Query.SysJobShare
{
    public static class SysJobShareQB
    {
        /// <summary>
        /// Single share record with user name JOINs.
        /// </summary>
        public const string GET_SYSJOBSHARE = @"
SELECT
    SH.SYSJOBSHAREID     AS SysJobShareId,
    SH.SYSJOBID          AS SysJobId,
    SH.SHAREDWITHUSERID  AS SharedWithUserId,
    UW.USERNAME          AS SharedWithUserName,
    SH.CANEXECUTE        AS CanExecute,
    SH.CANVIEWRESULT     AS CanViewResult,
    SH.SHAREDBYID        AS SharedById,
    UB.USERNAME          AS SharedByName,
    SH.SHAREDON          AS SharedOn,
    SH.TENANTID          AS TenantId
FROM TSYSJOBSHARE SH
LEFT JOIN MUSER UW ON UW.USERID = SH.SHAREDWITHUSERID
LEFT JOIN MUSER UB ON UB.USERID = SH.SHAREDBYID
WHERE SH.SYSJOBSHAREID = @SysJobShareId;";

        /// <summary>
        /// All share records for a given job (who has access).
        /// Required index: IX_TSYSJOBSHARE_SysJobId
        /// </summary>
        public const string GET_LIST_SYSJOBSHARE = @"
SELECT
    SH.SYSJOBSHAREID     AS SysJobShareId,
    SH.SYSJOBID          AS SysJobId,
    SH.SHAREDWITHUSERID  AS SharedWithUserId,
    UW.USERNAME          AS SharedWithUserName,
    SH.CANEXECUTE        AS CanExecute,
    SH.CANVIEWRESULT     AS CanViewResult,
    SH.SHAREDBYID        AS SharedById,
    UB.USERNAME          AS SharedByName,
    SH.SHAREDON          AS SharedOn,
    SH.TENANTID          AS TenantId
FROM TSYSJOBSHARE SH
LEFT JOIN MUSER UW ON UW.USERID = SH.SHAREDWITHUSERID
LEFT JOIN MUSER UB ON UB.USERID = SH.SHAREDBYID
WHERE SH.SYSJOBID  = @SysJobId
ORDER BY SH.SHAREDON DESC;";

        public const string SAVE_SYSJOBSHARE = @"
INSERT INTO TSYSJOBSHARE (
    SYSJOBSHAREID,
    SYSJOBID,
    SHAREDWITHUSERID,
    CANEXECUTE,
    CANVIEWRESULT,
    SHAREDBYID,
    SHAREDON,
    TENANTID
)
SELECT
    @SysJobShareId,
    @SysJobId,
    @SharedWithUserId,
    @CanExecute,
    @CanViewResult,
    @SharedById,
    @SharedOn,
    @TenantId
WHERE EXISTS (
    SELECT 1 FROM TSYSJOB
    WHERE SYSJOBID      = @SysJobId
    AND   SUBMITTEDBYID = @SharedById
    AND   TENANTID      = @TenantId
)
AND NOT EXISTS (
    SELECT 1 FROM TSYSJOBSHARE
    WHERE SYSJOBID         = @SysJobId
    AND   SHAREDWITHUSERID = @SharedWithUserId
    AND   TENANTID         = @TenantId
);";

        public const string UPDATE_SYSJOBSHARE = @"UPDATE TSYSJOBSHARE SET
                                                        CANEXECUTE    = @CanExecute,
                                                        CANVIEWRESULT = @CanViewResult
                                                    WHERE SYSJOBSHAREID = @SysJobShareId
                                                    AND   TENANTID      = @TenantId
                                                    AND   SHAREDBYID    = @SharedById;";

        public const string DELETE_SYSJOBSHARE = @"DELETE FROM TSYSJOBSHARE
                                                    WHERE SYSJOBSHAREID = @SysJobShareId
                                                    AND   TENANTID      = @TenantId
                                                    AND   SHAREDBYID    = @SharedById;";

        /// <summary>
        /// Check if a specific user has CanViewResult access to a job.
        /// Returns 1 if owner or has share, 0 otherwise.
        /// </summary>
        public const string CHECK_RESULT_ACCESS = @"
SELECT COUNT(1)
FROM TSYSJOB S
WHERE S.SYSJOBID  = @SysJobId
  AND (
        S.SUBMITTEDBYID = @UserId
        OR EXISTS (
            SELECT 1 FROM TSYSJOBSHARE SH
            WHERE SH.SYSJOBID         = S.SYSJOBID
              AND SH.SHAREDWITHUSERID = @UserId
              AND SH.CANVIEWRESULT    = 1
        )
      );";
    }
}