# Database-First 80/20 RateRight Fresh Start

## 🗃️ REPLIT AGENT: BUILD RATERIGHT DATABASE-FIRST APPROACH

**CRITICAL**: Build database schema FIRST, test it thoroughly, THEN build application on solid foundation.

---

## 📊 PHASE 1: DATABASE FOUNDATION (Hours 1-4)

### **1. CREATE COMPLETE DATABASE SCHEMA**
```python
# models.py - Build ALL models first
from flask_sqlalchemy import SQLAlchemy
from werkzeug.security import generate_password_hash, check_password_hash
from datetime import datetime

db = SQLAlchemy()

class User(db.Model):
    __tablename__ = 'users'
    
    # Identity (5 fields)
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(255), unique=True, nullable=False)
    password_hash = db.Column(db.String(255), nullable=False)
    first_name = db.Column(db.String(100), nullable=False)
    last_name = db.Column(db.String(100), nullable=False)
    
    # Business Logic (6 fields)
    role = db.Column(db.String(20), nullable=False)  # 'worker' or 'contractor'
    phone_number = db.Column(db.String(20), nullable=False)
    location = db.Column(db.String(100), nullable=False)  # "Sydney, NSW"
    primary_trade = db.Column(db.String(100))
    abn_number = db.Column(db.String(20))  # Required for contractors only
    is_active = db.Column(db.Boolean, default=True)
    
    # Performance (4 fields)
    jobs_completed = db.Column(db.Integer, default=0)
    average_rating = db.Column(db.Numeric(3,2), default=0.00)
    total_reviews = db.Column(db.Integer, default=0)
    response_rate = db.Column(db.Numeric(5,2), default=0.00)
    
    # Simple Gamification (3 fields)
    total_points = db.Column(db.Integer, default=0)
    current_level = db.Column(db.Integer, default=1)
    seasonal_league = db.Column(db.String(20), default='bronze')
    
    # System (4 fields)
    date_created = db.Column(db.DateTime, default=datetime.utcnow)
    last_login = db.Column(db.DateTime)
    terms_accepted = db.Column(db.Boolean, default=False)
    email_verified = db.Column(db.Boolean, default=False)
    
    # Relationships
    jobs_posted = db.relationship('Job', foreign_keys='Job.contractor_id', backref='contractor', lazy='dynamic')
    applications = db.relationship('Application', foreign_keys='Application.worker_id', backref='worker', lazy='dynamic')
    reviews_given = db.relationship('Review', foreign_keys='Review.reviewer_id', backref='reviewer', lazy='dynamic')
    reviews_received = db.relationship('Review', foreign_keys='Review.reviewee_id', backref='reviewee', lazy='dynamic')

class Category(db.Model):
    __tablename__ = 'categories'
    
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    description = db.Column(db.Text)
    parent_id = db.Column(db.Integer, db.ForeignKey('categories.id'))
    is_active = db.Column(db.Boolean, default=True)
    
    # Self-referential relationship
    subcategories = db.relationship('Category', backref=db.backref('parent', remote_side=[id]))
    jobs = db.relationship('Job', backref='category', lazy='dynamic')

class Job(db.Model):
    __tablename__ = 'jobs'
    
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
    description = db.Column(db.Text, nullable=False)
    contractor_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    category_id = db.Column(db.Integer, db.ForeignKey('categories.id'), nullable=False)
    location = db.Column(db.String(100), nullable=False)
    budget_min = db.Column(db.Numeric(10,2))
    budget_max = db.Column(db.Numeric(10,2))
    status = db.Column(db.String(20), default='open')  # open, assigned, completed, cancelled
    date_posted = db.Column(db.DateTime, default=datetime.utcnow)
    deadline = db.Column(db.DateTime)
    applications_count = db.Column(db.Integer, default=0)
    
    # Relationships
    applications = db.relationship('Application', backref='job', lazy='dynamic', cascade='all, delete-orphan')

class Application(db.Model):
    __tablename__ = 'applications'
    
    id = db.Column(db.Integer, primary_key=True)
    job_id = db.Column(db.Integer, db.ForeignKey('jobs.id'), nullable=False)
    worker_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    status = db.Column(db.String(20), default='pending')  # pending, accepted, rejected
    proposed_rate = db.Column(db.Numeric(10,2))
    cover_letter = db.Column(db.Text)
    date_applied = db.Column(db.DateTime, default=datetime.utcnow)

class Review(db.Model):
    __tablename__ = 'reviews'
    
    id = db.Column(db.Integer, primary_key=True)
    reviewer_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    reviewee_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    job_id = db.Column(db.Integer, db.ForeignKey('jobs.id'))
    rating = db.Column(db.Integer, nullable=False)  # 1-5 stars
    comment = db.Column(db.Text)
    date_created = db.Column(db.DateTime, default=datetime.utcnow)
```

### **2. DATABASE CONFIGURATION**
```python
# app.py - Database setup
import os
from flask import Flask
from models import db

def create_app():
    app = Flask(__name__)
    
    # SQLite configuration (stable, no SSL issues)
    basedir = os.path.abspath(os.path.dirname(__file__))
    app.config['SQLALCHEMY_DATABASE_URI'] = f'sqlite:///{os.path.join(basedir, "rateright.db")}'
    app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
    app.config['SECRET_KEY'] = 'your-secret-key-here'
    
    # Initialize database
    db.init_app(app)
    
    return app

if __name__ == '__main__':
    app = create_app()
    
    with app.app_context():
        # Create all tables
        db.create_all()
        print("Database tables created successfully!")
    
    app.run(debug=True)
```

### **3. SEED DATABASE WITH TEST DATA**
```python
# seed_data.py - Populate database with realistic test data
from app import create_app
from models import db, User, Category, Job, Application, Review
from werkzeug.security import generate_password_hash
from datetime import datetime, timedelta

def seed_database():
    app = create_app()
    
    with app.app_context():
        # Clear existing data
        db.drop_all()
        db.create_all()
        
        # Create Categories (Australian construction trades)
        categories = [
            Category(name='Electrical', description='Electrical work and installations'),
            Category(name='Plumbing', description='Plumbing and water systems'),
            Category(name='Carpentry', description='Woodworking and construction'),
            Category(name='Concrete & Formwork', description='Concrete work and formwork'),
            Category(name='Painting', description='Interior and exterior painting'),
            Category(name='Roofing', description='Roof installation and repairs'),
            Category(name='Landscaping', description='Garden and outdoor work'),
            Category(name='Tiling', description='Floor and wall tiling'),
            Category(name='HVAC', description='Heating, ventilation, air conditioning'),
            Category(name='General Handyman', description='Various maintenance tasks')
        ]
        
        for category in categories:
            db.session.add(category)
        
        # Create Test Users
        users = [
            # Contractors
            User(
                email='contractor1@test.com',
                password_hash=generate_password_hash('password123'),
                first_name='John',
                last_name='Smith',
                role='contractor',
                phone_number='0412345678',
                location='Sydney, NSW',
                primary_trade='General Contractor',
                abn_number='12345678901',
                jobs_completed=25,
                average_rating=4.7,
                total_reviews=20,
                total_points=1250,
                current_level=8,
                seasonal_league='gold',
                terms_accepted=True,
                email_verified=True
            ),
            User(
                email='contractor2@test.com',
                password_hash=generate_password_hash('password123'),
                first_name='Sarah',
                last_name='Wilson',
                role='contractor',
                phone_number='0498765432',
                location='Melbourne, VIC',
                primary_trade='Renovation Specialist',
                abn_number='98765432109',
                jobs_completed=15,
                average_rating=4.9,
                total_reviews=12,
                total_points=900,
                current_level=6,
                seasonal_league='silver',
                terms_accepted=True,
                email_verified=True
            ),
            
            # Workers
            User(
                email='worker1@test.com',
                password_hash=generate_password_hash('password123'),
                first_name='Michael',
                last_name='McLoughlin',
                role='worker',
                phone_number='0426246472',
                location='Sydney, NSW',
                primary_trade='Formwork Specialist',
                jobs_completed=87,
                average_rating=4.8,
                total_reviews=65,
                response_rate=95.0,
                total_points=2150,
                current_level=15,
                seasonal_league='gold',
                terms_accepted=True,
                email_verified=True
            ),
            User(
                email='worker2@test.com',
                password_hash=generate_password_hash('password123'),
                first_name='David',
                last_name='Chen',
                role='worker',
                phone_number='0455123789',
                location='Brisbane, QLD',
                primary_trade='Electrician',
                jobs_completed=42,
                average_rating=4.6,
                total_reviews=38,
                response_rate=88.0,
                total_points=1680,
                current_level=12,
                seasonal_league='silver',
                terms_accepted=True,
                email_verified=True
            ),
            User(
                email='worker3@test.com',
                password_hash=generate_password_hash('password123'),
                first_name='Emma',
                last_name='Taylor',
                role='worker',
                phone_number='0467891234',
                location='Perth, WA',
                primary_trade='Carpenter',
                jobs_completed=28,
                average_rating=4.9,
                total_reviews=22,
                response_rate=92.0,
                total_points=1120,
                current_level=8,
                seasonal_league='bronze',
                terms_accepted=True,
                email_verified=True
            )
        ]
        
        for user in users:
            db.session.add(user)
        
        db.session.commit()
        
        # Create Test Jobs
        jobs = [
            Job(
                title='Kitchen Renovation - Electrical Work',
                description='Need qualified electrician for complete kitchen renovation. Install new powerpoints, lighting, and appliance connections.',
                contractor_id=1,
                category_id=1,  # Electrical
                location='Sydney, NSW',
                budget_min=2000,
                budget_max=3500,
                status='open',
                deadline=datetime.now() + timedelta(days=14)
            ),
            Job(
                title='Bathroom Formwork for New Build',
                description='Require experienced formwork specialist for bathroom construction in new residential build.',
                contractor_id=1,
                category_id=4,  # Concrete & Formwork
                location='Sydney, NSW',
                budget_min=1500,
                budget_max=2500,
                status='open',
                deadline=datetime.now() + timedelta(days=10)
            ),
            Job(
                title='Deck Construction - Carpentry',
                description='Build new timber deck, 6m x 4m, including railings and stairs.',
                contractor_id=2,
                category_id=3,  # Carpentry
                location='Melbourne, VIC',
                budget_min=3000,
                budget_max=5000,
                status='open',
                deadline=datetime.now() + timedelta(days=21)
            )
        ]
        
        for job in jobs:
            db.session.add(job)
        
        db.session.commit()
        
        # Create Test Applications
        applications = [
            Application(
                job_id=1,
                worker_id=4,  # David Chen (Electrician)
                status='pending',
                proposed_rate=2800,
                cover_letter='I have 8 years experience with kitchen electrical work and am available to start immediately.'
            ),
            Application(
                job_id=2,
                worker_id=3,  # Michael McLoughlin (Formwork)
                status='pending',
                proposed_rate=2000,
                cover_letter='Formwork specialist with 12 years experience. Have done similar bathroom projects.'
            )
        ]
        
        for application in applications:
            db.session.add(application)
        
        db.session.commit()
        
        print("Database seeded successfully!")
        print(f"Created {len(categories)} categories")
        print(f"Created {len(users)} users")
        print(f"Created {len(jobs)} jobs") 
        print(f"Created {len(applications)} applications")

if __name__ == '__main__':
    seed_database()
```

### **4. TEST DATABASE THOROUGHLY**
```python
# test_database.py - Verify all relationships work
from app import create_app
from models import db, User, Job, Application, Category

def test_database():
    app = create_app()
    
    with app.app_context():
        # Test user queries
        contractors = User.query.filter_by(role='contractor').all()
        workers = User.query.filter_by(role='worker').all()
        
        print(f"Found {len(contractors)} contractors")
        print(f"Found {len(workers)} workers")
        
        # Test job relationships
        for job in Job.query.all():
            print(f"Job: {job.title}")
            print(f"  Contractor: {job.contractor.first_name} {job.contractor.last_name}")
            print(f"  Category: {job.category.name}")
            print(f"  Applications: {job.applications.count()}")
        
        # Test application relationships
        for app in Application.query.all():
            print(f"Application by {app.worker.first_name} for '{app.job.title}'")
        
        print("Database test completed successfully!")

if __name__ == '__main__':
    test_database()
```

---

## 🔧 PHASE 2: APPLICATION LAYER (Hours 5-8)

### **ONLY AFTER DATABASE IS 100% WORKING:**

1. **Authentication System** (builds on User model)
2. **Dashboard Routes** (uses database relationships)
3. **Job Management** (uses Job/Application models)
4. **Basic Templates** (displays database data)

---

## ✅ SUCCESS CRITERIA

### **Database Phase Complete When:**
- [x] All 5 models created without errors
- [x] Database seeded with realistic test data
- [x] All relationships working (job.contractor, application.worker, etc.)
- [x] Test queries return expected results
- [x] No foreign key errors
- [x] SQLite file created and stable

### **Application Phase Can Begin When:**
- Database foundation is rock solid
- All test data loads correctly
- Relationships proven to work

## 🚨 CRITICAL: DO NOT BUILD ROUTES UNTIL DATABASE IS PERFECT

**Last time: Built 70% of app → realized database was broken**  
**This time: Build 100% of database → build app on solid foundation**