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
    }