namespace FrameworkDAL.Query.EventRule
{
    /// <summary>
    /// SQL constants for EventRule CRUD.
    /// Wraps MACTION + MEVENTTYPEACTION + MEVENTTYPEACTIONDETAIL in a single logical "rule".
    ///
    /// Save creates/updates all three rows atomically.
    /// Delete deactivates only (sets STATUS=0 on MACTION + ISACTIVE=1 on MEVENTTYPEACTIONDETAIL).
    ///
    /// Required indexes:
    ///   MACTION (ACTIONID, STATUS, TENANTID)
    ///   MEVENTTYPEACTION (EVENTTYPEID, STATUS, TENANTID)
    ///   MEVENTTYPEACTIONDETAIL (EVENTTYPEACTIONID, ISACTIVE)
    /// </summary>
    public static class EventRuleQB
    {
        public const string GET_BY_ACTION_ID = @"
SELECT
    A.ACTIONID              AS ActionId,
    A.ACTIONNAME            AS ActionName,
    A.ACTIONTYPE            AS ActionType,
    A.SENDTO                AS SendTo,
    A.TODELIVERYTYPE        AS ToDeliveryType,
    A.REPLYTO               AS ReplyTo,
    A.TEMPLATEID            AS TemplateId,
    A.MAILCC                AS MailCc,
    A.MAILCCDELIVERYTYPE    AS MailCcDeliveryType,
    A.MAILBCC               AS MailBcc,
    A.MAILBCCDELIVERYTYPE   AS MailBccDeliveryType,
    A.WEBSERVICEID          AS WebServiceId,
    A.URIPARAMETERVALUE     AS UriParameterValue,
    A.REPORTID              AS ReportId,
    A.STATUS                AS Status,
    A.CONDITIONEXPRESSION   AS ConditionExpression,
    A.CREATEDBYID           AS CreatedById,
    A.MODIFIEDBYID          AS ModifiedById,
    ETA.EVENTTYPEID         AS EventTypeId,
    ETAD.SLNO               AS SlNo,
    ET.EVENTTYPECODE        AS EventTypeCode,
    ET.EVENTTYPENAME        AS EventTypeName
FROM MACTION A
JOIN MEVENTTYPEACTIONDETAIL ETAD ON ETAD.ACTIONID          = A.ACTIONID AND ETAD.ISACTIVE = 0
JOIN MEVENTTYPEACTION       ETA  ON ETA.EVENTTYPEACTIONID  = ETAD.EVENTTYPEACTIONID
JOIN MEVENTTYPE             ET   ON ET.EVENTTYPEID         = ETA.EVENTTYPEID
WHERE A.ACTIONID   = @ActionId
  AND A.TENANTID   = @TenantId";

        public const string GET_LIST_BY_EVENT_TYPE = @"
SELECT
    A.ACTIONID              AS ActionId,
    A.ACTIONNAME            AS ActionName,
    A.ACTIONTYPE            AS ActionType,
    A.SENDTO                AS SendTo,
    A.TODELIVERYTYPE        AS ToDeliveryType,
    A.REPLYTO               AS ReplyTo,
    A.TEMPLATEID            AS TemplateId,
    A.MAILCC                AS MailCc,
    A.MAILCCDELIVERYTYPE    AS MailCcDeliveryType,
    A.MAILBCC               AS MailBcc,
    A.MAILBCCDELIVERYTYPE   AS MailBccDeliveryType,
    A.WEBSERVICEID          AS WebServiceId,
    A.URIPARAMETERVALUE     AS UriParameterValue,
    A.REPORTID              AS ReportId,
    A.STATUS                AS Status,
    A.CONDITIONEXPRESSION   AS ConditionExpression,
    ETA.EVENTTYPEID         AS EventTypeId,
    ETAD.SLNO               AS SlNo,
    ET.EVENTTYPECODE        AS EventTypeCode,
    ET.EVENTTYPENAME        AS EventTypeName
FROM MACTION A
JOIN MEVENTTYPEACTIONDETAIL ETAD ON ETAD.ACTIONID          = A.ACTIONID
JOIN MEVENTTYPEACTION       ETA  ON ETA.EVENTTYPEACTIONID  = ETAD.EVENTTYPEACTIONID
JOIN MEVENTTYPE             ET   ON ET.EVENTTYPEID         = ETA.EVENTTYPEID
WHERE ETA.EVENTTYPEID  = @EventTypeId
  AND A.TENANTID       = @TenantId
  AND A.STATUS         = 1
  AND ETAD.ISACTIVE    = 0
ORDER BY ETAD.SLNO";

        public const string GET_SELECT_LIST = @"
SELECT A.ACTIONID AS Id, A.ACTIONNAME AS Name
FROM MACTION A
WHERE A.TENANTID = @TenantId
  AND A.STATUS   = 1
ORDER BY A.ACTIONNAME";

        // ── Save (INSERT) ─────────────────────────────────────────────────────

        public const string INSERT_ACTION = @"
INSERT INTO MACTION (
    ACTIONID, ACTIONNAME, ACTIONTYPE, SENDTO, TODELIVERYTYPE, REPLYTO, TEMPLATEID,
    MAILCC, MAILCCDELIVERYTYPE, MAILBCC, MAILBCCDELIVERYTYPE,
    WEBSERVICEID, URIPARAMETERVALUE, REPORTID, STATUS,
    CONDITIONEXPRESSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID
) VALUES (
    @ActionId, @ActionName, @ActionType, @SendTo, @ToDeliveryType, @ReplyTo, @TemplateId,
    @MailCc, @MailCcDeliveryType, @MailBcc, @MailBccDeliveryType,
    @WebServiceId, @UriParameterValue, @ReportId, @Status,
    @ConditionExpression, @CreatedById, GETDATE(), @ModifiedById, GETDATE(), @TenantId
)";

        public const string INSERT_EVENT_TYPE_ACTION = @"
INSERT INTO MEVENTTYPEACTION (EVENTTYPEACTIONID, EVENTTYPEID, STATUS, TENANTID)
VALUES (@EventTypeActionId, @EventTypeId, 1, @TenantId)";

        public const string INSERT_EVENT_TYPE_ACTION_DETAIL = @"
INSERT INTO MEVENTTYPEACTIONDETAIL (EVENTTYPEACTIONDETAILID, EVENTTYPEACTIONID, ACTIONID, SLNO, ISACTIVE)
VALUES (@DetailId, @EventTypeActionId, @ActionId, @SlNo, 0)";

        // ── Update ────────────────────────────────────────────────────────────

        public const string UPDATE_ACTION = @"
UPDATE MACTION SET
    ACTIONNAME          = @ActionName,
    ACTIONTYPE          = @ActionType,
    SENDTO              = @SendTo,
    TODELIVERYTYPE      = @ToDeliveryType,
    REPLYTO             = @ReplyTo,
    TEMPLATEID          = @TemplateId,
    MAILCC              = @MailCc,
    MAILCCDELIVERYTYPE  = @MailCcDeliveryType,
    MAILBCC             = @MailBcc,
    MAILBCCDELIVERYTYPE = @MailBccDeliveryType,
    WEBSERVICEID        = @WebServiceId,
    URIPARAMETERVALUE   = @UriParameterValue,
    REPORTID            = @ReportId,
    STATUS              = @Status,
    CONDITIONEXPRESSION = @ConditionExpression,
    MODIFIEDBYID        = @ModifiedById,
    MODIFIEDON          = GETDATE()
WHERE ACTIONID  = @ActionId
  AND TENANTID  = @TenantId";

        public const string UPDATE_DETAIL_SLNO = @"
UPDATE MEVENTTYPEACTIONDETAIL SET SLNO = @SlNo
WHERE ACTIONID = @ActionId";

        // ── Delete (soft) ─────────────────────────────────────────────────────

        public const string DEACTIVATE_ACTION = @"
UPDATE MACTION SET STATUS = 0, MODIFIEDBYID = @ModifiedById, MODIFIEDON = GETDATE()
WHERE ACTIONID = @ActionId AND TENANTID = @TenantId";

        public const string DEACTIVATE_DETAIL = @"
UPDATE MEVENTTYPEACTIONDETAIL SET ISACTIVE = 1
WHERE ACTIONID = @ActionId";

        // ── Next ID helper ────────────────────────────────────────────────────
        // App-generated PK (same convention as TEVENTACTIONRUN)

        public const string NEXT_ACTION_ID = @"
SELECT ISNULL(MAX(ACTIONID), 0) + 1 FROM MACTION WHERE TENANTID = @TenantId";

        public const string NEXT_ETA_ID = @"
SELECT ISNULL(MAX(EVENTTYPEACTIONID), 0) + 1 FROM MEVENTTYPEACTION WHERE TENANTID = @TenantId";

        public const string NEXT_DETAIL_ID = @"
SELECT ISNULL(MAX(EVENTTYPEACTIONDETAILID), 0) + 1 FROM MEVENTTYPEACTIONDETAIL";
    }
}