from app import db from datetime import datetime, timedelta import secrets from flask_login import UserMixin from werkzeug.security import generate_password_hash, check_password_hash from sqlalchemy.dialects.mysql import TEXT # Transparent field encryption helpers (see app/utils/encryption.py) from app.utils.encryption import EncryptedType, hash_token class User(UserMixin, 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(EncryptedType, nullable=False) # encrypted automatically 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) # ---- Phase 2 additions ------------------------------------------------ owner_name = db.Column(db.String(255)) role = db.Column(db.String(20), default='client', nullable=False) # client | admin | master last_login = db.Column(db.DateTime) # Business contact (encrypted at rest) business_email = db.Column(EncryptedType) # secondary contact, encrypted # Subscription summary (quick read for the dashboard; the Subscription # table below still holds the detailed billing records) subscription_tier = db.Column(db.String(50)) # growth | scale | dominator subscription_status = db.Column(db.String(20), default='pending', nullable=False) subscription_start_date = db.Column(db.DateTime) subscription_end_date = db.Column(db.DateTime) # Site / hosting custom_domain = db.Column(db.String(255), unique=True, nullable=True) site_status = db.Column(db.String(20), default='pending', nullable=False) # pending|deploying|active|error site_url = db.Column(db.String(255)) # Billing id -- NOT encrypted on purpose. It is an opaque Stripe id # (cus_...) that webhooks must look users up by, which needs a queryable, # unique column. Fernet encryption is non-deterministic and would break # both the unique constraint and lookups. stripe_customer_id = db.Column(db.String(255), unique=True, nullable=True, index=True) # Two-factor authentication two_factor_enabled = db.Column(db.Boolean, default=False, nullable=False) two_factor_secret = db.Column(EncryptedType) # encrypted TOTP secret two_factor_backup_codes = db.Column(EncryptedType) # encrypted, newline-separated # Email verification (store a HASH of the token, never the raw token) is_email_verified = db.Column(db.Boolean, default=False, nullable=False) email_verification_token_hash = db.Column(db.String(64), index=True) email_verification_sent_at = db.Column(db.DateTime) # Request metadata ip_address = db.Column(db.String(50)) user_agent = db.Column(db.String(500)) # 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) # ---- Password helpers ------------------------------------------------- def set_password(self, password): # pbkdf2:sha256 is werkzeug's stable default and works on Python 3.6. self.password_hash = generate_password_hash(password, method='pbkdf2:sha256') def check_password(self, password): return check_password_hash(self.password_hash, password) # ---- Email verification helpers -------------------------------------- def generate_email_verification_token(self): """Create a one-time token, store only its hash, and return the raw token so the caller can email it. Valid for 24h by default.""" raw = secrets.token_urlsafe(32) self.email_verification_token_hash = hash_token(raw) self.email_verification_sent_at = datetime.utcnow() return raw def verify_email_token(self, raw_token, max_age_hours=24): if not self.email_verification_token_hash or not self.email_verification_sent_at: return False if datetime.utcnow() - self.email_verification_sent_at > timedelta(hours=max_age_hours): return False if not secrets.compare_digest( self.email_verification_token_hash, hash_token(raw_token) ): return False self.is_email_verified = True self.email_verification_token_hash = None self.email_verification_sent_at = None return True # ---- 2FA backup-code helpers ----------------------------------------- def set_backup_codes(self, codes): self.two_factor_backup_codes = "\n".join(codes) if codes else None def get_backup_codes(self): if not self.two_factor_backup_codes: return [] return [c for c in self.two_factor_backup_codes.split("\n") if c] # ---- Subscription helpers -------------------------------------------- @property def is_subscription_active(self): if self.subscription_status != 'active': return False if self.subscription_end_date and self.subscription_end_date < datetime.utcnow(): return False return True @property def days_until_renewal(self): if not self.subscription_end_date: return None return max((self.subscription_end_date - datetime.utcnow()).days, 0) # ---- Role helpers ----------------------------------------------------- @property def is_admin(self): return self.role in ('admin', 'master') @property def is_master(self): return self.role == 'master' def __repr__(self): return "".format(self.email, self.role) 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) cancelled_at = db.Column(db.DateTime) cancellation_reason = db.Column(db.Text) refund_issued = db.Column(db.Boolean, default=False) refund_amount = db.Column(db.Numeric(10, 2)) 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)