"""Seed Script for Aera Risk Detection System (MySQL/SQLAlchemy)"""
import asyncio
import sys
import os
import hashlib  # <--- NEW IMPORT
from datetime import datetime
import uuid
 
# Add the backend directory to sys.path so we can import app modules
sys.path.insert(0, os.path.abspath(os.path.join(os.path.dirname(__file__), 'backend')))
 
from sqlalchemy import text, delete
from passlib.context import CryptContext
 
# Import your new database and models
from database import AsyncSessionLocal, engine, Base
from models import User, Conversation, Message, RiskAnalysis, ReviewAction, AuditLog
 
# Password Hashing
pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")
 
def generate_uuid():
    return str(uuid.uuid4())
 
def get_password_hash(password: str) -> str:
    """
    Generates a bcrypt hash for the password using SHA-256 pre-hashing.
    Matches the logic in backend/auth.py to fix 72-byte limit.
    """
    # 1. Pre-hash with SHA-256 to get a fixed 64-char string
    password_digest = hashlib.sha256(password.encode('utf-8')).hexdigest()
    
    # 2. Hash the digest
    return pwd_context.hash(password_digest)
 
async def seed_database():
    """Seed the database with Users and Mock Scenarios"""
    print("🌱 Starting database seed (MySQL)...")
    
    async with AsyncSessionLocal() as session:
        try:
            # 1. Clear existing data (Order matters due to Foreign Keys!)
            # We delete children first, then parents
            print("🧹 Clearing old data...")
            await session.execute(delete(ReviewAction))
            await session.execute(delete(RiskAnalysis))
            await session.execute(delete(Message))
            await session.execute(delete(Conversation))
            await session.execute(delete(AuditLog))
            await session.execute(delete(User))
            await session.commit()
            
            # 2. Create Users
            print("👤 Creating Users...")
            admin_id = generate_uuid()
            reviewer_id = generate_uuid()
            
            users = [
                User(
                    id=admin_id,
                    email="admin@aera.com",
                    full_name="System Administrator",
                    role="SuperAdmin",
                    # FIXED: Use the new helper function
                    hashed_password=get_password_hash("Admin123!"),
                    is_active=True
                ),
                User(
                    id=reviewer_id,
                    email="reviewer@aera.com",
                    full_name="Compliance Reviewer",
                    role="Reviewer",
                    # FIXED: Use the new helper function
                    hashed_password=get_password_hash("Reviewer123!"),
                    is_active=True
                )
            ]
            session.add_all(users)
            
            # 3. Create Scenario A: The Contradiction (Aniket)
            # Thread 1: Official status update (Low Risk context)
            thread_1_id = generate_uuid()
            conv_1 = Conversation(
                id=thread_1_id,
                teams_thread_id="thread_safe_001",
                participants=[
                    {"name": "Aniket Sharma", "email": "aniket@graycell.com"},
                    {"name": "Project Manager", "email": "manager@graycell.com"}
                ],
                topic="Project Update"
            )
            
            msg_1 = Message(
                conversation_id=thread_1_id,
                sender_email="aniket@graycell.com",
                sender_name="Aniket Sharma",
                content="Hi Manager, just wanted to update you that the production deployment was successful. All systems are green.",
                timestamp=datetime.utcnow()  # Ensure timestamp is set
            )
            
            # Thread 2: The Panic/Contradiction (High Risk context)
            thread_2_id = generate_uuid()
            conv_2 = Conversation(
                id=thread_2_id,
                teams_thread_id="thread_risk_001",
                participants=[
                    {"name": "Aniket Sharma", "email": "aniket@graycell.com"},
                    {"name": "Suraj", "email": "suraj@graycell.com"}
                ],
                topic="Urgent Help"
            )
            
            msg_2 = Message(
                conversation_id=thread_2_id,
                sender_email="aniket@graycell.com",
                sender_name="Aniket Sharma",
                content="Bro I messed up bad. I accidentally deleted the production database. Please help me restore it before the manager finds out. I told him it went fine.",
                timestamp=datetime.utcnow()
            )
            
            # 4. Create Scenario B: Bribery/Kickback
            thread_3_id = generate_uuid()
            conv_3 = Conversation(
                id=thread_3_id,
                teams_thread_id="thread_risk_002",
                participants=[
                    {"name": "Vendor Sales", "email": "sales@external-vendor.com"},
                    {"name": "Procurement Officer", "email": "procurement@graycell.com"}
                ],
                topic="Invoice Discussion"
            )
            
            msg_3 = Message(
                conversation_id=thread_3_id,
                sender_email="sales@external-vendor.com",
                sender_name="Vendor Sales",
                content="If you can approve this invoice by EOD, we can arrange a 5% kickback sent to your personal account. No one needs to know.",
                timestamp=datetime.utcnow()
            )
            
            # Add all conversations and messages
            session.add_all([conv_1, conv_2, conv_3])
            session.add_all([msg_1, msg_2, msg_3])
            
            await session.commit()
            print("✅ Database populated successfully!")
            
            print("\n📋 Login Credentials:")
            print("   Admin:    admin@aera.com / Admin123!")
            print("   Reviewer: reviewer@aera.com / Reviewer123!")
            
            print("\n🧪 Scenarios Loaded:")
            print("   1. Aniket (Contradiction): 'Deployment success' vs 'Deleted DB'")
            print("   2. Vendor (Bribery): '5% kickback'")
 
        except Exception as e:
            print(f"❌ Error during seed: {e}")
            await session.rollback()
            raise
        finally:
            await session.close()
 
if __name__ == "__main__":
    # Ensure tables exist before seeding (optional but safe)
    async def init_tables():
        async with engine.begin() as conn:
            await conn.run_sync(Base.metadata.create_all)
            
    loop = asyncio.get_event_loop()
    loop.run_until_complete(init_tables())
    loop.run_until_complete(seed_database())