from flask import Flask, render_template, request, redirect, url_for, flash, send_file, jsonify, session
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
from urllib.parse import urlparse, urljoin
import csv, io, os, re, time, requests, secrets
from functools import wraps
from werkzeug.security import check_password_hash, generate_password_hash
from bs4 import BeautifulSoup
from openpyxl import Workbook
from urllib.robotparser import RobotFileParser

# ============================================================
# CHARGEMENT DU FICHIER .env
# ============================================================
from dotenv import load_dotenv

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
load_dotenv(os.path.join(BASE_DIR, '.env'))

# ============================================================
# APPLICATION FLASK
# ============================================================
app = Flask(__name__)

app.config['SECRET_KEY'] = os.environ.get(
    'SECRET_KEY',
    'CHANGE-ME-IN-O2SWITCH'
)

app.config['SQLALCHEMY_DATABASE_URI'] = os.environ.get(
    'DATABASE_URL',
    'sqlite:///' + os.path.join(BASE_DIR, 'instance', 'prospector_v3.db')
)

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

# ============================================================
# CONFIGURATION
# ============================================================
POSTAL_URL = 'https://www.data.gouv.fr/api/1/datasets/r/008a2dda-2c60-4b63-b910-998f6f818089'

USER_AGENT = os.environ.get(
    'USER_AGENT',
    'LocationProspector/3.5 (+public-business-contact-research)'
)

EMAIL_RE = re.compile(
    r'(?i)\b[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}\b'
)

GENERIC_PREFIXES = (
    'contact',
    'info',
    'reservation',
    'reservations',
    'booking',
    'accueil',
    'commercial',
    'bonjour',
    'hello',
    'office',
    'location',
    'locations',
    'resa'
)

ACTIVITIES = [
    'quad',
    'buggy',
    'canoë',
    'kayak',
    'paddle',
    'rafting',
    'vélo',
    'VTT',
    'VAE',
    'trottinette',
    'scooter',
    'jet-ski',
    'bateau',
    'base nautique',
    'location de loisirs'
]

class Commune(db.Model):
 id=db.Column(db.Integer,primary_key=True); code_postal=db.Column(db.String(10),index=True); commune=db.Column(db.String(160),index=True); code_insee=db.Column(db.String(10)); departement=db.Column(db.String(3),index=True)
class User(db.Model):
 id=db.Column(db.Integer,primary_key=True); username=db.Column(db.String(80),unique=True,index=True,nullable=False); password_hash=db.Column(db.String(255),nullable=False); role=db.Column(db.String(20),default='USER'); active=db.Column(db.Boolean,default=True); created_at=db.Column(db.DateTime,default=datetime.utcnow); last_login=db.Column(db.DateTime)
class Business(db.Model):
 id=db.Column(db.Integer,primary_key=True); name=db.Column(db.String(250)); activity=db.Column(db.String(100),index=True); city=db.Column(db.String(160),index=True); postal_code=db.Column(db.String(10),index=True); department=db.Column(db.String(3),index=True); website=db.Column(db.String(500),unique=True,index=True); phone=db.Column(db.String(80)); email=db.Column(db.String(250),index=True); all_emails=db.Column(db.Text); email_source=db.Column(db.String(500)); discovery_source=db.Column(db.String(100)); status=db.Column(db.String(30),default='NOUVEAU',index=True); notes=db.Column(db.Text); created_at=db.Column(db.DateTime,default=datetime.utcnow); checked_at=db.Column(db.DateTime)
class AppSetting(db.Model):
 id=db.Column(db.Integer,primary_key=True); key=db.Column(db.String(80),unique=True,index=True,nullable=False); value=db.Column(db.String(500))
class BraveUsage(db.Model):
 id=db.Column(db.Integer,primary_key=True); month=db.Column(db.String(7),index=True,nullable=False); requests=db.Column(db.Integer,default=0); updated_at=db.Column(db.DateTime,default=datetime.utcnow)
class SearchJob(db.Model):
 id=db.Column(db.Integer,primary_key=True); activity=db.Column(db.String(100),index=True); location=db.Column(db.String(200)); department=db.Column(db.String(3),index=True); status=db.Column(db.String(30),default='QUEUED',index=True); total=db.Column(db.Integer,default=0); processed=db.Column(db.Integer,default=0); found=db.Column(db.Integer,default=0); created_at=db.Column(db.DateTime,default=datetime.utcnow); started_at=db.Column(db.DateTime); finished_at=db.Column(db.DateTime); notes=db.Column(db.Text)

# ---------- helpers ----------
def dept_from_cp(cp):
 cp=(cp or '').strip()
 if cp.startswith(('971','972','973','974','975','976')): return cp[:3]
 if cp.startswith('20') and len(cp)>=3:return '2A' if cp<'20200' else '2B'
 return cp[:2] if len(cp)>=2 else ''
def clean_url(url):
 if not url:return None
 url=url.strip()
 if not url.startswith(('http://','https://')):url='https://'+url
 p=urlparse(url);return f'{p.scheme}://{p.netloc}' if p.netloc else None
def domain(u):return urlparse(u).netloc.lower().removeprefix('www.') if u else ''
def same_domain(a,b):return domain(a)==domain(b)
def decode_obfuscated(t):
 t=re.sub(r'\s*(?:\[at\]|\(at\)|\sat\s)\s*','@',t,flags=re.I);return re.sub(r'\s*(?:\[dot\]|\(dot\)|\sdot\s)\s*','.',t,flags=re.I)
def extract_phone(t):
 m=re.search(r'(?:(?:\+33|0)[1-9](?:[ .-]?\d{2}){4})',t);return m.group(0).strip() if m else None
def robots_ok(url):
 try:
  p=urlparse(url);rp=RobotFileParser();rp.set_url(f'{p.scheme}://{p.netloc}/robots.txt');rp.read();return rp.can_fetch(USER_AGENT,url)
 except Exception:return True

def login_required(fn):
 @wraps(fn)
 def wrapper(*args,**kwargs):
  if not session.get('user_id'):return redirect(url_for('login',next=request.path))
  return fn(*args,**kwargs)
 return wrapper
def admin_required(fn):
 @wraps(fn)
 def wrapper(*args,**kwargs):
  if not session.get('user_id'):return redirect(url_for('login'))
  if session.get('role')!='ADMIN':flash('Accès administrateur requis.','danger');return redirect(url_for('index'))
  return fn(*args,**kwargs)
 return wrapper

def bootstrap_admin():
 if User.query.count()==0:
  username=os.environ.get('APP_LOGIN','admin');pwd=os.environ.get('APP_PASSWORD','')
  pwd_hash=os.environ.get('APP_PASSWORD_HASH','')
  if not pwd_hash and pwd:pwd_hash=generate_password_hash(pwd)
  if pwd_hash:db.session.add(User(username=username,password_hash=pwd_hash,role='ADMIN',active=True));db.session.commit()

def crawl_public_contacts(website,max_pages=7):
 base=clean_url(website)
 if not base:return [],None
 headers={'User-Agent':USER_AGENT};queue=[base];seen=set();found=[];phone=None;preferred=('contact','nous-contacter','contactez','mentions-legales','reservation','booking','legal')
 while queue and len(seen)<max_pages:
  url=queue.pop(0)
  if url in seen:continue
  seen.add(url)
  if not robots_ok(url):continue
  try:
   r=requests.get(url,headers=headers,timeout=(4,8),allow_redirects=True)
   if r.status_code!=200 or 'text/html' not in r.headers.get('content-type',''):continue
   soup=BeautifulSoup(r.text,'html.parser');text=decode_obfuscated(soup.get_text(' ',strip=True));phone=phone or extract_phone(text);candidates=set(EMAIL_RE.findall(text))
   for a in soup.select('a[href^="mailto:"]'):candidates.update(EMAIL_RE.findall(a.get('href','')))
   for email in candidates:
    low=email.lower()
    if not any(x in low for x in ('example.com','sentry.io','wixpress.com','domain.com')):found.append((low,r.url))
   for a in soup.find_all('a',href=True):
    href=urljoin(r.url,a['href'].split('#')[0]);label=(a.get_text(' ',strip=True)+' '+href).lower()
    if same_domain(base,href) and any(k in label for k in preferred) and href not in seen and href not in queue:queue.append(href)
  except requests.RequestException:pass
  time.sleep(.25)
 found=list(dict.fromkeys(found));found.sort(key=lambda x:(0 if x[0].split('@')[0] in GENERIC_PREFIXES else 1,x[0]));return found,phone

def import_postal_codes():
 r=requests.get(POSTAL_URL,headers={'User-Agent':USER_AGENT},timeout=90,allow_redirects=True);r.raise_for_status();text=r.content.decode('utf-8-sig',errors='replace');dialect=csv.Sniffer().sniff(text[:4096],delimiters=';,\t,');reader=csv.DictReader(io.StringIO(text),dialect=dialect);Commune.query.delete();n=0
 for row in reader:
  x={str(k).strip().lower():(v or '').strip() for k,v in row.items()};cp=x.get('code_postal') or x.get('code postal') or x.get('postal_code');name=x.get('nom_de_la_commune') or x.get('nom de la commune') or x.get('commune');ins=x.get('code_commune_insee') or x.get('code commune insee') or x.get('code_insee')
  if cp and name:
   db.session.add(Commune(code_postal=cp,commune=name,code_insee=ins,departement=dept_from_cp(cp)));n+=1
   if n%1000==0:db.session.commit()
 db.session.commit();return n

def get_setting(key, default=None):
 x=AppSetting.query.filter_by(key=key).first();return x.value if x else default
def set_setting(key,value):
 x=AppSetting.query.filter_by(key=key).first() or AppSetting(key=key);x.value=str(value);db.session.add(x);db.session.commit()
def brave_month():return datetime.utcnow().strftime('%Y-%m')
def brave_usage():
 x=BraveUsage.query.filter_by(month=brave_month()).first()
 if not x:x=BraveUsage(month=brave_month(),requests=0);db.session.add(x);db.session.commit()
 return x
def brave_limit():
 try:return max(0,int(get_setting('brave_monthly_limit',os.environ.get('BRAVE_MONTHLY_LIMIT','1000'))))
 except:return 1000
def brave_enabled():return bool(os.environ.get('BRAVE_SEARCH_API_KEY')) and get_setting('brave_enabled','1')=='1'
def brave_stats():
 u=brave_usage();limit=brave_limit();price=5.0/1000.0;free_credit=5.0;gross=u.requests*price;estimated=max(0.0,gross-free_credit)
 return {'enabled':brave_enabled(),'configured':bool(os.environ.get('BRAVE_SEARCH_API_KEY')),'used':u.requests,'limit':limit,'remaining':max(0,limit-u.requests),'gross_cost':gross,'estimated_cost':estimated,'percent':min(100,round((u.requests/limit*100) if limit else 100,1)),'month':u.month}

BRAVE_QUERY_CACHE=set()

def brave_discover(activity,city,cp):
 key=os.environ.get('BRAVE_SEARCH_API_KEY');usage=brave_usage();limit=brave_limit()
 if not key or not brave_enabled() or usage.requests>=limit:return []
 query_key=(activity.strip().lower(),city.strip().upper(),cp.strip())
 if query_key in BRAVE_QUERY_CACHE:
  print(f'[BRAVE] requete deja faite dans ce processus : {activity} {city} {cp}',flush=True)
  return []
 BRAVE_QUERY_CACHE.add(query_key)
 try:
  print(f'[BRAVE] location {activity} {city} {cp} France',flush=True)
  r=requests.get('https://api.search.brave.com/res/v1/web/search',headers={'X-Subscription-Token':key,'Accept':'application/json','Accept-Encoding':'gzip','User-Agent':USER_AGENT},params={'q':f'location {activity} {city} {cp} France','count':20,'country':'fr','search_lang':'fr'},timeout=(5,10))
  usage.requests+=1;usage.updated_at=datetime.utcnow();db.session.commit()
  r.raise_for_status();out=[]
  blocked=('facebook.com','instagram.com','tripadvisor.','pagesjaunes.','yelp.','linkedin.com','youtube.com','tiktok.com')
  for x in r.json().get('web',{}).get('results',[]):
   u=clean_url(x.get('url'))
   if u and not any(b in domain(u) for b in blocked):out.append({'name':x.get('title','')[:250],'website':u,'source':'Brave Search API'})
  print(f'[BRAVE] {len(out)} candidat(s)',flush=True);return out
 except requests.exceptions.Timeout:
  print(f'[BRAVE] Timeout {city} {cp}',flush=True);return []
 except requests.exceptions.RequestException as e:
  print(f'[BRAVE] Erreur: {e}',flush=True);return []
 except Exception as e:
  print(f'[BRAVE] Erreur inattendue: {e}',flush=True);return []

# V3.4 - protection Overpass pour hébergement mutualisé O2Switch
OVERPASS_FAILURES = 0
OVERPASS_DISABLED_UNTIL = 0.0

def osm_discover(activity,city):
 global OVERPASS_FAILURES,OVERPASS_DISABLED_UNTIL

 # Après plusieurs pannes consécutives, ne plus perdre de temps sur Overpass
 # pendant 15 minutes. Brave continue normalement.
 now=time.time()
 if now<OVERPASS_DISABLED_UNTIL:
  print('[OSM] temporairement ignore (serveurs indisponibles)',flush=True)
  return []

 q='''[out:json][timeout:6];area["name"="%s"]["boundary"="administrative"]->.a;(nwr["shop"="rental"](area.a);nwr["rental"](area.a);nwr["sport"~"canoe|kayak|cycling|quad|rafting"](area.a););out tags center 30;'''%city.replace('"','')

 servers=(
  'https://overpass-api.de/api/interpreter',
  'https://overpass.kumi.systems/api/interpreter'
 )

 for server in servers:
  try:
   print(f'[OSM] {activity} - {city}',flush=True)
   r=requests.post(server,data={'data':q},headers={'User-Agent':USER_AGENT},timeout=(3,6))
   r.raise_for_status()
   out=[]
   for e in r.json().get('elements',[]):
    t=e.get('tags',{});name=t.get('name');site=t.get('website') or t.get('contact:website');hay=' '.join(str(v) for v in t.values()).lower()
    if name and site and (activity.lower() in hay or any(k in hay for k in ('rental','location','canoe','kayak','cycle','vélo','quad','rafting'))):
     u=clean_url(site)
     if u:out.append({'name':name,'website':u,'source':'OpenStreetMap/Overpass'})

   # Une réponse valide réinitialise le compteur de pannes.
   OVERPASS_FAILURES=0
   print(f'[OSM] {len(out)} candidat(s)',flush=True)
   return out

  except requests.exceptions.Timeout:
   OVERPASS_FAILURES+=1
   print(f'[OSM] Timeout {server}',flush=True)
  except requests.exceptions.RequestException as e:
   OVERPASS_FAILURES+=1
   print(f'[OSM] Erreur {server}: {e}',flush=True)
  except Exception as e:
   OVERPASS_FAILURES+=1
   print(f'[OSM] Erreur inattendue: {e}',flush=True)

  if OVERPASS_FAILURES>=3:
   OVERPASS_DISABLED_UNTIL=time.time()+900
   print('[OSM] trop de pannes : ignore pendant 15 minutes',flush=True)
   return []

 return []

def save_candidate(cand,activity,c):
 u=clean_url(cand.get('website'))
 if not u:return False
 b=Business.query.filter_by(website=u).first();is_new=not b
 if not b:b=Business(website=u,created_at=datetime.utcnow())
 b.name=b.name or cand.get('name');b.activity=b.activity or activity;b.city=b.city or c.commune;b.postal_code=b.postal_code or c.code_postal;b.department=b.department or c.departement;b.discovery_source=b.discovery_source or cand.get('source');db.session.add(b);db.session.commit();return is_new

def process_one_job():
 # V3.3 : reprendre en priorité un job RUNNING, sinon prendre le prochain QUEUED.
 j=SearchJob.query.filter_by(status='RUNNING').order_by(SearchJob.id).first()
 if not j:
  j=SearchJob.query.filter_by(status='QUEUED').order_by(SearchJob.id).first()
 if not j:return False

 if j.status=='QUEUED':
  j.status='RUNNING'
  j.started_at=j.started_at or datetime.utcnow()
  db.session.commit()

 q=Commune.query
 if j.department:q=q.filter_by(departement=j.department)
 elif j.location and j.location.lower()!='france':q=q.filter(db.or_(Commune.commune.ilike(f'%{j.location}%'),Commune.code_postal.ilike(f'{j.location}%')))
 raw_rows=q.order_by(Commune.code_postal,Commune.commune).all()

 # V3.4 : le fichier postal peut contenir plusieurs lignes pour une même
 # commune/code postal. On ne lance qu'une recherche par couple CP + commune.
 rows=[]
 seen_communes=set()
 for c in raw_rows:
  key=((c.code_postal or '').strip(),(c.commune or '').strip().upper())
  if key in seen_communes:continue
  seen_communes.add(key)
  rows.append(c)

 j.total=len(rows)
 start=j.processed or 0
 db.session.commit()

 # Sur O2Switch on traite un petit lot par lancement CRON.
 try:batch=max(1,int(os.environ.get('JOB_COMMUNE_BATCH','5')))
 except Exception:batch=5
 done=0

 # Si la recherche était déjà entièrement parcourue, la terminer proprement.
 if start>=len(rows):
  j.status='DONE';j.finished_at=datetime.utcnow();db.session.commit()
  print(f'[JOB {j.id}] TERMINE - {j.found} prospect(s) trouves',flush=True)
  return True

 for c in rows[start:]:
  db.session.refresh(j)
  if j.status=='PAUSED':
   print(f'[JOB {j.id}] PAUSE',flush=True)
   return True

  print(f'[JOB {j.id}] {j.activity} - {c.commune} {c.code_postal} ({j.processed+1}/{j.total})',flush=True)

  # OpenStreetMap d'abord (gratuit), Brave ensuite en complément.
  osm_candidates=osm_discover(j.activity,c.commune)
  brave_candidates=brave_discover(j.activity,c.commune,c.code_postal)
  candidates=osm_candidates+brave_candidates

  # Déduplication par domaine pour éviter d'enregistrer deux fois le même site.
  unique={domain(x['website']):x for x in candidates if x.get('website') and domain(x.get('website'))}

  for cand in unique.values():
   if save_candidate(cand,j.activity,c):j.found+=1

  j.processed+=1
  done+=1
  db.session.commit()
  time.sleep(.15)

  if done>=batch:
   print(f'[JOB {j.id}] LOT TERMINE - progression {j.processed}/{j.total}, trouves={j.found}',flush=True)
   # IMPORTANT : on garde RUNNING. Le prochain CRON reprendra automatiquement.
   return True

 j.status='DONE'
 j.finished_at=datetime.utcnow()
 db.session.commit()
 print(f'[JOB {j.id}] TERMINE - {j.found} prospect(s) trouves',flush=True)
 return True

def scan_unchecked(limit=5):
 rows=Business.query.filter(Business.website.isnot(None),Business.checked_at.is_(None)).order_by(Business.id).limit(limit).all();n=0
 for b in rows:
  contacts,phone=crawl_public_contacts(b.website);b.all_emails=';'.join(x[0] for x in contacts)
  if contacts:b.email,b.email_source=contacts[0]
  b.phone=phone or b.phone;b.checked_at=datetime.utcnow();db.session.commit();n+=1
 return n

# ---------- auth ----------
@app.route('/login',methods=['GET','POST'])
def login():
 if request.method=='POST':
  u=User.query.filter_by(username=request.form.get('username','').strip()).first()
  if u and u.active and check_password_hash(u.password_hash,request.form.get('password','')):
   session.clear();session['user_id']=u.id;session['username']=u.username;session['role']=u.role;u.last_login=datetime.utcnow();db.session.commit();return redirect(url_for('index'))
  flash('Identifiant ou mot de passe incorrect.','danger')
 return render_template('login.html')
@app.post('/logout')
def logout():session.clear();return redirect(url_for('login'))

# ---------- dashboard / prospects ----------
@app.get('/')
@login_required
def index():
 stats={'businesses':Business.query.count(),'emails':Business.query.filter(Business.email.isnot(None)).count(),'phones':Business.query.filter(Business.phone.isnot(None)).count(),'communes':Commune.query.count(),'jobs':SearchJob.query.count()};jobs=SearchJob.query.order_by(SearchJob.id.desc()).limit(15).all();return render_template('index.html',stats=stats,jobs=jobs,activities=ACTIVITIES,brave=brave_stats())
@app.route('/settings/brave',methods=['GET','POST'])
@admin_required
def brave_settings():
 if request.method=='POST':
  set_setting('brave_enabled','1' if request.form.get('enabled') else '0');set_setting('brave_monthly_limit',request.form.get('monthly_limit','1000'));flash('Réglages Brave enregistrés.','success');return redirect(url_for('brave_settings'))
 return render_template('brave_settings.html',brave=brave_stats())

@app.get('/prospects')
@login_required
def prospects():
 q=request.args.get('q','').strip();activity=request.args.get('activity','').strip();dep=request.args.get('department','').strip().upper();has_email=request.args.get('has_email','');status=request.args.get('status','');page=max(int(request.args.get('page','1') or 1),1);query=Business.query
 if q:query=query.filter(db.or_(Business.name.ilike(f'%{q}%'),Business.city.ilike(f'%{q}%'),Business.email.ilike(f'%{q}%'),Business.website.ilike(f'%{q}%')))
 if activity:query=query.filter_by(activity=activity)
 if dep:query=query.filter_by(department=dep)
 if has_email=='1':query=query.filter(Business.email.isnot(None))
 if has_email=='0':query=query.filter(Business.email.is_(None))
 if status:query=query.filter_by(status=status)
 p=query.order_by(Business.id.desc()).paginate(page=page,per_page=50,error_out=False);return render_template('prospects.html',p=p,activities=ACTIVITIES,filters=request.args)
@app.route('/business/<int:bid>',methods=['GET','POST'])
@login_required
def business_detail(bid):
 b=Business.query.get_or_404(bid)
 if request.method=='POST':
  b.name=request.form.get('name','').strip();b.activity=request.form.get('activity','').strip();b.city=request.form.get('city','').strip();b.postal_code=request.form.get('postal_code','').strip();b.department=request.form.get('department','').strip().upper() or dept_from_cp(b.postal_code);b.phone=request.form.get('phone','').strip();b.email=request.form.get('email','').strip();b.status=request.form.get('status','NOUVEAU');b.notes=request.form.get('notes','').strip();db.session.commit();flash('Fiche enregistrée.','success');return redirect(url_for('business_detail',bid=b.id))
 return render_template('business_detail.html',b=b,activities=ACTIVITIES)
@app.post('/business/<int:bid>/delete')
@login_required
def business_delete(bid):db.session.delete(Business.query.get_or_404(bid));db.session.commit();flash('Prospect supprimé.','success');return redirect(url_for('prospects'))
@app.post('/business/<int:bid>/scan')
@login_required
def scan_business(bid):
 b=Business.query.get_or_404(bid);contacts,phone=crawl_public_contacts(b.website);b.all_emails=';'.join(x[0] for x in contacts)
 if contacts:b.email,b.email_source=contacts[0]
 b.phone=phone or b.phone;b.checked_at=datetime.utcnow();db.session.commit();flash(f'{len(contacts)} email(s) public(s) trouvé(s).','success');return redirect(request.referrer or url_for('prospects'))

# ---------- jobs ----------
@app.post('/postal/import')
@admin_required
def postal_import():
 try:flash(f'{import_postal_codes()} codes postaux importés.','success')
 except Exception as e:flash(f'Import impossible : {e}','danger')
 return redirect(url_for('index'))
@app.post('/job/create')
@login_required
def create_job():
 activities=[x.strip() for x in request.form.getlist('activities') if x and x.strip()]
 custom_activity=request.form.get('custom_activity','').strip()

 # V3.5 : la case "Autre" permet d'ajouter une activité libre.
 if request.form.get('other_activity')=='1':
  if custom_activity:
   if len(custom_activity)>100:
    flash("L'activité personnalisée est trop longue (100 caractères maximum).",'danger')
    return redirect(url_for('index'))
   activities.append(custom_activity)
  else:
   flash('Tu as coché "Autre" : indique une activité personnalisée.','danger')
   return redirect(url_for('index'))

 # Supprime les doublons sans modifier l'ordre.
 activities=list(dict.fromkeys(activities))
 if not activities:
  flash('Sélectionne au moins une activité ou utilise "Autre".','danger')
  return redirect(url_for('index'))

 loc=request.form.get('location','France').strip() or 'France'
 dep=request.form.get('department','').strip().upper()
 n=0
 for a in activities:
  db.session.add(SearchJob(activity=a,location=loc,department=dep or None,status='QUEUED'))
  n+=1
 db.session.commit()
 flash(f'{n} recherche(s) ajoutée(s). Le CRON O2Switch les traitera.','success')
 return redirect(url_for('index'))
@app.post('/jobs/pause')
@login_required
def jobs_pause():
 for j in SearchJob.query.filter(SearchJob.status.in_(['QUEUED','RUNNING'])).all():j.status='PAUSED'
 db.session.commit();flash('File mise en pause.','success');return redirect(url_for('index'))
@app.post('/jobs/start')
@login_required
def jobs_start():
 for j in SearchJob.query.filter_by(status='PAUSED').all():j.status='QUEUED'
 db.session.commit();flash('File reprise.','success');return redirect(url_for('index'))
@app.post('/job/<int:jid>/delete')
@login_required
def job_delete(jid):db.session.delete(SearchJob.query.get_or_404(jid));db.session.commit();flash('Recherche supprimée.','success');return redirect(url_for('index'))
@app.get('/api/status')
@login_required
def api_status():
 j=SearchJob.query.filter(SearchJob.status.in_(['RUNNING','QUEUED','PAUSED'])).order_by(SearchJob.id).first();return jsonify({'job':None if not j else {'id':j.id,'activity':j.activity,'status':j.status,'processed':j.processed,'total':j.total,'found':j.found}})

# ---------- users ----------
@app.get('/users')
@admin_required
def users():return render_template('users.html',users=User.query.order_by(User.username).all())
@app.post('/users/create')
@admin_required
def user_create():
 username=request.form.get('username','').strip();pwd=request.form.get('password','');role=request.form.get('role','USER')
 if not username or len(pwd)<8:flash('Identifiant requis et mot de passe de 8 caractères minimum.','danger')
 elif User.query.filter_by(username=username).first():flash('Cet utilisateur existe déjà.','danger')
 else:db.session.add(User(username=username,password_hash=generate_password_hash(pwd),role=role,active=True));db.session.commit();flash('Utilisateur créé.','success')
 return redirect(url_for('users'))
@app.post('/users/<int:uid>/toggle')
@admin_required
def user_toggle(uid):
 u=User.query.get_or_404(uid)
 if u.id==session['user_id']:flash('Tu ne peux pas désactiver ton propre compte.','danger')
 else:u.active=not u.active;db.session.commit();flash('Compte mis à jour.','success')
 return redirect(url_for('users'))
@app.post('/users/<int:uid>/password')
@admin_required
def user_password(uid):
 pwd=request.form.get('password','')
 if len(pwd)<8:flash('8 caractères minimum.','danger')
 else:u=User.query.get_or_404(uid);u.password_hash=generate_password_hash(pwd);db.session.commit();flash('Mot de passe modifié.','success')
 return redirect(url_for('users'))
@app.route('/account',methods=['GET','POST'])
@login_required
def account():
 u=User.query.get(session['user_id'])
 if request.method=='POST':
  if not check_password_hash(u.password_hash,request.form.get('current_password','')):flash('Mot de passe actuel incorrect.','danger')
  elif len(request.form.get('new_password',''))<8:flash('Le nouveau mot de passe doit faire au moins 8 caractères.','danger')
  else:u.password_hash=generate_password_hash(request.form['new_password']);db.session.commit();flash('Mot de passe changé.','success')
 return render_template('account.html',u=u)

# ---------- exports ----------
def export_query():
 q=Business.query;dep=request.args.get('department','').strip().upper();activity=request.args.get('activity','').strip()
 if dep:q=q.filter_by(department=dep)
 if activity:q=q.filter_by(activity=activity)
 return q.order_by(Business.department,Business.city,Business.activity,Business.name).all()
@app.get('/export.csv')
@login_required
def export_csv():
 out=io.StringIO();w=csv.writer(out,delimiter=';');w.writerow(['Entreprise','Activité','Département','Code postal','Ville','Téléphone','Email principal','Tous emails','Site','Statut','Source email','Découverte','Notes','Vérifié le'])
 for b in export_query():w.writerow([b.name,b.activity,b.department,b.postal_code,b.city,b.phone,b.email,b.all_emails,b.website,b.status,b.email_source,b.discovery_source,b.notes,b.checked_at.isoformat() if b.checked_at else ''])
 data=io.BytesIO(out.getvalue().encode('utf-8-sig'));data.seek(0);return send_file(data,mimetype='text/csv',as_attachment=True,download_name='prospects_location_v3.csv')
@app.get('/export.xlsx')
@login_required
def export_xlsx():
 wb=Workbook();ws=wb.active;ws.title='Prospects';ws.append(['Entreprise','Activité','Département','Code postal','Ville','Téléphone','Email principal','Tous emails','Site','Statut','Source email','Découverte','Notes','Vérifié le'])
 for b in export_query():ws.append([b.name,b.activity,b.department,b.postal_code,b.city,b.phone,b.email,b.all_emails,b.website,b.status,b.email_source,b.discovery_source,b.notes,b.checked_at.isoformat() if b.checked_at else ''])
 for col in ws.columns:ws.column_dimensions[col[0].column_letter].width=min(max(len(str(c.value or '')) for c in col)+2,55)
 data=io.BytesIO();wb.save(data);data.seek(0);return send_file(data,as_attachment=True,download_name='prospects_location_v3.xlsx',mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
@app.get('/communes')
@login_required
def communes():
 q=request.args.get('q','').strip();rows=Commune.query.filter(db.or_(Commune.commune.ilike(f'%{q}%'),Commune.code_postal.ilike(f'{q}%'))).limit(100).all() if q else [];return render_template('communes.html',rows=rows,q=q)

with app.app_context():db.create_all();bootstrap_admin()
if __name__=='__main__':app.run(host='0.0.0.0',port=int(os.environ.get('PORT',5000)),debug=os.environ.get('FLASK_DEBUG')=='1')