import os import sqlite3 import json from datetime import datetime, timezone DB_PATH = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'wcpy_monetization.db') def get_db(): """Returns a SQLite connection with Row factory enabled.""" conn = sqlite3.connect(DB_PATH, timeout=15.0) conn.row_factory = sqlite3.Row conn.execute("PRAGMA foreign_keys = ON") conn.execute("PRAGMA journal_mode = WAL") return conn def init_db(): """Initializes the database schema and seeds default packages, pricing, and admin.""" conn = get_db() cursor = conn.cursor() # 1. Packages Table cursor.execute(""" CREATE TABLE IF NOT EXISTS packages ( id TEXT PRIMARY KEY, name TEXT NOT NULL, price_monthly REAL NOT NULL DEFAULT 0.0, base_monthly_credits INTEGER NOT NULL DEFAULT 1000, over_quota_policy TEXT NOT NULL DEFAULT 'block', features TEXT NOT NULL, is_custom INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL, updated_at TEXT NOT NULL ) """) # 2. Users Table cursor.execute(""" CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY AUTOINCREMENT, email TEXT UNIQUE NOT NULL, password_hash TEXT NOT NULL, name TEXT NOT NULL, role TEXT NOT NULL DEFAULT 'user', package_id TEXT NOT NULL DEFAULT 'starter', custom_monthly_quota INTEGER, recurring_credits INTEGER NOT NULL DEFAULT 1000, topup_credits INTEGER NOT NULL DEFAULT 0, status TEXT NOT NULL DEFAULT 'active', billing_cycle_day INTEGER NOT NULL DEFAULT 1, last_billing_reset TEXT NOT NULL, api_key TEXT UNIQUE NOT NULL, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, FOREIGN KEY (package_id) REFERENCES packages(id) ON UPDATE CASCADE ) """) # 3. Credit Transactions Ledger cursor.execute(""" CREATE TABLE IF NOT EXISTS credit_transactions ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER NOT NULL, action_type TEXT NOT NULL, credits_deducted INTEGER NOT NULL, recurring_credits_after INTEGER NOT NULL, topup_credits_after INTEGER NOT NULL, description TEXT, metadata TEXT, created_at TEXT NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) """) # 4. Immutable Audit Logs Table cursor.execute(""" CREATE TABLE IF NOT EXISTS audit_logs ( id INTEGER PRIMARY KEY AUTOINCREMENT, admin_id INTEGER NOT NULL, admin_email TEXT NOT NULL, target_user_id INTEGER, target_user_email TEXT, action TEXT NOT NULL, credit_delta INTEGER NOT NULL DEFAULT 0, balance_before INTEGER NOT NULL DEFAULT 0, balance_after INTEGER NOT NULL DEFAULT 0, reason TEXT NOT NULL, ip_address TEXT, created_at TEXT NOT NULL ) """) # 5. Action Pricing Table cursor.execute(""" CREATE TABLE IF NOT EXISTS action_pricing ( action_key TEXT PRIMARY KEY, display_name TEXT NOT NULL, cost_credits INTEGER NOT NULL DEFAULT 1, description TEXT NOT NULL ) """) # 6. Auth Sessions Table cursor.execute(""" CREATE TABLE IF NOT EXISTS user_sessions ( token TEXT PRIMARY KEY, user_id INTEGER NOT NULL, created_at TEXT NOT NULL, expires_at TEXT NOT NULL, ip_address TEXT, user_agent TEXT, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) """) # 7. Crawl Sessions Table cursor.execute(""" CREATE TABLE IF NOT EXISTS crawl_sessions ( id TEXT PRIMARY KEY, user_id INTEGER NOT NULL, name TEXT NOT NULL, start_url TEXT NOT NULL, scope TEXT NOT NULL DEFAULT 'internal', pdf_only INTEGER NOT NULL DEFAULT 0, traversal_mode TEXT NOT NULL DEFAULT 'dfs', max_depth INTEGER NOT NULL DEFAULT 8, max_pages INTEGER NOT NULL DEFAULT 50, google_index_filter INTEGER NOT NULL DEFAULT 1, dead_link_filter INTEGER NOT NULL DEFAULT 1, ocr_enabled INTEGER NOT NULL DEFAULT 1, docs_enabled INTEGER NOT NULL DEFAULT 1, status TEXT NOT NULL DEFAULT 'queued', scanned_pages_count INTEGER NOT NULL DEFAULT 0, emails_found_count INTEGER NOT NULL DEFAULT 0, unique_emails_count INTEGER NOT NULL DEFAULT 0, docs_parsed_count INTEGER NOT NULL DEFAULT 0, credits_used INTEGER NOT NULL DEFAULT 0, primary_queue TEXT NOT NULL DEFAULT '[]', visited_urls TEXT NOT NULL DEFAULT '[]', visited_log TEXT NOT NULL DEFAULT '[]', scraped_emails TEXT NOT NULL DEFAULT '[]', created_at TEXT NOT NULL, updated_at TEXT NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) """) conn.commit() now_iso = datetime.now(timezone.utc).isoformat() # Seed Default Packages if empty default_packages = [ ( 'starter', 'Starter Tier', 19.00, 1000, 'block', json.dumps({ 'max_depth': 3, 'max_pages': 100, 'allow_ocr': False, 'allow_docs': True, 'allow_google_index': True, 'allow_export': True, 'max_concurrency': 1, 'rate_limit_rpm': 30 }), 0, now_iso, now_iso ), ( 'pro', 'Pro Tier', 49.00, 5000, 'block', json.dumps({ 'max_depth': 6, 'max_pages': 500, 'allow_ocr': True, 'allow_docs': True, 'allow_google_index': True, 'allow_export': True, 'max_concurrency': 3, 'rate_limit_rpm': 60 }), 0, now_iso, now_iso ), ( 'business', 'Business Tier', 129.00, 20000, 'block', json.dumps({ 'max_depth': 8, 'max_pages': 2000, 'allow_ocr': True, 'allow_docs': True, 'allow_google_index': True, 'allow_export': True, 'max_concurrency': 5, 'rate_limit_rpm': 120 }), 0, now_iso, now_iso ), ( 'custom_enterprise', 'Custom Enterprise Plan', 299.00, 50000, 'overage', json.dumps({ 'max_depth': 8, 'max_pages': 10000, 'allow_ocr': True, 'allow_docs': True, 'allow_google_index': True, 'allow_export': True, 'max_concurrency': 10, 'rate_limit_rpm': 300 }), 1, now_iso, now_iso ) ] for pkg in default_packages: cursor.execute(""" INSERT OR IGNORE INTO packages (id, name, price_monthly, base_monthly_credits, over_quota_policy, features, is_custom, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) """, pkg) # Seed Default Action Pricing if empty default_pricing = [ ('web_fetch', 'Web Page Crawl / Fetch', 1, 'Standard HTTP request & HTML parsing per page'), ('doc_parse', 'Document Extraction (PDF / Word / TXT)', 2, 'Binary document stream parsing & text extraction'), ('ocr_image', 'OCR Image Extraction', 3, 'Tesseract OCR image scanning & email extraction'), ('google_index_check', 'Google Index SERP Verification', 1, 'Google SERP indexing pre-flight validation'), ('data_export', 'Batch Data Export (Excel / CSV / JSON)', 5, 'Structured export generation of scraped datasets'), ('crawl_job_init', 'Crawl Session Initialization', 1, 'Starting a new multi-threaded crawl session') ] for item in default_pricing: cursor.execute(""" INSERT OR IGNORE INTO action_pricing (action_key, display_name, cost_credits, description) VALUES (?, ?, ?, ?) """, item) conn.commit() conn.close() if __name__ == '__main__': init_db() print("Database initialized successfully at:", DB_PATH)