from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
from werkzeug.security import generate_password_hash, check_password_hash

db = SQLAlchemy()

class User(db.Model):
    __tablename__ = 'users'
    
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    password_hash = db.Column(db.String(255), nullable=False)
    role = db.Column(db.String(20), nullable=False, default='caller')  # 'admin', 'emc', 'caller'
    status = db.Column(db.String(20), nullable=False, default='approved') # 'pending', 'approved', 'blocked'
    company_name = db.Column(db.String(120), nullable=True) # Only for EMC
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    # Relationships
    events_managed = db.relationship('Event', backref='emc', lazy=True, cascade="all, delete-orphan")
    assigned_guests = db.relationship('Guest', backref='caller', lazy=True)
    
    def set_password(self, password):
        self.password_hash = generate_password_hash(password)
        
    def check_password(self, password):
        return check_password_hash(self.password_hash, password)
        
    def serialize(self):
        return {
            'id': self.id,
            'username': self.username,
            'role': self.role,
            'status': self.status,
            'company_name': self.company_name,
            'created_at': self.created_at.isoformat() if self.created_at else None
        }

class Event(db.Model):
    __tablename__ = 'events'
    
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(120), nullable=False)
    description = db.Column(db.Text, nullable=True)
    date = db.Column(db.DateTime, nullable=False)
    location = db.Column(db.String(200), nullable=True)
    emc_id = db.Column(db.Integer, db.ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    # Relationships
    guests = db.relationship('Guest', backref='event', lazy=True, cascade="all, delete-orphan")
    schedule_items = db.relationship('ScheduleItem', backref='event', lazy=True, cascade="all, delete-orphan")
    
    def serialize(self):
        return {
            'id': self.id,
            'title': self.title,
            'description': self.description,
            'date': self.date.strftime('%Y-%m-%d %H:%M') if self.date else None,
            'location': self.location,
            'emc_id': self.emc_id,
            'created_at': self.created_at.isoformat() if self.created_at else None
        }

class ScheduleItem(db.Model):
    __tablename__ = 'schedule_items'
    
    id = db.Column(db.Integer, primary_key=True)
    event_id = db.Column(db.Integer, db.ForeignKey('events.id', ondelete='CASCADE'), nullable=False)
    title = db.Column(db.String(120), nullable=False)
    description = db.Column(db.Text, nullable=True)
    start_time = db.Column(db.DateTime, nullable=False)
    end_time = db.Column(db.DateTime, nullable=False)
    location = db.Column(db.String(200), nullable=True)
    
    # Relationships
    guest_links = db.relationship('GuestSchedule', backref='schedule_item', lazy=True, cascade="all, delete-orphan")
    
    def serialize(self):
        return {
            'id': self.id,
            'event_id': self.event_id,
            'title': self.title,
            'description': self.description,
            'start_time': self.start_time.strftime('%Y-%m-%d %H:%M') if self.start_time else None,
            'end_time': self.end_time.strftime('%Y-%m-%d %H:%M') if self.end_time else None,
            'location': self.location
        }

class Guest(db.Model):
    __tablename__ = 'guests'
    
    id = db.Column(db.Integer, primary_key=True)
    event_id = db.Column(db.Integer, db.ForeignKey('events.id', ondelete='CASCADE'), nullable=False)
    name = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(120), nullable=True)
    phone = db.Column(db.String(30), nullable=True)
    rsvp_status = db.Column(db.String(20), nullable=False, default='pending')  # 'pending', 'attending', 'not_attending', 'tentative'
    arrival_details = db.Column(db.Text, nullable=True)
    departure_details = db.Column(db.Text, nullable=True)
    caller_id = db.Column(db.Integer, db.ForeignKey('users.id', ondelete='SET NULL'), nullable=True)
    notes = db.Column(db.Text, nullable=True)
    call_completed_at = db.Column(db.DateTime, nullable=True)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    # Relationships
    schedule_links = db.relationship('GuestSchedule', backref='guest', lazy=True, cascade="all, delete-orphan")
    
    def serialize(self):
        return {
            'id': self.id,
            'event_id': self.event_id,
            'name': self.name,
            'email': self.email,
            'phone': self.phone,
            'rsvp_status': self.rsvp_status,
            'arrival_details': self.arrival_details,
            'departure_details': self.departure_details,
            'caller_id': self.caller_id,
            'caller_name': self.caller.username if self.caller else 'Unassigned',
            'notes': self.notes,
            'call_completed_at': self.call_completed_at.isoformat() if self.call_completed_at else None,
            'updated_at': self.updated_at.isoformat() if self.updated_at else None
        }

class GuestSchedule(db.Model):
    __tablename__ = 'guest_schedules'
    __table_args__ = (db.UniqueConstraint('guest_id', 'schedule_item_id', name='uq_guest_schedule'),)
    
    id = db.Column(db.Integer, primary_key=True)
    guest_id = db.Column(db.Integer, db.ForeignKey('guests.id', ondelete='CASCADE'), nullable=False)
    schedule_item_id = db.Column(db.Integer, db.ForeignKey('schedule_items.id', ondelete='CASCADE'), nullable=False)