"""Small-club transactional store. Encrypt private JSON and uploaded bytes.
Production key must be supplied separately from the database and backups.
"""
import os,sqlite3,json,secrets,hashlib,hmac,time,base64,struct
from pathlib import Path
from contextlib import contextmanager
from cryptography.fernet import Fernet

def password_hash(password):
 if len(password)<12 or len(password)>256:raise ValueError('Use a password of 12–256 characters')
 salt=secrets.token_bytes(16);value=hashlib.pbkdf2_hmac('sha256',password.encode(),salt,600000)
 return 'pbkdf2$600000$'+salt.hex()+'$'+value.hex()
def password_ok(password,stored):
 try:
  _,iterations,salt,value=stored.split('$');actual=hashlib.pbkdf2_hmac('sha256',password.encode(),bytes.fromhex(salt),int(iterations));return hmac.compare_digest(actual.hex(),value)
 except (ValueError,TypeError):return False

def totp_check(secret,code,last=-1,now=None):
 if not secret:return None
 try:key=base64.b32decode(secret.upper()+'='*((8-len(secret)%8)%8))
 except Exception:return None
 counter=int((now or time.time())//30)
 for n in (counter-1,counter,counter+1):
  h=hmac.new(key,struct.pack('>Q',n),hashlib.sha1).digest();o=h[-1]&15;v=(struct.unpack('>I',h[o:o+4])[0]&0x7fffffff)%1000000
  if n>last and hmac.compare_digest(str(code),f'{v:06}'):return n
 return None

class Store:
 def __init__(self,path,key=None,production=False):
  self.root=Path(path);self.root.mkdir(parents=True,exist_ok=True);self.files=self.root/'files';self.files.mkdir(exist_ok=True)
  self.path=self.root/'portal.sqlite3'
  if not key:
   if production:raise RuntimeError('ENCRYPTION_KEY is required in production')
   kp=self.root/'.local-encryption-key'
   if not kp.exists():kp.write_bytes(Fernet.generate_key());kp.chmod(0o600)
   key=kp.read_bytes()
  self.fernet=Fernet(key.encode() if isinstance(key,str) else key)
  with self.db() as c:
   c.executescript('''
   CREATE TABLE IF NOT EXISTS schema_version(version INTEGER NOT NULL);
   INSERT INTO schema_version SELECT 1 WHERE NOT EXISTS(SELECT 1 FROM schema_version);
   CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY,email TEXT UNIQUE NOT NULL,name TEXT NOT NULL,password_hash TEXT NOT NULL,role TEXT NOT NULL DEFAULT 'driver',approved INTEGER NOT NULL DEFAULT 0,disabled INTEGER NOT NULL DEFAULT 0,must_change INTEGER DEFAULT 0,totp_secret BLOB,last_totp INTEGER DEFAULT -1,created_at TEXT DEFAULT CURRENT_TIMESTAMP);
   CREATE TABLE IF NOT EXISTS sessions(token_hash TEXT PRIMARY KEY,user_id INTEGER REFERENCES users(id),csrf TEXT NOT NULL,expires REAL NOT NULL);
   CREATE TABLE IF NOT EXISTS profiles(driver_id INTEGER PRIMARY KEY REFERENCES users(id),cipher BLOB NOT NULL,updated_at TEXT DEFAULT CURRENT_TIMESTAMP);
   CREATE TABLE IF NOT EXISTS cars(id INTEGER PRIMARY KEY,driver_id INTEGER REFERENCES users(id),payload TEXT NOT NULL,current INTEGER DEFAULT 0,archived INTEGER DEFAULT 0);
   CREATE TABLE IF NOT EXISTS memberships(id INTEGER PRIMARY KEY,driver_id INTEGER REFERENCES users(id),season INTEGER NOT NULL,portrait_id TEXT,proof_id TEXT,status TEXT NOT NULL DEFAULT 'pending',paid INTEGER NOT NULL DEFAULT 0,member_number TEXT NOT NULL,verify_token TEXT UNIQUE NOT NULL,note TEXT DEFAULT '',decided_by INTEGER,updated_at TEXT DEFAULT CURRENT_TIMESTAMP,UNIQUE(driver_id,season));
   CREATE TABLE IF NOT EXISTS files(id TEXT PRIMARY KEY,owner_id INTEGER REFERENCES users(id),kind TEXT NOT NULL,name TEXT NOT NULL,mime TEXT NOT NULL,size INTEGER NOT NULL,sha256 TEXT NOT NULL,created_at TEXT DEFAULT CURRENT_TIMESTAMP);
   CREATE TABLE IF NOT EXISTS cases(id INTEGER PRIMARY KEY,owner_id INTEGER REFERENCES users(id),kind TEXT NOT NULL,event_id INTEGER,reference TEXT UNIQUE NOT NULL,status TEXT DEFAULT 'Submitted',cipher BLOB NOT NULL,created_at TEXT DEFAULT CURRENT_TIMESTAMP);
   CREATE TABLE IF NOT EXISTS tasks(id INTEGER PRIMARY KEY,driver_id INTEGER REFERENCES users(id),title TEXT NOT NULL,note TEXT DEFAULT '',status TEXT DEFAULT 'Open',reply TEXT DEFAULT '',file_id TEXT,created_at TEXT DEFAULT CURRENT_TIMESTAMP);
   CREATE TABLE IF NOT EXISTS events(id INTEGER PRIMARY KEY,season INTEGER NOT NULL,round_no INTEGER NOT NULL,event_date TEXT NOT NULL,label TEXT NOT NULL,payload TEXT NOT NULL,revision INTEGER DEFAULT 1,published_revision INTEGER,UNIQUE(season,round_no));
   CREATE TABLE IF NOT EXISTS publications(id INTEGER PRIMARY KEY,event_id INTEGER REFERENCES events(id),revision INTEGER NOT NULL,payload TEXT NOT NULL,created_by INTEGER,created_at TEXT DEFAULT CURRENT_TIMESTAMP,UNIQUE(event_id,revision));
   CREATE TABLE IF NOT EXISTS rules(season INTEGER PRIMARY KEY,payload TEXT NOT NULL);
   CREATE TABLE IF NOT EXISTS audit(id INTEGER PRIMARY KEY,user_id INTEGER,action TEXT NOT NULL,object_ref TEXT NOT NULL,created_at TEXT DEFAULT CURRENT_TIMESTAMP);
   CREATE TABLE IF NOT EXISTS attempts(key TEXT NOT NULL,at REAL NOT NULL);
   CREATE INDEX IF NOT EXISTS attempts_key ON attempts(key,at);
   ''')
  try:self.path.chmod(0o600)
  except OSError:pass
 @contextmanager
 def db(self):
  c=sqlite3.connect(self.path,timeout=20);c.row_factory=sqlite3.Row;c.execute('PRAGMA foreign_keys=ON');c.execute('PRAGMA journal_mode=WAL');c.execute('PRAGMA busy_timeout=20000')
  try:yield c;c.commit()
  except Exception:c.rollback();raise
  finally:c.close()
 def seal(self,obj):return self.fernet.encrypt(json.dumps(obj,ensure_ascii=False).encode())
 def unseal(self,value):return json.loads(self.fernet.decrypt(value))
 def audit(self,c,user,action,ref=''):c.execute('INSERT INTO audit(user_id,action,object_ref) VALUES(?,?,?)',(user,action,str(ref)))
 def profile(self,c,id):
  r=c.execute('SELECT cipher FROM profiles WHERE driver_id=?',(id,)).fetchone();return self.unseal(r['cipher']) if r else {}
 def cars(self,c,id):return [dict(json.loads(r['payload']),id=r['id'],current=bool(r['current']),archived=bool(r['archived'])) for r in c.execute('SELECT * FROM cars WHERE driver_id=? ORDER BY current DESC,id',(id,))]
 def event(self,c,id):
  r=c.execute('SELECT * FROM events WHERE id=?',(id,)).fetchone()
  if not r:return None
  return dict(json.loads(r['payload']),id=r['id'],season=r['season'],round_no=r['round_no'],event_date=r['event_date'],label=r['label'],revision=r['revision'],published_revision=r['published_revision'])
 def save_event(self,c,event,actor,action):
  c.execute('UPDATE events SET payload=?,revision=revision+1,published_revision=NULL WHERE id=?',(json.dumps(event),event['id']))
  # Later snapshots can depend on this event's class movements/standings. Withdraw,
  # do not delete, so a stale championship cannot silently remain public.
  c.execute('UPDATE events SET published_revision=NULL WHERE season=? AND round_no>?',(event['season'],event['round_no']))
  self.audit(c,actor,action,f'event:{event["id"]}')
 def rules(self,c,season):
  from scoring import RULES
  r=c.execute('SELECT payload FROM rules WHERE season=?',(season,)).fetchone();return json.loads(r['payload']) if r else dict(RULES)
 def published(self,c,season=None):
  sql='SELECT p.payload FROM publications p JOIN events e ON p.event_id=e.id AND p.revision=e.published_revision'
  args=()
  if season:sql+=' WHERE e.season=?';args=(season,)
  return [json.loads(r[0]) for r in c.execute(sql,args)]
