import hashlib
import json
import os
import secrets
import sqlite3
from contextlib import contextmanager, closing
from datetime import datetime, timezone
from pathlib import Path

ROOT = Path(__file__).resolve().parent
if (ROOT / '.env').exists():
    for line in (ROOT / '.env').read_text(encoding='utf-8').splitlines():
        if line.strip() and not line.lstrip().startswith('#') and '=' in line:
            key, value = line.split('=', 1)
            os.environ.setdefault(key.strip(), value.strip().strip('"').strip("'"))
DB_PATH = Path(os.environ.get('DATABASE_PATH') or str(ROOT / 'data' / 'routeflow.db'))

def now():
    return datetime.now(timezone.utc).isoformat()

@contextmanager
def connect():
    DB_PATH.parent.mkdir(parents=True, exist_ok=True)
    db = sqlite3.connect(DB_PATH, timeout=20)
    db.row_factory = sqlite3.Row
    db.execute('PRAGMA foreign_keys=ON')
    try:
        yield db
        db.commit()
    except BaseException:
        db.rollback()
        raise
    finally:
        db.close()

def rows(db, sql, params=()):
    return [dict(r) for r in db.execute(sql, params)]

def one(db, sql, params=()):
    row = db.execute(sql, params).fetchone()
    return dict(row) if row else None

def audit(db, action, entity='', entity_id=None, detail=None, actor='system'):
    db.execute('INSERT INTO audit_logs(business_id,actor,action,entity,entity_id,detail,created_at) VALUES(1,?,?,?,?,?,?)', (actor, action, entity, entity_id, json.dumps(detail or {}, ensure_ascii=False), now()))

def password_hash(password, salt=None):
    salt = salt or secrets.token_hex(16)
    return salt + ':' + hashlib.scrypt(password.encode(), salt=bytes.fromhex(salt), n=16384, r=8, p=1).hex()

def password_ok(password, encoded):
    return secrets.compare_digest(password_hash(password, encoded.split(':')[0]), encoded)

def init():
    with connect() as db:
        db.execute('PRAGMA journal_mode=WAL')
        db.executescript((ROOT / 'schema.sql').read_text(encoding='utf-8'))
        db.execute("INSERT OR IGNORE INTO businesses VALUES(1,'My Distribution Business','Asia/Kolkata')")
        db.execute("INSERT OR IGNORE INTO whatsapp_numbers VALUES(1,1,'Local simulator','local','WHATSAPP_ACCESS_TOKEN',1)")
        db.execute("INSERT OR IGNORE INTO settings VALUES(1,'duplicate_minutes','30')")
        db.execute("INSERT OR IGNORE INTO settings VALUES(1,'automation','true')")
        db.execute("INSERT OR IGNORE INTO settings VALUES(1,'summary_time','18:00')")
        db.execute('INSERT OR IGNORE INTO message_templates(business_id,name,body) VALUES(1,?,?)', ('confirmation', 'Hi {customer_name}, your order is confirmed. Order #{order_id}. Area: {area}. Expected delivery: {delivery_day}, {delivery_date}. Thank you!'))
        if not one(db, 'SELECT id FROM users LIMIT 1'):
            pw = os.environ.get('ADMIN_PASSWORD') or secrets.token_urlsafe(15)
            db.execute('INSERT INTO users(business_id,username,password_hash,role) VALUES(1,?,?,?)', (os.environ.get('ADMIN_USERNAME','admin'),password_hash(pw),'admin'))
            if not os.environ.get('ADMIN_PASSWORD'):
                path = ROOT / 'data' / 'first-login.txt'
                path.write_text('Username: admin\nPassword: ' + pw + '\nDelete this file after your first login.\n', encoding='utf-8')
                print('First login credentials saved to', path, flush=True)

def backup(destination):
    with connect() as db:
        with closing(sqlite3.connect(destination)) as target:
            db.backup(target)