namespace FrameworkDAL.Query.DirectAction.DirectActionAdmin
{
    // INDEX required on MDIRECTACTION:
    //   IX_MDIRECTACTION_TENANT_STATUS on (TENANTID, STATUS) INCLUDE (DIRECTACTIONCODE, DIRECTACTIONNAME)
    public static class DirectActionAdminQB
    {
        // =====================================================
        // List — DIRECTACTIONCODE is unique per-tenant (UK_MDIRECTACTION_CODE_TENANTID), and
        // TENANTID defaults to -1 (global); visibility mirrors the runtime lookup's own
        // TENANTID IN (@TenantId, -1) convention (DirectActionConfigQB.GET_HEADER_BY_ID).
        // =====================================================
        public const string GET_DIRECTACTION_LIST = @"
            SELECT
                A.DIRECTACTIONID          AS DirectActionId,
                A.TENANTID                AS TenantId,
                A.DIRECTACTIONCODE        AS DirectActionCode,
                A.DIRECTACTIONNAME        AS DirectActionName,
                A.CONTEXTIDFIELD          AS ContextIdField,
                A.ASSIGNEEUSERIDFIELD     AS AssigneeUserIdField,
                A.VERSION                 AS Version,
                A.STATUS                  AS Status,
                A.CREATEDBYID             AS CreatedById,
                CB.EMPLOYEENAME           AS CreatedByName,
                A.CREATEDON               AS CreatedOn,
                A.MODIFIEDBYID            AS ModifiedById,
                MB.EMPLOYEENAME           AS ModifiedByName,
                A.MODIFIEDON              AS ModifiedOn
            FROM MDIRECTACTION A
            LEFT JOIN MEMPLOYEE CB ON A.CREATEDBYID = CB.EMPLOYEEID
            LEFT JOIN MEMPLOYEE MB ON A.MODIFIEDBYID = MB.EMPLOYEEID
            WHERE A.TENANTID IN (@TenantId, -1)
              AND A.STATUS   = 1
              AND (
                    @Search IS NULL
                    OR A.DIRECTACTIONCODE LIKE '%' + @Search + '%'
                    OR A.DIRECTACTIONNAME LIKE '%' + @Search + '%'
                  )
            ORDER BY A.DIRECTACTIONCODE ASC";

        // =====================================================
        // Single record
        // =====================================================
        public const string GET_DIRECTACTION = @"
            SELECT
                A.DIRECTACTIONID          AS DirectActionId,
                A.TENANTID                AS TenantId,
                A.DIRECTACTIONCODE        AS DirectActionCode,
                A.DIRECTACTIONNAME        AS DirectActionName,
                A.CONTEXTIDFIELD          AS ContextIdField,
                A.ASSIGNEEUSERIDFIELD     AS AssigneeUserIdField,
                A.VERSION                 AS Version,
                A.STATUS                  AS Status,
                A.CREATEDBYID             AS CreatedById,
                CB.EMPLOYEENAME           AS CreatedByName,
                A.CREATEDON               AS CreatedOn,
                A.MODIFIEDBYID            AS ModifiedById,
                MB.EMPLOYEENAME           AS ModifiedByName,
                A.MODIFIEDON              AS ModifiedOn
            FROM MDIRECTACTION A
            LEFT JOIN MEMPLOYEE CB ON A.CREATEDBYID = CB.EMPLOYEEID
            LEFT JOIN MEMPLOYEE MB ON A.MODIFIEDBYID = MB.EMPLOYEEID
            WHERE A.DIRECTACTIONID = @DirectActionId
              AND A.TENANTID       IN (@TenantId, -1)
              AND A.STATUS         = 1";

        // =====================================================
        // Insert — DIRECTACTIONID is a plain INT PK (NOT IDENTITY)
        // =====================================================
        public const string INSERT_DIRECTACTION = @"
            INSERT INTO MDIRECTACTION (
                DIRECTACTIONID,
                TENANTID,
                DIRECTACTIONCODE,
                DIRECTACTIONNAME,
                CONTEXTIDFIELD,
                ASSIGNEEUSERIDFIELD,
                VERSION,
                STATUS,
                SORTORDER,
                CREATEDBYID,
                CREATEDON,
                MODIFIEDBYID,
                MODIFIEDON,
                SOURCETYPE
            )
            VALUES (
                @DirectActionId,
                @TenantId,
                @DirectActionCode,
                @DirectActionName,
                @ContextIdField,
                @AssigneeUserIdField,
                @Version,
                1,
                9999,
                @CreatedById,
                GETDATE(),
                @CreatedById,
                GETDATE(),
                1
            )";

        // =====================================================
        // Update
        // =====================================================
        public const string UPDATE_DIRECTACTION = @"
            UPDATE MDIRECTACTION
            SET
                DIRECTACTIONCODE     = @DirectActionCode,
                DIRECTACTIONNAME     = @DirectActionName,
                CONTEXTIDFIELD       = @ContextIdField,
                ASSIGNEEUSERIDFIELD  = @AssigneeUserIdField,
                VERSION              = @Version,
                MODIFIEDBYID         = @ModifiedById,
                MODIFIEDON           = GETDATE()
            WHERE DIRECTACTIONID = @DirectActionId
              AND TENANTID       IN (@TenantId, -1)
              AND STATUS         = 1";

        // =====================================================
        // Soft-delete
        // =====================================================
        public const string DELETE_DIRECTACTION = @"
            UPDATE MDIRECTACTION
            SET
                STATUS       = 0,
                MODIFIEDBYID = @ModifiedById,
                MODIFIEDON   = GETDATE()
            WHERE DIRECTACTIONID = @DirectActionId
              AND TENANTID       IN (@TenantId, -1)
              AND STATUS         = 1";

        // =====================================================
        // Duplicate-check before insert — DIRECTACTIONCODE unique per (CODE, TENANTID)
        // =====================================================
        public const string EXISTS_DIRECTACTIONCODE = @"
            SELECT CASE WHEN EXISTS (
                SELECT 1
                FROM   MDIRECTACTION
                WHERE  DIRECTACTIONCODE = @DirectActionCode
                  AND  TENANTID         = @TenantId
                  AND  STATUS           = 1
                  AND  (@DirectActionId = 0 OR DIRECTACTIONID <> @DirectActionId)
            ) THEN 1 ELSE 0 END";

        // =====================================================
        // FK existence check — used by DirectActionDetailAdmin's save to validate DirectActionId
        // =====================================================
        public const string EXISTS_DIRECTACTIONID = @"
            SELECT CASE WHEN EXISTS (
                SELECT 1 FROM MDIRECTACTION
                WHERE DIRECTACTIONID = @DirectActionId
                  AND TENANTID       IN (@TenantId, -1)
                  AND STATUS         = 1
            ) THEN 1 ELSE 0 END";
    }
}