from sqlalchemy import Column, Integer, String, Float, JSON, DateTime, Boolean
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime

Base = declarative_base()

class EnrichedLead(Base):
    __tablename__ = "leads_insights"

    id = Column(Integer, primary_key=True, index=True)
    run_id = Column(String, index=True)
    user_id = Column(String, index=True)
    job_id = Column(String, index=True)
    title = Column(String)
    website = Column(String)
    email = Column(String)
    whatsapp_number = Column(String)
    additional_phone_numbers = Column(JSON)  # List of strings: ["+91...", "+91..."]
    social_links = Column(JSON)  # Dict: {"facebook": "url", "instagram": "url"}
    enrichment_confidence_score = Column(Float)
    campaign_name = Column(String)
    # New Columns from Input Data
    input_id = Column(String, index=True)
    link = Column(String)
    category = Column(String)
    address = Column(String)
    open_hours = Column(JSON)
    popular_times = Column(JSON)
    phone = Column(String)
    plus_code = Column(String)
    review_count = Column(Integer)
    review_rating = Column(Float)
    reviews_per_rating = Column(JSON)
    latitude = Column(Float)
    longitude = Column(Float)
    cid = Column(String)
    status = Column(String)
    descriptions = Column(JSON)
    reviews_link = Column(String)
    thumbnail = Column(String)
    timezone = Column(String)
    price_range = Column(String)
    data_id = Column(String)
    place_id = Column(String, unique=True, index=True)
    images = Column(JSON)
    reservations = Column(JSON)
    order_online = Column(JSON)
    menu = Column(JSON)
    owner = Column(JSON)
    complete_address = Column(JSON)
    about = Column(JSON)
    user_reviews = Column(JSON)
    user_reviews_extended = Column(JSON)
    booking_system = Column(Boolean, default=False)
    
    # Approval and Generation Status
    email_generation_status = Column(String, default="pending") # pending, approved, wait
    is_email_generated = Column(Boolean, default=False)
    valid_email_exists = Column(Boolean, default=True)

    
    # AI Business Insight Analyzer fields (Updated for deep analysis)
    business_type = Column(String)  # e.g., "D2C Specialty Coffee Roaster"
    target_audience = Column(String) # Description of the demographic
    
    # Quantitative Scores
    seo_score = Column(Integer)  # 0-100
    conversion_score = Column(Integer)  # 0-100
    lead_score = Column(Integer)  # Overall priority score
    
    # Qualitative Data (Stored as JSON for flexibility)
    tech_stack = Column(JSON)  # List of detected tools: ["Shopify", "Google Analytics"]
    strengths = Column(JSON)  # List of positive findings
    identified_problems = Column(JSON)  # List of weaknesses/pain points
    missing_opportunities = Column(JSON)  # List of growth opportunities
    recommended_actions = Column(JSON)  # List of specific tasks to improve the business
    
    # Metadata
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)