Spaces:
Paused
Paused
File size: 25,387 Bytes
9e497a6 637b36c d4ac480 238a2ce 9e497a6 637b36c 9e497a6 637b36c 9e497a6 5ad28da 9e497a6 637b36c 9e497a6 5ad28da 9e497a6 5ad28da 9e497a6 637b36c 9e497a6 637b36c 50257ac 637b36c 50257ac 637b36c 50257ac 9e497a6 50257ac 9e497a6 637b36c 9e497a6 637b36c 9e497a6 637b36c 9e497a6 d4ac480 9e497a6 5e005fe 9e497a6 5e005fe 9e497a6 bee7763 9e497a6 d4ac480 3f64f7a ac02e1d 3f64f7a d4ac480 9e497a6 5e005fe 9e497a6 0979f89 9e497a6 64e57f1 0979f89 64e57f1 a5e9e6a 0979f89 9ec255b e767948 9e497a6 de60c2e 238a2ce 06167f4 238a2ce 9e497a6 3f64f7a 80078d8 028f096 5b1fd67 028f096 5b1fd67 028f096 5b1fd67 028f096 5b1fd67 028f096 2a848f1 1fa1225 14fbecd 1fa1225 ed191e7 9ec255b ed191e7 a91ee9c 6b2f36c 9e497a6 d4ac480 9ec255b 06167f4 6b2f36c 6c2d0ad 6b2f36c 6c2d0ad 6b2f36c 6c2d0ad 6b2f36c 9ec255b 7b38420 fd477ea 7b38420 fd477ea 7b38420 aa58c7d 7b38420 aa58c7d b31cb3a aa58c7d b31cb3a 8a49d3e 9e497a6 d4ac480 ac02e1d bbc4c5a d4ac480 3f64f7a 028f096 0aefca0 028f096 bee7763 028f096 0aefca0 ed191e7 14fbecd ed2fb7c 504c45c 028f096 8fded15 637b36c 1fa1225 0979f89 91381bf 017347c a4921c5 7b38420 fd477ea 1fa1225 a4921c5 91381bf a4921c5 91381bf a4921c5 1fa1225 9e497a6 504c45c 9e497a6 504c45c 9e497a6 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 | """
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"<TursoRow {self._data}>"
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()
|