qshub / utils /db.py
basel00omar's picture
feat: add customer reviews with admin approval and inquiry support system
51a8001
Raw History Blame Contribute Delete
24.3 kB
"""
وحدة اتصال قاعدة البيانات المركزية — Centralized DB Connection
================================================================
استخدام موحّد لقاعدة البيانات عبر جميع الملفات.
يضمن إغلاق الاتصال بأمان حتى عند حدوث خطأ (Context Manager).
الاستخدام:
from utils.db import get_connection, safe_query, safe_execute
# طريقة 1: Context manager (مُوصى به)
with get_connection() as conn:
with conn.cursor() as cur:
cur.execute("SELECT * FROM qto_users")
rows = cur.fetchall()
# طريقة 2: استعلام آمن مع إرجاع DataFrame
df = safe_query("SELECT id, email FROM qto_users WHERE role=%s", ("admin",))
# طريقة 3: تنفيذ أمر كتابة آمن
success, msg = safe_execute(
"INSERT INTO qto_market_prices (item_name, unit, rate_aed) VALUES (%s, %s, %s)",
("Rebar 8mm", "ton", 2668)
)
"""
import pandas as pd
from contextlib import contextmanager
from utils.settings import DbSettings
import sqlite3
import logging
import re
import os
logger = logging.getLogger("qto.db")
def is_sqlite() -> bool:
try:
settings = DbSettings.from_env()
return not settings.host
except Exception:
return True
def translate_sql(sql: str) -> str:
# 1. Parameter markers: %s -> ?
sql = sql.replace("%s", "?")
# 2. DATE_SUB(NOW(), INTERVAL 15 MINUTE) -> datetime('now', 'localtime', '-15 minutes')
sql = re.sub(
r"DATE_SUB\(\s*NOW\(\)\s*,\s*INTERVAL\s+(\d+)\s+MINUTE\)",
r"datetime('now', 'localtime', '-\1 minutes')",
sql,
flags=re.IGNORECASE
)
# 3. NOW() - INTERVAL 30 DAY -> datetime('now', 'localtime', '-30 days')
sql = re.sub(
r"NOW\(\)\s*-\s*INTERVAL\s+(\d+)\s+DAY",
r"datetime('now', 'localtime', '-\1 days')",
sql,
flags=re.IGNORECASE
)
# 4. UTC_TIMESTAMP() -> datetime('now')
sql = re.sub(r"\bUTC_TIMESTAMP\(\)", "datetime('now')", sql, flags=re.IGNORECASE)
# 5. NOW() -> datetime('now', 'localtime')
sql = re.sub(r"\bNOW\(\)", "datetime('now', 'localtime')", sql, flags=re.IGNORECASE)
return sql
class SQLiteCursorWrapper:
def __init__(self, sqlite_cursor):
self.cursor = sqlite_cursor
def execute(self, sql, params=None):
translated_sql = translate_sql(sql)
if params is None:
return self.cursor.execute(translated_sql)
else:
return self.cursor.execute(translated_sql, params)
def executemany(self, sql, data_list):
translated_sql = translate_sql(sql)
return self.cursor.executemany(translated_sql, data_list)
def fetchone(self):
row = self.cursor.fetchone()
if row is None:
return None
return dict(row)
def fetchall(self):
rows = self.cursor.fetchall()
return [dict(r) for r in rows]
@property
def lastrowid(self):
return self.cursor.lastrowid
@property
def description(self):
return self.cursor.description
def __enter__(self):
return self
def __exit__(self, exc_type, exc_val, exc_tb):
self.cursor.close()
class SQLiteConnectionWrapper:
def __init__(self, sqlite_conn):
self.conn = sqlite_conn
self.conn.row_factory = sqlite3.Row
def cursor(self):
return SQLiteCursorWrapper(self.conn.cursor())
def commit(self):
self.conn.commit()
def rollback(self):
self.conn.rollback()
def close(self):
self.conn.close()
def __enter__(self):
return self
def __exit__(self, exc_type, exc_val, exc_tb):
pass
def initialize_sqlite_db(conn):
cur = conn.cursor()
cur.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='qto_users'")
if cur.fetchone():
return
# 1. qto_users
cur.execute("""
CREATE TABLE IF NOT EXISTS qto_users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
email TEXT UNIQUE NOT NULL,
password_hash TEXT,
role TEXT DEFAULT 'user',
email_verified INTEGER DEFAULT 0,
reset_token_hash TEXT,
reset_token_expires_at TEXT,
verification_token_hash TEXT,
last_login_at TEXT,
google_id TEXT,
microsoft_id TEXT,
extra_projects_allowance INTEGER DEFAULT 0,
name TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")
# 2. qto_subscriptions
cur.execute("""
CREATE TABLE IF NOT EXISTS qto_subscriptions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
feature TEXT DEFAULT 'qto',
plan_tier INTEGER DEFAULT 0,
provider TEXT DEFAULT 'manual',
provider_customer_id TEXT,
provider_subscription_id TEXT,
status TEXT DEFAULT 'inactive',
current_period_start TEXT,
current_period_end TEXT,
cancel_at_period_end INTEGER DEFAULT 0,
projects_used INTEGER DEFAULT 0,
ai_calls_used INTEGER DEFAULT 0,
exports_used INTEGER DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")
# 3. qto_projects
cur.execute("""
CREATE TABLE IF NOT EXISTS qto_projects (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
name TEXT NOT NULL,
date TEXT,
boq_data TEXT,
status TEXT DEFAULT 'completed',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE(user_id, name)
);
""")
# 4. qto_usage_logs
cur.execute("""
CREATE TABLE IF NOT EXISTS qto_usage_logs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER,
project_id INTEGER,
event_type TEXT NOT NULL,
quantity INTEGER DEFAULT 1,
metadata TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")
# 5. qto_files
cur.execute("""
CREATE TABLE IF NOT EXISTS qto_files (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
project_id INTEGER,
original_name TEXT NOT NULL,
storage_provider TEXT DEFAULT 'local',
storage_key TEXT NOT NULL,
content_type TEXT,
size_bytes INTEGER DEFAULT 0,
checksum_sha256 TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")
# 6. qto_invoices
cur.execute("""
CREATE TABLE IF NOT EXISTS qto_invoices (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
subscription_id INTEGER,
provider TEXT NOT NULL,
provider_invoice_id TEXT,
amount_aed REAL DEFAULT 0,
currency TEXT DEFAULT 'AED',
status TEXT DEFAULT 'draft',
hosted_invoice_url TEXT,
issued_at TEXT,
paid_at TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")
# 7. qto_audit_logs
cur.execute("""
CREATE TABLE IF NOT EXISTS qto_audit_logs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
actor_user_id INTEGER,
action TEXT NOT NULL,
target_type TEXT,
target_id TEXT,
metadata TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")
# 8. qto_deleted_accounts
cur.execute("""
CREATE TABLE IF NOT EXISTS qto_deleted_accounts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
email TEXT NOT NULL,
deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")
# 8. qto_background_jobs
cur.execute("""
CREATE TABLE IF NOT EXISTS qto_background_jobs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER,
project_id INTEGER,
job_type TEXT NOT NULL,
status TEXT DEFAULT 'queued',
payload TEXT,
result TEXT,
error TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
started_at TEXT,
finished_at TEXT
);
""")
# 9. qto_market_prices
cur.execute("""
CREATE TABLE IF NOT EXISTS qto_market_prices (
id INTEGER PRIMARY KEY AUTOINCREMENT,
item_name TEXT NOT NULL,
unit TEXT,
rate_aed REAL,
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")
# 10. qto_memory_rules
cur.execute("""
CREATE TABLE IF NOT EXISTS qto_memory_rules (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NULL,
original_text TEXT NOT NULL,
mapped_category TEXT NOT NULL,
status TEXT DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")
# 11. qto_agent_conversations
cur.execute("""
CREATE TABLE IF NOT EXISTS qto_agent_conversations (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
agent_role TEXT NOT NULL,
sender TEXT NOT NULL,
message TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")
# 12. qto_customer_complaints
cur.execute("""
CREATE TABLE IF NOT EXISTS qto_customer_complaints (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
complaint_text TEXT NOT NULL,
status TEXT DEFAULT 'open',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")
# 13. qto_project_feedback
cur.execute("""
CREATE TABLE IF NOT EXISTS qto_project_feedback (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
tool_name TEXT NOT NULL,
project_name TEXT NOT NULL,
rating INTEGER NOT NULL,
reason TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")
# 14. qto_inquiries
cur.execute("""
CREATE TABLE IF NOT EXISTS qto_inquiries (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER,
name TEXT,
email TEXT NOT NULL,
subject TEXT NOT NULL,
message TEXT NOT NULL,
category TEXT DEFAULT 'general',
status TEXT DEFAULT 'new',
admin_notes TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")
# 15. qto_reviews
cur.execute("""
CREATE TABLE IF NOT EXISTS qto_reviews (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER,
user_name TEXT NOT NULL,
user_role TEXT,
company TEXT,
rating INTEGER NOT NULL,
review_title TEXT,
review_text TEXT NOT NULL,
is_approved INTEGER DEFAULT 0,
is_featured INTEGER DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")
# Seed admin user if none exists
cur.execute("SELECT COUNT(*) FROM qto_users")
count = cur.fetchone()[0]
if count == 0:
import bcrypt
admin_emails = ["basel00omar.92@gmail.com", "basel.omar.qs@gmail.com", "admin@qto.com"]
admin_password = os.environ.get("QTO_ADMIN_PASSWORD") or "Nodnod1606"
hashed = bcrypt.hashpw(admin_password.encode("utf-8"), bcrypt.gensalt()).decode("utf-8")
for em in admin_emails:
cur.execute(
"INSERT INTO qto_users (email, password_hash, role) VALUES (?, ?, ?)",
(em, hashed, "admin")
)
conn.commit()
print("Local SQLite database seeded with admin users.")
# Seed default prices
try:
from utils.market_prices_logic import DEFAULT_MATERIALS
for m in DEFAULT_MATERIALS:
for em in ["dubai", "abudhabi", "sharjah", "ajman"]:
rate = m[em]
em_name = "Abu Dhabi" if em == "abudhabi" else em.capitalize()
item_name = f"{m['name_en']} ({em_name})"
cur.execute(
"INSERT INTO qto_market_prices (item_name, unit, rate_aed) VALUES (?, ?, ?)",
(item_name, m["unit"], rate)
)
conn.commit()
print("Local SQLite database seeded with default market prices.")
except Exception as ex:
print(f"Could not seed market prices: {ex}")
MOCKUP_REVIEWS = [
{
"user_name": "Eng. Tariq Al-Mansoor",
"user_role": "Senior Quantity Surveyor",
"company": "Al-Mansoor Engineering Consultants, Dubai",
"rating": 5,
"review_title": "Saved 3 days of manual takeoff on our first villa",
"review_text": "أداة استثنائية لحصر كميات الخرسانة والمباني والتشطيبات بدقة عالية ومقارنتها بأسعار السوق الإماراتي. تصدير الـ BOQ إلى Excel منسق جاهز للاستخدام مباشرة وفر علي وفريق العمل ساعات طويلة.",
"is_approved": 1,
"is_featured": 1
},
{
"user_name": "Eng. Sarah Khalil",
"user_role": "Commercial & Tendering Manager",
"company": "Apex Contracting LLC, Abu Dhabi",
"rating": 5,
"review_title": "Seamless integration between Takeoff and Work Programme",
"review_text": "The automatic derivation of cash flow and Gantt charts from the extracted BOQ quantities is a game changer for estimating and planning tenders in the UAE construction market.",
"is_approved": 1,
"is_featured": 1
},
{
"user_name": "Eng. Mohammed Al-Hashimi",
"user_role": "Project Director",
"company": "Dar Al-Binaa Contracting, Sharjah",
"rating": 5,
"review_title": "دقة قراءة المخططات الإنشائية والمعمارية",
"review_text": "جربت عدة أدوات ولكن THE QS HUB متوافق تماماً مع مخططات بلديات دبي والشارقة وعجمان. التعرف على جداول القواعد والميدات والأعمدة تم باحترافية عالية جداً.",
"is_approved": 1,
"is_featured": 1
},
{
"user_name": "Eng. Omar Farooq",
"user_role": "Cost Estimator & QS",
"company": "Modern Build Contracting, Ajman",
"rating": 5,
"review_title": "Game changer for contractor tender submissions",
"review_text": "Speeding up our tender submissions by over 70%. Being able to customize the unit rates based on live UAE market prices gives our management immense confidence.",
"is_approved": 1,
"is_featured": 1
},
{
"user_name": "Eng. Mahmoud Youssef",
"user_role": "Civil & Estimation Engineer",
"company": "Falcon Construction Group, Ras Al Khaimah",
"rating": 5,
"review_title": "واجهة ممتازة ودعم فني متميز وسريع",
"review_text": "المنصة سهلة الاستخدام جداً والمساعد الذكي سارة يساعد في أي استفسار هندسي فوراً. حصر كل عناصر الفيلا مع حماية البنود من السهو والنسيان نقل عملنا لمستوى آخر.",
"is_approved": 1,
"is_featured": 1
}
]
def seed_mockup_reviews():
"""Seed pre-approved mockup reviews if qto_reviews table is empty."""
try:
df = safe_query("SELECT COUNT(*) as cnt FROM qto_reviews")
if not df.empty and int(df.iloc[0]["cnt"]) > 0:
return
for r in MOCKUP_REVIEWS:
safe_execute(
"""
INSERT INTO qto_reviews (user_name, user_role, company, rating, review_title, review_text, is_approved, is_featured)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
""",
(r["user_name"], r["user_role"], r["company"], r["rating"], r["review_title"], r["review_text"], r["is_approved"], r["is_featured"])
)
logger.info("Mockup reviews seeded successfully.")
except Exception as e:
logger.warning(f"Could not seed mockup reviews: {e}")
_POOL = None
def get_pool():
global _POOL
if _POOL is None:
import pymysql
from dbutils.pooled_db import PooledDB
kwargs = DbSettings.from_env().pymysql_kwargs()
_POOL = PooledDB(
creator=pymysql,
maxconnections=15,
mincached=2,
maxcached=5,
blocking=True,
ping=1,
**kwargs
)
return _POOL
_SQLITE_INITIALIZED = False
@contextmanager
def get_connection():
"""
Context manager for safe database connections.
Guarantees connection is closed even if an error occurs.
Usage:
with get_connection() as conn:
with conn.cursor() as cur:
cur.execute(...)
"""
global _SQLITE_INITIALIZED
# SQLite Fallback check
try:
settings = DbSettings.from_env()
use_sqlite = not settings.host
except Exception:
use_sqlite = True
if use_sqlite:
import sqlite3
db_path = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), "qto_local.db")
conn = sqlite3.connect(db_path, timeout=60.0, check_same_thread=False)
try:
conn.execute("PRAGMA journal_mode=WAL;")
conn.execute("PRAGMA synchronous=NORMAL;")
except Exception:
pass
if not _SQLITE_INITIALIZED:
initialize_sqlite_db(conn)
_SQLITE_INITIALIZED = True
wrapper = SQLiteConnectionWrapper(conn)
try:
yield wrapper
finally:
try:
conn.close()
except Exception:
pass
else:
pool = get_pool()
conn = pool.connection()
try:
yield conn
finally:
try:
conn.close()
except Exception:
pass
def safe_query(sql: str, params=None) -> pd.DataFrame:
"""
Executes a SELECT query safely and returns results as a pandas DataFrame.
Always uses parameterized queries to prevent SQL injection.
Args:
sql: SQL query string with %s placeholders
params: tuple of parameters to bind
Returns:
pd.DataFrame of results, or empty DataFrame on error
"""
try:
with get_connection() as conn:
with conn.cursor() as cur:
cur.execute(sql, params or ())
rows = cur.fetchall()
return pd.DataFrame(rows) if rows else pd.DataFrame()
except Exception as e:
# Log the real error instead of silently returning "no rows" — an empty
# DataFrame from a transient failure has caused bugs where users looked
# unsubscribed / access was wrongly denied.
logger.error("safe_query failed: %s | sql=%s", e, sql.strip().split("\n")[0][:200])
return pd.DataFrame()
def safe_execute(sql: str, params=None, many=False, data_list=None) -> tuple:
"""
Executes an INSERT/UPDATE/DELETE query safely with auto-commit.
Always uses parameterized queries to prevent SQL injection.
Args:
sql: SQL statement with %s placeholders
params: tuple of parameters (for single execution)
many: if True, uses executemany with data_list
data_list: list of tuples for batch execution
Returns:
(True, "OK") on success, (False, error_message) on failure
"""
try:
with get_connection() as conn:
with conn.cursor() as cur:
if many and data_list:
cur.executemany(sql, data_list)
else:
cur.execute(sql, params or ())
conn.commit()
return True, "OK"
except Exception as e:
logger.error("safe_execute failed: %s | sql=%s", e, sql.strip().split("\n")[0][:200])
return False, str(e)
@contextmanager
def transaction():
"""Run several writes atomically — commits on success, rolls back on error.
Usage:
with transaction() as cur:
cur.execute("UPDATE ...", (...))
cur.execute("INSERT ...", (...))
Either all statements land or none do.
"""
with get_connection() as conn:
cur = conn.cursor()
try:
yield cur
conn.commit()
except Exception:
try:
conn.rollback()
except Exception:
pass
logger.exception("transaction rolled back")
raise
finally:
try:
cur.close()
except Exception:
pass
# ---------------------------------------------------------------------------
# Centralised active-project state writer
# ---------------------------------------------------------------------------
_MAX_STATE_BYTES = 4 * 1024 * 1024 # 4 MB hard cap (TiDB Cloud JSON ~6 MB)
def _trim_state(state_data: dict) -> dict:
"""Aggressively trim heavy fields so state_json fits in the DB column."""
import copy
sd = copy.copy(state_data)
# Truncate per-page text arrays to 4000 chars (preserves full title block & drawing text)
for key in ("str_texts", "arch_texts"):
if key in sd and isinstance(sd[key], list):
sd[key] = [(t[:4000] if isinstance(t, str) else "") for t in sd[key]]
# Strip classified_pages to absolute minimum
if "classified_pages" in sd and isinstance(sd["classified_pages"], list):
clean = []
for p in sd["classified_pages"]:
clean.append({
"pdf": p.get("pdf", "structural"),
"page_index": p.get("page_index", 0),
"page_num": p.get("page_num", 1),
"detected_type": p.get("detected_type", "unknown"),
"confidence": p.get("confidence", "low"),
"items": p.get("items", []),
})
sd["classified_pages"] = clean
return sd
def upsert_active_state(user_id: int, project_id: int, step: int,
state_data: dict) -> tuple:
"""
Single entry-point for writing to qto_active_projects.
Returns (True, "OK") on success, (False, error_msg) on failure.
"""
import json as _json
trimmed = _trim_state(state_data)
state_json = _json.dumps(trimmed, ensure_ascii=False, default=str)
# Progressive key-dropping until we fit under the limit
for drop_key in ("str_texts", "arch_texts", "classified_pages",
"extraction_results", "confirmed_auto_data",
"boq_items", "boq_meta"):
if len(state_json.encode("utf-8")) <= _MAX_STATE_BYTES:
break
if drop_key in trimmed:
logger.warning("upsert_active_state: dropping '%s' (%d bytes > %d limit)",
drop_key, len(state_json.encode("utf-8")), _MAX_STATE_BYTES)
trimmed[drop_key] = [] if isinstance(trimmed.get(drop_key), list) else {}
state_json = _json.dumps(trimmed, ensure_ascii=False, default=str)
byte_size = len(state_json.encode("utf-8"))
logger.info("upsert_active_state: project=%s step=%s size=%d bytes", project_id, step, byte_size)
try:
df = safe_query(
"SELECT id FROM qto_active_projects WHERE user_id=%s AND project_id=%s",
(user_id, project_id),
)
if df.empty:
return safe_execute(
"INSERT INTO qto_active_projects (user_id, project_id, current_step, state_data) "
"VALUES (%s, %s, %s, %s)",
(user_id, project_id, step, state_json),
)
else:
return safe_execute(
"UPDATE qto_active_projects SET current_step=%s, state_data=%s "
"WHERE user_id=%s AND project_id=%s",
(step, state_json, user_id, project_id),
)
except Exception as e:
logger.error("upsert_active_state FAILED: %s", e)
return False, str(e)