from app import db
from datetime import datetime
from sqlalchemy.dialects.mysql import TEXT

class User(db.Model):
    """Customer/User model"""
    __tablename__ = 'users'
    
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    phone = db.Column(db.Text, nullable=False)  # Encrypted
    password_hash = db.Column(db.String(255), nullable=False)
    business_name = db.Column(db.String(255))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    # Relationships
    subscriptions = db.relationship('Subscription', backref='user', lazy=True)
    consultations = db.relationship('Consultation', backref='user', lazy=True)
    orders = db.relationship('Order', backref='user', lazy=True)

class Product(db.Model):
    """Product/Service model"""
    __tablename__ = 'products'
    
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(120), nullable=False)
    description = db.Column(db.Text)
    product_type = db.Column(db.String(50), nullable=False)  # template, custom, addon
    tier = db.Column(db.String(50))  # basic, pro, custom
    setup_fee = db.Column(db.Numeric(10, 2), default=0)
    monthly_price = db.Column(db.Numeric(10, 2))
    maintenance_increase = db.Column(db.Numeric(10, 2), default=0)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    # Relationships
    order_items = db.relationship('OrderItem', backref='product', lazy=True)

class Order(db.Model):
    """Order model"""
    __tablename__ = 'orders'
    
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    order_number = db.Column(db.String(50), unique=True, nullable=False)
    status = db.Column(db.String(50), default='pending')  # pending, completed, cancelled
    subtotal = db.Column(db.Numeric(10, 2))
    setup_fees = db.Column(db.Numeric(10, 2))
    total = db.Column(db.Numeric(10, 2))
    stripe_payment_intent_id = db.Column(db.String(255))
    paypal_order_id = db.Column(db.String(255))
    payment_method = db.Column(db.String(50))  # stripe, paypal
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    # Relationships
    items = db.relationship('OrderItem', backref='order', lazy=True)
    subscription = db.relationship('Subscription', uselist=False, backref='order')

class OrderItem(db.Model):
    """Order line items"""
    __tablename__ = 'order_items'
    
    id = db.Column(db.Integer, primary_key=True)
    order_id = db.Column(db.Integer, db.ForeignKey('orders.id'), nullable=False)
    product_id = db.Column(db.Integer, db.ForeignKey('products.id'), nullable=False)
    quantity = db.Column(db.Integer, default=1)
    price = db.Column(db.Numeric(10, 2))
    setup_fee = db.Column(db.Numeric(10, 2), default=0)

class Subscription(db.Model):
    """Active subscription model"""
    __tablename__ = 'subscriptions'
    
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    order_id = db.Column(db.Integer, db.ForeignKey('orders.id'))
    status = db.Column(db.String(50), default='active')  # active, cancelled, suspended
    billing_cycle_start = db.Column(db.DateTime)
    billing_cycle_end = db.Column(db.DateTime)
    stripe_subscription_id = db.Column(db.String(255))
    paypal_subscription_id = db.Column(db.String(255))
    auto_renew = db.Column(db.Boolean, default=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

class Consultation(db.Model):
    """Consultation booking model"""
    __tablename__ = 'consultations'
    
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    email = db.Column(db.String(120), nullable=False)
    phone = db.Column(db.String(20), nullable=False)
    business_name = db.Column(db.String(255))
    questionnaire_data = db.Column(db.JSON)  # Stores all questionnaire responses
    scheduled_time = db.Column(db.DateTime, nullable=False)
    status = db.Column(db.String(50), default='scheduled')  # scheduled, completed, cancelled
    payment_status = db.Column(db.String(50), default='pending')  # pending, authorized, charged, refunded
    consultation_fee_paid = db.Column(db.Numeric(10, 2), default=50)
    notes = db.Column(db.Text)  # Your prep notes + follow-up notes
    converted_to_order_id = db.Column(db.Integer, db.ForeignKey('orders.id'))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

class StripeApiKey(db.Model):
    """Encrypted Stripe API keys for customers"""
    __tablename__ = 'stripe_api_keys'
    
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), unique=True, nullable=False)
    api_key_encrypted = db.Column(db.Text, nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

class PaypalApiKey(db.Model):
    """Encrypted PayPal API keys for customers"""
    __tablename__ = 'paypal_api_keys'
    
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), unique=True, nullable=False)
    api_key_encrypted = db.Column(db.Text, nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)