File size: 24,521 Bytes
e6be672
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
# Copyright (C) 2026 Mattia Zatelli
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU Affero General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU Affero General Public License for more details.
#
# You should have received a copy of the GNU Affero General Public License
# along with this program. If not, see <https://www.gnu.org/licenses/>.

"""
Query model for storing natural language queries and results

This model manages the entire lifecycle of BIM queries:
- Storage of original and processed queries
- NLP/LLM processing metadata
- Vector embeddings for semantic search
- Complex multi-intent query support
- Result caching for performance
- Analytics and feedback for ML
- Billing system integration

Supports both simple and complex queries with automatic decomposition
and dependency management between sub-queries.
"""
import enum
from typing import Dict, Any, List, Optional
from sqlalchemy import Column, Integer, String, Text, JSON, Float, Boolean, ForeignKey, DateTime
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from pgvector.sqlalchemy import Vector

from app.core.database import Base


class QueryType(str, enum.Enum):
    """Query types supported by the system

    - NATURAL_LANGUAGE: Natural language queries (Italian/English)
    - STRUCTURED: Structured programmatic queries
    - BATCH: Batch processing of multiple queries
    - CALCULATE: Specific calculations (area, volume, counts)
    - EXPORT: Data export requests
    """
    NATURAL_LANGUAGE = "nl"
    STRUCTURED = "structured"
    BATCH = "batch"
    CALCULATE = "calculate"
    EXPORT = "export"


class QueryStatus(str, enum.Enum):
    """Query processing states

    - PENDING: Query received, waiting for processing
    - PROCESSING: Processing in progress (NLP, database, calculations)
    - COMPLETED: Processing completed successfully
    - FAILED: Processing failed (parsing error, timeout, etc.)
    - CACHED: Result retrieved from cache (no processing)
    """
    PENDING = "pending"
    PROCESSING = "processing"
    COMPLETED = "completed"
    FAILED = "failed"
    CACHED = "cached"


class Query(Base):
    """
    Query model for storing NL queries and results

    **Identification and Security:**
    - Link to project with CASCADE foreign key
    - Mandatory tenant_id isolation
    - Query hash for deduplication and cache

    **NLP/LLM Processing:**
    - Original and processed query
    - Vector embeddings for semantic search (1536 dim)
    - LLM metadata (model, tokens, reasoning)
    - Classification time and performance

    **Multi-Intent Support:**
    - Support for decomposed complex queries
    - Sub-queries with relationships and dependencies
    - Primary intent for backward compatibility

    **Results and Cache:**
    - Structured JSON result data
    - Human-readable explanations generated by LLM
    - Provenance for traceability
    - Cache with confidence score

    **Analytics and ML:**
    - User feedback for training
    - Performance metrics for optimization
    - Query patterns for insights

    Used for:
    - Semantic search for similar queries
    - Intelligent result caching
    - Usage pattern analytics
    - Custom NLP model training
    - Billing integration
    """
    __tablename__ = "queries"

    # === PRIMARY IDENTIFICATION ===
    id = Column(Integer, primary_key=True, index=True)

    # === RELATIONSHIPS AND SECURITY ===
    project_id = Column(Integer, ForeignKey("projects.id", ondelete="CASCADE"), nullable=False, index=True)
    tenant_id = Column(String(50), nullable=False, index=True)     # CRITICAL: tenant isolation
    api_key_hash = Column(String(100), nullable=True, index=True)  # For billing and analytics

    # === QUERY INFORMATION ===
    query_type = Column(String(20), nullable=False, default=QueryType.NATURAL_LANGUAGE.value)
    original_query = Column(Text, nullable=False)         # Original user query
    processed_query = Column(Text, nullable=True)        # Cleaned/normalized query
    query_hash = Column(String(64), nullable=False, index=True)  # SHA256 for cache/deduplication

    # Plain text prompt without metadata
    plain_text_prompt = Column(Text, nullable=True)

    # Vector embedding for semantic search (OpenAI ada-002: 1536 dimensions)
    query_embedding = Column(Vector(1536), nullable=True)

    # === PROCESSING STATE ===
    status = Column(String(20), nullable=False, default=QueryStatus.PENDING.value)
    processing_time_ms = Column(Integer, nullable=True)   # Total processing time
    error_message = Column(Text, nullable=True)          # Human-readable error message

    # === MULTI-INTENT SUPPORT ===
    # For complex queries that require multiple coordinated processing steps
    is_multi_intent = Column(Boolean, nullable=False, default=False)
    sub_queries = Column(JSON, nullable=True)            # Sub-query plan array
    intent_relationships = Column(JSON, nullable=True)   # Relationships between sub-queries

    # Primary intent (for backward compatibility with existing system)
    primary_intent = Column(String(50), nullable=True, index=True)
    primary_subintent = Column(String(100), nullable=True, index=True)

    # === LLM PROCESSING METADATA ===
    # Hint for result presentation format
    presentation_hint = Column(String(20), nullable=False, default="text")
    
    # LLM model information used
    llm_model_used = Column(String(50), nullable=True, index=True)    # e.g. "gpt-4o-mini"
    tokens_used = Column(Integer, nullable=True)                     # Tokens consumed
    classification_time_ms = Column(Integer, nullable=True)          # Classification time only
    classification_reasoning = Column(Text, nullable=True)           # LLM reasoning

    # Legacy query structure (kept for backwards compatibility)
    intent = Column(String(50), nullable=True)  # count, calculate, list, find, etc.
    entities = Column(JSON, nullable=True)  # Extracted entities
    filters = Column(JSON, nullable=True)  # Applied filters
    metrics = Column(JSON, nullable=True)  # Requested metrics
    aggregations = Column(JSON, nullable=True)  # Aggregation functions

    # Generated SQL/operations
    generated_sql = Column(Text, nullable=True)
    executed_operations = Column(JSON, nullable=True)  # List of operations performed

    # Results
    result_data = Column(JSON, nullable=True)  # Raw result data
    result_summary = Column(Text, nullable=True)  # Human-readable summary
    result_explanation = Column(Text, nullable=True)  # AI-generated explanation
    result_count = Column(Integer, nullable=True)  # Number of elements/results
    result_units = Column(String(20), nullable=True)  # Units (sqm, m3, count, etc.)

    # Enhanced result metadata
    result_provenance = Column(JSON, nullable=True)  # Element IDs that contributed to result
    data_quality_notes = Column(JSON, nullable=True)  # Data quality observations
    suggested_actions = Column(JSON, nullable=True)  # Suggested follow-up actions
    related_queries = Column(JSON, nullable=True)  # Suggested related queries

    # Processing metadata
    elements_processed = Column(Integer, nullable=True)  # Number of elements analyzed
    total_processing_time_ms = Column(Integer, nullable=True)  # Total time including sub-queries

    # Confidence and quality metrics
    confidence_score = Column(Float, nullable=True)  # 0.0 - 1.0
    accuracy_score = Column(Float, nullable=True)  # If validated
    complexity_score = Column(Float, nullable=True)  # Query complexity

    # Context and options
    query_options = Column(JSON, nullable=True)  # Query-specific options
    context_data = Column(JSON, nullable=True)  # Additional context used

    # Caching and performance
    cache_key = Column(String(100), nullable=True, index=True)
    cache_hit = Column(Boolean, default=False)
    similar_query_id = Column(Integer, ForeignKey("queries.id"), nullable=True)

    # Export and sharing
    is_exportable = Column(Boolean, default=True)
    export_formats = Column(JSON, nullable=True)  # Available export formats
    shared = Column(Boolean, default=False)

    # Feedback and learning
    user_feedback = Column(Integer, nullable=True)  # 1-5 rating
    feedback_comments = Column(Text, nullable=True)
    marked_as_favorite = Column(Boolean, default=False)

    # Usage tracking
    view_count = Column(Integer, default=0)
    last_accessed = Column(DateTime(timezone=True), nullable=True)

    # Timestamps
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())
    completed_at = Column(DateTime(timezone=True), nullable=True)

    # Relationships
    project = relationship("Project", back_populates="queries")
    similar_query = relationship("Query", remote_side=[id])
    usage_events = relationship("UsageEvent", back_populates="query")
    results = relationship("QueryResult", back_populates="query", cascade="all, delete-orphan")
    sub_query_results = relationship("SubQueryResult", back_populates="query", cascade="all, delete-orphan")

    def __repr__(self):
        return f"<Query(id={self.id}, type='{self.query_type}', status='{self.status}')>"

    def to_dict(self) -> Dict[str, Any]:
        """Convert to dictionary for API responses"""
        return {
            "id": f"qry_{self.id}",
            "project_id": f"prj_{self.project_id}",
            "query_type": self.query_type,
            "original_query": self.original_query,
            "status": self.status,
            "processing_time_ms": self.processing_time_ms,
            "intent": self.intent,
            "entities": self.entities or [],
            "filters": self.filters or {},
            "metrics": self.metrics or [],
            "result_summary": self.result_summary,
            "result_explanation": self.result_explanation,
            "result_count": self.result_count,
            "result_units": self.result_units,
            "confidence_score": self.confidence_score,
            "is_exportable": self.is_exportable,
            "export_formats": self.export_formats or [],
            "user_feedback": self.user_feedback,
            "marked_as_favorite": self.marked_as_favorite,
            "created_at": self.created_at.isoformat() if self.created_at else None,
            "completed_at": self.completed_at.isoformat() if self.completed_at else None,
        }

    def get_full_result(self) -> Dict[str, Any]:
        """Get complete result including data for API responses"""
        base_dict = self.to_dict()
        base_dict.update({
            "result_data": self.result_data,
            "filters": self.filters,
            "aggregations": self.aggregations,
            "context_data": self.context_data,
            "query_options": self.query_options,
        })
        return base_dict

    @classmethod
    def get_public_id(cls, internal_id: int) -> str:
        """Convert internal ID to public format"""
        return f"qry_{internal_id}"

    @classmethod
    def parse_public_id(cls, public_id: str) -> Optional[int]:
        """Parse public ID to internal ID"""
        if not public_id.startswith("qry_"):
            return None
        try:
            return int(public_id[4:])
        except ValueError:
            return None

    def mark_completed(self, processing_time_ms: int = None):
        """Mark query as completed"""
        self.status = QueryStatus.COMPLETED.value
        self.completed_at = func.now()
        if processing_time_ms:
            self.processing_time_ms = processing_time_ms

    def mark_failed(self, error_message: str):
        """Mark query as failed"""
        self.status = QueryStatus.FAILED.value
        self.error_message = error_message
        self.completed_at = func.now()

    def mark_cached(self, similar_query_id: int = None):
        """Mark query as served from cache"""
        self.status = QueryStatus.CACHED.value
        self.cache_hit = True
        if similar_query_id:
            self.similar_query_id = similar_query_id
        self.completed_at = func.now()

    def increment_view_count(self):
        """Increment view count and update last accessed"""
        self.view_count += 1
        self.last_accessed = func.now()

    def set_user_feedback(self, rating: int, comments: str = None):
        """Set user feedback for query"""
        if 1 <= rating <= 5:
            self.user_feedback = rating
            self.feedback_comments = comments

    def calculate_complexity_score(self) -> float:
        """Calculate query complexity score based on various factors"""
        score = 0.0

        # Base complexity from query length
        query_length = len(self.original_query.split()) if self.original_query else 0
        score += min(query_length / 20, 0.3)  # Max 0.3 for length

        # Entity complexity
        if self.entities:
            entity_count = len(self.entities)
            score += min(entity_count / 10, 0.2)  # Max 0.2 for entities

        # Filter complexity
        if self.filters:
            filter_count = len(self.filters)
            score += min(filter_count / 5, 0.2)  # Max 0.2 for filters

        # Aggregation complexity
        if self.aggregations:
            agg_count = len(self.aggregations)
            score += min(agg_count / 3, 0.2)  # Max 0.2 for aggregations

        # Processing time factor
        if self.processing_time_ms:
            time_factor = min(self.processing_time_ms / 10000, 0.1)  # Max 0.1 for time
            score += time_factor

        return min(score, 1.0)

    def get_similar_queries(self, limit: int = 5) -> List[Dict]:
        """Get similar queries based on embedding similarity"""
        # This would use vector similarity search
        # Placeholder for now
        return []

    def is_successful(self) -> bool:
        """Check if query was successful"""
        return self.status in [QueryStatus.COMPLETED.value, QueryStatus.CACHED.value]

    def has_results(self) -> bool:
        """Check if query has results"""
        return bool(self.result_data) or bool(self.result_summary)

    def get_metrics_summary(self) -> Dict[str, Any]:
        """Get summary of query metrics for analytics"""
        return {
            "query_id": f"qry_{self.id}",
            "query_type": self.query_type,
            "status": self.status,
            "processing_time_ms": self.processing_time_ms,
            "confidence_score": self.confidence_score,
            "complexity_score": self.complexity_score,
            "result_count": self.result_count,
            "cache_hit": self.cache_hit,
            "user_feedback": self.user_feedback,
            "view_count": self.view_count,
            "created_at": self.created_at.isoformat() if self.created_at else None,
        }

    # Multi-intent helper methods
    def set_multi_intent_data(self, query_plan, classification_result):
        """Set multi-intent data from query plan and classification result"""
        self.is_multi_intent = query_plan.is_multi_intent
        self.sub_queries = [sq.dict() for sq in query_plan.sub_queries] if query_plan.sub_queries else []
        self.intent_relationships = query_plan.intent_relationships
        self.primary_intent = query_plan.primary_intent.value if query_plan.primary_intent else None

        # Set primary subintent from first sub-query
        if query_plan.sub_queries:
            self.primary_subintent = query_plan.sub_queries[0].subtype

        self.presentation_hint = query_plan.presentation_hint.value if query_plan.presentation_hint else "text"

        # Set classification metadata
        if classification_result:
            self.llm_model_used = classification_result.llm_model
            self.tokens_used = classification_result.tokens_used
            self.classification_time_ms = classification_result.classification_time_ms
            self.classification_reasoning = classification_result.reasoning

    def get_sub_query_count(self) -> int:
        """Get number of sub-queries"""
        return len(self.sub_queries) if self.sub_queries else 0

    def get_all_intents(self) -> List[str]:
        """Get list of all intents in this query"""
        if not self.sub_queries:
            return []
        return [sq.get("intent") for sq in self.sub_queries if sq.get("intent")]

    def get_all_subintents(self) -> List[str]:
        """Get list of all sub-intents in this query"""
        if not self.sub_queries:
            return []
        return [sq.get("subtype") for sq in self.sub_queries if sq.get("subtype")]

    def has_intent(self, intent: str) -> bool:
        """Check if query contains a specific intent"""
        return intent in self.get_all_intents()

    def has_subintent(self, subintent: str) -> bool:
        """Check if query contains a specific sub-intent"""
        return subintent in self.get_all_subintents()

    def get_parallel_sub_queries(self) -> List[Dict]:
        """Get sub-queries that can be executed in parallel"""
        if not self.is_multi_intent or "parallel" not in (self.intent_relationships or []):
            return []
        return self.sub_queries or []

    def get_sequential_sub_queries(self) -> List[Dict]:
        """Get sub-queries that must be executed sequentially"""
        if not self.is_multi_intent or "sequential" not in (self.intent_relationships or []):
            return []
        return self.sub_queries or []

    def update_with_enhanced_result(self, enhanced_result):
        """Update query with enhanced result data"""
        from app.schemas.intent import EnhancedQueryResult

        if isinstance(enhanced_result, EnhancedQueryResult):
            self.result_summary = enhanced_result.combined_result
            self.result_explanation = enhanced_result.overall_explanation
            self.confidence_score = enhanced_result.overall_confidence
            self.result_provenance = enhanced_result.all_provenance
            self.data_quality_notes = enhanced_result.data_quality_notes
            self.suggested_actions = enhanced_result.next_steps
            self.related_queries = enhanced_result.related_queries
            self.elements_processed = enhanced_result.total_elements_processed
            self.total_processing_time_ms = enhanced_result.total_processing_time_ms
            self.cache_hit = enhanced_result.cache_hit

            # Set result count from primary result
            if enhanced_result.sub_results:
                self.result_count = enhanced_result.primary_result.elements_processed

    def to_dict_enhanced(self) -> Dict[str, Any]:
        """Convert to dictionary with enhanced multi-intent fields"""
        base_dict = self.to_dict()
        base_dict.update({
            "is_multi_intent": self.is_multi_intent,
            "sub_queries": self.sub_queries,
            "intent_relationships": self.intent_relationships,
            "primary_intent": self.primary_intent,
            "primary_subintent": self.primary_subintent,
            "presentation_hint": self.presentation_hint,
            "llm_model_used": self.llm_model_used,
            "tokens_used": self.tokens_used,
            "classification_time_ms": self.classification_time_ms,
            "classification_reasoning": self.classification_reasoning,
            "result_provenance": self.result_provenance,
            "data_quality_notes": self.data_quality_notes,
            "suggested_actions": self.suggested_actions,
            "related_queries": self.related_queries,
            "elements_processed": self.elements_processed,
            "total_processing_time_ms": self.total_processing_time_ms,
            "plain_text_prompt": self.plain_text_prompt,
        })
        return base_dict


class SubQueryResult(Base):
    """
    Individual sub-query results for multi-intent queries
    """
    __tablename__ = "sub_query_results"

    id = Column(Integer, primary_key=True, index=True)
    query_id = Column(Integer, ForeignKey("queries.id", ondelete="CASCADE"), nullable=False, index=True)
    sub_query_index = Column(Integer, nullable=False)

    # Sub-query identification
    intent = Column(String(50), nullable=False, index=True)
    subtype = Column(String(100), nullable=False, index=True)
    query_fragment = Column(Text, nullable=False)
    confidence = Column(Float, nullable=True, index=True)

    # Results
    result = Column(Text, nullable=True)
    result_data = Column(JSON, nullable=True)
    explanation = Column(Text, nullable=True)
    provenance = Column(JSON, nullable=True)  # Element IDs
    processing_steps = Column(JSON, nullable=True)
    limitations = Column(JSON, nullable=True)

    # Performance metrics
    elements_processed = Column(Integer, nullable=True)
    processing_time_ms = Column(Integer, nullable=True)

    created_at = Column(DateTime(timezone=True), server_default=func.now())

    # Relationship
    query = relationship("Query", back_populates="sub_query_results")

    def __repr__(self):
        return f"<SubQueryResult(id={self.id}, intent='{self.intent}', subtype='{self.subtype}')>"

    def to_dict(self) -> Dict[str, Any]:
        """Convert to dictionary for API responses"""
        return {
            "id": self.id,
            "sub_query_index": self.sub_query_index,
            "intent": self.intent,
            "subtype": self.subtype,
            "query_fragment": self.query_fragment,
            "confidence": self.confidence,
            "result": self.result,
            "result_data": self.result_data,
            "explanation": self.explanation,
            "provenance": self.provenance or [],
            "processing_steps": self.processing_steps or [],
            "limitations": self.limitations or [],
            "elements_processed": self.elements_processed,
            "processing_time_ms": self.processing_time_ms,
            "created_at": self.created_at.isoformat() if self.created_at else None,
        }

    @classmethod
    def create_from_single_result(cls, query_id: int, index: int, single_result):
        """Create SubQueryResult from SingleQueryResult"""
        from app.schemas.intent import SingleQueryResult

        if isinstance(single_result, SingleQueryResult):
            return cls(
                query_id=query_id,
                sub_query_index=index,
                intent=single_result.intent.value,
                subtype=single_result.subtype,
                query_fragment=single_result.query_fragment,
                confidence=single_result.confidence,
                result=single_result.result,
                result_data=single_result.result_data,
                explanation=single_result.explanation,
                provenance=single_result.provenance,
                processing_steps=single_result.processing_steps,
                limitations=single_result.limitations,
                elements_processed=single_result.elements_processed,
                processing_time_ms=single_result.processing_time_ms,
            )


class QueryResultStorage(Base):
    """
    Separate table for storing large query results
    """
    __tablename__ = "query_result_storage"

    id = Column(Integer, primary_key=True, index=True)
    query_id = Column(Integer, ForeignKey("queries.id", ondelete="CASCADE"), nullable=False, unique=True)

    # Large result data stored separately
    full_result_data = Column(JSON, nullable=True)  # Complete results
    raw_sql_result = Column(JSON, nullable=True)  # Raw SQL results
    processed_elements = Column(JSON, nullable=True)  # Processed element data

    # File-based storage for very large results
    result_file_path = Column(String(500), nullable=True)  # S3 path for large results
    result_file_format = Column(String(20), nullable=True)  # json, csv, excel

    # Result metadata
    result_size_bytes = Column(Integer, nullable=True)
    compression_used = Column(Boolean, default=False)

    created_at = Column(DateTime(timezone=True), server_default=func.now())

    # Relationship
    query = relationship("Query", backref="query_result")