Maxim Snesarev
33d6bb7ebd
- Updated database configuration to switch from SQLite to PostgreSQL, including changes to alembic.ini, Docker Compose, and environment settings. - Refactored application code to utilize PostgreSQL database adapters, ensuring compatibility with the new database structure. - Enhanced API routes and data handling to support the new database, including adjustments in data models and query logic. - Introduced new job processing mechanisms for full synchronization of AMO CRM entities, leveraging FastStream for background tasks. - Improved logging and error handling across the application to facilitate better monitoring and debugging. - Removed obsolete SQLite adapter files and migrations, streamlining the project structure for PostgreSQL integration.
325 lines
13 KiB
Python
325 lines
13 KiB
Python
from fastapi import APIRouter, Depends, HTTPException
|
|
from sqlalchemy.orm import Session
|
|
from sqlalchemy import func, distinct
|
|
from typing import List, Dict, Any, Optional
|
|
from datetime import datetime
|
|
|
|
from adapters.postgres.database import get_db
|
|
from adapters.postgres.models import (
|
|
Deal, Contact, Company, Pipeline, User, Event, CustomField
|
|
)
|
|
|
|
router = APIRouter()
|
|
|
|
|
|
@router.get("/")
|
|
async def list_entities(db: Session = Depends(get_db)) -> Dict[str, Any]:
|
|
"""List all entities with statistics"""
|
|
|
|
entities = {}
|
|
|
|
# Deals statistics
|
|
deals_count = db.query(Deal).count()
|
|
deals_date_range = db.query(
|
|
func.min(Deal.created_at).label('earliest'),
|
|
func.max(Deal.updated_at).label('latest')
|
|
).first()
|
|
|
|
entities["deals"] = {
|
|
"count": deals_count,
|
|
"last_updated": datetime.fromtimestamp(deals_date_range.latest).isoformat() + "Z" if deals_date_range.latest else None,
|
|
"date_range": {
|
|
"earliest": datetime.fromtimestamp(deals_date_range.earliest).isoformat() + "Z" if deals_date_range.earliest else None,
|
|
"latest": datetime.fromtimestamp(deals_date_range.latest).isoformat() + "Z" if deals_date_range.latest else None
|
|
}
|
|
}
|
|
|
|
# Contacts statistics
|
|
contacts_count = db.query(Contact).count()
|
|
contacts_date_range = db.query(
|
|
func.min(Contact.created_at).label('earliest'),
|
|
func.max(Contact.updated_at).label('latest')
|
|
).first()
|
|
|
|
entities["contacts"] = {
|
|
"count": contacts_count,
|
|
"last_updated": datetime.fromtimestamp(contacts_date_range.latest).isoformat() + "Z" if contacts_date_range.latest else None,
|
|
"date_range": {
|
|
"earliest": datetime.fromtimestamp(contacts_date_range.earliest).isoformat() + "Z" if contacts_date_range.earliest else None,
|
|
"latest": datetime.fromtimestamp(contacts_date_range.latest).isoformat() + "Z" if contacts_date_range.latest else None
|
|
}
|
|
}
|
|
|
|
# Companies statistics
|
|
companies_count = db.query(Company).count()
|
|
companies_date_range = db.query(
|
|
func.min(Company.created_at).label('earliest'),
|
|
func.max(Company.updated_at).label('latest')
|
|
).first()
|
|
|
|
entities["companies"] = {
|
|
"count": companies_count,
|
|
"last_updated": datetime.fromtimestamp(companies_date_range.latest).isoformat() + "Z" if companies_date_range.latest else None,
|
|
"date_range": {
|
|
"earliest": datetime.fromtimestamp(companies_date_range.earliest).isoformat() + "Z" if companies_date_range.earliest else None,
|
|
"latest": datetime.fromtimestamp(companies_date_range.latest).isoformat() + "Z" if companies_date_range.latest else None
|
|
}
|
|
}
|
|
|
|
# Pipelines statistics
|
|
pipelines_count = db.query(Pipeline).count()
|
|
pipelines_date_range = db.query(
|
|
func.min(Pipeline.created_at).label('earliest'),
|
|
func.max(Pipeline.updated_at).label('latest')
|
|
).first()
|
|
|
|
entities["pipelines"] = {
|
|
"count": pipelines_count,
|
|
"last_updated": datetime.fromtimestamp(pipelines_date_range.latest).isoformat() + "Z" if pipelines_date_range.latest else None,
|
|
"date_range": {
|
|
"earliest": datetime.fromtimestamp(pipelines_date_range.earliest).isoformat() + "Z" if pipelines_date_range.earliest else None,
|
|
"latest": datetime.fromtimestamp(pipelines_date_range.latest).isoformat() + "Z" if pipelines_date_range.latest else None
|
|
}
|
|
}
|
|
|
|
# Users statistics (no date range as they don't change often)
|
|
users_count = db.query(User).count()
|
|
entities["users"] = {
|
|
"count": users_count,
|
|
"last_updated": None,
|
|
"date_range": {
|
|
"earliest": None,
|
|
"latest": None
|
|
}
|
|
}
|
|
|
|
# Events statistics
|
|
events_count = db.query(Event).count()
|
|
events_date_range = db.query(
|
|
func.min(Event.created_at).label('earliest'),
|
|
func.max(Event.created_at).label('latest')
|
|
).first()
|
|
|
|
entities["events"] = {
|
|
"count": events_count,
|
|
"last_updated": datetime.fromtimestamp(events_date_range.latest).isoformat() + "Z" if events_date_range.latest else None,
|
|
"date_range": {
|
|
"earliest": datetime.fromtimestamp(events_date_range.earliest).isoformat() + "Z" if events_date_range.earliest else None,
|
|
"latest": datetime.fromtimestamp(events_date_range.latest).isoformat() + "Z" if events_date_range.latest else None
|
|
}
|
|
}
|
|
|
|
return {"entities": entities}
|
|
|
|
|
|
@router.get("/{entity_type}/fields")
|
|
async def list_entity_fields(entity_type: str, db: Session = Depends(get_db)) -> Dict[str, Any]:
|
|
"""List fields with statistics and examples for specified entity"""
|
|
|
|
if entity_type not in ["deals", "contacts", "companies", "pipelines", "users", "events"]:
|
|
raise HTTPException(status_code=404, detail="Entity type not found")
|
|
|
|
# Get standard fields based on entity type
|
|
standard_fields = []
|
|
|
|
if entity_type == "deals":
|
|
# Sample some deals to get examples
|
|
sample_deals = db.query(Deal).limit(3).all()
|
|
standard_fields = [
|
|
{
|
|
"name": "name",
|
|
"type": "text",
|
|
"is_custom": False,
|
|
"usage_count": db.query(Deal).filter(Deal.name.isnot(None)).count(),
|
|
"examples": [deal.name for deal in sample_deals if deal.name][:3],
|
|
"null_count": db.query(Deal).filter(Deal.name.is_(None)).count()
|
|
},
|
|
{
|
|
"name": "price",
|
|
"type": "numeric",
|
|
"is_custom": False,
|
|
"usage_count": db.query(Deal).filter(Deal.price.isnot(None)).count(),
|
|
"examples": [deal.price for deal in sample_deals if deal.price is not None][:3],
|
|
"null_count": db.query(Deal).filter(Deal.price.is_(None)).count()
|
|
},
|
|
{
|
|
"name": "created_at",
|
|
"type": "date",
|
|
"is_custom": False,
|
|
"usage_count": db.query(Deal).filter(Deal.created_at.isnot(None)).count(),
|
|
"examples": [datetime.fromtimestamp(deal.created_at).isoformat() + "Z" for deal in sample_deals if deal.created_at][:3],
|
|
"null_count": db.query(Deal).filter(Deal.created_at.is_(None)).count()
|
|
},
|
|
{
|
|
"name": "updated_at",
|
|
"type": "date",
|
|
"is_custom": False,
|
|
"usage_count": db.query(Deal).filter(Deal.updated_at.isnot(None)).count(),
|
|
"examples": [datetime.fromtimestamp(deal.updated_at).isoformat() + "Z" for deal in sample_deals if deal.updated_at][:3],
|
|
"null_count": db.query(Deal).filter(Deal.updated_at.is_(None)).count()
|
|
}
|
|
]
|
|
|
|
elif entity_type == "contacts":
|
|
sample_contacts = db.query(Contact).limit(3).all()
|
|
standard_fields = [
|
|
{
|
|
"name": "name",
|
|
"type": "text",
|
|
"is_custom": False,
|
|
"usage_count": db.query(Contact).filter(Contact.name.isnot(None)).count(),
|
|
"examples": [contact.name for contact in sample_contacts if contact.name][:3],
|
|
"null_count": db.query(Contact).filter(Contact.name.is_(None)).count()
|
|
},
|
|
{
|
|
"name": "first_name",
|
|
"type": "text",
|
|
"is_custom": False,
|
|
"usage_count": db.query(Contact).filter(Contact.first_name.isnot(None)).count(),
|
|
"examples": [contact.first_name for contact in sample_contacts if contact.first_name][:3],
|
|
"null_count": db.query(Contact).filter(Contact.first_name.is_(None)).count()
|
|
},
|
|
{
|
|
"name": "last_name",
|
|
"type": "text",
|
|
"is_custom": False,
|
|
"usage_count": db.query(Contact).filter(Contact.last_name.isnot(None)).count(),
|
|
"examples": [contact.last_name for contact in sample_contacts if contact.last_name][:3],
|
|
"null_count": db.query(Contact).filter(Contact.last_name.is_(None)).count()
|
|
}
|
|
]
|
|
|
|
elif entity_type == "companies":
|
|
sample_companies = db.query(Company).limit(3).all()
|
|
standard_fields = [
|
|
{
|
|
"name": "name",
|
|
"type": "text",
|
|
"is_custom": False,
|
|
"usage_count": db.query(Company).filter(Company.name.isnot(None)).count(),
|
|
"examples": [company.name for company in sample_companies if company.name][:3],
|
|
"null_count": db.query(Company).filter(Company.name.is_(None)).count()
|
|
}
|
|
]
|
|
|
|
elif entity_type == "pipelines":
|
|
sample_pipelines = db.query(Pipeline).limit(3).all()
|
|
standard_fields = [
|
|
{
|
|
"name": "name",
|
|
"type": "text",
|
|
"is_custom": False,
|
|
"usage_count": db.query(Pipeline).filter(Pipeline.name.isnot(None)).count(),
|
|
"examples": [pipeline.name for pipeline in sample_pipelines if pipeline.name][:3],
|
|
"null_count": db.query(Pipeline).filter(Pipeline.name.is_(None)).count()
|
|
},
|
|
{
|
|
"name": "is_main",
|
|
"type": "checkbox",
|
|
"is_custom": False,
|
|
"usage_count": db.query(Pipeline).filter(Pipeline.is_main.isnot(None)).count(),
|
|
"examples": [pipeline.is_main for pipeline in sample_pipelines if pipeline.is_main is not None][:3],
|
|
"null_count": db.query(Pipeline).filter(Pipeline.is_main.is_(None)).count()
|
|
}
|
|
]
|
|
|
|
elif entity_type == "users":
|
|
sample_users = db.query(User).limit(3).all()
|
|
standard_fields = [
|
|
{
|
|
"name": "name",
|
|
"type": "text",
|
|
"is_custom": False,
|
|
"usage_count": db.query(User).filter(User.name.isnot(None)).count(),
|
|
"examples": [user.name for user in sample_users if user.name][:3],
|
|
"null_count": db.query(User).filter(User.name.is_(None)).count()
|
|
},
|
|
{
|
|
"name": "email",
|
|
"type": "text",
|
|
"is_custom": False,
|
|
"usage_count": db.query(User).filter(User.email.isnot(None)).count(),
|
|
"examples": [user.email for user in sample_users if user.email][:3],
|
|
"null_count": db.query(User).filter(User.email.is_(None)).count()
|
|
}
|
|
]
|
|
|
|
elif entity_type == "events":
|
|
sample_events = db.query(Event).limit(3).all()
|
|
standard_fields = [
|
|
{
|
|
"name": "type",
|
|
"type": "select",
|
|
"is_custom": False,
|
|
"usage_count": db.query(Event).filter(Event.type.isnot(None)).count(),
|
|
"examples": [event.type for event in sample_events if event.type][:3],
|
|
"null_count": db.query(Event).filter(Event.type.is_(None)).count(),
|
|
"possible_values": ["incoming_call", "outgoing_call", "lead_status_changed"]
|
|
},
|
|
{
|
|
"name": "entity_type",
|
|
"type": "text",
|
|
"is_custom": False,
|
|
"usage_count": db.query(Event).filter(Event.entity_type.isnot(None)).count(),
|
|
"examples": [event.entity_type for event in sample_events if event.entity_type][:3],
|
|
"null_count": db.query(Event).filter(Event.entity_type.is_(None)).count()
|
|
}
|
|
]
|
|
|
|
# Get custom fields for this entity type
|
|
custom_fields_query = db.query(CustomField).filter(
|
|
CustomField.entity_type == entity_type
|
|
).group_by(CustomField.field_name, CustomField.field_type).all()
|
|
|
|
custom_fields = []
|
|
for field_group in custom_fields_query:
|
|
# Get examples for this custom field
|
|
examples_query = db.query(CustomField.field_value).filter(
|
|
CustomField.entity_type == entity_type,
|
|
CustomField.field_name == field_group.field_name,
|
|
CustomField.field_value.isnot(None)
|
|
).limit(3).all()
|
|
|
|
examples = [ex[0] for ex in examples_query if ex[0]]
|
|
|
|
# Count usage
|
|
usage_count = db.query(CustomField).filter(
|
|
CustomField.entity_type == entity_type,
|
|
CustomField.field_name == field_group.field_name,
|
|
CustomField.field_value.isnot(None)
|
|
).count()
|
|
|
|
null_count = db.query(CustomField).filter(
|
|
CustomField.entity_type == entity_type,
|
|
CustomField.field_name == field_group.field_name,
|
|
CustomField.field_value.is_(None)
|
|
).count()
|
|
|
|
custom_field = {
|
|
"name": field_group.field_name,
|
|
"type": field_group.field_type,
|
|
"is_custom": True,
|
|
"field_id": field_group.field_id,
|
|
"usage_count": usage_count,
|
|
"examples": examples,
|
|
"null_count": null_count
|
|
}
|
|
|
|
# For select fields, get possible values
|
|
if field_group.field_type in ["select", "multiselect"]:
|
|
possible_values = db.query(distinct(CustomField.field_value)).filter(
|
|
CustomField.entity_type == entity_type,
|
|
CustomField.field_name == field_group.field_name,
|
|
CustomField.field_value.isnot(None)
|
|
).all()
|
|
custom_field["possible_values"] = [pv[0] for pv in possible_values if pv[0]]
|
|
|
|
custom_fields.append(custom_field)
|
|
|
|
all_fields = standard_fields + custom_fields
|
|
|
|
return {
|
|
"entity_type": entity_type,
|
|
"fields": all_fields
|
|
}
|