islamahmed/serptracker
0
1# -*- coding: utf-8 -*-2 3import os4import time5import psycopg26from datetime import datetime7from typing import Dict, List, Optional, Tuple8import requests9import pandas as pd10import gradio as gr11from urllib.parse import urlparse, urlunparse12 13# --- 1. تحميل الإعدادات الحساسة ---14GOOGLE_API_KEY = os.getenv("GOOGLE_API_KEY")15GOOGLE_CSE_ID = os.getenv("GOOGLE_CSE_ID")16DATABASE_URL = os.getenv("DATABASE_URL")17 18# --- 2. الإعدادات العامة والثوابت ---19APP_NAME = "SERP Tracker"20MATCH_MODES = ["domain", "subdomain", "exact_url"]21SEARCH_ENDPOINT = "https://www.googleapis.com/customsearch/v1"22 23# --- قائمة الدول الكاملة ---24COUNTRIES = {25 "Global": {"gl": "", "cr": ""},26 "United States": {"gl": "us", "cr": "countryUS"},27 "United Kingdom": {"gl": "uk", "cr": "countryUK"},28 "Canada": {"gl": "ca", "cr": "countryCA"},29 "Australia": {"gl": "au", "cr": "countryAU"},30 "Germany": {"gl": "de", "cr": "countryDE"},31 "France": {"gl": "fr", "cr": "countryFR"},32 "Spain": {"gl": "es", "cr": "countryES"},33 "Italy": {"gl": "it", "cr": "countryIT"},34 "Netherlands": {"gl": "nl", "cr": "countryNL"},35 "Sweden": {"gl": "se", "cr": "countrySE"},36 "Norway": {"gl": "no", "cr": "countryNO"},37 "Finland": {"gl": "fi", "cr": "countryFI"},38 "Denmark": {"gl": "dk", "cr": "countryDK"},39 "Ireland": {"gl": "ie", "cr": "countryIE"},40 "India": {"gl": "in", "cr": "countryIN"},41 "Singapore": {"gl": "sg", "cr": "countrySG"},42 "United Arab Emirates": {"gl": "ae", "cr": "countryAE"},43 "Saudi Arabia": {"gl": "sa", "cr": "countrySA"},44 "Egypt": {"gl": "eg", "cr": "countryEG"},45 "Morocco": {"gl": "ma", "cr": "countryMA"},46 "Algeria": {"gl": "dz", "cr": "countryDZ"},47 "Tunisia": {"gl": "tn", "cr": "countryTN"},48 "Kuwait": {"gl": "kw", "cr": "countryKW"},49 "Qatar": {"gl": "qa", "cr": "countryQA"},50 "Bahrain": {"gl": "bh", "cr": "countryBH"},51 "Oman": {"gl": "om", "cr": "countryOM"},52 "Turkey": {"gl": "tr", "cr": "countryTR"},53 "Israel": {"gl": "il", "cr": "countryIL"},54 "Brazil": {"gl": "br", "cr": "countryBR"},55 "Mexico": {"gl": "mx", "cr": "countryMX"},56 "Argentina": {"gl": "ar", "cr": "countryAR"},57 "Chile": {"gl": "cl", "cr": "countryCL"},58 "Colombia": {"gl": "co", "cr": "countryCO"},59 "Japan": {"gl": "jp", "cr": "countryJP"},60 "South Korea": {"gl": "kr", "cr": "countryKR"},61 "Indonesia": {"gl": "id", "cr": "countryID"},62 "Philippines": {"gl": "ph", "cr": "countryPH"},63 "Vietnam": {"gl": "vn", "cr": "countryVN"},64 "Thailand": {"gl": "th", "cr": "countryTH"},65}66 67 68# --- 3. دوال التعامل مع قاعدة البيانات (PostgreSQL) ---69 70def get_db_connection():71 return psycopg2.connect(DATABASE_URL)72 73def get_user_by_code(code: str) -> Optional[Dict]:74 user_dict = None75 if not code: return None76 try:77 with get_db_connection() as conn, conn.cursor() as cur:78 cur.execute("SELECT * FROM users WHERE code = %s;", (code,))79 user_row = cur.fetchone()80 if user_row:81 user_dict = {desc[0]: value for desc, value in zip(cur.description, user_row)}82 except Exception as e:83 print(f"Database Error (get_user_by_code): {e}")84 return user_dict85 86def deduct_credits(code: str, amount: int):87 try:88 with get_db_connection() as conn, conn.cursor() as cur:89 cur.execute("UPDATE users SET credits_remaining = credits_remaining - %s, last_used_at = NOW() WHERE code = %s;", (amount, code))90 except Exception as e:91 print(f"Database Error (deduct_credits): {e}")92 93def save_snapshot_rows_psql(rows: List[Tuple]):94 if not rows: return95 try:96 with get_db_connection() as conn, conn.cursor() as cur:97 cur.executemany("""98 INSERT INTO snapshots (keyword, domain, country, gl, hl, google_host, cr, match_mode, rank, matched_url, checked_at, run_id)99 VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)100 """, rows)101 except Exception as e:102 print(f"Database Error (save_snapshot_rows_psql): {e}")103 104def load_previous_ranks_psql(domain: str, keywords: List[str], country_label: str) -> Dict[Tuple[str, str], int]:105 last = {}106 if not keywords: return last107 try:108 with get_db_connection() as conn, conn.cursor() as cur:109 query = """110 SELECT DISTINCT ON (keyword) keyword, rank111 FROM snapshots112 WHERE domain = %s AND keyword = ANY(%s) AND country = %s113 ORDER BY keyword, checked_at DESC;114 """115 cur.execute(query, (domain, keywords, country_label))116 rows = cur.fetchall()117 for kw, r in rows:118 last[(kw, domain)] = r119 except Exception as e:120 print(f"Database Error (load_previous_ranks_psql): {e}")121 return last122 123# --- 4. دوال مساعدة ومنطق البحث ---124 125def normalize_url(u: str) -> str:126 try:127 p = urlparse(u)128 netloc = p.netloc.lower().removeprefix("www.")129 path = p.path.rstrip('/') or "/"130 return urlunparse((p.scheme.lower() or "https", netloc, path, '', '', ''))131 except Exception: return u132 133def match_result(target_domain: str, candidate_url: str, mode: str = "domain") -> bool:134 td_norm = target_domain.lower().strip().removeprefix("www.")135 c_norm = normalize_url(candidate_url)136 netloc = urlparse(c_norm).netloc137 if mode == "domain": return netloc == td_norm or netloc.endswith("." + td_norm)138 elif mode == "subdomain": return netloc == td_norm139 elif mode == "exact_url": return c_norm == normalize_url(td_norm if td_norm.startswith("http") else f"https://{td_norm}")140 return False141 142class GoogleSearchClient:143 def __init__(self, api_key: str, cse_id: str, rate_qps: float = 1.5, max_retries: int = 3):144 self.api_key, self.cse_id = api_key, cse_id145 self.rate_interval = 1.0 / rate_qps146 self.max_retries = max_retries147 self._last_call_ts = 0.0148 149 def _respect_rate(self):150 elapsed = time.time() - self._last_call_ts151 if elapsed < self.rate_interval: time.sleep(self.rate_interval - elapsed)152 153 def search_page(self, query: str, **kwargs) -> dict:154 params = {"key": self.api_key, "cx": self.cse_id, "q": query, "googlehost": "google.com", **kwargs}155 for attempt in range(self.max_retries + 1):156 self._respect_rate()157 try:158 resp = requests.get(SEARCH_ENDPOINT, params=params, timeout=20)159 self._last_call_ts = time.time()160 resp.raise_for_status()161 return resp.json()162 except requests.exceptions.RequestException as e:163 print(f"Attempt {attempt+1} failed: {e}")164 if attempt >= self.max_retries: raise165 time.sleep(2 ** (attempt + 1))166 return {}167 168 def find_rank_smart(self, query: str, domain: str, mode: str, **kw) -> Tuple[Optional[int], Optional[str], int]:169 api_calls = 0170 for start in range(1, 101, 10):171 data = self.search_page(query, start=start, num=10, **kw)172 api_calls += 1173 items = data.get("items", [])174 175 for i, item in enumerate(items):176 rank = start + i177 link = item.get("link") or ""178 if link and match_result(domain, link, mode=mode):179 return rank, normalize_url(link), api_calls180 181 if not items or len(items) < 10:182 break183 184 return None, None, api_calls185 186def run_keywords(keywords: List[str], domain: str, country_label: str,187 gl: str, cr: str, match_mode: str,188 progress: Optional[gr.Progress]) -> Tuple[pd.DataFrame, int]:189 190 client = GoogleSearchClient(GOOGLE_API_KEY, GOOGLE_CSE_ID)191 run_id = datetime.utcnow().strftime("%Y%m%dT%H%M%SZ")192 now_utc = datetime.utcnow()193 rows_to_save, out_records = [], []194 total_api_calls = 0195 196 last_ranks = load_previous_ranks_psql(domain, keywords, country_label)197 198 if progress: progress(0, desc="البدء...")199 200 for i, kw in enumerate(keywords, 1):201 if progress: progress(i / len(keywords), desc=f"جاري البحث عن: {kw}")202 try:203 rank, matched_url, api_calls = client.find_rank_smart(kw, domain, match_mode, gl=gl, cr=cr)204 total_api_calls += api_calls205 206 prev = last_ranks.get((kw, domain))207 delta = prev - rank if rank is not None and prev is not None else None208 209 out_records.append({"keyword": kw, "rank": rank, "rank_prev": prev, "delta": delta, "matched_url": matched_url, "checked_at": now_utc.isoformat()})210 rows_to_save.append((kw, domain, country_label, gl, 'en', 'google.com', cr, match_mode, rank, matched_url, now_utc, run_id))211 except Exception as e:212 print(f"Error processing keyword '{kw}': {e}")213 out_records.append({"keyword": kw, "rank": None, "rank_prev": last_ranks.get((kw, domain)), "delta": None, "matched_url": None, "checked_at": now_utc.isoformat(), "error": str(e)[:200]})214 215 save_snapshot_rows_psql(rows_to_save)216 return pd.DataFrame(out_records), total_api_calls217 218# --- 5. بناء واجهة المستخدم Gradio ---219 220def format_credit_dashboard(user: Optional[Dict], api_calls: Optional[int] = None) -> str:221 if not user: return "<div style='border: 2px solid #FFD700; padding: 10px; border-radius: 5px; text-align:right;'><p style='color:#FFD700; margin:0;'>⚠️ يرجى إدخال رمز دعوة صالح للبدء.</p></div>"222 credits_total, credits_remaining = user.get('credits_total', 0), user.get('credits_remaining', 0)223 credits_used = credits_total - credits_remaining224 color = "green" if credits_remaining > 0 else "red"225 api_call_message = f"<p style='margin: 5px 0;'><strong>استهلاك البحث الأخير:</strong> {api_calls} طلب API</p>" if api_calls is not None else ""226 return f"""227 <div style='border: 2px solid {color}; padding: 10px; border-radius: 5px; text-align:right;'>228 <h4 style='color: {color}; margin: 0;'>✅ مرحبًا! حالة حسابك:</h4>229 <p style='margin: 5px 0;'><strong>إجمالي الرصيد:</strong> {credits_total}</p>230 <p style='margin: 5px 0;'><strong>الرصيد المستخدم:</strong> {credits_used}</p>231 <p style='margin: 5px 0;'><strong>الرصيد المتبقي:</strong> <strong>{credits_remaining}</strong></p>232 {api_call_message}233 </div>234 """235 236def check_invitation_code(code: str) -> str:237 if not code: return "" 238 user = get_user_by_code(code)239 if user: return format_credit_dashboard(user)240 return "<div style='border: 2px solid red; padding: 10px; border-radius: 5px; text-align:right;'><p style='color:red; margin:0;'>❌ رمز الدعوة الذي أدخلته غير صالح.</p></div>"241 242def build_app_interface():243 with gr.Blocks(title=APP_NAME, theme=gr.themes.Soft(primary_hue="blue", secondary_hue="sky")) as demo:244 results_state = gr.State()245 gr.Markdown(f"## 🏃 {APP_NAME}")246 247 with gr.Row():248 with gr.Column(scale=3):249 invitation_code = gr.Textbox(label="🔑 رمز الدعوة", placeholder="أدخل رمز الدعوة هنا ثم اضغط Enter")250 with gr.Column(scale=2):251 credit_dashboard_html = gr.HTML()252 253 with gr.Accordion("⚙️ إعدادات البحث الأساسية", open=True):254 with gr.Row():255 domain = gr.Textbox(label="الدومين (مثال: example.com)", scale=2)256 country = gr.Dropdown(choices=list(COUNTRIES.keys()), value="Egypt", label="الدولة", scale=1)257 with gr.Row():258 match_mode = gr.Dropdown(choices=MATCH_MODES, value="domain", label="طريقة المطابقة")259 use_cr = gr.Checkbox(label="تقييد النتائج على مواقع من الدولة (cr)", value=False, info="يفضل تركه غير مفعل لمعظم عمليات البحث")260 261 with gr.Tab("📝 إدخال الكلمات المفتاحية"):262 keywords_text_bulk = gr.Textbox(label="الكلمات المفتاحية (كل كلمة في سطر)", lines=10)263 run_bulk_btn = gr.Button("🚀 فحص الترتيب الآن", variant="primary")264 265 gr.Markdown("---")266 gr.Markdown("### 📊 النتائج")267 results_df = gr.Dataframe(headers=["الكلمة", "الترتيب الحالي", "الترتيب السابق", "التغير", "الرابط المطابق", "تاريخ الفحص", "خطأ"], interactive=False, wrap=True)268 269 with gr.Row():270 copy_btn = gr.Button("📋 نسخ النتائج كنص")271 export_csv_btn = gr.Button("📄 تصدير كملف CSV")272 273 copy_output_text = gr.Textbox(label="النتائج المنسوخة", visible=False, lines=5)274 export_output_file = gr.File(label="تحميل ملف CSV", visible=False)275 276 invitation_code.submit(fn=check_invitation_code, inputs=[invitation_code], outputs=[credit_dashboard_html])277 278 def _prepare_keywords(text: str) -> List[str]:279 kws = [x.strip() for x in text.splitlines() if x.strip()]280 return list(dict.fromkeys(kws))281 282 def _run_wrapper(inv_code_v, country_label, use_cr_v, kw_text, dom, mode, progress=gr.Progress(track_tqdm=True)):283 if not inv_code_v or not dom: raise gr.Error("يرجى إدخال رمز الدعوة والدومين في 'إعدادات البحث الأساسية'.")284 user = get_user_by_code(inv_code_v)285 if not user: raise gr.Error("رمز الدعوة غير صالح. يرجى التحقق منه.")286 kws = _prepare_keywords(kw_text)287 if not kws: raise gr.Error("يرجى إدخال كلمة مفتاحية واحدة على الأقل.")288 289 if user['credits_remaining'] < len(kws):290 raise gr.Error(f"رصيدك غير كافٍ. تحتاج {len(kws)} كريدت، والمتاح لديك {user['credits_remaining']} فقط.")291 292 deduct_credits(inv_code_v, len(kws))293 294 country_params = COUNTRIES.get(country_label, {})295 gl_param = country_params.get("gl", "")296 cr_param = country_params.get("cr", "") if use_cr_v else ""297 298 df, api_calls = run_keywords(kws, dom, country_label, gl_param, cr_param, mode, progress)299 300 updated_user = get_user_by_code(inv_code_v)301 final_dashboard_html = format_credit_dashboard(updated_user, api_calls)302 303 df_renamed = df.rename(columns={'keyword': 'الكلمة', 'rank': 'الترتيب الحالي', 'rank_prev': 'الترتيب السابق', 'delta': 'التغير', 'matched_url': 'الرابط المطابق', 'checked_at': 'تاريخ الفحص', 'error': 'خطأ'})304 305 return df, df_renamed, final_dashboard_html306 307 run_bulk_btn.click(308 _run_wrapper, 309 inputs=[invitation_code, country, use_cr, keywords_text_bulk, domain, match_mode], 310 outputs=[results_state, results_df, credit_dashboard_html]311 )312 313 def copy_results(df_state):314 if df_state is None: return gr.update(value="لا توجد بيانات لنسخها.", visible=True)315 return gr.update(value=df_state.to_markdown(index=False), visible=True)316 copy_btn.click(fn=copy_results, inputs=[results_state], outputs=[copy_output_text])317 318 def export_csv(df_state):319 if df_state is None: return gr.update(visible=False)320 temp_filename = f"serp_results_{int(time.time())}.csv"321 df_state.to_csv(temp_filename, index=False, encoding='utf-8-sig')322 return gr.update(value=temp_filename, visible=True)323 export_csv_btn.click(fn=export_csv, inputs=[results_state], outputs=[export_output_file])324 325 return demo326 327# --- 6. نقطة بداية تشغيل التطبيق ---328if __name__ == "__main__":329 if not all([GOOGLE_API_KEY, GOOGLE_CSE_ID, DATABASE_URL]):330 with gr.Blocks() as error_demo:331 gr.Markdown("# ❌ خطأ في الإعدادات")332 gr.Markdown("التطبيق غير قادر على البدء. أحد الأسرار (Secrets) المطلوبة غير موجود.")333 error_demo.launch()334 else:335 app = build_app_interface()336 app.launch()