Autoparts-DB/docs/plans/2026-03-15-saas-aftermarket-plan.md

# SaaS + Aftermarket Implementation Plan

> **For Claude:** REQUIRED SUB-SKILL: Use superpowers:executing-plans to implement this plan task-by-task.

**Goal:** Add JWT authentication with role-based access, warehouse inventory uploads with flexible column mapping, and migrate 357K AFT- aftermarket parts to their proper table.

**Architecture:** Monolith Flask + PostgreSQL. Auth via JWT (PyJWT + bcrypt). File uploads via openpyxl/csv. All new tables in same DB. Middleware decorator for route protection.

**Tech Stack:** Flask, PostgreSQL, PyJWT, bcrypt (already installed), openpyxl (new), python-dotenv (optional)

---

## Task 1: Install Dependencies & Update Config

**Files:**
- Modify: `/home/Autopartes/requirements.txt`
- Modify: `/home/Autopartes/config.py`

**Step 1: Install new packages**

Run:
```bash
pip install PyJWT openpyxl
```

**Step 2: Update requirements.txt**

Add to `/home/Autopartes/requirements.txt`:
```
PyJWT>=2.8
openpyxl>=3.1
```

**Step 3: Add JWT config to config.py**

Add to `/home/Autopartes/config.py`:
```python
import os
JWT_SECRET = os.environ.get("JWT_SECRET", "nexus-saas-secret-change-in-prod-2026")
JWT_ACCESS_EXPIRES = 900       # 15 minutes
JWT_REFRESH_EXPIRES = 2592000  # 30 days
```

**Step 4: Commit**

```bash
git add requirements.txt config.py
git commit -m "feat: add JWT and openpyxl dependencies"
```

---

## Task 2: Database Schema Changes

**Files:**
- Create: `/home/Autopartes/scripts/migrate_saas_schema.py`

**Step 1: Write migration script**

Create `/home/Autopartes/scripts/migrate_saas_schema.py`:
```python
#!/usr/bin/env python3
"""Add SaaS tables: sessions, warehouse_inventory, inventory_uploads, inventory_column_mappings.
   Alter users table: add business_name, is_active, last_login.
   Update roles to new names."""

import psycopg2

DB_URL = "postgresql://nexus:nexus_autoparts_2026@localhost/nexus_autoparts"

def run():
    conn = psycopg2.connect(DB_URL)
    conn.autocommit = True
    cur = conn.cursor()

    # Update role names
    print("Updating roles...")
    cur.execute("UPDATE roles SET name_rol = 'ADMIN' WHERE id_rol = 1")
    cur.execute("UPDATE roles SET name_rol = 'OWNER' WHERE id_rol = 2")
    cur.execute("UPDATE roles SET name_rol = 'TALLER' WHERE id_rol = 3")
    cur.execute("UPDATE roles SET name_rol = 'BODEGA' WHERE id_rol = 4")
    # Insert if missing
    cur.execute("""
        INSERT INTO roles (id_rol, name_rol) VALUES (3, 'TALLER')
        ON CONFLICT (id_rol) DO UPDATE SET name_rol = EXCLUDED.name_rol
    """)
    cur.execute("""
        INSERT INTO roles (id_rol, name_rol) VALUES (4, 'BODEGA')
        ON CONFLICT (id_rol) DO UPDATE SET name_rol = EXCLUDED.name_rol
    """)

    # Alter users table
    print("Altering users table...")
    alterations = [
        "ALTER TABLE users ADD COLUMN IF NOT EXISTS business_name VARCHAR(200)",
        "ALTER TABLE users ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT false",
        "ALTER TABLE users ADD COLUMN IF NOT EXISTS created_at TIMESTAMP DEFAULT now()",
        "ALTER TABLE users ADD COLUMN IF NOT EXISTS last_login TIMESTAMP",
    ]
    for sql in alterations:
        try:
            cur.execute(sql)
        except Exception as e:
            print(f"  Skip: {e}")

    # Make email unique if not already
    cur.execute("""
        DO $$ BEGIN
            IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE indexname = 'users_email_unique') THEN
                CREATE UNIQUE INDEX users_email_unique ON users(email);
            END IF;
        END $$;
    """)

    # Sessions table
    print("Creating sessions table...")
    cur.execute("""
        CREATE TABLE IF NOT EXISTS sessions (
            id_session SERIAL PRIMARY KEY,
            user_id INTEGER NOT NULL REFERENCES users(id_user) ON DELETE CASCADE,
            refresh_token VARCHAR(500) UNIQUE NOT NULL,
            expires_at TIMESTAMP NOT NULL,
            created_at TIMESTAMP DEFAULT now()
        )
    """)
    cur.execute("CREATE INDEX IF NOT EXISTS idx_sessions_token ON sessions(refresh_token)")
    cur.execute("CREATE INDEX IF NOT EXISTS idx_sessions_user ON sessions(user_id)")

    # Warehouse inventory table
    print("Creating warehouse_inventory table...")
    cur.execute("""
        CREATE TABLE IF NOT EXISTS warehouse_inventory (
            id_inventory BIGSERIAL PRIMARY KEY,
            user_id INTEGER NOT NULL REFERENCES users(id_user) ON DELETE CASCADE,
            part_id INTEGER NOT NULL REFERENCES parts(id_part) ON DELETE CASCADE,
            price NUMERIC(12,2),
            stock_quantity INTEGER DEFAULT 0,
            min_order_quantity INTEGER DEFAULT 1,
            warehouse_location VARCHAR(100) DEFAULT 'Principal',
            updated_at TIMESTAMP DEFAULT now(),
            UNIQUE(user_id, part_id, warehouse_location)
        )
    """)
    cur.execute("CREATE INDEX IF NOT EXISTS idx_wi_part ON warehouse_inventory(part_id)")
    cur.execute("CREATE INDEX IF NOT EXISTS idx_wi_user ON warehouse_inventory(user_id)")

    # Inventory uploads table
    print("Creating inventory_uploads table...")
    cur.execute("""
        CREATE TABLE IF NOT EXISTS inventory_uploads (
            id_upload SERIAL PRIMARY KEY,
            user_id INTEGER NOT NULL REFERENCES users(id_user) ON DELETE CASCADE,
            filename VARCHAR(200),
            status VARCHAR(20) DEFAULT 'pending',
            rows_total INTEGER DEFAULT 0,
            rows_imported INTEGER DEFAULT 0,
            rows_errors INTEGER DEFAULT 0,
            error_log TEXT,
            created_at TIMESTAMP DEFAULT now(),
            completed_at TIMESTAMP
        )
    """)

    # Column mappings table
    print("Creating inventory_column_mappings table...")
    cur.execute("""
        CREATE TABLE IF NOT EXISTS inventory_column_mappings (
            id_mapping SERIAL PRIMARY KEY,
            user_id INTEGER NOT NULL REFERENCES users(id_user) ON DELETE CASCADE UNIQUE,
            mapping JSONB NOT NULL DEFAULT '{}'::jsonb
        )
    """)

    # Set existing admin user as active
    cur.execute("UPDATE users SET is_active = true WHERE id_rol = 1")

    cur.close()
    conn.close()
    print("Migration complete!")

if __name__ == "__main__":
    run()
```

**Step 2: Run migration**

Run:
```bash
python3 /home/Autopartes/scripts/migrate_saas_schema.py
```

Expected: All tables created, roles updated, existing admin set active.

**Step 3: Verify**

Run:
```bash
PGPASSWORD=nexus_autoparts_2026 psql -U nexus -d nexus_autoparts -h localhost \
  -c "SELECT * FROM roles;" \
  -c "\d sessions" \
  -c "\d warehouse_inventory" \
  -c "\d inventory_uploads" \
  -c "\d inventory_column_mappings"
```

**Step 4: Commit**

```bash
git add scripts/migrate_saas_schema.py
git commit -m "feat: add SaaS schema — sessions, inventory, mappings tables"
```

---

## Task 3: Auth Middleware & Helpers

**Files:**
- Create: `/home/Autopartes/dashboard/auth.py`

**Step 1: Create auth module**

Create `/home/Autopartes/dashboard/auth.py`:
```python
"""JWT authentication helpers for Nexus Autoparts SaaS."""

import jwt
import bcrypt
import psycopg2
import secrets
from datetime import datetime, timedelta, timezone
from functools import wraps
from flask import request, jsonify, g

import sys
sys.path.insert(0, '/home/Autopartes')
from config import DB_URL, JWT_SECRET, JWT_ACCESS_EXPIRES, JWT_REFRESH_EXPIRES


def get_db():
    """Get a psycopg2 connection."""
    return psycopg2.connect(DB_URL)


def hash_password(password):
    return bcrypt.hashpw(password.encode('utf-8'), bcrypt.gensalt()).decode('utf-8')


def check_password(password, hashed):
    return bcrypt.checkpw(password.encode('utf-8'), hashed.encode('utf-8'))


def create_access_token(user_id, role, business_name):
    payload = {
        'user_id': user_id,
        'role': role,
        'business_name': business_name or '',
        'exp': datetime.now(timezone.utc) + timedelta(seconds=JWT_ACCESS_EXPIRES),
        'type': 'access'
    }
    return jwt.encode(payload, JWT_SECRET, algorithm='HS256')


def create_refresh_token(user_id):
    token = secrets.token_urlsafe(64)
    expires = datetime.now(timezone.utc) + timedelta(seconds=JWT_REFRESH_EXPIRES)
    conn = get_db()
    cur = conn.cursor()
    cur.execute(
        "INSERT INTO sessions (user_id, refresh_token, expires_at) VALUES (%s, %s, %s)",
        (user_id, token, expires)
    )
    conn.commit()
    cur.close()
    conn.close()
    return token


def decode_token(token):
    try:
        return jwt.decode(token, JWT_SECRET, algorithms=['HS256'])
    except jwt.ExpiredSignatureError:
        return None
    except jwt.InvalidTokenError:
        return None


def require_auth(*allowed_roles):
    """Decorator: require JWT with optional role check.
    Usage:
        @require_auth()                  # any authenticated user
        @require_auth('ADMIN', 'OWNER')  # only these roles
        @require_auth('BODEGA')          # only bodegas
    """
    def decorator(f):
        @wraps(f)
        def decorated(*args, **kwargs):
            auth_header = request.headers.get('Authorization', '')
            if not auth_header.startswith('Bearer '):
                return jsonify({'error': 'Token required'}), 401

            payload = decode_token(auth_header[7:])
            if not payload or payload.get('type') != 'access':
                return jsonify({'error': 'Invalid or expired token'}), 401

            if allowed_roles and payload['role'] not in allowed_roles:
                return jsonify({'error': 'Insufficient permissions'}), 403

            g.user = payload
            return f(*args, **kwargs)
        return decorated
    return decorator
```

**Step 2: Commit**

```bash
git add dashboard/auth.py
git commit -m "feat: add JWT auth module with middleware decorator"
```

---

## Task 4: Auth API Endpoints

**Files:**
- Modify: `/home/Autopartes/dashboard/server.py` (add auth routes)

**Step 1: Add imports at top of server.py**

After existing imports (around line 12), add:
```python
from auth import (
    hash_password, check_password,
    create_access_token, create_refresh_token,
    decode_token, require_auth
)
```

**Step 2: Add auth endpoints**

Add before the `if __name__ == '__main__'` block at the end of server.py:

```python
# ─── Auth Endpoints ─────────────────────────────────────────

@app.route('/api/auth/register', methods=['POST'])
def auth_register():
    data = request.json or {}
    required = ['name', 'email', 'password', 'role']
    for f in required:
        if not data.get(f):
            return jsonify({'error': f'Missing field: {f}'}), 400

    role = data['role'].upper()
    if role not in ('TALLER', 'BODEGA'):
        return jsonify({'error': 'Role must be TALLER or BODEGA'}), 400

    role_map = {'TALLER': 3, 'BODEGA': 4}
    hashed = hash_password(data['password'])

    conn = get_db_connection()
    cur = conn.cursor()
    try:
        cur.execute("""
            INSERT INTO users (name_user, email, pass, id_rol, business_name, phone, address, is_active)
            VALUES (%s, %s, %s, %s, %s, %s, %s, false)
            RETURNING id_user
        """, (data['name'], data['email'], hashed, role_map[role],
              data.get('business_name', ''), data.get('phone', ''), data.get('address', '')))
        user_id = cur.fetchone()[0]
        conn.commit()
        return jsonify({'message': 'Account created. Pending admin approval.', 'user_id': user_id}), 201
    except Exception as e:
        conn.rollback()
        if 'users_email_unique' in str(e) or 'duplicate' in str(e).lower():
            return jsonify({'error': 'Email already registered'}), 409
        return jsonify({'error': str(e)}), 500
    finally:
        cur.close()
        conn.close()


@app.route('/api/auth/login', methods=['POST'])
def auth_login():
    data = request.json or {}
    email = data.get('email', '')
    password = data.get('password', '')
    if not email or not password:
        return jsonify({'error': 'Email and password required'}), 400

    conn = get_db_connection()
    cur = conn.cursor()
    cur.execute("""
        SELECT u.id_user, u.name_user, u.pass, u.is_active, u.business_name, r.name_rol
        FROM users u JOIN roles r ON u.id_rol = r.id_rol
        WHERE u.email = %s
    """, (email,))
    row = cur.fetchone()

    if not row:
        cur.close(); conn.close()
        return jsonify({'error': 'Invalid credentials'}), 401

    user_id, name, hashed, is_active, business, role = row

    if not hashed or not check_password(password, hashed):
        cur.close(); conn.close()
        return jsonify({'error': 'Invalid credentials'}), 401

    if not is_active:
        cur.close(); conn.close()
        return jsonify({'error': 'Account pending approval'}), 403

    # Update last_login
    cur.execute("UPDATE users SET last_login = now() WHERE id_user = %s", (user_id,))
    conn.commit()
    cur.close()
    conn.close()

    access = create_access_token(user_id, role, business)
    refresh = create_refresh_token(user_id)

    return jsonify({
        'access_token': access,
        'refresh_token': refresh,
        'user': {'id': user_id, 'name': name, 'role': role, 'business_name': business}
    })


@app.route('/api/auth/refresh', methods=['POST'])
def auth_refresh():
    data = request.json or {}
    token = data.get('refresh_token', '')
    if not token:
        return jsonify({'error': 'Refresh token required'}), 400

    conn = get_db_connection()
    cur = conn.cursor()
    cur.execute("""
        SELECT s.user_id, s.expires_at, u.name_user, u.business_name, r.name_rol
        FROM sessions s
        JOIN users u ON s.user_id = u.id_user
        JOIN roles r ON u.id_rol = r.id_rol
        WHERE s.refresh_token = %s
    """, (token,))
    row = cur.fetchone()

    if not row:
        cur.close(); conn.close()
        return jsonify({'error': 'Invalid refresh token'}), 401

    user_id, expires, name, business, role = row
    from datetime import datetime, timezone
    if expires.replace(tzinfo=timezone.utc) < datetime.now(timezone.utc):
        cur.execute("DELETE FROM sessions WHERE refresh_token = %s", (token,))
        conn.commit(); cur.close(); conn.close()
        return jsonify({'error': 'Refresh token expired'}), 401

    cur.close()
    conn.close()
    access = create_access_token(user_id, role, business)
    return jsonify({'access_token': access})


@app.route('/api/auth/me', methods=['GET'])
@require_auth()
def auth_me():
    return jsonify({'user': g.user})
```

**Step 3: Commit**

```bash
git add dashboard/server.py
git commit -m "feat: add auth endpoints — register, login, refresh, me"
```

---

## Task 5: Admin User Management Endpoints

**Files:**
- Modify: `/home/Autopartes/dashboard/server.py`

**Step 1: Add admin user endpoints**

Add after the auth endpoints in server.py:

```python
# ─── Admin User Management ──────────────────────────────────

@app.route('/api/admin/users', methods=['GET'])
@require_auth('ADMIN', 'OWNER')
def admin_list_users():
    conn = get_db_connection()
    cur = conn.cursor()
    cur.execute("""
        SELECT u.id_user, u.name_user, u.email, u.business_name,
               u.phone, u.is_active, u.created_at, u.last_login, r.name_rol
        FROM users u JOIN roles r ON u.id_rol = r.id_rol
        ORDER BY u.created_at DESC
    """)
    users = []
    for row in cur.fetchall():
        users.append({
            'id': row[0], 'name': row[1], 'email': row[2],
            'business_name': row[3], 'phone': row[4],
            'is_active': row[5], 'created_at': str(row[6]),
            'last_login': str(row[7]) if row[7] else None, 'role': row[8]
        })
    cur.close(); conn.close()
    return jsonify({'data': users})


@app.route('/api/admin/users/<int:user_id>/activate', methods=['PUT'])
@require_auth('ADMIN', 'OWNER')
def admin_toggle_user(user_id):
    data = request.json or {}
    active = data.get('is_active', True)
    conn = get_db_connection()
    cur = conn.cursor()
    cur.execute("UPDATE users SET is_active = %s WHERE id_user = %s", (active, user_id))
    conn.commit()
    cur.close(); conn.close()
    return jsonify({'message': f'User {"activated" if active else "deactivated"}'})
```

**Step 2: Commit**

```bash
git add dashboard/server.py
git commit -m "feat: add admin user management endpoints"
```

---

## Task 6: Inventory Upload & Mapping Endpoints

**Files:**
- Modify: `/home/Autopartes/dashboard/server.py`

**Step 1: Add inventory endpoints**

Add after admin user endpoints in server.py:

```python
# ─── Inventory Endpoints (BODEGA) ───────────────────────────

import csv
import io
import openpyxl
from datetime import datetime, timezone

@app.route('/api/inventory/mapping', methods=['GET'])
@require_auth('BODEGA', 'ADMIN')
def get_inventory_mapping():
    user_id = g.user['user_id']
    conn = get_db_connection()
    cur = conn.cursor()
    cur.execute("SELECT mapping FROM inventory_column_mappings WHERE user_id = %s", (user_id,))
    row = cur.fetchone()
    cur.close(); conn.close()
    return jsonify({'mapping': row[0] if row else {}})


@app.route('/api/inventory/mapping', methods=['PUT'])
@require_auth('BODEGA', 'ADMIN')
def set_inventory_mapping():
    user_id = g.user['user_id']
    mapping = (request.json or {}).get('mapping', {})
    required_keys = ['part_number', 'price', 'stock']
    for k in required_keys:
        if k not in mapping:
            return jsonify({'error': f'Mapping must include: {", ".join(required_keys)}'}), 400

    conn = get_db_connection()
    cur = conn.cursor()
    cur.execute("""
        INSERT INTO inventory_column_mappings (user_id, mapping)
        VALUES (%s, %s::jsonb)
        ON CONFLICT (user_id) DO UPDATE SET mapping = EXCLUDED.mapping
    """, (user_id, json.dumps(mapping)))
    conn.commit()
    cur.close(); conn.close()
    return jsonify({'message': 'Mapping saved'})


@app.route('/api/inventory/upload', methods=['POST'])
@require_auth('BODEGA', 'ADMIN')
def upload_inventory():
    user_id = g.user['user_id']
    if 'file' not in request.files:
        return jsonify({'error': 'No file uploaded'}), 400

    file = request.files['file']
    filename = file.filename or 'unknown'

    # Get mapping
    conn = get_db_connection()
    cur = conn.cursor()
    cur.execute("SELECT mapping FROM inventory_column_mappings WHERE user_id = %s", (user_id,))
    row = cur.fetchone()
    if not row:
        cur.close(); conn.close()
        return jsonify({'error': 'Configure column mapping first'}), 400
    mapping = row[0]

    # Create upload record
    cur.execute("""
        INSERT INTO inventory_uploads (user_id, filename, status)
        VALUES (%s, %s, 'processing') RETURNING id_upload
    """, (user_id, filename))
    upload_id = cur.fetchone()[0]
    conn.commit()

    # Parse file
    try:
        rows = []
        if filename.endswith(('.xlsx', '.xls')):
            wb = openpyxl.load_workbook(io.BytesIO(file.read()), read_only=True)
            ws = wb.active
            headers = [str(c.value or '').strip() for c in next(ws.iter_rows(min_row=1, max_row=1))]
            for row_cells in ws.iter_rows(min_row=2, values_only=True):
                rows.append({headers[i]: row_cells[i] for i in range(len(headers)) if i < len(row_cells)})
            wb.close()
        else:  # CSV
            content = file.read().decode('utf-8-sig')
            reader = csv.DictReader(io.StringIO(content))
            rows = list(reader)

        # Apply mapping and upsert
        imported = 0
        errors = []
        part_col = mapping['part_number']
        price_col = mapping['price']
        stock_col = mapping['stock']
        location_col = mapping.get('location')

        for i, r in enumerate(rows):
            try:
                part_num = str(r.get(part_col, '')).strip()
                if not part_num:
                    continue
                price = float(str(r.get(price_col, 0)).replace(',', '').replace('$', ''))
                stock = int(float(str(r.get(stock_col, 0)).replace(',', '')))
                location = str(r.get(location_col, 'Principal')).strip() if location_col else 'Principal'

                # Find part by oem_part_number
                cur.execute("SELECT id_part FROM parts WHERE oem_part_number = %s", (part_num,))
                part_row = cur.fetchone()
                if not part_row:
                    # Try aftermarket_parts
                    cur.execute("SELECT oem_part_id FROM aftermarket_parts WHERE part_number = %s LIMIT 1", (part_num,))
                    aft_row = cur.fetchone()
                    if aft_row:
                        part_id = aft_row[0]
                    else:
                        errors.append(f"Row {i+2}: Part '{part_num}' not found")
                        continue
                else:
                    part_id = part_row[0]

                cur.execute("""
                    INSERT INTO warehouse_inventory (user_id, part_id, price, stock_quantity, warehouse_location, updated_at)
                    VALUES (%s, %s, %s, %s, %s, now())
                    ON CONFLICT (user_id, part_id, warehouse_location)
                    DO UPDATE SET price = EXCLUDED.price, stock_quantity = EXCLUDED.stock_quantity, updated_at = now()
                """, (user_id, part_id, price, stock, location))
                imported += 1
            except Exception as e:
                errors.append(f"Row {i+2}: {str(e)[:100]}")

        conn.commit()

        # Update upload record
        cur.execute("""
            UPDATE inventory_uploads
            SET status = 'completed', rows_total = %s, rows_imported = %s,
                rows_errors = %s, error_log = %s, completed_at = now()
            WHERE id_upload = %s
        """, (len(rows), imported, len(errors), '\n'.join(errors[:100]), upload_id))
        conn.commit()

        cur.close(); conn.close()
        return jsonify({
            'message': f'Imported {imported}/{len(rows)} rows',
            'upload_id': upload_id,
            'imported': imported,
            'errors': len(errors),
            'error_samples': errors[:10]
        })

    except Exception as e:
        cur.execute("""
            UPDATE inventory_uploads SET status = 'failed', error_log = %s, completed_at = now()
            WHERE id_upload = %s
        """, (str(e), upload_id))
        conn.commit(); cur.close(); conn.close()
        return jsonify({'error': f'File processing failed: {str(e)}'}), 500


@app.route('/api/inventory/uploads', methods=['GET'])
@require_auth('BODEGA', 'ADMIN')
def list_inventory_uploads():
    user_id = g.user['user_id']
    conn = get_db_connection()
    cur = conn.cursor()
    cur.execute("""
        SELECT id_upload, filename, status, rows_total, rows_imported, rows_errors, created_at, completed_at
        FROM inventory_uploads WHERE user_id = %s ORDER BY created_at DESC LIMIT 50
    """, (user_id,))
    uploads = []
    for r in cur.fetchall():
        uploads.append({
            'id': r[0], 'filename': r[1], 'status': r[2],
            'rows_total': r[3], 'rows_imported': r[4], 'rows_errors': r[5],
            'created_at': str(r[6]), 'completed_at': str(r[7]) if r[7] else None
        })
    cur.close(); conn.close()
    return jsonify({'data': uploads})


@app.route('/api/inventory/items', methods=['GET'])
@require_auth('BODEGA', 'ADMIN')
def list_inventory_items():
    user_id = g.user['user_id']
    page = int(request.args.get('page', 1))
    per_page = min(int(request.args.get('per_page', 50)), 200)
    search = request.args.get('q', '')

    conn = get_db_connection()
    cur = conn.cursor()

    where = "WHERE wi.user_id = %s"
    params = [user_id]
    if search:
        where += " AND (p.oem_part_number ILIKE %s OR p.name_part ILIKE %s)"
        params.extend([f'%{search}%', f'%{search}%'])

    cur.execute(f"SELECT count(*) FROM warehouse_inventory wi JOIN parts p ON wi.part_id = p.id_part {where}", params)
    total = cur.fetchone()[0]

    cur.execute(f"""
        SELECT wi.id_inventory, p.oem_part_number, p.name_part, wi.price,
               wi.stock_quantity, wi.warehouse_location, wi.updated_at
        FROM warehouse_inventory wi
        JOIN parts p ON wi.part_id = p.id_part
        {where}
        ORDER BY wi.updated_at DESC
        LIMIT %s OFFSET %s
    """, params + [per_page, (page - 1) * per_page])

    items = []
    for r in cur.fetchall():
        items.append({
            'id': r[0], 'part_number': r[1], 'part_name': r[2],
            'price': float(r[3]) if r[3] else None,
            'stock': r[4], 'location': r[5], 'updated_at': str(r[6])
        })
    cur.close(); conn.close()

    total_pages = (total + per_page - 1) // per_page
    return jsonify({
        'data': items,
        'pagination': {'page': page, 'per_page': per_page, 'total': total, 'total_pages': total_pages}
    })


@app.route('/api/inventory/items', methods=['DELETE'])
@require_auth('BODEGA', 'ADMIN')
def clear_inventory():
    user_id = g.user['user_id']
    conn = get_db_connection()
    cur = conn.cursor()
    cur.execute("DELETE FROM warehouse_inventory WHERE user_id = %s", (user_id,))
    deleted = cur.rowcount
    conn.commit()
    cur.close(); conn.close()
    return jsonify({'message': f'Deleted {deleted} inventory items'})
```

**Step 2: Commit**

```bash
git add dashboard/server.py
git commit -m "feat: add inventory upload, mapping, and listing endpoints"
```

---

## Task 7: Part Availability & Aftermarket Endpoints

**Files:**
- Modify: `/home/Autopartes/dashboard/server.py`

**Step 1: Add availability and aftermarket endpoints**

Add after inventory endpoints in server.py:

```python
# ─── Part Availability & Aftermarket ────────────────────────

@app.route('/api/parts/<int:part_id>/availability', methods=['GET'])
@require_auth('TALLER', 'ADMIN', 'OWNER')
def part_availability(part_id):
    conn = get_db_connection()
    cur = conn.cursor()
    cur.execute("""
        SELECT u.business_name, wi.price, wi.stock_quantity,
               wi.warehouse_location, wi.updated_at
        FROM warehouse_inventory wi
        JOIN users u ON wi.user_id = u.id_user
        WHERE wi.part_id = %s AND wi.stock_quantity > 0 AND u.is_active = true
        ORDER BY wi.price ASC
    """, (part_id,))
    results = []
    for r in cur.fetchall():
        results.append({
            'bodega': r[0], 'price': float(r[1]) if r[1] else None,
            'stock': r[2], 'location': r[3], 'updated_at': str(r[4])
        })
    cur.close(); conn.close()
    return jsonify({'data': results})


@app.route('/api/parts/<int:part_id>/aftermarket', methods=['GET'])
def part_aftermarket(part_id):
    conn = get_db_connection()
    cur = conn.cursor()
    cur.execute("""
        SELECT ap.id_aftermarket_parts, ap.part_number, ap.name_aftermarket_parts,
               m.name_manufacture, qt.name_quality, ap.price_usd
        FROM aftermarket_parts ap
        LEFT JOIN manufacturers m ON ap.manufacturer_id = m.id_manufacture
        LEFT JOIN quality_tier qt ON ap.id_quality_tier = qt.id_quality_tier
        WHERE ap.oem_part_id = %s
        ORDER BY m.name_manufacture
    """, (part_id,))
    results = []
    for r in cur.fetchall():
        results.append({
            'id': r[0], 'part_number': r[1], 'name': r[2],
            'manufacturer': r[3], 'quality_tier': r[4],
            'price': float(r[5]) if r[5] else None
        })

    # Also include cross-references as aftermarket alternatives
    cur.execute("""
        SELECT pcr.cross_reference_number, pcr.source_ref, pcr.notes
        FROM part_cross_references pcr
        WHERE pcr.part_id = %s
        ORDER BY pcr.source_ref
    """, (part_id,))
    for r in cur.fetchall():
        results.append({
            'id': None, 'part_number': r[0], 'name': None,
            'manufacturer': r[1], 'quality_tier': None,
            'price': None, 'source': 'cross_reference'
        })

    cur.close(); conn.close()
    return jsonify({'data': results})
```

**Step 2: Commit**

```bash
git add dashboard/server.py
git commit -m "feat: add part availability and aftermarket endpoints"
```

---

## Task 8: Aftermarket Parts Migration Script

**Files:**
- Create: `/home/Autopartes/scripts/migrate_aftermarket.py`

**Step 1: Write migration script**

Create `/home/Autopartes/scripts/migrate_aftermarket.py`:
```python
#!/usr/bin/env python3
"""Migrate AFT- parts from `parts` table into `aftermarket_parts`.
   1. Parse AFT-{partNo}-{manufacturer} format
   2. Find or create manufacturer
   3. Find OEM part via cross-references or vehicle overlap
   4. Insert into aftermarket_parts
   5. Re-link vehicle_parts to OEM part
   6. Delete AFT- part from parts
"""

import psycopg2
from psycopg2.extras import execute_values

DB_URL = "postgresql://nexus:nexus_autoparts_2026@localhost/nexus_autoparts"
BATCH_SIZE = 5000


def parse_aft_key(oem_part_number, manufacturer_set):
    """Parse 'AFT-AC191-PARTQUIP' → ('AC191', 'PARTQUIP').
    The manufacturer is the longest suffix match after AFT- that exists in manufacturer_set.
    """
    if not oem_part_number.startswith('AFT-'):
        return None, None

    rest = oem_part_number[4:]  # Remove 'AFT-'
    # Try splitting from the right — manufacturer is last segment(s)
    parts = rest.rsplit('-', 1)
    if len(parts) == 2 and parts[1] in manufacturer_set:
        return parts[0], parts[1]

    # Try multi-word manufacturers (e.g., "MEAT & DORIA" won't have dashes)
    # But the format uses dashes as separators, so the manufacturer is always after the last dash
    # that leaves a valid manufacturer name
    segments = rest.split('-')
    for i in range(len(segments) - 1, 0, -1):
        candidate_mfr = '-'.join(segments[i:])
        candidate_part = '-'.join(segments[:i])
        if candidate_mfr in manufacturer_set:
            return candidate_part, candidate_mfr

    # Fallback: last segment is manufacturer
    if len(parts) == 2:
        return parts[0], parts[1]

    return rest, 'UNKNOWN'


def run():
    conn = psycopg2.connect(DB_URL)
    cur = conn.cursor()

    # Load manufacturer names
    print("Loading manufacturers...", flush=True)
    cur.execute("SELECT id_manufacture, name_manufacture FROM manufacturers")
    mfr_map = {r[1]: r[0] for r in cur.fetchall()}
    mfr_set = set(mfr_map.keys())
    print(f"  {len(mfr_set):,} manufacturers loaded", flush=True)

    # Load all AFT- parts
    print("Loading AFT- parts...", flush=True)
    cur.execute("""
        SELECT id_part, oem_part_number, name_part, description, group_id
        FROM parts WHERE oem_part_number LIKE 'AFT-%%'
    """)
    aft_parts = cur.fetchall()
    print(f"  {len(aft_parts):,} AFT- parts found", flush=True)

    stats = {'migrated': 0, 'no_oem': 0, 'mfr_created': 0, 'vp_relinked': 0, 'deleted': 0}

    for batch_start in range(0, len(aft_parts), BATCH_SIZE):
        batch = aft_parts[batch_start:batch_start + BATCH_SIZE]
        aft_inserts = []
        parts_to_delete = []
        vp_updates = []  # (old_part_id, new_part_id)

        for id_part, oem_pn, name_part, desc, group_id in batch:
            part_number, mfr_name = parse_aft_key(oem_pn, mfr_set)
            if not part_number:
                continue

            # Ensure manufacturer exists
            if mfr_name not in mfr_map:
                cur.execute("""
                    INSERT INTO manufacturers (name_manufacture)
                    VALUES (%s)
                    ON CONFLICT (name_manufacture) DO NOTHING
                    RETURNING id_manufacture
                """, (mfr_name,))
                row = cur.fetchone()
                if row:
                    mfr_map[mfr_name] = row[0]
                    mfr_set.add(mfr_name)
                    stats['mfr_created'] += 1
                else:
                    cur.execute("SELECT id_manufacture FROM manufacturers WHERE name_manufacture = %s", (mfr_name,))
                    mfr_map[mfr_name] = cur.fetchone()[0]

            mfr_id = mfr_map[mfr_name]

            # Find OEM part via cross-references
            cur.execute("""
                SELECT part_id FROM part_cross_references
                WHERE cross_reference_number = %s AND source_ref = %s
                LIMIT 1
            """, (part_number, mfr_name))
            oem_row = cur.fetchone()

            oem_part_id = None
            if oem_row:
                oem_part_id = oem_row[0]
            else:
                # Try finding OEM part linked to same vehicles
                cur.execute("""
                    SELECT DISTINCT vp2.part_id
                    FROM vehicle_parts vp1
                    JOIN vehicle_parts vp2 ON vp1.model_year_engine_id = vp2.model_year_engine_id
                    JOIN parts p ON vp2.part_id = p.id_part
                    WHERE vp1.part_id = %s
                    AND p.oem_part_number NOT LIKE 'AFT-%%'
                    AND p.group_id = %s
                    LIMIT 1
                """, (id_part, group_id))
                vp_row = cur.fetchone()
                if vp_row:
                    oem_part_id = vp_row[0]

            if not oem_part_id:
                stats['no_oem'] += 1
                continue

            # Queue aftermarket insert
            aft_inserts.append((oem_part_id, mfr_id, part_number, name_part))
            vp_updates.append((id_part, oem_part_id))
            parts_to_delete.append(id_part)

        # Batch insert aftermarket_parts
        if aft_inserts:
            execute_values(cur, """
                INSERT INTO aftermarket_parts (oem_part_id, manufacturer_id, part_number, name_aftermarket_parts)
                VALUES %s ON CONFLICT DO NOTHING
            """, aft_inserts, page_size=1000)

        # Re-link vehicle_parts
        for old_id, new_id in vp_updates:
            cur.execute("""
                UPDATE vehicle_parts SET part_id = %s
                WHERE part_id = %s
            """, (new_id, old_id))
            stats['vp_relinked'] += cur.rowcount

        # Delete migrated AFT- parts
        if parts_to_delete:
            # First delete cross-references pointing to these parts
            cur.execute(
                "DELETE FROM part_cross_references WHERE part_id = ANY(%s)",
                (parts_to_delete,)
            )
            cur.execute(
                "DELETE FROM parts WHERE id_part = ANY(%s)",
                (parts_to_delete,)
            )
            stats['deleted'] += len(parts_to_delete)

        conn.commit()
        stats['migrated'] += len(aft_inserts)
        print(f"  Batch {batch_start//BATCH_SIZE + 1}: "
              f"migrated={stats['migrated']:,} no_oem={stats['no_oem']:,} "
              f"deleted={stats['deleted']:,} vp_relinked={stats['vp_relinked']:,}", flush=True)

    cur.close()
    conn.close()

    print(f"\n{'='*50}", flush=True)
    print(f"AFTERMARKET MIGRATION COMPLETE", flush=True)
    print(f"  Migrated to aftermarket_parts: {stats['migrated']:,}", flush=True)
    print(f"  No OEM match (skipped):        {stats['no_oem']:,}", flush=True)
    print(f"  Manufacturers created:          {stats['mfr_created']:,}", flush=True)
    print(f"  Vehicle_parts re-linked:        {stats['vp_relinked']:,}", flush=True)
    print(f"  AFT- parts deleted:             {stats['deleted']:,}", flush=True)
    print(f"{'='*50}", flush=True)


if __name__ == "__main__":
    run()
```

**Step 2: Commit**

```bash
git add scripts/migrate_aftermarket.py
git commit -m "feat: add aftermarket migration script — AFT- parts to aftermarket_parts"
```

---

## Task 9: Fix Import Pipeline (Stop Creating AFT- Parts)

**Files:**
- Modify: `/home/Autopartes/scripts/import_live.py` (find AFT- creation logic)
- Modify: `/home/Autopartes/scripts/import_phase1.py` (find AFT- creation logic)
- Modify: `/home/Autopartes/scripts/link_vehicle_parts.py` (update AFT- lookup)

**Step 1: Update import_live.py**

Change the AFT- part creation to insert into `aftermarket_parts` instead of `parts`. Where the script creates `AFT-{article_no}-{supplier}` in the parts table, replace with:
1. Skip creating the AFT- part in `parts`
2. Instead, find the OEM part from `articleOemNo`
3. Insert into `aftermarket_parts` with clean part_number and manufacturer

**Step 2: Update import_phase1.py**

Same change — line 128 area where `AFT-` keys are created. Replace with aftermarket_parts insert.

**Step 3: Update link_vehicle_parts.py**

Line 218: Remove the AFT- fallback lookup in `part_cache`. Vehicle_parts should only link to real OEM parts.

```python
# REMOVE these lines (218-220):
# aft_key = f"AFT-{article_no}-{supplier}"
# pid = part_cache.get(aft_key)
# if pid:
#     part_ids.add(pid)
```

**Step 4: Commit**

```bash
git add scripts/import_live.py scripts/import_phase1.py scripts/link_vehicle_parts.py
git commit -m "fix: stop creating AFT- parts in pipeline, use aftermarket_parts instead"
```

---

## Task 10: Login Page

**Files:**
- Create: `/home/Autopartes/dashboard/login.html`
- Create: `/home/Autopartes/dashboard/login.js`
- Create: `/home/Autopartes/dashboard/login.css`

**Step 1: Create login page**

Create a login/register page with:
- Email + password login form
- Registration form (name, email, password, business_name, phone, role selector: TALLER/BODEGA)
- Toggle between login/register
- On login success: store tokens in localStorage, redirect based on role:
  - ADMIN/OWNER → `/admin.html`
  - BODEGA → `/bodega.html`
  - TALLER → `/index.html`

**Step 2: Add static route in server.py**

Add route to serve login.html (same pattern as other pages).

**Step 3: Commit**

```bash
git add dashboard/login.html dashboard/login.js dashboard/login.css
git commit -m "feat: add login/register page"
```

---

## Task 11: Bodega Dashboard Page

**Files:**
- Create: `/home/Autopartes/dashboard/bodega.html`
- Create: `/home/Autopartes/dashboard/bodega.js`
- Create: `/home/Autopartes/dashboard/bodega.css`

**Step 1: Create bodega panel**

Page with 3 tabs:
1. **Mapeo de columnas** — configure which columns in their CSV/Excel map to part_number, price, stock, location
2. **Subir inventario** — file upload with drag & drop, shows progress and results
3. **Mi inventario** — searchable paginated table of their current inventory

**Step 2: Add static route in server.py**

**Step 3: Commit**

```bash
git add dashboard/bodega.html dashboard/bodega.js dashboard/bodega.css
git commit -m "feat: add bodega dashboard — mapping, upload, inventory view"
```

---

## Task 12: Update Nav & Catalog for Auth

**Files:**
- Modify: `/home/Autopartes/dashboard/nav.js`
- Modify: `/home/Autopartes/dashboard/demo.html` (or its JS)
- Modify: `/home/Autopartes/dashboard/index.html`

**Step 1: Update nav.js**

Add to the nav bar:
- If token in localStorage → show username + logout button
- If no token → show "Iniciar Sesión" link to `/login.html`
- Auto-refresh token when near expiry

**Step 2: Update catalog part detail**

In the part detail modal/section:
- Add "Alternativas aftermarket" section (public, calls `/api/parts/{id}/aftermarket`)
- Add "Disponibilidad en bodegas" section (only if authenticated TALLER, calls `/api/parts/{id}/availability`)

**Step 3: Commit**

```bash
git add dashboard/nav.js dashboard/demo.html dashboard/index.html
git commit -m "feat: add auth to nav, aftermarket + availability to catalog"
```

---

## Task 13: Admin Users Tab

**Files:**
- Modify: `/home/Autopartes/dashboard/admin.html`
- Modify: `/home/Autopartes/dashboard/admin.js`

**Step 1: Add users tab to admin panel**

Add a new tab "Usuarios" to the admin sidebar that:
- Lists all users with name, email, role, business_name, is_active, last_login
- Toggle activate/deactivate button per user
- Badge showing pending (inactive) users count

**Step 2: Commit**

```bash
git add dashboard/admin.html dashboard/admin.js
git commit -m "feat: add users management tab to admin panel"
```

---

## Task 14: Create Admin User & Test

**Step 1: Set password for existing admin**

Run:
```bash
python3 -c "
import bcrypt, psycopg2
pw = bcrypt.hashpw(b'admin2026', bcrypt.gensalt()).decode()
conn = psycopg2.connect('postgresql://nexus:nexus_autoparts_2026@localhost/nexus_autoparts')
cur = conn.cursor()
cur.execute('UPDATE users SET pass = %s, email = %s, is_active = true WHERE id_rol = 1', (pw, 'admin@nexus.com'))
conn.commit()
print('Admin password set')
"
```

**Step 2: Test auth flow**

```bash
# Login
curl -X POST http://localhost:5000/api/auth/login \
  -H 'Content-Type: application/json' \
  -d '{"email":"admin@nexus.com","password":"admin2026"}'

# Register a bodega
curl -X POST http://localhost:5000/api/auth/register \
  -H 'Content-Type: application/json' \
  -d '{"name":"Test Bodega","email":"bodega@test.com","password":"test123","role":"BODEGA","business_name":"Bodega Central"}'

# Activate the bodega (use admin token)
curl -X PUT http://localhost:5000/api/admin/users/2/activate \
  -H 'Authorization: Bearer <admin_token>' \
  -H 'Content-Type: application/json' \
  -d '{"is_active":true}'
```

**Step 3: Commit**

```bash
git commit -m "chore: set up admin user credentials"
```

---

## Task 15: Run Aftermarket Migration

**Step 1: Run migration**

```bash
python3 /home/Autopartes/scripts/migrate_aftermarket.py 2>&1 | tee /tmp/aftermarket_migration.log
```

**Step 2: Verify results**

```bash
PGPASSWORD=nexus_autoparts_2026 psql -U nexus -d nexus_autoparts -h localhost \
  -c "SELECT count(*) as aftermarket_parts FROM aftermarket_parts;" \
  -c "SELECT count(*) as remaining_aft FROM parts WHERE oem_part_number LIKE 'AFT-%';" \
  -c "SELECT count(*) as total_parts FROM parts;"
```

Expected: aftermarket_parts populated, remaining AFT- parts minimal (only those without OEM match), total parts reduced.

**Step 3: Commit**

```bash
git commit -m "chore: run aftermarket migration — 357K AFT- parts moved"
```

---

## Execution Order

The tasks should be executed in this order due to dependencies:
1. **Task 1** (deps) → **Task 2** (schema) → **Task 3** (auth module)
2. **Task 4** (auth endpoints) → **Task 5** (admin users)
3. **Task 6** (inventory endpoints) → **Task 7** (availability endpoints)
4. **Task 8** (aftermarket migration script) → **Task 9** (fix pipeline) → **Task 15** (run migration)
5. **Task 10** (login page) → **Task 11** (bodega page) → **Task 12** (nav update) → **Task 13** (admin users tab)
6. **Task 14** (test)

Tasks 1-3 are sequential. After that, the auth branch (4-5) and aftermarket branch (8-9) can run in parallel. Frontend tasks (10-13) depend on the backend being ready.