import os, sqlite3, secrets, hashlib, hmac, datetime, json
from decimal import Decimal, ROUND_HALF_UP
from functools import wraps
from pathlib import Path
from flask import Flask, request, session, redirect, url_for, render_template, flash, abort, send_file
from werkzeug.security import generate_password_hash, check_password_hash
from werkzeug.utils import secure_filename

BASE=Path(__file__).resolve().parent
DATA=Path(os.environ.get('DATA_DIR',str(BASE/'data')))
DATA.mkdir(parents=True,exist_ok=True)
app=Flask(__name__)
app.secret_key=os.environ.get('SECRET_KEY') or secrets.token_hex(32)
app.config.update(SESSION_COOKIE_HTTPONLY=True,SESSION_COOKIE_SAMESITE='Lax',SESSION_COOKIE_SECURE=os.environ.get('COOKIE_SECURE','0')=='1',MAX_CONTENT_LENGTH=12*1024*1024)
DB=DATA/'vacancia.sqlite3'

def db():
 c=sqlite3.connect(DB);c.row_factory=sqlite3.Row;c.execute('PRAGMA foreign_keys=ON');return c

def init_db():
 with db() as c:
  c.executescript('''CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY,email TEXT UNIQUE NOT NULL,password_hash TEXT NOT NULL,role TEXT NOT NULL CHECK(role IN ('admin','contracting','reservations')),active INTEGER NOT NULL DEFAULT 1);
  CREATE TABLE IF NOT EXISTS hotels(id INTEGER PRIMARY KEY,name TEXT NOT NULL,city TEXT);
  CREATE TABLE IF NOT EXISTS contracts(id INTEGER PRIMARY KEY,hotel_id INTEGER NOT NULL REFERENCES hotels(id),name TEXT NOT NULL,valid_from TEXT NOT NULL,valid_to TEXT NOT NULL,currency TEXT NOT NULL,status TEXT NOT NULL DEFAULT 'draft' CHECK(status IN ('draft','validated')),pdf_path TEXT,created_at TEXT DEFAULT CURRENT_TIMESTAMP);
  CREATE TABLE IF NOT EXISTS rates(id INTEGER PRIMARY KEY,contract_id INTEGER NOT NULL REFERENCES contracts(id),room_type TEXT NOT NULL,board TEXT NOT NULL,start_date TEXT NOT NULL,end_date TEXT NOT NULL,price_pppn TEXT NOT NULL,source_page INTEGER,verified INTEGER DEFAULT 0);
  CREATE TABLE IF NOT EXISTS quotes(id INTEGER PRIMARY KEY,reference TEXT UNIQUE NOT NULL,created_by INTEGER REFERENCES users(id),contract_id INTEGER REFERENCES contracts(id),created_at TEXT DEFAULT CURRENT_TIMESTAMP,arrival TEXT NOT NULL,departure TEXT NOT NULL,rooms INTEGER NOT NULL,adults INTEGER NOT NULL,children INTEGER NOT NULL,room_type TEXT NOT NULL,board TEXT NOT NULL,markup TEXT NOT NULL,cost TEXT NOT NULL,sale TEXT NOT NULL,status TEXT NOT NULL DEFAULT 'indicative',details_json TEXT NOT NULL);
  CREATE TABLE IF NOT EXISTS bookings(id INTEGER PRIMARY KEY,quote_id INTEGER UNIQUE REFERENCES quotes(id),status TEXT NOT NULL DEFAULT 'request',supplier_reference TEXT,created_at TEXT DEFAULT CURRENT_TIMESTAMP);
  CREATE TABLE IF NOT EXISTS audit(id INTEGER PRIMARY KEY,user_id INTEGER,action TEXT,details TEXT,created_at TEXT DEFAULT CURRENT_TIMESTAMP);''')
  if not c.execute('SELECT id FROM hotels LIMIT 1').fetchone():
   c.execute("INSERT INTO hotels(name,city) VALUES('Mahdia Beach & Aqua Park','Mahdia')")
   c.execute("INSERT INTO contracts(hotel_id,name,valid_from,valid_to,currency,status) VALUES(1,'Winter 2026/2027','2026-11-01','2027-04-30','EUR','draft')")
   for board,price in [('UAI','40'),('HB','30')]:
    c.execute('INSERT INTO rates(contract_id,room_type,board,start_date,end_date,price_pppn,source_page,verified) VALUES(1,?,?,?,?,?,1,0)',('DBL',board,'2026-11-01','2027-04-30',price))

def log(action,details=''):
 with db() as c:c.execute('INSERT INTO audit(user_id,action,details) VALUES(?,?,?)',(session.get('uid'),action,details))

def auth(fn):
 @wraps(fn)
 def wrapper(*a,**kw):
  if not session.get('uid'):return redirect(url_for('login'))
  return fn(*a,**kw)
 return wrapper

def roles(*allowed):
 def decorate(fn):
  @wraps(fn)
  @auth
  def wrapper(*a,**kw):
   if session.get('role') not in allowed:abort(403)
   return fn(*a,**kw)
  return wrapper
 return decorate

@app.before_request
def csrf_guard():
 if 'csrf' not in session:session['csrf']=secrets.token_urlsafe(32)
 if request.method=='POST' and not hmac.compare_digest(request.form.get('csrf',''),session['csrf']):abort(400,'CSRF invalid')

@app.context_processor
def inject():return {'csrf':session.get('csrf'),'today':datetime.date.today().isoformat()}

@app.route('/login',methods=['GET','POST'])
def login():
 if request.method=='POST':
  email=request.form.get('email','').strip().lower();password=request.form.get('password','')
  with db() as c:u=c.execute('SELECT * FROM users WHERE email=? AND active=1',(email,)).fetchone()
  if u and check_password_hash(u['password_hash'],password):
   session.clear();session.update(uid=u['id'],email=u['email'],role=u['role'],csrf=secrets.token_urlsafe(32));log('login');return redirect(url_for('dashboard'))
  flash('Identifiants invalides','error')
 return render_template('login.html')

@app.post('/logout')
@auth
def logout():session.clear();return redirect(url_for('login'))

@app.route('/')
@auth
def dashboard():
 with db() as c:
  stats={k:c.execute('SELECT COUNT(*) FROM '+t).fetchone()[0] for k,t in [('hotels','hotels'),('contracts','contracts'),('quotes','quotes'),('bookings','bookings')]}
  recent=c.execute('SELECT * FROM quotes ORDER BY id DESC LIMIT 8').fetchall()
 return render_template('dashboard.html',stats=stats,recent=recent)

@app.route('/contracts')
@auth
def contracts():
 with db() as c:
  items=c.execute('SELECT contracts.*,hotels.name hotel FROM contracts JOIN hotels ON hotels.id=contracts.hotel_id ORDER BY contracts.id DESC').fetchall()
  rates=c.execute('SELECT * FROM rates ORDER BY id').fetchall()
 return render_template('contracts.html',items=items,rates=rates)

@app.post('/contracts/<int:cid>/upload')
@roles('admin','contracting')
def upload(cid):
 f=request.files.get('pdf')
 if not f or not f.filename.lower().endswith('.pdf') or f.stream.read(5)!=b'%PDF-':abort(400,'PDF requis')
 f.stream.seek(0)
 name=f'{cid}_{secrets.token_hex(8)}.pdf';dest=DATA/name;f.save(dest)
 with db() as c:c.execute('UPDATE contracts SET pdf_path=?,status=? WHERE id=?',(name,'draft',cid))
 log('pdf_uploaded',str(cid));flash('PDF archivé. Extraction automatique non activée : validez les tarifs manuellement.','warning');return redirect(url_for('contracts'))

@app.post('/contracts/<int:cid>/validate')
@roles('admin','contracting')
def validate(cid):
 with db() as c:
  rates=c.execute('SELECT COUNT(*) FROM rates WHERE contract_id=? AND verified=0',(cid,)).fetchone()[0]
  if rates:flash('Vérifiez les lignes tarifaires avant publication.','error');return redirect(url_for('contracts'))
  c.execute("UPDATE contracts SET status='validated' WHERE id=?",(cid,))
 log('contract_validated',str(cid));return redirect(url_for('contracts'))

@app.post('/rates/<int:rid>/verify')
@roles('admin','contracting')
def verify(rid):
 with db() as c:c.execute('UPDATE rates SET verified=1 WHERE id=?',(rid,))
 log('rate_verified',str(rid));return redirect(url_for('contracts'))

@app.route('/quote',methods=['GET','POST'])
@auth
def quote():
 with db() as c:contracts=c.execute('SELECT c.*,h.name hotel FROM contracts c JOIN hotels h ON h.id=c.hotel_id').fetchall()
 if request.method=='POST':
  try:
   cid=int(request.form['contract']);arrival=datetime.date.fromisoformat(request.form['arrival']);departure=datetime.date.fromisoformat(request.form['departure'])
   rooms=int(request.form['rooms']);adults=int(request.form['adults']);children=int(request.form['children']);board=request.form['board'];room=request.form['room_type'];markup=Decimal(request.form['markup'])
   if not (1<=rooms<=10 and 1<=adults<=20 and children==0 and adults==rooms*2 and room=='DBL' and board in ('UAI','HB') and 0<=markup<=100 and 0<(departure-arrival).days<=90):raise ValueError('Occupation ou durée non prise en charge par cette V1')
   with db() as c:
    contract=c.execute('SELECT * FROM contracts WHERE id=?',(cid,)).fetchone()
    if not contract:raise ValueError('Contrat inconnu')
    if not (str(arrival)>=contract['valid_from'] and str(departure-datetime.timedelta(days=1))<=contract['valid_to']):raise ValueError('Séjour hors période contractuelle')
    rate=c.execute('SELECT * FROM rates WHERE contract_id=? AND room_type=? AND board=? AND start_date<=? AND end_date>=?',(cid,room,board,str(arrival),str(departure-datetime.timedelta(days=1)))).fetchone()
    if not rate:raise ValueError('Aucun tarif contractuel applicable')
    cost=(Decimal(rate['price_pppn'])*adults*(departure-arrival).days).quantize(Decimal('.01'),rounding=ROUND_HALF_UP)
    sale=(cost*(1+markup/100)).quantize(Decimal('.01'),rounding=ROUND_HALF_UP)
    indicative=contract['status']!='validated' or not rate['verified']
    ref='VHB-'+datetime.datetime.now().strftime('%Y%m%d')+'-'+secrets.token_hex(3).upper()
    detail={'rate_id':rate['id'],'rate_pppn':rate['price_pppn'],'currency':contract['currency'],'contract_status':contract['status'],'verified':bool(rate['verified']),'notice':'Indicatif : disponibilité, restrictions, promotions, taxes et suppléments non vérifiés.'}
    c.execute('INSERT INTO quotes(reference,created_by,contract_id,arrival,departure,rooms,adults,children,room_type,board,markup,cost,sale,status,details_json) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',(ref,session['uid'],cid,str(arrival),str(departure),rooms,adults,children,room,board,str(markup),str(cost),str(sale),'indicative' if indicative else 'priced_unconfirmed',json.dumps(detail)))
   log('quote_created',ref);return redirect(url_for('quote_detail',ref=ref))
  except (ValueError,KeyError,ArithmeticError) as exc:flash(str(exc),'error')
 return render_template('quote.html',contracts=contracts)

@app.route('/quotes/<ref>')
@auth
def quote_detail(ref):
 with db() as c:q=c.execute('SELECT q.*,h.name hotel FROM quotes q JOIN contracts ct ON ct.id=q.contract_id JOIN hotels h ON h.id=ct.hotel_id WHERE reference=?',(ref,)).fetchone()
 if not q:abort(404)
 return render_template('quote_detail.html',q=q,detail=json.loads(q['details_json']))

@app.route('/bookings')
@auth
def bookings():
 with db() as c:items=c.execute('SELECT b.*,q.reference,q.arrival,q.departure,q.sale FROM bookings b JOIN quotes q ON q.id=b.quote_id ORDER BY b.id DESC').fetchall()
 return render_template('bookings.html',items=items)

@app.post('/quotes/<ref>/request')
@auth
def booking_request(ref):
 with db() as c:
  q=c.execute('SELECT id FROM quotes WHERE reference=?',(ref,)).fetchone()
  if not q:abort(404)
  c.execute('INSERT OR IGNORE INTO bookings(quote_id,status) VALUES(?,?)',(q['id'],'request'))
 log('booking_request',ref);flash('Demande enregistrée : aucune réservation fournisseur confirmée.','warning');return redirect(url_for('bookings'))

@app.route('/users',methods=['GET','POST'])
@roles('admin')
def users():
 if request.method=='POST':
  email=request.form.get('email','').strip().lower();password=request.form.get('password','');role=request.form.get('role','')
  if len(password)<12 or role not in ('admin','contracting','reservations') or '@' not in email:flash('E-mail, rôle ou mot de passe (12 caractères minimum) invalide.','error')
  else:
   try:
    with db() as c:c.execute('INSERT INTO users(email,password_hash,role) VALUES(?,?,?)',(email,generate_password_hash(password),role))
    log('user_created',email)
   except sqlite3.IntegrityError:flash('Utilisateur déjà existant','error')
 with db() as c:items=c.execute('SELECT id,email,role,active FROM users').fetchall()
 return render_template('users.html',items=items)

@app.cli.command('init-db')
def init_command():init_db();print('Base initialisée')

@app.cli.command('create-admin')
def admin_command():
 import getpass
 init_db();email=input('E-mail administrateur : ').strip().lower();password=getpass.getpass('Mot de passe (12+ caractères) : ')
 if len(password)<12:raise SystemExit('Mot de passe trop court')
 with db() as c:c.execute('INSERT INTO users(email,password_hash,role) VALUES(?,?,?)',(email,generate_password_hash(password),'admin'))
 print('Administrateur créé')

if __name__=='__main__':init_db();app.run(host='127.0.0.1',port=8000,debug=False)