# admin/data_manager.py
"""Data management functionality for admin operations"""
import sqlite3
import json
from datetime import datetime
import random
import uuid
import pandas as pd
from pathlib import Path
from faker import Faker

class RobataDataManager:
    def __init__(self, db_path: str = "robata.db"):
        """Initialize database manager"""
        self.db_path = db_path
        self.setup_database()
    
    def setup_database(self):
        """Create database tables if they don't exist"""
        conn = sqlite3.connect(self.db_path)
        c = conn.cursor()
        
        # Create tables
        c.executescript('''
        CREATE TABLE IF NOT EXISTS users (
            id TEXT PRIMARY KEY,
            email TEXT UNIQUE,
            name TEXT,
            role TEXT,
            department TEXT,
            title TEXT,
            created_at TEXT
        );
        
        CREATE TABLE IF NOT EXISTS accounts (
            id TEXT PRIMARY KEY,
            name TEXT,
            industry TEXT,
            status TEXT,
            website TEXT,
            annual_revenue REAL,
            employee_count INTEGER,
            created_at TEXT
        );
        
        CREATE TABLE IF NOT EXISTS contacts (
            id TEXT PRIMARY KEY,
            account_id TEXT,
            first_name TEXT,
            last_name TEXT,
            email TEXT,
            title TEXT,
            department TEXT,
            influence_level TEXT,
            created_at TEXT,
            FOREIGN KEY (account_id) REFERENCES accounts (id)
        );
        
        CREATE TABLE IF NOT EXISTS interactions (
            id TEXT PRIMARY KEY,
            type TEXT,
            account_id TEXT,
            owner_id TEXT,
            transcript TEXT,
            summary TEXT,
            sentiment_score REAL,
            metadata TEXT,
            created_at TEXT,
            FOREIGN KEY (account_id) REFERENCES accounts (id),
            FOREIGN KEY (owner_id) REFERENCES users (id)
        );
        ''')
        
        conn.commit()
        conn.close()
    
    def generate_sample_data(self):
        """Generate and insert sample data"""
        fake = Faker()
        conn = sqlite3.connect(self.db_path)
        c = conn.cursor()
        
        # Generate users
        users = []
        roles = ['sales_rep'] * 6 + ['regional_lead'] * 3 + ['head_of_sales']
        departments = ['Sales', 'Consulting', 'Technology', 'Operations', 'Strategy']
        
        for _ in range(10):
            department = random.choice(departments)
            role = random.choice(roles)
            user = (
                str(uuid.uuid4()),
                fake.company_email(),
                fake.name(),
                role,
                department,
                f"Senior {department} Consultant",
                datetime.now().isoformat()
            )
            users.append(user)
        
        c.executemany(
            'INSERT OR REPLACE INTO users (id, email, name, role, department, title, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)',
            users
        )
        
        # Generate accounts
        accounts = []
        client_companies = [
            ('Quantum Financial', 'Financial Services'),
            ('HealthTech Solutions', 'Healthcare'),
            ('InnovateMfg', 'Manufacturing'),
            ('RetailPro Global', 'Retail'),
            ('TechVision Corp', 'Technology'),
            ('EnergyFuture Ltd', 'Energy')
        ]
        
        for name, industry in client_companies:
            account = (
                str(uuid.uuid4()),
                name,
                industry,
                random.choice(['active', 'active', 'prospect']),
                f"https://www.{name.lower().replace(' ', '')}.com",
                round(random.uniform(10, 500), 2),
                random.randint(100, 10000),
                datetime.now().isoformat()
            )
            accounts.append(account)
        
        c.executemany(
            'INSERT OR REPLACE INTO accounts VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
            accounts
        )
        
        # Generate contacts and interactions
        contacts = []
        interactions = []
        
        for account in accounts:
            # Generate contacts for each account
            for _ in range(random.randint(3, 7)):
                contact = (
                    str(uuid.uuid4()),
                    account[0],
                    fake.first_name(),
                    fake.last_name(),
                    fake.email(),
                    random.choice(['CTO', 'CIO', 'IT Director', 'Innovation Lead']),
                    random.choice(['IT', 'Operations', 'Finance', 'Strategy']),
                    random.choice(['high', 'medium', 'low']),
                    datetime.now().isoformat()
                )
                contacts.append(contact)
            
            # Generate interactions
            for _ in range(random.randint(3, 8)):
                metadata = {
                    'location': 'Virtual' if random.random() < 0.7 else 'In-Person',
                    'duration': random.randint(30, 120),
                    'platform': random.choice(['Zoom', 'Teams', 'Google Meet'])
                }
                
                interaction = (
                    str(uuid.uuid4()),
                    random.choice(['meeting', 'call', 'email', 'presentation']),
                    account[0],
                    random.choice(users)[0],
                    fake.text(max_nb_chars=200),
                    fake.text(max_nb_chars=100),
                    round(random.uniform(0, 1), 2),
                    json.dumps(metadata),
                    datetime.now().isoformat()
                )
                interactions.append(interaction)
        
        c.executemany(
            'INSERT OR REPLACE INTO contacts VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)',
            contacts
        )
        
        c.executemany(
            'INSERT OR REPLACE INTO interactions VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)',
            interactions
        )
        
        conn.commit()
        conn.close()
        
        return len(users), len(accounts), len(contacts), len(interactions)
    
    def get_dashboard_data(self):
        """Get data for dashboard display"""
        conn = sqlite3.connect(self.db_path)
        
        # Get counts
        counts = {}
        counts['accounts'] = pd.read_sql('SELECT COUNT(*) as count FROM accounts', conn).iloc[0]['count']
        counts['contacts'] = pd.read_sql('SELECT COUNT(*) as count FROM contacts', conn).iloc[0]['count']
        counts['interactions'] = pd.read_sql('SELECT COUNT(*) as count FROM interactions', conn).iloc[0]['count']
        
        # Get recent interactions
        recent_interactions = pd.read_sql('''
            SELECT i.*, a.name as account_name, u.name as owner_name
            FROM interactions i
            JOIN accounts a ON i.account_id = a.id
            JOIN users u ON i.owner_id = u.id
            ORDER BY i.created_at DESC
            LIMIT 10
        ''', conn)
        
        # Get account distribution
        account_distribution = pd.read_sql('''
            SELECT industry, COUNT(*) as count
            FROM accounts
            GROUP BY industry
        ''', conn)
        
        conn.close()
        
        return counts, recent_interactions, account_distribution