mivanchenko
c3e520aabb
Test backoffice (smb-crm) / test (push) Successful in 1m43s
Public booking API now rejects a 6th active booking from the same customer_contact within 24h (429), stopping one contact from filling every slot on every resource, while owner-entered manual bookings stay unaffected. Add POST /api/contact: client sites can reach their own owner's inbox directly (via their existing login email) for general inquiries, separate from the agency's leads/Telegram pipeline (n8n/lead-intake.json), which stays reserved for actual prospects contacting the agency itself. Paris Barber Shop's contact form and Rückruf widget now point here; the Rückruf floating widget itself has been removed from the site. Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
607 lines
24 KiB
Python
607 lines
24 KiB
Python
"""Tenancy-safe data-access layer for the booking module (#15, #16).
|
|
|
|
This is the only place that runs raw SQL against resources, resource_hours,
|
|
services, bookings, users, and password_reset_tokens (plus a read-only
|
|
lookup against the existing clients table). Every function takes a
|
|
client_id (or, for password-reset consumption, an unguessable token that
|
|
resolves straight to a user) and injects the tenant filter itself -- callers
|
|
never write "WHERE client_id = ..." by hand.
|
|
"""
|
|
import secrets
|
|
import time
|
|
from datetime import datetime, timedelta, timezone
|
|
|
|
import psycopg
|
|
from werkzeug.security import check_password_hash, generate_password_hash
|
|
|
|
import db
|
|
|
|
|
|
class BookingConflict(Exception):
|
|
"""Raised when a booking would overlap another on the same resource."""
|
|
|
|
|
|
class UnknownResource(Exception):
|
|
"""Raised when a resource_id doesn't belong to the given client_id --
|
|
guards against a booking write smuggling in another tenant's resource."""
|
|
|
|
|
|
class UnknownLocation(Exception):
|
|
"""Raised when a location_id doesn't belong to the given client_id --
|
|
guards create_resource against a location_id smuggled in from another
|
|
tenant."""
|
|
|
|
|
|
def new_id(prefix):
|
|
return f"{prefix}-{int(time.time() * 1000)}-{secrets.token_hex(3)}"
|
|
|
|
|
|
def _list_active(table, client_id):
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
f"SELECT * FROM {table} WHERE client_id = %s AND active ORDER BY name",
|
|
(client_id,))
|
|
return cur.fetchall()
|
|
|
|
|
|
# ---- locations ----
|
|
|
|
def create_location(client_id, name, active=True):
|
|
location_id = new_id("LOC")
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"INSERT INTO locations (location_id, client_id, name, active) "
|
|
"VALUES (%s, %s, %s, %s) RETURNING *",
|
|
(location_id, client_id, name, active))
|
|
row = cur.fetchone()
|
|
conn.commit()
|
|
return row
|
|
|
|
|
|
def get_location(client_id, location_id):
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"SELECT * FROM locations WHERE client_id = %s AND location_id = %s",
|
|
(client_id, location_id))
|
|
return cur.fetchone()
|
|
|
|
|
|
def list_active_locations(client_id):
|
|
return _list_active("locations", client_id)
|
|
|
|
|
|
def list_locations(client_id):
|
|
"""All of client_id's locations, active or not -- for the owner settings
|
|
page, same reasoning as list_resources/list_services."""
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"SELECT * FROM locations WHERE client_id = %s ORDER BY name",
|
|
(client_id,))
|
|
return cur.fetchall()
|
|
|
|
|
|
_LOCATION_UPDATABLE = {"name", "active"}
|
|
|
|
|
|
def update_location(client_id, location_id, **fields):
|
|
"""Update a location's own fields, scoped to client_id. Returns the
|
|
updated row, or None if no such location exists for this client."""
|
|
bad = set(fields) - _LOCATION_UPDATABLE
|
|
if bad:
|
|
raise ValueError(f"not updatable: {', '.join(sorted(bad))}")
|
|
if not fields:
|
|
return get_location(client_id, location_id)
|
|
setsql = ", ".join(f"{c} = %s" for c in fields)
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
f"UPDATE locations SET {setsql} WHERE client_id = %s AND location_id = %s "
|
|
"RETURNING *",
|
|
[*fields.values(), client_id, location_id])
|
|
row = cur.fetchone()
|
|
conn.commit()
|
|
return row
|
|
|
|
|
|
# ---- resources ----
|
|
|
|
def create_resource(client_id, location_id, name, active=True, min_notice_minutes=60,
|
|
max_advance_days=30, buffer_minutes=0):
|
|
"""location_id must be one of client_id's own locations -- resources
|
|
(individual bookable staff) always belong to a Filiale. Raises
|
|
UnknownLocation if it isn't."""
|
|
if get_location(client_id, location_id) is None:
|
|
raise UnknownLocation(f"no location {location_id} for client {client_id}")
|
|
resource_id = new_id("RS")
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"INSERT INTO resources (resource_id, client_id, location_id, name, active, "
|
|
"min_notice_minutes, max_advance_days, buffer_minutes) "
|
|
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s) RETURNING *",
|
|
(resource_id, client_id, location_id, name, active, min_notice_minutes,
|
|
max_advance_days, buffer_minutes))
|
|
row = cur.fetchone()
|
|
conn.commit()
|
|
return row
|
|
|
|
|
|
def get_resource(client_id, resource_id):
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"SELECT * FROM resources WHERE client_id = %s AND resource_id = %s",
|
|
(client_id, resource_id))
|
|
return cur.fetchone()
|
|
|
|
|
|
def set_resource_hours(client_id, resource_id, weekday, opens_at, closes_at):
|
|
"""Upsert the opening hours for one weekday (0=Monday..6=Sunday) of a
|
|
client's own resource, or clear that weekday (closed all day) if either
|
|
opens_at/closes_at is None. Raises UnknownResource if resource_id isn't
|
|
client_id's."""
|
|
if get_resource(client_id, resource_id) is None:
|
|
raise UnknownResource(f"no resource {resource_id} for client {client_id}")
|
|
if opens_at is None or closes_at is None:
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"DELETE FROM resource_hours WHERE resource_id = %s AND weekday = %s",
|
|
(resource_id, weekday))
|
|
conn.commit()
|
|
return None
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"INSERT INTO resource_hours (resource_id, weekday, opens_at, closes_at) "
|
|
"VALUES (%s, %s, %s, %s) "
|
|
"ON CONFLICT (resource_id, weekday) DO UPDATE SET "
|
|
"opens_at = EXCLUDED.opens_at, closes_at = EXCLUDED.closes_at "
|
|
"RETURNING *",
|
|
(resource_id, weekday, opens_at, closes_at))
|
|
row = cur.fetchone()
|
|
conn.commit()
|
|
return row
|
|
|
|
|
|
_RESOURCE_UPDATABLE = {"name", "active", "min_notice_minutes", "max_advance_days",
|
|
"buffer_minutes"}
|
|
|
|
|
|
def update_resource(client_id, resource_id, **fields):
|
|
"""Update a resource's own settings (min_notice/max_advance/buffer/etc.),
|
|
scoped to client_id. Returns the updated row, or None if no such resource
|
|
exists for this client."""
|
|
bad = set(fields) - _RESOURCE_UPDATABLE
|
|
if bad:
|
|
raise ValueError(f"not updatable: {', '.join(sorted(bad))}")
|
|
if not fields:
|
|
return get_resource(client_id, resource_id)
|
|
setsql = ", ".join(f"{c} = %s" for c in fields)
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
f"UPDATE resources SET {setsql} WHERE client_id = %s AND resource_id = %s "
|
|
"RETURNING *",
|
|
[*fields.values(), client_id, resource_id])
|
|
row = cur.fetchone()
|
|
conn.commit()
|
|
return row
|
|
|
|
|
|
def delete_resource(client_id, resource_id):
|
|
"""Permanently remove a resource (and its resource_hours), scoped to
|
|
client_id. Returns resource_id on success, None if no such resource
|
|
exists for this client. Existing bookings against this resource_id are
|
|
left as-is (no FK, matching this schema's convention) -- the owner
|
|
agenda already falls back to showing the raw resource_id in place of a
|
|
name for a booking whose resource no longer resolves (see
|
|
owner_booking.py's _agenda_days), same as it does for a deactivated one."""
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"DELETE FROM resources WHERE client_id = %s AND resource_id = %s RETURNING resource_id",
|
|
(client_id, resource_id))
|
|
row = cur.fetchone()
|
|
if row is None:
|
|
conn.commit()
|
|
return None
|
|
cur.execute("DELETE FROM resource_hours WHERE resource_id = %s", (resource_id,))
|
|
conn.commit()
|
|
return row["resource_id"]
|
|
|
|
|
|
def get_resource_hours(client_id, resource_id):
|
|
"""Return {weekday: (opens_at, closes_at)} for client_id's own resource
|
|
(empty for a resource with no hours configured yet, or one that isn't
|
|
client_id's)."""
|
|
if get_resource(client_id, resource_id) is None:
|
|
return {}
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"SELECT weekday, opens_at, closes_at FROM resource_hours "
|
|
"WHERE resource_id = %s", (resource_id,))
|
|
return {r["weekday"]: (r["opens_at"], r["closes_at"]) for r in cur.fetchall()}
|
|
|
|
|
|
# ---- services ----
|
|
|
|
def create_service(client_id, name, duration_minutes, price=None, active=True):
|
|
service_id = new_id("SV")
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"INSERT INTO services (service_id, client_id, name, duration_minutes, "
|
|
"price, active) VALUES (%s, %s, %s, %s, %s, %s) RETURNING *",
|
|
(service_id, client_id, name, duration_minutes, price, active))
|
|
row = cur.fetchone()
|
|
conn.commit()
|
|
return row
|
|
|
|
|
|
def get_service(client_id, service_id):
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"SELECT * FROM services WHERE client_id = %s AND service_id = %s",
|
|
(client_id, service_id))
|
|
return cur.fetchone()
|
|
|
|
|
|
def list_active_services(client_id):
|
|
return _list_active("services", client_id)
|
|
|
|
|
|
def list_services(client_id):
|
|
"""All of client_id's services, active or not -- for the owner settings
|
|
page (#21), which must show (and let the owner reactivate) deactivated
|
|
services too, unlike the public/manual-booking pickers."""
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"SELECT * FROM services WHERE client_id = %s ORDER BY name",
|
|
(client_id,))
|
|
return cur.fetchall()
|
|
|
|
|
|
_SERVICE_UPDATABLE = {"name", "duration_minutes", "price", "active"}
|
|
|
|
|
|
def update_service(client_id, service_id, **fields):
|
|
"""Update a service's own fields, scoped to client_id. Returns the
|
|
updated row, or None if no such service exists for this client."""
|
|
bad = set(fields) - _SERVICE_UPDATABLE
|
|
if bad:
|
|
raise ValueError(f"not updatable: {', '.join(sorted(bad))}")
|
|
if not fields:
|
|
return get_service(client_id, service_id)
|
|
setsql = ", ".join(f"{c} = %s" for c in fields)
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
f"UPDATE services SET {setsql} WHERE client_id = %s AND service_id = %s "
|
|
"RETURNING *",
|
|
[*fields.values(), client_id, service_id])
|
|
row = cur.fetchone()
|
|
conn.commit()
|
|
return row
|
|
|
|
|
|
def list_active_resources(client_id):
|
|
return _list_active("resources", client_id)
|
|
|
|
|
|
def list_resources(client_id):
|
|
"""All of client_id's resources, active or not -- unlike
|
|
list_active_resources, used where a name lookup must still resolve for a
|
|
booking made against a resource that's since been deactivated (the owner
|
|
agenda, #20)."""
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"SELECT * FROM resources WHERE client_id = %s ORDER BY name",
|
|
(client_id,))
|
|
return cur.fetchall()
|
|
|
|
|
|
# ---- bookings ----
|
|
|
|
_BOOKING_UPDATABLE = {"resource_id", "customer_name", "customer_contact",
|
|
"service", "start_time", "end_time", "status"}
|
|
|
|
|
|
def create_booking(client_id, resource_id, customer_name, customer_contact,
|
|
service, start_time, end_time, source=None, status="confirmed"):
|
|
"""Insert a booking. Raises UnknownResource if resource_id isn't one of
|
|
client_id's own resources (the EXCLUDE constraint only scopes overlap by
|
|
resource_id, so this is the one place that has to stop a guessed
|
|
resource_id from another tenant). Raises BookingConflict if it overlaps
|
|
an existing booking on the same resource -- the Postgres EXCLUDE
|
|
constraint is the single source of truth for that, this just turns it
|
|
into a clean error."""
|
|
if get_resource(client_id, resource_id) is None:
|
|
raise UnknownResource(f"no resource {resource_id} for client {client_id}")
|
|
booking_id = new_id("BK")
|
|
try:
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"INSERT INTO bookings (booking_id, created_at, client_id, resource_id, "
|
|
"customer_name, customer_contact, service, start_time, end_time, "
|
|
"source, status) VALUES (%s, now(), %s, %s, %s, %s, %s, %s, %s, %s, %s) "
|
|
"RETURNING *",
|
|
(booking_id, client_id, resource_id, customer_name, customer_contact,
|
|
service, start_time, end_time, source, status))
|
|
row = cur.fetchone()
|
|
conn.commit()
|
|
except (psycopg.errors.ExclusionViolation, psycopg.errors.DeadlockDetected) as e:
|
|
# Two genuinely concurrent inserts racing the same exclusion-constraint
|
|
# index probe can deadlock instead of one cleanly losing to the other
|
|
# (each waits on a lock the other holds) -- Postgres aborts one side
|
|
# with DeadlockDetected rather than ExclusionViolation. The only way
|
|
# this INSERT can deadlock at all is via that index, so it still means
|
|
# "this resource/time is already booked."
|
|
raise BookingConflict(
|
|
f"resource {resource_id} is already booked for that time") from e
|
|
return row
|
|
|
|
|
|
def get_booking(client_id, booking_id):
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"SELECT * FROM bookings WHERE client_id = %s AND booking_id = %s",
|
|
(client_id, booking_id))
|
|
return cur.fetchone()
|
|
|
|
|
|
def list_bookings(client_id):
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"SELECT * FROM bookings WHERE client_id = %s ORDER BY start_time",
|
|
(client_id,))
|
|
return cur.fetchall()
|
|
|
|
|
|
def list_bookings_between(client_id, start, end):
|
|
"""Every one of client_id's bookings (any resource, any status --
|
|
including cancelled, so the owner agenda (#20) can still show a
|
|
cancelled slot rather than silently dropping it) whose start_time falls
|
|
in [start, end)."""
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"SELECT * FROM bookings WHERE client_id = %s AND start_time >= %s "
|
|
"AND start_time < %s ORDER BY start_time",
|
|
(client_id, start, end))
|
|
return cur.fetchall()
|
|
|
|
|
|
def list_active_bookings_for_resource(client_id, resource_id, start, end,
|
|
exclude_booking_id=None):
|
|
"""Bookings on client_id's own resource_id, not cancelled, that fall
|
|
within [start, end) -- used by the availability engine to keep booked
|
|
slots (and their buffer) out of the generated slot list. Pass
|
|
exclude_booking_id when validating a reschedule so a booking doesn't
|
|
count as a conflict against itself."""
|
|
if get_resource(client_id, resource_id) is None:
|
|
return []
|
|
sql = ("SELECT * FROM bookings WHERE client_id = %s AND resource_id = %s "
|
|
"AND status != 'cancelled' AND start_time < %s AND end_time > %s")
|
|
params = [client_id, resource_id, end, start]
|
|
if exclude_booking_id is not None:
|
|
sql += " AND booking_id != %s"
|
|
params.append(exclude_booking_id)
|
|
sql += " ORDER BY start_time"
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute(sql, params)
|
|
return cur.fetchall()
|
|
|
|
|
|
def count_recent_bookings_by_contact(client_id, customer_contact, since):
|
|
"""Count of client_id's non-cancelled bookings for customer_contact
|
|
created at or after `since` -- the public booking API's per-contact rate
|
|
limit reads this to stop one contact from filling every slot on every
|
|
resource. Owner-entered bookings (source="owner") count here too, since
|
|
an owner double-booking themselves in isn't the scenario this guards
|
|
against and excluding it would only add a footgun for no benefit."""
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"SELECT count(*) AS n FROM bookings WHERE client_id = %s "
|
|
"AND customer_contact = %s AND status != 'cancelled' "
|
|
"AND created_at >= %s",
|
|
(client_id, customer_contact, since))
|
|
return cur.fetchone()["n"]
|
|
|
|
|
|
def update_booking(client_id, booking_id, **fields):
|
|
"""Update a booking scoped to client_id (e.g. reschedule/cancel).
|
|
Returns the updated row, or None if no such booking exists for this
|
|
client. Raises UnknownResource if fields moves the booking onto another
|
|
tenant's resource_id. Raises BookingConflict if the update would overlap
|
|
another booking on the same resource."""
|
|
bad = set(fields) - _BOOKING_UPDATABLE
|
|
if bad:
|
|
raise ValueError(f"not updatable: {', '.join(sorted(bad))}")
|
|
if not fields:
|
|
return get_booking(client_id, booking_id)
|
|
if "resource_id" in fields and get_resource(client_id, fields["resource_id"]) is None:
|
|
raise UnknownResource(
|
|
f"no resource {fields['resource_id']} for client {client_id}")
|
|
setsql = ", ".join(f"{c} = %s" for c in fields)
|
|
try:
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
f"UPDATE bookings SET {setsql} WHERE client_id = %s AND booking_id = %s "
|
|
"RETURNING *",
|
|
[*fields.values(), client_id, booking_id])
|
|
row = cur.fetchone()
|
|
conn.commit()
|
|
except (psycopg.errors.ExclusionViolation, psycopg.errors.DeadlockDetected) as e:
|
|
raise BookingConflict("resource is already booked for that time") from e
|
|
return row
|
|
|
|
|
|
# ---- clients (the clients table itself is db.py's; these are just the
|
|
# booking flow's own read/write of its own client's config) ----
|
|
|
|
def get_client(client_id):
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute("SELECT * FROM clients WHERE client_id = %s", (client_id,))
|
|
return cur.fetchone()
|
|
|
|
|
|
def get_client_by_slug(slug):
|
|
"""Resolve a client for the public /book/<slug> page. slug is
|
|
unauthenticated user input, so this is the one lookup that goes straight
|
|
from an untrusted string to a client_id -- every other public-booking
|
|
call still requires the resolved client_id explicitly."""
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute("SELECT * FROM clients WHERE slug = %s", (slug,))
|
|
return cur.fetchone()
|
|
|
|
|
|
def get_client_by_ics_token(token):
|
|
"""Resolve a client for the per-client ICS feed (#22) -- like
|
|
get_client_by_slug, this is the one lookup that goes straight from
|
|
untrusted request input (the ?token= query param) to a client_id, so the
|
|
feed can be scoped to exactly one tenant without a separate client_id
|
|
param that could be swapped independently of the token."""
|
|
if not token:
|
|
return None
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute("SELECT * FROM clients WHERE ics_token = %s", (token,))
|
|
return cur.fetchone()
|
|
|
|
|
|
def ensure_ics_token(client_id):
|
|
"""Return the client's ics_token, generating and persisting one on first
|
|
use (#22). Lazy rather than a one-off backfill migration, so clients
|
|
onboarded before this ticket still get a working subscribe URL the first
|
|
time their settings page loads. The UPDATE ... WHERE ics_token IS NULL
|
|
guard means a losing concurrent call re-reads the winner's token instead
|
|
of overwriting it -- same single-connection read-then-write shape as
|
|
consume_password_reset_token."""
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute("SELECT ics_token FROM clients WHERE client_id = %s", (client_id,))
|
|
row = cur.fetchone()
|
|
if row is None:
|
|
return None
|
|
if row["ics_token"]:
|
|
return row["ics_token"]
|
|
cur.execute(
|
|
"UPDATE clients SET ics_token = %s WHERE client_id = %s AND ics_token IS NULL "
|
|
"RETURNING ics_token",
|
|
(secrets.token_urlsafe(24), client_id))
|
|
row = cur.fetchone()
|
|
if row is None:
|
|
cur.execute("SELECT ics_token FROM clients WHERE client_id = %s", (client_id,))
|
|
row = cur.fetchone()
|
|
conn.commit()
|
|
return row["ics_token"]
|
|
|
|
|
|
_CLIENT_UPDATABLE = {"auto_confirm", "notify_channel"}
|
|
|
|
|
|
def update_client(client_id, **fields):
|
|
"""Update a client's own booking settings (auto_confirm/notify_channel),
|
|
for the owner settings page (#21). The only booking-flow write to
|
|
clients -- everything else about a client is the CRM operator's via
|
|
db.py/app.py. Returns the updated row, or None if client_id is
|
|
unknown."""
|
|
bad = set(fields) - _CLIENT_UPDATABLE
|
|
if bad:
|
|
raise ValueError(f"not updatable: {', '.join(sorted(bad))}")
|
|
if not fields:
|
|
return get_client(client_id)
|
|
setsql = ", ".join(f"{c} = %s" for c in fields)
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
f"UPDATE clients SET {setsql} WHERE client_id = %s RETURNING *",
|
|
[*fields.values(), client_id])
|
|
row = cur.fetchone()
|
|
conn.commit()
|
|
return row
|
|
|
|
|
|
# ---- users (owner login) ----
|
|
|
|
def create_user(client_id, email, password):
|
|
user_id = new_id("U")
|
|
password_hash = generate_password_hash(password)
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"INSERT INTO users (user_id, client_id, email, password_hash) "
|
|
"VALUES (%s, %s, %s, %s) RETURNING *",
|
|
(user_id, client_id, email, password_hash))
|
|
row = cur.fetchone()
|
|
conn.commit()
|
|
return row
|
|
|
|
|
|
def get_user_by_email(client_id, email):
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"SELECT * FROM users WHERE client_id = %s AND email = %s",
|
|
(client_id, email))
|
|
return cur.fetchone()
|
|
|
|
|
|
def find_user_by_email(email):
|
|
"""Login lookup (#19): users.email is globally unique, and at login time
|
|
there's no client_id yet to scope by -- resolving client_id is exactly
|
|
what a successful login establishes for the session."""
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute("SELECT * FROM users WHERE email = %s", (email,))
|
|
return cur.fetchone()
|
|
|
|
|
|
def get_user(user_id):
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute("SELECT * FROM users WHERE user_id = %s", (user_id,))
|
|
return cur.fetchone()
|
|
|
|
|
|
def list_users(client_id=None):
|
|
"""Owner accounts for the CRM operator dashboard (#19). Unlike the rest
|
|
of this module, this one is deliberately allowed to span every tenant
|
|
when client_id is omitted -- an operator manages all clients, not one."""
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
if client_id is None:
|
|
cur.execute(
|
|
"SELECT user_id, client_id, email, created_at FROM users "
|
|
"ORDER BY client_id, email")
|
|
else:
|
|
cur.execute(
|
|
"SELECT user_id, client_id, email, created_at FROM users "
|
|
"WHERE client_id = %s ORDER BY email", (client_id,))
|
|
return cur.fetchall()
|
|
|
|
|
|
def verify_password(user, password):
|
|
return check_password_hash(user["password_hash"], password)
|
|
|
|
|
|
# ---- password reset ----
|
|
|
|
def create_password_reset_token(user_id, ttl_minutes=30):
|
|
token = secrets.token_urlsafe(32)
|
|
expires_at = datetime.now(timezone.utc) + timedelta(minutes=ttl_minutes)
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"INSERT INTO password_reset_tokens (token, user_id, expires_at) "
|
|
"VALUES (%s, %s, %s) RETURNING *",
|
|
(token, user_id, expires_at))
|
|
row = cur.fetchone()
|
|
conn.commit()
|
|
return row
|
|
|
|
|
|
def consume_password_reset_token(token, new_password):
|
|
"""Validate + single-use consume a reset token, then set the new
|
|
password. Returns the user_id on success, None if the token is
|
|
missing/expired/already used -- the UPDATE ... WHERE used_at IS NULL
|
|
makes the consume-and-invalidate step atomic."""
|
|
with db.connect() as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"UPDATE password_reset_tokens SET used_at = now() "
|
|
"WHERE token = %s AND used_at IS NULL AND expires_at > now() "
|
|
"RETURNING user_id",
|
|
(token,))
|
|
row = cur.fetchone()
|
|
if row is None:
|
|
conn.commit()
|
|
return None
|
|
user_id = row["user_id"]
|
|
cur.execute(
|
|
"UPDATE users SET password_hash = %s WHERE user_id = %s",
|
|
(generate_password_hash(new_password), user_id))
|
|
conn.commit()
|
|
return user_id
|