""" database.py - Turso HTTP API client for the Telegram Automation Platform. Uses Turso's standard HTTP API directly via `requests` for maximum compatibility. Falls back to local SQLite if TURSO_DATABASE_URL is not set. """ import os import sqlite3 import json import urllib.request import urllib.parse import urllib.error from typing import Any, List, Optional, Dict, Union, cast from dotenv import load_dotenv # type: ignore load_dotenv() # --------------------------------------------------------------------------- # Turso HTTP Client wrapper # --------------------------------------------------------------------------- class TursoRow: """Mimics sqlite3.Row for Turso results so app code stays the same.""" def __init__(self, columns: List[str], values: List[Any]): self._columns = columns self._values = values self._data = dict(zip(columns, values)) def __len__(self): return len(self._values) def __getitem__(self, key): if isinstance(key, int): return self._values[key] return self._data[key] def __iter__(self): return iter(self._values) def keys(self): return self._columns def items(self): return self._data.items() def values(self): return self._values def get(self, key, default=None): return self._data.get(key, default) def __contains__(self, key): return key in self._data def __repr__(self): return f"" class TursoCursor: def __init__(self, columns: List[str], rows: List[List[Any]]): self._columns = columns self._rows = [TursoRow(columns, r) for r in rows] def __len__(self): return len(self._rows) def __iter__(self): return iter(self._rows) def fetchone(self): return self._rows[0] if self._rows else None def fetchall(self): return self._rows class TursoConnection: """Thin wrapper around the Turso HTTP API (pipeline endpoint) using standard urllib.""" def __init__(self, db_url: str, auth_token: str): if db_url.startswith("libsql://"): db_url = db_url.replace("libsql://", "https://", 1) self._url = db_url.rstrip("/") + "/v2/pipeline" self._headers = { "Authorization": f"Bearer {auth_token}", "Content-Type": "application/json", } def execute(self, sql: str, params: tuple = ()) -> TursoCursor: # Build args list for Turso args = [] for p in params: if p is None: args.append({"type": "null", "value": "null"}) elif isinstance(p, bool): # SQLite usually treats bool as integer 0/1, but let's be explicit args.append({"type": "integer", "value": "1" if p else "0"}) elif isinstance(p, int): args.append({"type": "integer", "value": str(p)}) elif isinstance(p, float): args.append({"type": "float", "value": str(p)}) else: args.append({"type": "text", "value": str(p)}) payload = { "requests": [ { "type": "execute", "stmt": { "sql": sql, "args": args, } }, {"type": "close"} ] } data = json.dumps(payload).encode('utf-8') req = urllib.request.Request(self._url, data=data, headers=self._headers, method='POST') try: with urllib.request.urlopen(req, timeout=10) as response: resp_body = response.read().decode('utf-8') resp_json = json.loads(resp_body) # Turso v2 pipeline response structure: # { "results": [ { "type": "ok", "response": { "result": { "cols": [...], "rows": [...] } } }, ... ] } results = resp_json.get("results", []) if not results: return TursoCursor([], []) # The first result corresponds to our 'execute' statement exec_res = results[0] if exec_res.get("type") == "error": raise Exception(exec_res.get("error", {}).get("message", "Unknown Turso Error")) # Extract 'result' object from inside 'response' inner_res = exec_res.get("response", {}).get("result", {}) # Columns cols = [c["name"] for c in inner_res.get("cols", [])] # Rows # Turso returns rows as arrays of { "type": "...", "value": "..." } # We need to extract the raw values. final_rows = [] raw_rows = inner_res.get("rows", []) for r in raw_rows: # r is a list of cells row_vals = [] for cell in r: # cell is {"type": "text", "value": "foo"} # or {"type": "null", "value": None} # or {"type": "integer", "value": "123"} val = cell.get("value") if cell.get("type") == "integer" and val is not None: val = int(val) elif cell.get("type") == "float" and val is not None: val = float(val) row_vals.append(val) final_rows.append(row_vals) return TursoCursor(cols, final_rows) except urllib.error.HTTPError as e: err_body = e.read().decode('utf-8') print(f"Turso HTTP Error {e.code}: {err_body}") raise Exception(f"Turso API Error: {err_body}") except Exception as e: print(f"Turso Connection Error: {str(e)}") raise def commit(self): pass def close(self): pass # --------------------------------------------------------------------------- # Public helpers # --------------------------------------------------------------------------- def get_db_connection() -> Any: db_url = os.getenv("TURSO_DATABASE_URL", "") auth_token = os.getenv("TURSO_AUTH_TOKEN", "") if db_url and (db_url.startswith("libsql://") or db_url.startswith("https://")): # print("DB: Using Turso Cloud") return TursoConnection(db_url, auth_token) # Local SQLite fallback local_path = db_url.replace("file:", "") if db_url else "./telegram_app.db" # print(f"DB: Using local SQLite ({local_path})") conn = sqlite3.connect(local_path) conn.row_factory = sqlite3.Row return conn def init_db(): """Create all tables and seed default settings.""" table_queries = [ """CREATE TABLE IF NOT EXISTS users ( id TEXT PRIMARY KEY, username TEXT UNIQUE NOT NULL, password_hash TEXT NOT NULL, role TEXT DEFAULT 'user', email TEXT, is_active INTEGER DEFAULT 1, passkey_verified INTEGER DEFAULT 0 )""", """CREATE TABLE IF NOT EXISTS accounts ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT NOT NULL, phone_number TEXT NOT NULL, session_string TEXT NOT NULL, api_id TEXT, api_hash TEXT, first_name TEXT, last_name TEXT, username TEXT, profile_photo TEXT, country TEXT, status TEXT DEFAULT 'active', status_detail TEXT, is_active INTEGER DEFAULT 0, last_active DATETIME DEFAULT CURRENT_TIMESTAMP, created_at DATETIME DEFAULT CURRENT_TIMESTAMP )""", """CREATE TABLE IF NOT EXISTS tasks ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT NOT NULL, name TEXT, phone_number TEXT, message_text TEXT, target_groups TEXT, scheduled_time TEXT, status TEXT DEFAULT 'pending', interval_hours INTEGER DEFAULT 0, interval_minutes INTEGER DEFAULT 0, total_targets INTEGER DEFAULT 0, sent_count INTEGER DEFAULT 0, batch_number INTEGER DEFAULT 1, failed_groups TEXT DEFAULT '[]', exclude_groups TEXT DEFAULT '[]', processed_groups TEXT DEFAULT '[]', media_url TEXT, speed_mode TEXT DEFAULT 'balanced', sweep_status TEXT DEFAULT 'idle', deleted_groups TEXT DEFAULT '[]' )""", """CREATE TABLE IF NOT EXISTS leads ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT NOT NULL, group_id TEXT, username TEXT, first_name TEXT, last_name TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP )""", """CREATE TABLE IF NOT EXISTS scrape_history ( id INTEGER PRIMARY KEY AUTOINCREMENT, group_id TEXT NOT NULL, user_id TEXT NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP )""", """CREATE TABLE IF NOT EXISTS scraped_groups ( id TEXT PRIMARY KEY, user_id TEXT NOT NULL, title TEXT, username TEXT, participants_count INTEGER DEFAULT 0, type TEXT, is_private INTEGER DEFAULT 0, country TEXT, user_shows INTEGER DEFAULT 1, global_shows INTEGER DEFAULT 1, source TEXT, can_send_messages INTEGER DEFAULT 1, created_at DATETIME DEFAULT CURRENT_TIMESTAMP )""", """CREATE TABLE IF NOT EXISTS system_settings ( key TEXT PRIMARY KEY, value TEXT )""", """CREATE TABLE IF NOT EXISTS broadcasts ( id INTEGER PRIMARY KEY AUTOINCREMENT, message TEXT NOT NULL, type TEXT DEFAULT 'info', is_active INTEGER DEFAULT 1, created_at DATETIME DEFAULT CURRENT_TIMESTAMP )""", """CREATE TABLE IF NOT EXISTS lead_members ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT NOT NULL, group_id TEXT, tg_id TEXT, username TEXT, first_name TEXT, last_name TEXT, phone TEXT, country TEXT, scraped_at DATETIME DEFAULT CURRENT_TIMESTAMP )""", """CREATE TABLE IF NOT EXISTS account_joins ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT NOT NULL, phone_number TEXT NOT NULL, group_id TEXT NOT NULL, joined_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE(phone_number, group_id) )""", """CREATE TABLE IF NOT EXISTS coupons ( id INTEGER PRIMARY KEY AUTOINCREMENT, code TEXT UNIQUE NOT NULL, price INTEGER DEFAULT 0, max_daily_campaigns INTEGER, max_daily_keywords INTEGER, scrape_limit INTEGER, is_active INTEGER DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP )""", """CREATE TABLE IF NOT EXISTS keyword_logs ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT, username TEXT, keyword TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP )""", """CREATE TABLE IF NOT EXISTS payments ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT NOT NULL, username TEXT, plan TEXT, amount INTEGER DEFAULT 0, status TEXT DEFAULT 'approved', created_at DATETIME DEFAULT CURRENT_TIMESTAMP )""", """CREATE TABLE IF NOT EXISTS plans ( key TEXT PRIMARY KEY, name TEXT NOT NULL, price INTEGER DEFAULT 0, max_daily_campaigns INTEGER DEFAULT 20, max_accounts INTEGER DEFAULT 1, max_daily_keywords INTEGER DEFAULT 5, scrape_limit INTEGER DEFAULT 50, max_templates INTEGER DEFAULT 1, has_premium_access INTEGER DEFAULT 0, perks TEXT -- JSON string of perks )""", """CREATE TABLE IF NOT EXISTS templates ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT NOT NULL, name TEXT NOT NULL, content TEXT NOT NULL, media_url TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP )""", """CREATE TABLE IF NOT EXISTS contacted_targets ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT NOT NULL, target_identifier TEXT NOT NULL, account_phone TEXT NOT NULL, contacted_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE(user_id, target_identifier) )""", """CREATE TABLE IF NOT EXISTS payment_requests ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT NOT NULL, username TEXT, email TEXT, plan_key TEXT NOT NULL, method TEXT NOT NULL, currency TEXT NOT NULL, amount REAL NOT NULL, proof_details TEXT, status TEXT DEFAULT 'pending', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, verified_at DATETIME )""", ] conn = cast(Any, get_db_connection()) # Ensure media_url columns exist on tasks and templates try: conn.execute("ALTER TABLE tasks ADD COLUMN media_url TEXT") except: pass try: conn.execute("ALTER TABLE templates ADD COLUMN media_url TEXT") except: pass try: conn.execute("ALTER TABLE scraped_groups ADD COLUMN can_send_messages INTEGER DEFAULT 1") except: pass try: conn.execute("ALTER TABLE users ADD COLUMN country TEXT DEFAULT 'Nigeria'") except: pass try: conn.execute("""CREATE TABLE IF NOT EXISTS payment_requests ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT NOT NULL, username TEXT, email TEXT, plan_key TEXT NOT NULL, method TEXT NOT NULL, currency TEXT NOT NULL, amount REAL NOT NULL, proof_details TEXT, status TEXT DEFAULT 'pending', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, verified_at DATETIME )""") except Exception: pass try: conn.execute("INSERT OR REPLACE INTO system_settings (key, value) VALUES ('whatsapp_number', 'https://wa.link/p0uv2l')") conn.execute("INSERT OR REPLACE INTO system_settings (key, value) VALUES ('support_email', 'arkilostudios@gmail.com')") conn.execute("INSERT OR REPLACE INTO system_settings (key, value) VALUES ('telegram_support', 'arkitel_support')") except Exception: pass # Sync plan configurations with latest limits: # Free: 5 keywords, 1 template # Basic: 15 keywords, 2 templates # Standard: 30 keywords, 5 templates # Premium: 50 keywords, 10 templates # Unlimited: Full unrestricted limits try: conn.execute("UPDATE plans SET max_daily_keywords = 5, max_templates = 1 WHERE key = 'free'") conn.execute("UPDATE plans SET max_daily_keywords = 15, max_templates = 2 WHERE key = 'basic'") conn.execute("UPDATE plans SET max_daily_keywords = 30, max_templates = 5 WHERE key = 'standard'") conn.execute("UPDATE plans SET max_daily_keywords = 50, max_templates = 10 WHERE key = 'premium'") conn.execute("UPDATE plans SET max_daily_keywords = 999999, max_templates = 999999, max_accounts = 999999, max_daily_campaigns = 999999, scrape_limit = 999999 WHERE key = 'unlimited'") if hasattr(conn, "commit"): conn.commit() except Exception: pass # Ensure coupon table has new fields: target_plan, duration_hours, max_accounts, max_templates for col_name, col_type in [ ("target_plan", "TEXT DEFAULT 'premium'"), ("duration_hours", "INTEGER DEFAULT 0"), ("max_accounts", "INTEGER"), ("max_templates", "INTEGER") ]: try: conn.execute(f"ALTER TABLE coupons ADD COLUMN {col_name} {col_type}") except Exception: pass # Ensure users table has coupon and plan tracking fields: for col_name, col_type in [ ("original_plan", "TEXT DEFAULT 'free'"), ("active_coupon_code", "TEXT"), ("coupon_expires_at", "TEXT"), ("max_accounts", "INTEGER"), ("max_templates", "INTEGER"), ("max_daily_campaigns", "INTEGER"), ("max_daily_keywords", "INTEGER"), ("scrape_limit", "INTEGER"), ("full_name", "TEXT"), ("profile_picture", "TEXT"), ("business_type", "TEXT"), ("company_name", "TEXT"), ("platform_goal", "TEXT"), ("heard_about", "TEXT"), ("country", "TEXT DEFAULT 'Nigeria'") ]: try: conn.execute(f"ALTER TABLE users ADD COLUMN {col_name} {col_type}") except Exception: pass # Seed default welcome coupon if absent try: conn.execute(""" INSERT OR IGNORE INTO coupons (code, price, max_daily_campaigns, max_daily_keywords, scrape_limit, is_active, target_plan, duration_hours) VALUES ('WELCOME20', 0, 50, 15, 100, 1, 'basic', 168) """) if hasattr(conn, "commit"): conn.commit() except Exception: pass # Fast path: check if database schema and seed data already exist to avoid 50+ sequential network round-trips try: plans_check = conn.execute("SELECT COUNT(*) FROM plans").fetchone() if plans_check and plans_check[0] >= 5: print("Database already initialized and up to date.") if hasattr(conn, "close"): conn.close() return except Exception: pass for q in table_queries: conn.execute(q) # Migration: Ensure accounts table has all needed fields needed_cols = [ ("user_id", "TEXT"), ("first_name", "TEXT"), ("last_name", "TEXT"), ("username", "TEXT"), ("profile_photo", "TEXT"), ("country", "TEXT"), ("is_active", "INTEGER DEFAULT 0"), ("created_at", "DATETIME"), ("status_detail", "TEXT") ] for col_name, col_type in needed_cols: try: conn.execute(f"ALTER TABLE accounts ADD COLUMN {col_name} {col_type}") except: pass # Column likely exists # Migration for leads table try: conn.execute("ALTER TABLE leads ADD COLUMN type TEXT DEFAULT 'group'") conn.execute("ALTER TABLE leads ADD COLUMN source TEXT DEFAULT 'scraper'") except: pass # Migration for users table (Premium Features & Plan Limits) user_cols = [ ("plan", "TEXT DEFAULT 'free'"), ("email", "TEXT"), ("scrape_limit", "INTEGER DEFAULT 100"), ("max_accounts", "INTEGER DEFAULT 1"), ("total_scraped", "INTEGER DEFAULT 0"), ("is_approved", "INTEGER DEFAULT 0"), ("payment_proof", "TEXT"), ("max_daily_campaigns", "INTEGER DEFAULT 20"), ("daily_campaign_count", "INTEGER DEFAULT 0"), ("max_daily_keywords", "INTEGER DEFAULT 5"), ("daily_keyword_count", "INTEGER DEFAULT 0"), ("last_reset_date", "TEXT"), ("permanent_excludes", "TEXT DEFAULT '[]'"), ("plan_activated_at", "TEXT"), ("plan_expires_at", "TEXT"), ("created_at", "DATETIME"), ("country", "TEXT"), ("full_name", "TEXT"), ("profile_picture", "TEXT"), ("business_type", "TEXT"), ("company_name", "TEXT"), ("platform_goal", "TEXT"), ("heard_about", "TEXT") ] for col_name, col_def in user_cols: try: conn.execute(f"ALTER TABLE users ADD COLUMN {col_name} {col_def}") except: pass # Migration for coupons table coupon_cols = [ ("max_daily_campaigns", "INTEGER"), ("max_daily_keywords", "INTEGER"), ("scrape_limit", "INTEGER") ] for col_name, col_def in coupon_cols: try: conn.execute(f"ALTER TABLE coupons ADD COLUMN {col_name} {col_def}") except: pass # Migration for tasks table: interval_minutes try: conn.execute("ALTER TABLE tasks ADD COLUMN interval_minutes INTEGER DEFAULT 0") except: pass # Migration for tasks table: exclude_groups try: conn.execute("ALTER TABLE tasks ADD COLUMN exclude_groups TEXT DEFAULT '[]'") except: pass # Migration for tasks table: processed_groups try: conn.execute("ALTER TABLE tasks ADD COLUMN processed_groups TEXT DEFAULT '[]'") except: pass # Migration for plans table: max_templates try: conn.execute("ALTER TABLE plans ADD COLUMN max_templates INTEGER DEFAULT 1") except: pass # Migration for users table: max_templates try: conn.execute("ALTER TABLE users ADD COLUMN max_templates INTEGER DEFAULT 1") except: pass # Update seeding to match user's explicit request: Free(1), Basic(1), Standard(2), Premium(2), Unlimited(999) # Prices: Basic: 2000, Standard: 3500, Premium: 5000 default_plans = [ ("free", "Free Starter", 0, 20, 1, 5, 50, 1, 0, json.dumps(["20 Daily Campaigns", "1 Template", "5 AI Keywords", "Basic Support"])), ("basic", "Growth Basic", 2000, 50, 2, 15, 100, 2, 1, json.dumps(["50 Daily Campaigns", "2 Templates", "15 AI Keywords", "Fleet Compare (2 Accounts)", "Priority Support"])), ("standard", "Pro Standard", 3500, 150, 5, 30, 250, 5, 1, json.dumps(["150 Daily Campaigns", "5 Templates", "30 AI Keywords", "Fleet Compare (5 Accounts)", "Bulk Join Minimizer", "24/7 Support"])), ("premium", "Enterprise Premium", 5000, 300, 10, 50, 500, 10, 1, json.dumps(["300 Daily Campaigns", "10 Templates", "50 AI Keywords", "Fleet Compare (10 Accounts)", "Bulk Join Minimizer", "Dedicated Manager"])), ("unlimited", "Owner Unlimited", 0, 999999, 999999, 999999, 999999, 999999, 1, json.dumps(["Absolute Control", "Unlimited Templates", "Full 100 AI Keywords Batch", "Unlimited Scrapes", "Unlimited Accounts", "Admin Privilege"])) ] for p in default_plans: # Use INSERT OR REPLACE if the key matches, to force update existing seeds conn.execute( "INSERT OR REPLACE INTO plans (key, name, price, max_daily_campaigns, max_accounts, max_daily_keywords, scrape_limit, max_templates, has_premium_access, perks) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", p ) # Seed default settings conn.execute("INSERT OR IGNORE INTO system_settings (key, value) VALUES ('admin_password', 'admin123')") conn.execute("INSERT OR IGNORE INTO system_settings (key, value) VALUES ('global_passkey', '123456')") # High-Performance Indexes for 10,000 - 1,000,000+ User Scale indexes = [ "CREATE INDEX IF NOT EXISTS idx_users_username ON users(username)", "CREATE INDEX IF NOT EXISTS idx_users_role ON users(role)", "CREATE INDEX IF NOT EXISTS idx_accounts_user_id ON accounts(user_id)", "CREATE INDEX IF NOT EXISTS idx_accounts_phone ON accounts(phone_number)", "CREATE INDEX IF NOT EXISTS idx_accounts_status ON accounts(status)", "CREATE INDEX IF NOT EXISTS idx_tasks_user_id ON tasks(user_id)", "CREATE INDEX IF NOT EXISTS idx_tasks_user_status ON tasks(user_id, status)", "CREATE INDEX IF NOT EXISTS idx_tasks_status_time ON tasks(status, scheduled_time)", "CREATE INDEX IF NOT EXISTS idx_leads_user_id ON leads(user_id)", "CREATE INDEX IF NOT EXISTS idx_leads_group_id ON leads(group_id)", "CREATE INDEX IF NOT EXISTS idx_scraped_groups_user_id ON scraped_groups(user_id)", "CREATE INDEX IF NOT EXISTS idx_scrape_history_user_group ON scrape_history(user_id, group_id)", "CREATE INDEX IF NOT EXISTS idx_lead_members_user_group ON lead_members(user_id, group_id)", "CREATE INDEX IF NOT EXISTS idx_account_joins_user_phone ON account_joins(user_id, phone_number)", "CREATE INDEX IF NOT EXISTS idx_payments_user_id ON payments(user_id)", "CREATE INDEX IF NOT EXISTS idx_payment_requests_user ON payment_requests(user_id, status)", "CREATE INDEX IF NOT EXISTS idx_templates_user_id ON templates(user_id)", "CREATE INDEX IF NOT EXISTS idx_keyword_logs_user ON keyword_logs(user_id)" ] for idx_sql in indexes: try: conn.execute(idx_sql) except Exception: pass if hasattr(conn, "commit"): conn.commit() if hasattr(conn, "close"): conn.close() print("Database initialized successfully with performance indexes.") if __name__ == "__main__": init_db()