namespace DMSDAL.Query.ESSDocument
{
    public static class ESSDocumentQB
    {
        // ─────────────────────────────────────────────────────────────────
        // GET EMPLOYEE ID BY USER
        // ─────────────────────────────────────────────────────────────────
        // KEY FINDING:
        //   MUSER.ISEMPLOYEE = 1  → this user IS an employee
        //   MUSER.USERID          → equals TATTACHMENT.OBJECTID directly
        //   No separate mapping table needed
        // ─────────────────────────────────────────────────────────────────
        // NEW — checks MEMPLOYEE directly, no ISEMPLOYEE flag
        public const string GET_EMPLOYEE_ID_BY_USERID = @"
    SELECT TOP 1
        E.EMPLOYEEID    AS EmployeeId
    FROM MEMPLOYEE E
    WHERE E.EMPLOYEEID = @UserId
    AND   E.STATUS     = 1;";

        public const string GET_EMPLOYEE_ID_BY_USERCODE = @"
    SELECT TOP 1
        E.EMPLOYEEID    AS EmployeeId
    FROM MEMPLOYEE E
    WHERE E.EMPLOYEECODE = @UserCode
    AND   E.STATUS       = 1;";
        // ─────────────────────────────────────────────────────────────────
        // LIST MY DOCUMENTS
        // ─────────────────────────────────────────────────────────────────
        // FILTER PRIORITY (Senior's requirement):
        //   1st → OBJECTID      = @EmployeeId    (mandatory)
        //   2nd → OBJECTTYPEID  = @ObjectTypeId  (mandatory)
        //   3rd → CategoryCode                   (optional)
        //   4th → Year                           (optional)
        //   5th → Month — only when Year is set  (optional)
        //
        // STATUS = 0 → Active records only
        // ─────────────────────────────────────────────────────────────────
        public const string LIST_MY_DOCUMENTS = @"
            SELECT
                A.ATTACHMENTID          AS AttachmentId,
                A.OBJECTTYPEID          AS ObjectTypeId,
                A.OBJECTID              AS EmployeeId,
                A.DOCUMENTTYPEID        AS DocumentTypeId,
                DT.DOCUMENTTYPECODE     AS CategoryCode,
                DT.DOCUMENTTYPENAME     AS CategoryName,
                A.VALIDFROM             AS ValidFrom,
                A.VALIDTO               AS ValidTo,
                A.FOLDERNAME            AS PeriodLabel,
                A.DISPLAYFILENAME       AS DisplayFileName,
                A.ATTACHEDFILENAME      AS AttachedFileName,
                A.CMSID                 AS StoragePath,
                A.ATTACHMENTOPTION      AS AttachmentOption,
                A.STATUS                AS Status,
                A.CREATEDON             AS CreatedOn
            FROM  TATTACHMENT A
            JOIN  MDOCUMENTTYPE DT ON DT.DOCUMENTTYPEID = A.DOCUMENTTYPEID
            WHERE A.OBJECTID     = @EmployeeId
            AND   A.OBJECTTYPEID = @ObjectTypeId
            AND   A.STATUS       = 0
            AND   (
                      @CategoryCode IS NULL
                      OR DT.DOCUMENTTYPECODE = @CategoryCode
                  )
            AND   (
                      @Year IS NULL
                      OR YEAR(A.VALIDFROM) = @Year
                  )
            AND   (
                      @Month IS NULL
                      OR @Year  IS NULL
                      OR MONTH(A.VALIDFROM) = @Month
                  )
            ORDER BY A.CREATEDON DESC;";

        // ─────────────────────────────────────────────────────────────────
        // GET ATTACHMENT BY ID
        // ─────────────────────────────────────────────────────────────────
        public const string GET_BY_ID = @"
            SELECT
                A.ATTACHMENTID              AS AttachmentId,
                A.OBJECTTYPEID              AS ObjectTypeId,
                A.OBJECTID                  AS EmployeeId,
                A.DOCUMENTTYPEID            AS DocumentTypeId,
                A.VALIDFROM                 AS ValidFrom,
                A.VALIDTO                   AS ValidTo,
                A.FOLDERNAME                AS PeriodLabel,
                A.DISPLAYFILENAME           AS DisplayFileName,
                A.ATTACHEDFILENAME          AS AttachedFileName,
                A.CMSID                     AS StoragePath,
                A.MIMETYPE                  AS MimeType,
                ISNULL(A.FILESIZEBYTES, 0)  AS FileSizeBytes,
                A.ATTACHMENTOPTION          AS AttachmentOption,
                A.STATUS                    AS Status,
                A.CREATEDON                 AS CreatedOn
            FROM  TATTACHMENT A
            WHERE A.ATTACHMENTID = @AttachmentId;";

        // ─────────────────────────────────────────────────────────────────
        // INSERT ATTACHMENT
        // ─────────────────────────────────────────────────────────────────
        public const string INSERT_ATTACHMENT = @"
            INSERT INTO TATTACHMENT
            (
                OBJECTTYPEID, OBJECTID, BIZTRANSACTIONTYPEID, SLNO,
                DOCUMENTSETDETAILID, ATTACHMENTTYPE, DOCUMENTTYPEID,
                VALIDFROM, VALIDTO, CLASSIFICATIONMARKID,
                TAGS, CMSID, FOLDERNAME, LINKURL,
                DISPLAYFILENAME, ATTACHEDFILENAME, REMARKS,
                MIMETYPE, FILESIZEBYTES,
                STATUS, VERSION, CREATEDBYID, CREATEDON,
                MODIFIEDBYID, MODIFIEDON, THUMBNAIL, ATTACHMENTOPTION
            )
            VALUES
            (
                @ObjectTypeId, @EmployeeId, -1, 1,
                -1, 0, @DocumentTypeId,
                @ValidFrom, @ValidTo, -1,
                '', @StoragePath, @PeriodLabel, '',
                @DisplayFileName, @AttachedFileName, '',
                @MimeType, @FileSizeBytes,
                0, 1, @CreatedById, @CreatedOn,
                @ModifiedById, @ModifiedOn, NULL, @AttachmentOption
            );";

        // ─────────────────────────────────────────────────────────────────
        // GET ATTACHMENTID BY STORAGE PATH
        // ─────────────────────────────────────────────────────────────────
        public const string GET_ATTACHMENTID_BY_STORAGEPATH = @"
            SELECT ATTACHMENTID
            FROM   TATTACHMENT
            WHERE  CMSID = @StoragePath;";

        // ─────────────────────────────────────────────────────────────────
        // SOFT DELETE
        // ─────────────────────────────────────────────────────────────────
        public const string DEACTIVATE_ATTACHMENT = @"
            UPDATE TATTACHMENT
            SET    STATUS       = 1,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = @ModifiedOn
            WHERE  ATTACHMENTID = @AttachmentId;";
    }
}