models.py

from app import db
from datetime import datetime
from sqlalchemy import Text

class ScrapingJob(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    status = db.Column(db.String(20), default='pending')  # pending, running, paused, completed, failed, cancelled
    progress = db.Column(db.Integer, default=0)  # number of companies found
    total_target = db.Column(db.Integer, default=100)
    current_page = db.Column(db.Integer, default=1)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    completed_at = db.Column(db.DateTime)
    error_message = db.Column(Text)
    csv_filename = db.Column(db.String(255))
    current_url = db.Column(Text)  # Current page being processed
    last_activity = db.Column(db.DateTime, default=datetime.utcnow)  # For real-time tracking
    
    # Configuration parameters
    search_term = db.Column(db.String(255), default='São Paulo')
    start_date = db.Column(db.String(10), default='01/12/2024')  # DD/MM/YYYY
    end_date = db.Column(db.String(10), default='31/12/2024')  # DD/MM/YYYY
    min_capital = db.Column(db.Float, default=0.0)
    max_capital = db.Column(db.Float)
    phone_required = db.Column(db.Boolean, default=True)
    mei_only = db.Column(db.Boolean, default=True)

class CompanyData(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    job_id = db.Column(db.Integer, db.ForeignKey('scraping_job.id'), nullable=False)
    cnpj = db.Column(db.String(18), nullable=False)
    nome = db.Column(db.String(255), nullable=False)
    data_abertura = db.Column(db.String(20), nullable=False)
    capital_social = db.Column(db.String(50))
    telefone = db.Column(db.String(20))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    selected_for_export = db.Column(db.Boolean, default=True)  # Allow user selection
    
    job = db.relationship('ScrapingJob', backref=db.backref('companies', lazy=True))

class JobLog(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    job_id = db.Column(db.Integer, db.ForeignKey('scraping_job.id'), nullable=False)
    level = db.Column(db.String(10), nullable=False)  # INFO, ERROR, WARNING, DEBUG
    message = db.Column(Text, nullable=False)
    timestamp = db.Column(db.DateTime, default=datetime.utcnow)
    page_number = db.Column(db.Integer)
    company_count = db.Column(db.Integer)
    
    job = db.relationship('ScrapingJob', backref=db.backref('logs', lazy=True, order_by='JobLog.timestamp.desc()'))