namespace FrameworkDAL.Query.DirectAction.DirectActionDetailAdmin
{
    // INDEX required on MDIRECTACTIONDETAIL: covering index on (DIRECTACTIONID) already exists
    // (DirectActionConfigQB.GET_DETAILS_BY_DIRECTACTIONID), reused here.
    //
    // MDIRECTACTIONDETAIL has NO Version/Status/audit columns (confirmed against the live DDL,
    // unlike every other EIP admin master table) — Delete here is a real hard DELETE, and
    // list/get scope by tenant via a join to the parent MDIRECTACTION.
    public static class DirectActionDetailAdminQB
    {
        public const string GET_DETAIL_LIST = @"
            SELECT
                D.DIRECTACTIONDETAILID  AS DirectActionDetailId,
                D.DIRECTACTIONID        AS DirectActionId,
                D.SLNO                  AS Slno,
                D.ACTIONCODE            AS ActionCode,
                D.ACTIONLABEL           AS ActionLabel,
                D.EMAILPLACEHOLDER      AS EmailPlaceholder,
                D.BUTTONPAYLOADPREFIX   AS ButtonPayloadPrefix,
                D.APIENDPOINT           AS ApiEndpoint,
                D.HTTPMETHOD            AS HttpMethod,
                D.PAYLOADTEMPLATE       AS PayloadTemplate,
                D.NEEDSINPUT            AS NeedsInput,
                D.FLOWID                AS FlowId
            FROM MDIRECTACTIONDETAIL D
            INNER JOIN MDIRECTACTION H ON H.DIRECTACTIONID = D.DIRECTACTIONID
            WHERE H.TENANTID IN (@TenantId, -1)
              AND H.STATUS   = 1
              AND (@DirectActionId = 0 OR D.DIRECTACTIONID = @DirectActionId)
            ORDER BY D.DIRECTACTIONID, D.SLNO";

        public const string GET_DETAIL = @"
            SELECT
                D.DIRECTACTIONDETAILID  AS DirectActionDetailId,
                D.DIRECTACTIONID        AS DirectActionId,
                D.SLNO                  AS Slno,
                D.ACTIONCODE            AS ActionCode,
                D.ACTIONLABEL           AS ActionLabel,
                D.EMAILPLACEHOLDER      AS EmailPlaceholder,
                D.BUTTONPAYLOADPREFIX   AS ButtonPayloadPrefix,
                D.APIENDPOINT           AS ApiEndpoint,
                D.HTTPMETHOD            AS HttpMethod,
                D.PAYLOADTEMPLATE       AS PayloadTemplate,
                D.NEEDSINPUT            AS NeedsInput,
                D.FLOWID                AS FlowId
            FROM MDIRECTACTIONDETAIL D
            INNER JOIN MDIRECTACTION H ON H.DIRECTACTIONID = D.DIRECTACTIONID
            WHERE D.DIRECTACTIONDETAILID = @DirectActionDetailId
              AND H.TENANTID             IN (@TenantId, -1)
              AND H.STATUS               = 1";

        // DIRECTACTIONDETAILID is a plain INT PK (NOT IDENTITY)
        public const string INSERT_DETAIL = @"
            INSERT INTO MDIRECTACTIONDETAIL (
                DIRECTACTIONDETAILID,
                DIRECTACTIONID,
                SLNO,
                ACTIONCODE,
                ACTIONLABEL,
                EMAILPLACEHOLDER,
                BUTTONPAYLOADPREFIX,
                APIENDPOINT,
                HTTPMETHOD,
                PAYLOADTEMPLATE,
                NEEDSINPUT,
                FLOWID
            )
            VALUES (
                @DirectActionDetailId,
                @DirectActionId,
                @Slno,
                @ActionCode,
                @ActionLabel,
                @EmailPlaceholder,
                @ButtonPayloadPrefix,
                @ApiEndpoint,
                @HttpMethod,
                @PayloadTemplate,
                @NeedsInput,
                @FlowId
            )";

        public const string UPDATE_DETAIL = @"
            UPDATE MDIRECTACTIONDETAIL
            SET
                SLNO                 = @Slno,
                ACTIONCODE           = @ActionCode,
                ACTIONLABEL          = @ActionLabel,
                EMAILPLACEHOLDER     = @EmailPlaceholder,
                BUTTONPAYLOADPREFIX  = @ButtonPayloadPrefix,
                APIENDPOINT          = @ApiEndpoint,
                HTTPMETHOD           = @HttpMethod,
                PAYLOADTEMPLATE      = @PayloadTemplate,
                NEEDSINPUT           = @NeedsInput,
                FLOWID               = @FlowId
            WHERE DIRECTACTIONDETAILID = @DirectActionDetailId";

        // Real hard delete — no STATUS column to soft-delete against.
        public const string DELETE_DETAIL = @"
            DELETE FROM MDIRECTACTIONDETAIL
            WHERE DIRECTACTIONDETAILID = @DirectActionDetailId";

        // ACTIONCODE must be globally unique (used in token lookup) — UK_MDIRECTACTIONDETAIL_ACTIONCODE_PARENTID
        // is actually (ACTIONCODE, DIRECTACTIONID), but DirectActionConfigQB.GET_DETAIL_BY_ACTIONCODE
        // looks up by ACTIONCODE alone (token payload has no DirectActionId), so treat it as
        // effectively-global here too, matching that runtime lookup's own assumption.
        public const string EXISTS_ACTIONCODE = @"
            SELECT CASE WHEN EXISTS (
                SELECT 1 FROM MDIRECTACTIONDETAIL
                WHERE ACTIONCODE = @ActionCode
                  AND (@DirectActionDetailId = 0 OR DIRECTACTIONDETAILID <> @DirectActionDetailId)
            ) THEN 1 ELSE 0 END";
    }
}