namespace FrameworkDAL.Query.GOP
{
    // ============================================================
    // GopTargetOperationQB — API registry for GOP ApiCall nodes
    //
    // Table (ALL UPPERCASE per CLAUDE-DB-SCHEMA.md):
    //   MGOPTARGETOPERATION — tenant-scoped registry of external API targets
    //
    // Security: TENANTID = @TenantId mandatory on all queries.
    // @TenantId is auto-populated by IQueryExecutor from LoginDTO.TenantId.
    //
    // Required indexes:
    //   UQ_MGOPTARGETOPERATION_CODE_TID  (TENANTID, OPERATIONCODE) — unique per tenant
    //   IX_MGOPTARGETOPERATION_TID_STATUS (TENANTID, STATUS) — list queries
    // ============================================================
    public static class GopTargetOperationQB
    {
        // ── Read ─────────────────────────────────────────────────

        // LEFT JOIN on the ENTITYCODE FK (MGOPTARGETOPERATION has no ENTITYID column — the
        // join key is ENTITYCODE, matched against MENTITY.ENTITYCODE, which is exactly how
        // EntityId/EntityName get resolved). ENTITYCODE is NULL for GOP Flow ApiCall rows
        // (every row that exists today), so an INNER JOIN would drop them from
        // GetGopTargetOperation entirely. EntityId/EntityName are simply NULL when there's
        // no entity-CRUD binding.
        public const string GET_BY_ID = @"
            SELECT  op.TENANTID             AS TenantId,
                    op.GOPTARGETOPERATIONID AS TargetOperationId,
                    op.OPERATIONCODE        AS OperationCode,
                    op.OPERATIONNAME        AS OperationName,
                    op.BASEURL              AS BaseUrl,
                    op.RELATIVEPATH         AS RelativePath,
                    op.HTTPMETHOD           AS HttpMethod,
                    op.REQUESTSCHEMAJSON    AS RequestSchemaJson,
                    op.RESPONSESCHEMAJSON   AS ResponseSchemaJson,
                    op.SPECHASH             AS SpecHash,
                    op.ENTITYCODE           AS EntityCode,
                    op.IDFIELDNAME          AS IdFieldName,
                    e.ENTITYID              AS EntityId,
                    e.ENTITYNAME            AS EntityName,
                    op.VERSION              AS Version,
                    op.STATUS               AS Status,
                    op.SORTORDER            AS SortOrder,
                    op.CREATEDBYID          AS CreatedById,
                    op.MODIFIEDBYID         AS ModifiedById
            FROM    MGOPTARGETOPERATION op
            LEFT JOIN MENTITY e
                    ON  e.ENTITYCODE      = op.ENTITYCODE
            WHERE   op.TENANTID             = @TenantId
              AND   op.GOPTARGETOPERATIONID = @TargetOperationId;";

        public const string 
            GET_LIST = @"
            SELECT  op.TENANTID             AS TenantId,
                    op.GOPTARGETOPERATIONID AS TargetOperationId,
                    op.OPERATIONCODE        AS OperationCode,
                    op.OPERATIONNAME        AS OperationName,
                    op.BASEURL              AS BaseUrl,
                    op.RELATIVEPATH         AS RelativePath,
                    op.HTTPMETHOD           AS HttpMethod,
                    op.ENTITYCODE           AS EntityCode,
                    op.IDFIELDNAME          AS IdFieldName,
                    op.VERSION              AS Version,
                    op.STATUS               AS Status,
                    op.SORTORDER            AS SortOrder
            FROM    MGOPTARGETOPERATION op
            WHERE   op.TENANTID = @TenantId
              AND   op.STATUS  <> 2
            ORDER BY op.SORTORDER, op.OPERATIONCODE;";

        // Dropdown population — id/code/name only, no schema/URL payload.
        public const string GET_SELECTLIST_TARGET_OPERATION = @"
            SELECT  op.GOPTARGETOPERATIONID AS TargetOperationId,
                    op.OPERATIONCODE        AS OperationCode,
                    op.OPERATIONNAME        AS OperationName
            FROM    MGOPTARGETOPERATION op
            WHERE   op.TENANTID = @TenantId
              AND   op.STATUS  <> 2
            ORDER BY op.SORTORDER, op.OPERATIONCODE;";

        // EntityViewer's real read pattern: every operation registered for one entity type.
        public const string GET_LIST_BY_ENTITY_CODE = @"
            SELECT  op.TENANTID             AS TenantId,
                    op.GOPTARGETOPERATIONID AS TargetOperationId,
                    op.OPERATIONCODE        AS OperationCode,
                    op.OPERATIONNAME        AS OperationName,
                    op.BASEURL              AS BaseUrl,
                    op.RELATIVEPATH         AS RelativePath,
                    op.HTTPMETHOD           AS HttpMethod,
                    op.ENTITYCODE           AS EntityCode,
                    op.IDFIELDNAME          AS IdFieldName,
                    op.VERSION              AS Version,
                    op.STATUS               AS Status,
                    op.SORTORDER            AS SortOrder
            FROM    MGOPTARGETOPERATION op
            WHERE   op.TENANTID   = @TenantId
              AND   op.ENTITYCODE = @EntityCode
              AND   op.STATUS    <> 2
            ORDER BY op.SORTORDER, op.OPERATIONCODE;";

        // ── Write ─────────────────────────────────────────────────

        // GOPTARGETOPERATIONID is a plain app-generated INT PK (AutoNumber), not IDENTITY — see
        // 20260723_GOP_IdentityColumn_Fix.sql.
        public const string SAVE = @"
            INSERT INTO MGOPTARGETOPERATION
                (GOPTARGETOPERATIONID, OPERATIONCODE, OPERATIONNAME,
                 BASEURL, RELATIVEPATH, HTTPMETHOD,
                 REQUESTSCHEMAJSON, RESPONSESCHEMAJSON, SPECHASH,
                 ENTITYCODE, IDFIELDNAME,
                 VERSION, STATUS, SORTORDER, SOURCETYPE,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID)
            VALUES
                (@TargetOperationId, @OperationCode, @OperationName,
                 @BaseUrl, @RelativePath, @HttpMethod,
                 @RequestSchemaJson, @ResponseSchemaJson, @SpecHash,
                 @EntityCode, @IdFieldName,
                 1, 1, @SortOrder, 5,
                 @CreatedById, GETDATE(), @ModifiedById, GETDATE(), @TenantId);";

        public const string UPDATE = @"
            UPDATE MGOPTARGETOPERATION
            SET    OPERATIONNAME       = @OperationName,
                   BASEURL             = @BaseUrl,
                   RELATIVEPATH        = @RelativePath,
                   HTTPMETHOD          = @HttpMethod,
                   REQUESTSCHEMAJSON   = @RequestSchemaJson,
                   RESPONSESCHEMAJSON  = @ResponseSchemaJson,
                   SPECHASH            = @SpecHash,
                   ENTITYCODE          = @EntityCode,
                   IDFIELDNAME         = @IdFieldName,
                   SORTORDER           = @SortOrder,
                   VERSION             = VERSION + 1,
                   MODIFIEDBYID        = @ModifiedById,
                   MODIFIEDON          = GETDATE()
            WHERE  GOPTARGETOPERATIONID = @TargetOperationId
              AND  TENANTID             = @TenantId;";

        public const string DELETE = @"
            UPDATE MGOPTARGETOPERATION
            SET    STATUS       = 2,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = GETDATE()
            WHERE  GOPTARGETOPERATIONID = @TargetOperationId
              AND  TENANTID             = @TenantId;";
    }
}