"""
SQLAlchemy Database Models for Enterprise CX AI Agent
"""
from sqlalchemy import (
    Column, Integer, String, Text, Float, Boolean, DateTime, ForeignKey,
    UniqueConstraint, Index, Date
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from datetime import datetime
import json

Base = declarative_base()


class Company(Base):
    __tablename__ = 'companies'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String, nullable=False)
    domain = Column(String, unique=True)
    industry = Column(String)
    size = Column(String)
    revenue = Column(String)
    location = Column(String)
    description = Column(Text)
    pain_points = Column(Text)  # JSON
    website = Column(String)
    linkedin_url = Column(String)
    summary = Column(Text)  # AI-generated comprehensive summary
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # Relationships
    contacts = relationship("Contact", back_populates="company")

    def to_dict(self):
        return {
            'id': self.id,
            'name': self.name,
            'domain': self.domain,
            'industry': self.industry,
            'size': self.size,
            'revenue': self.revenue,
            'location': self.location,
            'description': self.description,
            'pain_points': json.loads(self.pain_points) if self.pain_points else [],
            'website': self.website,
            'linkedin_url': self.linkedin_url,
            'summary': self.summary,
            'created_at': self.created_at.isoformat() if self.created_at else None,
        }


class ClientProfile(Base):
    """Stores CLIENT company profiles for email personalization"""
    __tablename__ = 'client_profiles'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String, nullable=False, unique=True)
    website = Column(String)
    domain = Column(String)
    description = Column(Text)
    industry = Column(String)

    # What they offer
    offerings = Column(Text)  # JSON list of offerings/products/services
    value_propositions = Column(Text)  # JSON list of value props/benefits
    target_customers = Column(Text)  # JSON list of who they serve
    use_cases = Column(Text)  # JSON list of use cases
    differentiators = Column(Text)  # JSON list of what makes them unique
    summary = Column(Text)  # AI-generated comprehensive summary for personalization

    # Metadata
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    last_researched_at = Column(DateTime, default=datetime.utcnow)

    def to_dict(self):
        return {
            'id': self.id,
            'name': self.name,
            'website': self.website,
            'domain': self.domain,
            'description': self.description,
            'industry': self.industry,
            'offerings': json.loads(self.offerings) if self.offerings else [],
            'value_propositions': json.loads(self.value_propositions) if self.value_propositions else [],
            'target_customers': json.loads(self.target_customers) if self.target_customers else [],
            'use_cases': json.loads(self.use_cases) if self.use_cases else [],
            'differentiators': json.loads(self.differentiators) if self.differentiators else [],
            'summary': self.summary,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'updated_at': self.updated_at.isoformat() if self.updated_at else None,
            'last_researched_at': self.last_researched_at.isoformat() if self.last_researched_at else None,
        }


class Contact(Base):
    __tablename__ = 'contacts'

    id = Column(Integer, primary_key=True, autoincrement=True)
    company_id = Column(Integer, ForeignKey('companies.id', ondelete='SET NULL'))
    first_name = Column(String)
    last_name = Column(String)
    email = Column(String, unique=True, nullable=False)
    phone = Column(String)
    job_title = Column(String)
    department = Column(String)
    seniority_level = Column(String)
    linkedin_url = Column(String)
    twitter_url = Column(String)
    location = Column(String)
    timezone = Column(String)

    # Scoring
    fit_score = Column(Float, default=0.0)
    engagement_score = Column(Float, default=0.0)
    intent_score = Column(Float, default=0.0)
    overall_score = Column(Float, default=0.0)

    # Status & Lifecycle
    status = Column(String, default='new')
    lifecycle_stage = Column(String, default='lead')

    # Tracking
    source = Column(String)
    first_contacted_at = Column(DateTime)
    last_contacted_at = Column(DateTime)
    last_activity_at = Column(DateTime)

    # Metadata
    tags = Column(Text)  # JSON
    notes = Column(Text)
    custom_fields = Column(Text)  # JSON
    is_suppressed = Column(Boolean, default=False)
    suppression_reason = Column(String)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # Relationships
    company = relationship("Company", back_populates="contacts")
    campaign_associations = relationship("CampaignContact", back_populates="contact")
    email_activities = relationship("EmailActivity", back_populates="contact")
    meetings = relationship("Meeting", back_populates="contact")
    activities = relationship("Activity", back_populates="contact")

    @property
    def full_name(self):
        return f"{self.first_name or ''} {self.last_name or ''}".strip()

    def to_dict(self):
        return {
            'id': self.id,
            'company_id': self.company_id,
            'company_name': self.company.name if self.company else None,
            'first_name': self.first_name,
            'last_name': self.last_name,
            'full_name': self.full_name,
            'email': self.email,
            'phone': self.phone,
            'job_title': self.job_title,
            'department': self.department,
            'seniority_level': self.seniority_level,
            'linkedin_url': self.linkedin_url,
            'fit_score': self.fit_score,
            'engagement_score': self.engagement_score,
            'overall_score': self.overall_score,
            'status': self.status,
            'lifecycle_stage': self.lifecycle_stage,
            'source': self.source,
            'tags': json.loads(self.tags) if self.tags else [],
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'last_activity_at': self.last_activity_at.isoformat() if self.last_activity_at else None,
        }


class Campaign(Base):
    __tablename__ = 'campaigns'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String, nullable=False)
    description = Column(Text)
    status = Column(String, default='draft')

    # Targeting
    target_industries = Column(Text)  # JSON
    target_company_sizes = Column(Text)  # JSON
    target_locations = Column(Text)  # JSON
    target_job_titles = Column(Text)  # JSON

    # Configuration
    sequence_id = Column(Integer, ForeignKey('sequences.id', ondelete='SET NULL'))
    goal_contacts = Column(Integer)
    goal_response_rate = Column(Float)
    goal_meetings = Column(Integer)

    # Tracking
    contacts_discovered = Column(Integer, default=0)
    contacts_enriched = Column(Integer, default=0)
    contacts_scored = Column(Integer, default=0)
    contacts_contacted = Column(Integer, default=0)
    contacts_responded = Column(Integer, default=0)
    meetings_booked = Column(Integer, default=0)

    # Dates
    started_at = Column(DateTime)
    completed_at = Column(DateTime)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    created_by = Column(String)

    # Relationships
    sequence = relationship("Sequence", back_populates="campaigns")
    contact_associations = relationship("CampaignContact", back_populates="campaign")
    email_activities = relationship("EmailActivity", back_populates="campaign")
    meetings = relationship("Meeting", back_populates="campaign")
    activities = relationship("Activity", back_populates="campaign")

    def to_dict(self):
        return {
            'id': self.id,
            'name': self.name,
            'description': self.description,
            'status': self.status,
            'sequence_id': self.sequence_id,
            'sequence_name': self.sequence.name if self.sequence else None,
            'goal_contacts': self.goal_contacts,
            'goal_response_rate': self.goal_response_rate,
            'goal_meetings': self.goal_meetings,
            'contacts_discovered': self.contacts_discovered,
            'contacts_enriched': self.contacts_enriched,
            'contacts_contacted': self.contacts_contacted,
            'contacts_responded': self.contacts_responded,
            'meetings_booked': self.meetings_booked,
            'started_at': self.started_at.isoformat() if self.started_at else None,
            'created_at': self.created_at.isoformat() if self.created_at else None,
        }


class CampaignContact(Base):
    __tablename__ = 'campaign_contacts'

    id = Column(Integer, primary_key=True, autoincrement=True)
    campaign_id = Column(Integer, ForeignKey('campaigns.id', ondelete='CASCADE'), nullable=False)
    contact_id = Column(Integer, ForeignKey('contacts.id', ondelete='CASCADE'), nullable=False)
    stage = Column(String, default='discovery')
    stage_updated_at = Column(DateTime, default=datetime.utcnow)
    added_at = Column(DateTime, default=datetime.utcnow)
    notes = Column(Text)

    # Relationships
    campaign = relationship("Campaign", back_populates="contact_associations")
    contact = relationship("Contact", back_populates="campaign_associations")

    __table_args__ = (
        UniqueConstraint('campaign_id', 'contact_id', name='uq_campaign_contact'),
    )


class Sequence(Base):
    __tablename__ = 'sequences'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String, nullable=False)
    description = Column(Text)
    category = Column(String, default='outbound')
    is_active = Column(Boolean, default=True)
    is_template = Column(Boolean, default=False)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    created_by = Column(String)

    # Relationships
    emails = relationship("SequenceEmail", back_populates="sequence", order_by="SequenceEmail.step_number")
    campaigns = relationship("Campaign", back_populates="sequence")

    def to_dict(self):
        return {
            'id': self.id,
            'name': self.name,
            'description': self.description,
            'category': self.category,
            'is_active': self.is_active,
            'email_count': len(self.emails) if self.emails else 0,
            'created_at': self.created_at.isoformat() if self.created_at else None,
        }


class SequenceEmail(Base):
    __tablename__ = 'sequence_emails'

    id = Column(Integer, primary_key=True, autoincrement=True)
    sequence_id = Column(Integer, ForeignKey('sequences.id', ondelete='CASCADE'), nullable=False)
    step_number = Column(Integer, nullable=False)
    wait_days = Column(Integer, default=0)
    subject = Column(String, nullable=False)
    body = Column(Text, nullable=False)
    send_time_preference = Column(String)
    created_at = Column(DateTime, default=datetime.utcnow)

    # Relationships
    sequence = relationship("Sequence", back_populates="emails")
    email_activities = relationship("EmailActivity", back_populates="sequence_email")

    __table_args__ = (
        UniqueConstraint('sequence_id', 'step_number', name='uq_sequence_step'),
    )

    def to_dict(self):
        return {
            'id': self.id,
            'sequence_id': self.sequence_id,
            'step_number': self.step_number,
            'wait_days': self.wait_days,
            'subject': self.subject,
            'body': self.body,
            'send_time_preference': self.send_time_preference,
        }


class EmailActivity(Base):
    __tablename__ = 'email_activities'

    id = Column(Integer, primary_key=True, autoincrement=True)
    contact_id = Column(Integer, ForeignKey('contacts.id', ondelete='CASCADE'), nullable=False)
    campaign_id = Column(Integer, ForeignKey('campaigns.id', ondelete='SET NULL'))
    sequence_email_id = Column(Integer, ForeignKey('sequence_emails.id', ondelete='SET NULL'))
    type = Column(String, nullable=False)  # sent, opened, clicked, replied, etc.
    subject = Column(String)
    preview = Column(Text)
    link_url = Column(String)
    meta_data = Column(Text)  # JSON
    occurred_at = Column(DateTime, default=datetime.utcnow)

    # Relationships
    contact = relationship("Contact", back_populates="email_activities")
    campaign = relationship("Campaign", back_populates="email_activities")
    sequence_email = relationship("SequenceEmail", back_populates="email_activities")


class Meeting(Base):
    __tablename__ = 'meetings'

    id = Column(Integer, primary_key=True, autoincrement=True)
    contact_id = Column(Integer, ForeignKey('contacts.id', ondelete='CASCADE'), nullable=False)
    campaign_id = Column(Integer, ForeignKey('campaigns.id', ondelete='SET NULL'))
    title = Column(String, nullable=False)
    description = Column(Text)
    scheduled_at = Column(DateTime, nullable=False)
    duration_minutes = Column(Integer, default=30)
    meeting_url = Column(String)
    location = Column(String)
    status = Column(String, default='scheduled')
    outcome = Column(String)
    notes = Column(Text)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # Relationships
    contact = relationship("Contact", back_populates="meetings")
    campaign = relationship("Campaign", back_populates="meetings")
    activities = relationship("Activity", back_populates="meeting")

    def to_dict(self):
        return {
            'id': self.id,
            'contact_id': self.contact_id,
            'contact_name': self.contact.full_name if self.contact else None,
            'campaign_id': self.campaign_id,
            'title': self.title,
            'scheduled_at': self.scheduled_at.isoformat() if self.scheduled_at else None,
            'duration_minutes': self.duration_minutes,
            'meeting_url': self.meeting_url,
            'status': self.status,
            'outcome': self.outcome,
        }


class Activity(Base):
    __tablename__ = 'activities'

    id = Column(Integer, primary_key=True, autoincrement=True)
    contact_id = Column(Integer, ForeignKey('contacts.id', ondelete='CASCADE'))
    campaign_id = Column(Integer, ForeignKey('campaigns.id', ondelete='SET NULL'))
    meeting_id = Column(Integer, ForeignKey('meetings.id', ondelete='SET NULL'))
    type = Column(String, nullable=False)
    description = Column(Text)
    meta_data = Column(Text)  # JSON
    performed_by = Column(String)
    occurred_at = Column(DateTime, default=datetime.utcnow)

    # Relationships
    contact = relationship("Contact", back_populates="activities")
    campaign = relationship("Campaign", back_populates="activities")
    meeting = relationship("Meeting", back_populates="activities")


class ABTest(Base):
    __tablename__ = 'ab_tests'

    id = Column(Integer, primary_key=True, autoincrement=True)
    campaign_id = Column(Integer, ForeignKey('campaigns.id', ondelete='CASCADE'), nullable=False)
    sequence_id = Column(Integer, ForeignKey('sequences.id', ondelete='CASCADE'), nullable=False)
    name = Column(String, nullable=False)
    description = Column(Text)
    test_type = Column(String, nullable=False)
    variant_a = Column(Text, nullable=False)  # JSON
    variant_b = Column(Text, nullable=False)  # JSON
    winner = Column(String)
    status = Column(String, default='running')
    started_at = Column(DateTime, default=datetime.utcnow)
    completed_at = Column(DateTime)

    # Relationships
    results = relationship("ABTestResult", back_populates="ab_test")


class ABTestResult(Base):
    __tablename__ = 'ab_test_results'

    id = Column(Integer, primary_key=True, autoincrement=True)
    ab_test_id = Column(Integer, ForeignKey('ab_tests.id', ondelete='CASCADE'), nullable=False)
    variant = Column(String, nullable=False)
    emails_sent = Column(Integer, default=0)
    emails_delivered = Column(Integer, default=0)
    emails_opened = Column(Integer, default=0)
    emails_clicked = Column(Integer, default=0)
    emails_replied = Column(Integer, default=0)
    meetings_booked = Column(Integer, default=0)

    # Relationships
    ab_test = relationship("ABTest", back_populates="results")

    __table_args__ = (
        UniqueConstraint('ab_test_id', 'variant', name='uq_ab_test_variant'),
    )


class Template(Base):
    __tablename__ = 'templates'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String, nullable=False)
    category = Column(String)
    subject = Column(String, nullable=False)
    body = Column(Text, nullable=False)
    variables = Column(Text)  # JSON
    is_active = Column(Boolean, default=True)
    usage_count = Column(Integer, default=0)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)


class AnalyticsSnapshot(Base):
    __tablename__ = 'analytics_snapshots'

    id = Column(Integer, primary_key=True, autoincrement=True)
    campaign_id = Column(Integer, ForeignKey('campaigns.id', ondelete='CASCADE'))
    date = Column(Date, nullable=False)
    hour = Column(Integer)

    # Metrics
    contacts_discovered = Column(Integer, default=0)
    contacts_enriched = Column(Integer, default=0)
    emails_sent = Column(Integer, default=0)
    emails_opened = Column(Integer, default=0)
    emails_clicked = Column(Integer, default=0)
    emails_replied = Column(Integer, default=0)
    meetings_booked = Column(Integer, default=0)

    # Rates
    open_rate = Column(Float, default=0.0)
    click_rate = Column(Float, default=0.0)
    response_rate = Column(Float, default=0.0)
    meeting_rate = Column(Float, default=0.0)

    created_at = Column(DateTime, default=datetime.utcnow)

    __table_args__ = (
        UniqueConstraint('campaign_id', 'date', 'hour', name='uq_analytics_snapshot'),
    )


class Setting(Base):
    __tablename__ = 'settings'

    key = Column(String, primary_key=True)
    value = Column(String, nullable=False)
    description = Column(Text)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)