# -*- coding: utf-8 -*-
import psycopg2
from psycopg2.extras import RealDictCursor
from datetime import datetime
from colorama import Fore, Back, Style
from playwright.sync_api import sync_playwright, TimeoutError as PlaywrightTimeoutError
import time, subprocess, sys
import os, json, re
import random
import smtplib
import linecache
import requests, urllib, psutil, sys
from urllib.parse import urlparse
import fcntl

# --- Inbox API (use instead of email; mail code kept but commented) ---
INBOX_API_URL = os.environ.get("INBOX_API_URL", "https://axg.house").rstrip("/")
INBOX_SCRIPT_TOKEN = "indextoken_axg"
LOCK_FILE_HANDLE = None
RETRYABLE_AFTER_CLICK_WARNINGS = {
    "Quota exceeded",
    "Unable to submit form",
    "Try resubmitting the form",
    "Uploaded file have more than 1k links per group",
    "Post-submit timeout; Google may already have the report",
    "Success page is not visible after submit; Google may already have the report",
}


def send_to_inbox(subject, message, notification_type="script"):
    """Send notification to app inbox (script type). Used instead of email."""
    if not INBOX_SCRIPT_TOKEN:
        print("INBOX_SCRIPT_TOKEN not set; skipping inbox notification")
        return False
    try:
        import urllib.request
        data = json.dumps({
            "subject": subject,
            "message": message,
            "type": notification_type,
            "inbox_token": INBOX_SCRIPT_TOKEN,
        }).encode("utf-8")
        req = urllib.request.Request(
            INBOX_API_URL + "/api/inbox/notification",
            data=data,
            headers={"Content-Type": "application/json", "X-Inbox-Token": INBOX_SCRIPT_TOKEN},
            method="POST",
        )
        with urllib.request.urlopen(req, timeout=30) as r:
            if r.status in (200, 201):
                print("Successfully sent to inbox")
                return True
        return False
    except Exception as e:
        print("Inbox error: " + str(e))
        return False


def print_executed_line(frame, event, arg):
    if event == 'line':
        filename = frame.f_code.co_filename
        lineno = frame.f_lineno
        current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

        if "google_dmca_two.py" in filename:
            line = linecache.getline(filename, lineno)
            print(f"{current_time} - Executing line {lineno}: {line.strip()}")

    return print_executed_line


class App:
    def __init__(self):
        # --- PostgreSQL Config ---
        pg_config = {
            'host': '37.27.12.199',
            'user': 'axg_crm_new1',
            'password': 'Yyv1QT2JUMLyxt71cYLG',
            'dbname': 'axg_crm_new'
        }
        
        self.con = psycopg2.connect(**pg_config)
        self.con.autocommit = True
        self.cur = self.con.cursor(cursor_factory=RealDictCursor)
        self.google_load_form_url = 'https://reportcontent.google.com/forms/dmca_search'
        self.excluded_sites = set()
        self.excluded_extensions = set()

        self.link_ids = []
        self.all_dmcaDescription = []
        self.project_titles = []
        self.all_pirate_urls = []
        self.all_white_urls = []
        self.project_ids = []
        self.project_names = []
        self.file_name = ""
        self.the_file_name = ""
        self.video_name = ""
        self.copyright_holder = ""
        self.google_user = ""
        self.warning_msg = ""
        self.error_msg = ""
        self.result_form = ""
        self.content = ""
        self.form_state = "5"
        self.maxLinks = 999
        self.all_form_ids = []
        self.all_skip_form_ids = []
        self.all_new_form_ids = []
        self.current_google_user = None
        self.current_google_user_email = ""
        self.is_unauthorized_livestream_related = False
        self.dmca_form_id = None
        self.submit_clicked = False
        self.claimed_form_ids = []
        self.locked_link_ids = set()

    def log(self, message):
        now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        print(f"[{now}] {message}", flush=True)

    def log_system_stats(self, label=""):
        """Log host CPU/RAM and this process + Chromium child RSS. Helps diagnose mid-send crashes."""
        try:
            vm = psutil.virtual_memory()
            swap = psutil.swap_memory()
            cpu = psutil.cpu_percent(interval=0.2)
            proc = psutil.Process()
            rss_mb = proc.memory_info().rss / (1024.0 * 1024.0)
            child_rss = 0
            for child in proc.children(recursive=True):
                try:
                    child_rss += child.memory_info().rss
                except (psutil.NoSuchProcess, psutil.AccessDenied):
                    pass
            child_mb = child_rss / (1024.0 * 1024.0)
            self.log(
                f"SYS {label}: cpu={cpu:.1f}% "
                f"ram={vm.percent:.1f}% used={vm.used / (1024 * 1024):.0f}MB "
                f"avail={vm.available / (1024 * 1024):.0f}MB "
                f"swap={swap.percent:.1f}% "
                f"proc={rss_mb:.0f}MB chrome={child_mb:.0f}MB"
            )
        except BaseException as e:
            self.log(f"SYS {label}: failed to read stats: {e}")

    def resources_too_low(self):
        """True when starting Chromium is likely to OOM-kill the process."""
        try:
            vm = psutil.virtual_memory()
            avail_mb = vm.available / (1024.0 * 1024.0)
            return vm.percent >= 80 or avail_mb < 400
        except BaseException:
            return False

    def _parse_dmca_link_ids(self, raw_link_ids):
        """Parse comma- or star-separated link_ids from google_dmca_form."""
        if not raw_link_ids:
            return []
        ids = []
        for part in re.split(r'[,*]', str(raw_link_ids)):
            part = part.strip()
            if part.isdigit():
                ids.append(int(part))
        return ids

    def load_locked_link_ids(self):
        """
        Link IDs already on an in-progress (1-4), completed (5), no-new-URLs (6),
        or post-submit timeout (7) google_dmca_form.
        Prevents another run from sending the same references while pending, done, or timed out after click.
        """
        locked = set()
        try:
            self.cur.execute("""
                SELECT link_ids
                FROM google_dmca_form
                WHERE state::text IN ('1', '2', '3', '4', '5', '6', '7')
                  AND link_ids IS NOT NULL
                  AND link_ids <> ''
                ORDER BY id DESC
                LIMIT 5000
            """)
            for row in self.cur.fetchall():
                locked.update(self._parse_dmca_link_ids(row.get('link_ids')))
        except BaseException as e:
            self.log("Failed loading locked google_dmca_form link_ids: " + str(e))
        self.locked_link_ids = locked
        self.log(f"Loaded {len(self.locked_link_ids)} locked link_ids from google_dmca_form")
        return locked

    def is_link_locked(self, link_id):
        try:
            return int(link_id) in self.locked_link_ids
        except (TypeError, ValueError):
            return False

    def _dmca_form_payload(self):
        all_pirate_urls = []
        all_white_urls = []
        no_of_text_areas = len(self.project_ids)
        for i in range(0, no_of_text_areas):
            all_white_urls.append({self.project_names[i]: self.all_white_urls[i]})
            all_pirate_urls.append({self.project_names[i]: self.all_pirate_urls[i]})

        return {
            'state': self.form_state,
            'error_msg': self.error_msg,
            'warning_msg': self.warning_msg,
            'google_user': self.google_user,
            'google_user_email': self.current_google_user_email,
            'copyright_holder': self.copyright_holder,
            'white_urls': json.dumps(all_white_urls),
            'pirate_urls': json.dumps(all_pirate_urls),
            'description': '\n'.join(self.all_dmcaDescription),
            'project_id': ','.join(str(e) for e in self.project_ids),
            'link_ids': ','.join(str(e) for e in self.link_ids),
            'project_name': '\n'.join(self.project_names),
            'result_form': self.result_form,
            'load_form_error_count': '0',
            'text_file': self.the_file_name,
            'video': self.video_name,
        }

    def create_pending_dmca_form(self):
        """Insert google_dmca_form with state=1 before Playwright so /google_form shows Pending."""
        self.form_state = "1"
        self.error_msg = ""
        self.warning_msg = "Pending send"
        self.google_user = ""
        self.result_form = ""
        self.dmca_form_id = None
        self.submit_clicked = False

        data = self._dmca_form_payload()
        try:
            keys = list(data.keys())
            columns = ','.join(keys)
            placeholders = ','.join(['%s'] * len(keys))
            sql = (
                f"INSERT INTO google_dmca_form({columns}) "
                f"VALUES({placeholders}) RETURNING id"
            )
            self.cur.execute(sql, tuple(data[k] for k in keys))
            row = self.cur.fetchone()
            self.dmca_form_id = row['id'] if row else None
            self.con.commit()
            # Lock these link_ids for concurrent runs in the same process / next ticks
            for link_id in self.link_ids:
                try:
                    self.locked_link_ids.add(int(link_id))
                except (TypeError, ValueError):
                    pass
            self.log(f"Created pending google_dmca_form id={self.dmca_form_id} state=1")
            print(Fore.YELLOW + f"Pending google_dmca_form created: {self.dmca_form_id}")
            print(Style.RESET_ALL)
            return self.dmca_form_id
        except BaseException as e:
            print("Error creating pending google_dmca_form: " + str(e))
            self.dmca_form_id = None
            return None

    def claim_google_form_queue(self):
        """Mark batch google_form rows non-selectable (grouped=0) before Playwright."""
        filtered = list(set(self.all_form_ids).difference(self.all_skip_form_ids))
        self.claimed_form_ids = filtered
        for a_form_id in filtered:
            try:
                self.cur.execute(
                    "UPDATE google_form SET grouped = 0 WHERE id=%s",
                    [a_form_id],
                )
            except BaseException as e:
                print(f"Error claiming google_form id={a_form_id}: {e}")
        self.con.commit()
        self.log(f"Claimed {len(filtered)} google_form row(s) (grouped=0)")

    def unlock_batch_link_ids(self):
        """Drop this batch from the in-memory lock so a fail-before-click can be retried."""
        unlocked = 0
        for link_id in self.link_ids:
            try:
                lid = int(link_id)
            except (TypeError, ValueError):
                continue
            if lid in self.locked_link_ids:
                self.locked_link_ids.discard(lid)
                unlocked += 1
        if unlocked:
            self.log(f"Unlocked {unlocked} link_ids after fail-before-click restore")

    def restore_google_form_queue(self):
        """Restore claimed rows so a fail-before-click can be retried."""
        self.unlock_batch_link_ids()
        for a_form_id in self.claimed_form_ids:
            try:
                self.cur.execute(
                    "UPDATE google_form SET grouped = 1 WHERE id=%s",
                    [a_form_id],
                )
            except BaseException as e:
                print(f"Error restoring google_form id={a_form_id}: {e}")
        self.con.commit()
        self.log(f"Restored {len(self.claimed_form_ids)} google_form row(s) (grouped=1)")
        self.claimed_form_ids = []

    def delete_claimed_google_form_queue(self):
        """Delete claimed queue rows after success or non-retryable post-click outcome."""
        for a_form_id in self.claimed_form_ids:
            try:
                self.cur.execute("DELETE FROM google_form WHERE id=%s", [a_form_id])
            except BaseException as e:
                print(f"Error deleting google_form id={a_form_id}: {e}")
        self.con.commit()
        self.log(f"Deleted {len(self.claimed_form_ids)} claimed google_form row(s)")
        self.claimed_form_ids = []

    def _google_form_ids_overlapping_links(self, link_ids, grouped=0):
        """Find google_form rows whose link_ids overlap the given reference IDs."""
        link_set = set()
        for link_id in link_ids or []:
            try:
                link_set.add(int(link_id))
            except (TypeError, ValueError):
                pass
        if not link_set:
            return []
        try:
            self.cur.execute(
                "SELECT id, link_ids FROM google_form WHERE grouped = %s",
                [grouped],
            )
            rows = self.cur.fetchall() or []
        except BaseException as e:
            self.log("Failed loading google_form rows for overlap check: " + str(e))
            return []
        matched = []
        for row in rows:
            row_ids = set(self._parse_dmca_link_ids(str(row.get('link_ids') or '').replace('*', ',')))
            if row_ids & link_set:
                matched.append(row['id'])
        return matched

    def recover_stale_pending_dmca_forms(self):
        """
        A previous process can die after INSERT state=1 / claim, leaving a row stuck as Pending.
        This process holds the lock, so any leftover state=1 row is stale.
        Pending send = died before click → restore queue.
        Submitting to Google = click may have happened → do not restore.
        """
        try:
            self.cur.execute("""
                SELECT id, link_ids, warning_msg
                FROM google_dmca_form
                WHERE state::text = '1'
                ORDER BY id ASC
            """)
            rows = self.cur.fetchall() or []
        except BaseException as e:
            self.log("Failed loading stale pending google_dmca_form rows: " + str(e))
            return
        if not rows:
            return
        for row in rows:
            form_id = row['id']
            warning = (row.get('warning_msg') or "").strip()
            link_ids = self._parse_dmca_link_ids(row.get('link_ids'))
            overlapping_ids = self._google_form_ids_overlapping_links(link_ids, grouped=0)
            died_before_click = warning in ("", "Pending send")
            if died_before_click:
                for form_row_id in overlapping_ids:
                    try:
                        self.cur.execute(
                            "UPDATE google_form SET grouped = 1 WHERE id = %s",
                            [form_row_id],
                        )
                    except BaseException as e:
                        print(f"Error restoring google_form id={form_row_id}: {e}")
                try:
                    self.cur.execute(
                        """
                        UPDATE google_dmca_form
                        SET state = %s,
                            error_msg = %s,
                            warning_msg = %s
                        WHERE id = %s
                        """,
                        (
                            "-1",
                            "Submission Failed",
                            "Stale pending send; process died before submit",
                            form_id,
                        ),
                    )
                    self.con.commit()
                except BaseException as e:
                    self.log(f"Failed marking stale pending google_dmca_form id={form_id}: {e}")
                    continue
                self.log(
                    f"Recovered stale pending google_dmca_form id={form_id}; "
                    f"restored {len(overlapping_ids)} google_form row(s)"
                )
            else:
                for form_row_id in overlapping_ids:
                    try:
                        self.cur.execute("DELETE FROM google_form WHERE id = %s", [form_row_id])
                    except BaseException as e:
                        print(f"Error deleting google_form id={form_row_id}: {e}")
                saved_link_ids = self.link_ids
                self.link_ids = link_ids
                try:
                    self.cur.execute(
                        """
                        UPDATE google_dmca_form
                        SET state = %s,
                            error_msg = %s,
                            warning_msg = %s
                        WHERE id = %s
                        """,
                        (
                            "7",
                            "Your removal notice may have been submitted",
                            "Stale submit; Google may already have the report",
                            form_id,
                        ),
                    )
                    self.con.commit()
                    self.update()
                except BaseException as e:
                    self.log(f"Failed marking stale submit google_dmca_form id={form_id}: {e}")
                finally:
                    self.link_ids = saved_link_ids
                self.log(
                    f"Recovered stale submit google_dmca_form id={form_id}; "
                    f"deleted {len(overlapping_ids)} google_form row(s) to avoid duplicate send"
                )

    def mark_pending_form_submitting(self):
        """Persist that we are about to click Submit, so a crash can be distinguished from pre-click death."""
        self.warning_msg = "Submitting to Google"
        if not self.dmca_form_id:
            return
        try:
            self.cur.execute(
                "UPDATE google_dmca_form SET warning_msg = %s WHERE id = %s",
                [self.warning_msg, self.dmca_form_id],
            )
            self.con.commit()
        except BaseException as e:
            self.log("Failed marking pending form as submitting: " + str(e))

    def batch_already_on_recent_form(self):
        """True if this exact link_ids payload was already submitted (or is in-flight) recently."""
        if not self.link_ids:
            return False
        link_ids_str = ','.join(str(e) for e in self.link_ids)
        try:
            self.cur.execute(
                """
                SELECT id, state, google_user
                FROM google_dmca_form
                WHERE created > NOW() - INTERVAL '48 hours'
                  AND link_ids = %s
                  AND state::text IN ('1', '5', '6', '7')
                ORDER BY id DESC
                LIMIT 1
                """,
                [link_ids_str],
            )
            row = self.cur.fetchone()
            if row:
                self.log(
                    f"Skipping duplicate batch; same link_ids already on google_dmca_form "
                    f"id={row['id']} state={row['state']} report={row.get('google_user')}"
                )
                return True
        except BaseException as e:
            self.log("Duplicate batch check failed: " + str(e))
        return False

    def save_trace(self, context, reason):
        safe_reason = re.sub(r"[^a-zA-Z0-9_-]", "_", reason)[:60]
        trace_dir = "/var/www/html/google_dmca_traces"
        fallback_trace_dir = "/tmp/google_dmca_traces"
        try:
            os.makedirs(trace_dir, exist_ok=True)
        except BaseException as e:
            self.log(f"Primary trace directory unavailable ({trace_dir}): {e}")
            trace_dir = fallback_trace_dir
            os.makedirs(trace_dir, exist_ok=True)
            self.log(f"Using fallback trace directory: {trace_dir}")

        trace_name = os.path.join(trace_dir, f"trace_google_{int(time.time() * 1000)}_{safe_reason}.zip")
        try:
            context.tracing.stop(path=trace_name)
            self.log(f"Saved playwright trace: {trace_name}")
        except BaseException as e:
            self.log("Failed to save playwright trace: " + str(e))

    def wait_for_post_submit_state(self, page, total_timeout_ms=120000, interval_ms=2000):
        """
        Wait for a meaningful post-submit page state instead of relying on a fixed sleep.
        Returns one of: success, quota, unable, no_new_urls, max_links, timeout.
        """
        elapsed = 0
        while elapsed < total_timeout_ms:
            try:
                if page.locator('//success-page').is_visible():
                    return "success"
                if page.locator('//*[contains(text(),"Your daily quota is not enough for this submission")]').is_visible():
                    return "quota"
                if page.locator('//*[contains(text(),"uploaded file only allows")]').is_visible():
                    return "max_links"
                if page.locator('//*[text()="Unable to submit"]').is_visible():
                    content = page.content()
                    if "No new URLs were found" in content:
                        return "no_new_urls"
                    return "unable"
                page.wait_for_timeout(interval_ms)
                elapsed += interval_ms
                self.log(f"Waiting for result page... {elapsed}/{total_timeout_ms}ms")
            except BaseException as e:
                self.log("wait_for_post_submit_state check error: " + str(e))
                err = str(e).lower()
                if any(token in err for token in (
                    "target closed",
                    "has been closed",
                    "browser has been closed",
                    "connection closed",
                    "context or browser has been closed",
                )):
                    # Click already happened; treat as timeout so this batch is not retried.
                    return "timeout"
                try:
                    page.wait_for_timeout(interval_ms)
                except BaseException as wait_err:
                    self.log("wait_for_post_submit_state wait error: " + str(wait_err))
                    return "timeout"
                elapsed += interval_ms
                self.log(f"Waiting for result page... {elapsed}/{total_timeout_ms}ms")

        return "timeout"

    def is_url_excluded(self, link_url, display_link):
        """
        Centralized exclusion checking function.
        Returns True if URL should be excluded, False otherwise.
        
        Rules:
        - dailymotion.com matches dailymotion.com and www.dailymotion.com
        - *.dailymotion.com matches all subdomains (dailymotion.com, www.dailymotion.com, test.dailymotion.com, etc.)
        """
        # Single, robust exclusion check
        # Check file extensions
        if any(link_url.endswith(ext) for ext in self.excluded_extensions):
            return True
        
        # Check specific exclusions
        if link_url.startswith("https://gateway.pinata.cloud/"):
            return True
        
        # Normalize display_link: remove www. prefix for comparison
        display_normalized = display_link
        if display_link.startswith('www.'):
            display_base = display_link[4:]  # Remove 'www.' prefix
        else:
            display_base = display_link
        
        display_parts = display_link.split('.')
        
        # Check each exclusion pattern against the display_link
        for exclusion in self.excluded_sites:
            # Exact match
            if display_link == exclusion:
                return True
            
            exclusion_parts = exclusion.split('.')
            
            # Normalize exclusion: remove www. prefix for comparison
            if exclusion.startswith('www.'):
                exclusion_base = exclusion[4:]  # Remove 'www.' prefix
            else:
                exclusion_base = exclusion
            
            # Check wildcard patterns (e.g., *.dailymotion.com)
            # This should match all subdomains: dailymotion.com, www.dailymotion.com, test.dailymotion.com, etc.
            if exclusion.startswith('*.'):
                base_domain = exclusion[2:]  # Remove '*.' prefix (e.g., "dailymotion.com")
                # Extract base domain from display_link (last 2 parts: domain.tld)
                if len(display_parts) >= 2:
                    display_domain_base = '.'.join(display_parts[-2:])
                    # Match if the base domain matches (works for any subdomain or base domain)
                    if display_domain_base == base_domain:
                        return True
            
            # For base domain exclusions (2 parts: domain.tld), match both base and www. versions
            # e.g., if exclusion is "dailymotion.com", match both "dailymotion.com" and "www.dailymotion.com"
            # Also handle if exclusion is "www.dailymotion.com", match both "dailymotion.com" and "www.dailymotion.com"
            if len(exclusion_parts) == 2 or (len(exclusion_parts) == 3 and exclusion_parts[0] == 'www'):
                # Compare normalized base domains
                if display_base == exclusion_base:
                    return True
                # Also check if display_link is www. + exclusion_base
                if display_link == 'www.' + exclusion_base:
                    return True
                # Also check if exclusion is www. + display_base
                if exclusion == 'www.' + display_base:
                    return True
            
        return False

    def get_proxy(self):
        lines = open('/opt/aparser/files/proxy/proxy.txt').read().splitlines()
        proxy = random.choice(lines)
        return proxy.strip()

    def resolve_google_user_id(self, project_id):
        self.cur.execute(
            "SELECT google_user_id FROM projects WHERE project_id = %s",
            [project_id]
        )
        row = self.cur.fetchone()
        if row and row.get('google_user_id'):
            configured_id = row['google_user_id']
            self.cur.execute(
                "SELECT google_user_id FROM google_user WHERE google_user_id = %s AND status = 1",
                [configured_id]
            )
            if self.cur.rowcount:
                return configured_id

        self.cur.execute("""
            SELECT google_user_id FROM google_user
            WHERE status = 1 AND LOWER(TRIM(country)) = 'estonia'
            ORDER BY google_user_id ASC
            LIMIT 1
        """)
        if self.cur.rowcount:
            return self.cur.fetchone()['google_user_id']

        self.cur.execute("""
            SELECT google_user_id FROM google_user
            WHERE status = 1
            ORDER BY COALESCE(date_modified, 0) ASC, google_user_id ASC
            LIMIT 1
        """)
        if self.cur.rowcount:
            return self.cur.fetchone()['google_user_id']
        return None

    def load_google_user(self, google_user_id):
        if not google_user_id:
            print("Missing google_user_id for batch")
            return False
        self.cur.execute(
            "SELECT * FROM google_user WHERE google_user_id = %s AND status = 1",
            [google_user_id]
        )
        if not self.cur.rowcount:
            print(f"Missing active google user (google_user_id={google_user_id})")
            return False
        self.current_google_user = self.cur.fetchone()
        self.current_google_user_email = self.current_google_user.get('email') or ""
        print(f"Using google_user_id: {google_user_id}, email: {self.current_google_user_email}")
        cookies = self.current_google_user['cookies']
        new_cookies = '{"cookies": ' + cookies + '}'
        with open('new_cookies.json', 'w') as fh:
            fh.write(new_cookies)
        return True

    def begin(self):
        # self.check_and_remove_duplicate_linkids()

        # Delete duplicate entries in sub database
        """
        This does not solve duplicates issue. It just adds an unnecessary backlog. You are checking for an exact match on links
        delete_query = "DELETE g1 FROM google_form g1 JOIN google_form g2 ON g1.project_id = g2.project_id AND g1.link_ids = g2.link_ids AND g1.id > g2.id;"
        self.cur.execute(delete_query)
        self.con.commit()
        print("Duplicate entries deleted.")
        """

        self.cur.execute("SELECT * FROM user_groups WHERE user_group_id = '7'")
        if not self.cur.rowcount:
            print("User group is empty")
            quit_google = True
            return
        user_group = self.cur.fetchall()
        settings = user_group[0]['settings']
        if settings is None:
            print("User group settings is empty.")
            quit_google = True
            return
        settings = json.loads(settings)

        self.recover_stale_pending_dmca_forms()
        self.log_system_stats("startup")

        try:
            if settings['auto_send_google_dmca_form'] == '0':
                print(Fore.YELLOW + "auto_send_google_dmca_form is empty.")
                print(Style.RESET_ALL)
                quit_google = True
                return
        except BaseException as e:
            print(str(e))
        # Grouping the projects with maximum link count 1000
        self.regroup_projects()

        # Fetch excluded extensions from the database
        self.cur.execute("SELECT google_file_extention FROM google_extension_exclusions")
        self.excluded_extensions = {row['google_file_extention'] for row in self.cur.fetchall()}

        # Fetch excluded sites from the database
        self.cur.execute("SELECT google_exclusion_site_url FROM google_exclusions")
        self.excluded_sites = {row['google_exclusion_site_url'] for row in self.cur.fetchall()}

        self.cur.execute(
            "SELECT DISTINCT publisher FROM google_form WHERE link_count != 0 and grouped = 1"
        )
        current_publishers = self.cur.fetchall()
        random.shuffle(current_publishers)
        quit_google = False
        self.load_locked_link_ids()
        for current_publisher in current_publishers:
            # Clear all self.* variables at the start of each publisher to prevent data leakage
            self.link_ids = []
            self.all_dmcaDescription = []
            self.project_titles = []
            self.all_pirate_urls = []
            self.all_white_urls = []
            self.project_ids = []
            self.project_names = []
            self.all_form_ids = []
            self.all_skip_form_ids = []
            self.file_name = ""
            self.video_name = ""
            self.copyright_holder = ""
            self.google_user = ""
            self.warning_msg = ""
            self.error_msg = ""
            self.content = ""
            self.dmca_form_id = None
            self.submit_clicked = False
            self.claimed_form_ids = []
            self.form_state = "5"
            
            self.cur.execute("SELECT * FROM user_groups WHERE user_group_id = '7'")
            sent_links = set()
            if not self.cur.rowcount:
                print("User group is empty")
                quit_google = True
                break
            user_group = self.cur.fetchall()
            settings = user_group[0]['settings']
            if settings is None:
                print("User group settings is empty.")
                quit_google = True
                break
            settings = json.loads(settings)

            try:
                if settings['auto_send_google_dmca_form'] == '0':
                    print(Fore.YELLOW + "auto_send_google_dmca_form is empty.")
                    print(Style.RESET_ALL)
                    quit_google = True
                    break
            except BaseException as e:
                print(str(e))
            # self.check_and_remove_duplicate_linkids()
            break_project = False
            link_ids = []
            form_ids = []
            self.all_new_form_ids = []
            skip_form_ids = []
            all_dmcaDescription = []
            project_titles = []
            all_pirate_urls = []
            all_white_urls = []
            project_ids = []
            project_names = []
            is_unauthorized_livestream_related = False
            initKeys = ['date1', 'date2', 'date_form_sent']
            project_publisher = current_publisher['publisher']

            print(Fore.YELLOW + "Publisher: " + project_publisher)
            print(Style.RESET_ALL)

            self.copyright_holder = project_publisher
            self.cur.execute(
                "SELECT * FROM google_form WHERE publisher = %s and link_count != 0 and grouped = 1 ORDER BY project_id LIMIT 50",
                [project_publisher]
            )
            if not self.cur.rowcount:
                print(Fore.BLUE + "projects not found")
                print(Style.RESET_ALL)
                continue
            pending_projects = self.cur.fetchall()
            batch_google_user_id = self.resolve_google_user_id(str(pending_projects[0]['project_id']))
            if not batch_google_user_id:
                print("No google user resolved for publisher batch")
                continue
            current_projects = []
            for pending_row in pending_projects:
                project_user_id = self.resolve_google_user_id(str(pending_row['project_id']))
                if project_user_id == batch_google_user_id:
                    current_projects.append(pending_row)
                if len(current_projects) >= 10:
                    break
            if not current_projects:
                continue
            count_projects = 0
            for current_project in current_projects:
                # if count_projects > 20:
                #     break
                google_form_id = current_project['id']
                form_ids.append(google_form_id)
                project_id = str(current_project['project_id'])
                content_type = self.getContentType(project_id)
                project_content_type_name = content_type['name']
                if str(content_type.get('google_live_event_unauthorized_stream', 0)) == "1":
                    is_unauthorized_livestream_related = True
                google_form_status = str(current_project['status'])
                project_name = current_project['project_name']
                current_link_ids = current_project['link_ids'].split('*')
                # Guard against malformed values like '' that break bigint comparisons.
                current_link_ids = list({
                    link_id.strip()
                    for link_id in current_link_ids
                    if link_id and link_id.strip().isdigit()
                })
                if not current_link_ids:
                    print(f"Skipping form row {google_form_id}: no valid numeric link IDs.")
                    skip_form_ids.append(google_form_id)
                    continue
                added_link_ids = []
                linksToSend = []
                pirateUrls = []
                whiteUrls = []

                publisher = current_project['publisher']
                project_official_rightholder_page = current_project['project_official_rightholder_page']

                project_author = current_project['project_author']
                if not project_author:
                    project_author = ""
                project_title = current_project['project_title']

                # Fetch all relevant rows in one go
                link_ids_str = ','.join(['%s'] * len(current_link_ids))
                sql = f"SELECT * FROM reference WHERE id IN ({link_ids_str})"
                self.cur.execute(sql, current_link_ids)
                reference_links = self.cur.fetchall()
                reference_backup_links = ()
                # If some links are missing, check in reference_backup
                if len(reference_links) != len(current_link_ids):
                    fetched_ids = {link['id'] for link in reference_links}  # IDs already fetched
                    missing_ids = [link_id for link_id in current_link_ids if int(link_id) not in fetched_ids]

                    if missing_ids:  # Only query backup table if there are missing IDs
                        backup_ids_str = ','.join(['%s'] * len(missing_ids))
                        sql_backup = f"SELECT * FROM reference_backup WHERE id IN ({backup_ids_str})"
                        self.cur.execute(sql_backup, missing_ids)
                        reference_backup_links = self.cur.fetchall()
    

                added_link_ids = []
                skipped_locked = 0
                for link in reference_links:
                    # print(link['id'])
                    if len(str(link['id'])) < 4:
                        continue
                    if self.is_link_locked(link['id']):
                        skipped_locked += 1
                        continue
                    linksToSend.append(link)
                    added_link_ids.append(link['id'])
                
                if len(reference_backup_links) > 0:
                    for link in reference_backup_links:
                        # print(link['id'])
                        if len(str(link['id'])) < 4:
                            continue
                        if self.is_link_locked(link['id']):
                            skipped_locked += 1
                            continue
                        linksToSend.append(link)
                        added_link_ids.append(link['id'])

                links_to_insert = []
                for link in linksToSend:
                    if link.get('screenshot') and str(link['screenshot']).strip():
                        link_new = str(link['id'])

                        links_to_insert.append((
                            project_id,
                            project_name,
                            project_title,
                            project_author,
                            project_official_rightholder_page,
                            publisher,
                            link_new
                        ))
                        continue

                        # thesql_insert = "INSERT INTO google_form_with_screenshot (project_id, project_name, project_title, project_author, project_official_rightholder_page, publisher, link_ids) VALUES (%s, %s, %s, %s, %s, %s, %s)"
                        # try:
                        #     self.cur.execute(thesql_insert, (
                        #         project_id, project_name, project_title, project_author,
                        #         project_official_rightholder_page,
                        #         publisher, link_new))
                        #     self.con.commit()
                        # except BaseException as e:
                        #     print("Error inserting: " + str(e))
                        # continue

                    # Generate display_link from URL to ensure consistency
                    try:
                        parsed_url = urlparse(link['link'])
                        display_link = parsed_url.netloc.lower()
                    except:
                        # Fallback to database value if URL parsing fails
                        display_link = link['displayLink']
                    
                    if '...' in link['link']:
                        link_ids.append(link['id'])
                        continue
                    # if '#' in link['link']:
                    #     link_ids.append(link['id'])
                    #     continue
                    
                    # Single exclusion check - if excluded, skip completely
                    if self.is_url_excluded(link['link'], display_link):
                        link_ids.append(link['id'])
                        continue
                    
                    if 'blogspot.com' in link['link']:
                        data = {
                            'publisher': project_publisher,
                            'project_id': project_id,
                            'link_id': link['id'],
                            'link': link['link']
                        }
                        try:
                            print(Fore.YELLOW + "Blogspot link ")
                            print(link['link'])
                            print(Style.RESET_ALL)
                            keys = ','.join(data.keys())
                            values = ','.join(['%s'] * len(data))
                            sql = 'INSERT IGNORE INTO {thetable}({keys}) VALUES({values})'.format(
                                thetable='google_form_blogspot', keys=keys, values=values)
                            if self.cur.execute(sql, tuple(data.values())):
                                self.con.commit()
                        except BaseException as e:
                            print("Error: " + str(e))
                            pass
                        link_ids.append(link['id'])
                        continue
                    if not link['link'].endswith('.jpg'):
                        # Add to pirateUrls (already checked for exclusion above)
                        pirateUrls.append(link['link'])
                    
                    link_ids.append(link['id'])

                if links_to_insert:
                    thesql_insert = """
                        INSERT INTO google_form_with_screenshot 
                        (project_id, project_name, project_title, project_author, project_official_rightholder_page, publisher, link_ids) 
                        VALUES (%s, %s, %s, %s, %s, %s, %s)
                    """
                    try:
                        self.cur.executemany(thesql_insert, links_to_insert)
                        self.con.commit()
                        print(f"Inserted {len(links_to_insert)} links.")
                    except Exception as e:
                        print("Bulk insert error:", str(e))

                if len(pirateUrls) < 1:
                    if skipped_locked > 0 and not linksToSend:
                        sql = "DELETE FROM google_form WHERE id=%s"
                        self.cur.execute(sql, [google_form_id])
                        self.con.commit()
                        print(
                            f"Deleted form row {google_form_id}: all links already locked on google_dmca_form"
                        )
                        continue
                    sql = "DELETE FROM google_form WHERE id=%s"
                    self.cur.execute(sql, [google_form_id])
                    self.con.commit()

                    if len(link_ids) > 0:
                        for id_ in link_ids:
                            # if len(str(id)) > 9:
                            #     continue
                            sql = "UPDATE reference SET google_form_sent = '1' WHERE id=%s"
                            sql_backup = "UPDATE reference_backup SET google_form_sent = '1' WHERE id=%s"
                            self.cur.execute(sql, [id_])
                            self.cur.execute(sql_backup, [id_])
                        self.con.commit()
                    continue

                if project_official_rightholder_page is not None:
                    cleanr = re.compile('<.*?>')
                    project_official_rightholder_page = re.sub(cleanr, '', project_official_rightholder_page)
                    all_urls = re.findall(r'(https?://[^\s]+)', project_official_rightholder_page)

                    for j in range(2):
                        try:
                            whiteUrls.append(all_urls[j])
                        except:
                            pass

                whiteUrls = list(set(whiteUrls))
                if project_content_type_name == "Onlyfans/ Fansly / Artwork":
                    project_content_type_name = "onlyfans "
                if project_content_type_name == "IT_ebook":
                    project_content_type_name = "ebook"
                if project_content_type_name == "UA/RU_Film":
                    project_content_type_name = "movie"

                all_dmcaDescription.append("Unauthorized shared " + project_content_type_name + " " + project_title + " " + str(project_author))
                project_titles.append(project_title)
                project_ids.append(project_id)
                project_names.append(project_name)

                print("Count links to send: " + str(len(pirateUrls)))
                
                
                all_pirate_urls.append(pirateUrls)
                all_white_urls.append(whiteUrls)

                count_projects += 1
                print("#######################################################################")

            # Assign accumulated data to self.* variables after processing all projects
            self.link_ids = link_ids
            self.all_dmcaDescription = all_dmcaDescription
            self.project_titles = project_titles
            self.all_pirate_urls = all_pirate_urls
            self.all_white_urls = all_white_urls
            self.project_ids = project_ids
            self.project_names = project_names
            self.all_form_ids = form_ids
            self.all_skip_form_ids = skip_form_ids
            self.is_unauthorized_livestream_related = is_unauthorized_livestream_related
            
            if len(self.all_pirate_urls) < 1:
                print("No pirate URLs to send for this publisher; moving to next.")
                continue
            if not self.load_google_user(batch_google_user_id):
                print("Skipping batch: could not load google user cookies")
                continue
            self.log_system_stats("before-batch")
            if self.resources_too_low():
                self.log("RAM too low to start Chromium; waiting 20s")
                time.sleep(20)
                self.log_system_stats("after-ram-wait")
                if self.resources_too_low():
                    self.log("Still low RAM; skipping this publisher batch to avoid mid-send crash")
                    continue
            if self.batch_already_on_recent_form():
                self.claim_google_form_queue()
                self.update()
                self.delete_claimed_google_form_queue()
                continue
            if not self.create_pending_dmca_form():
                print("Skipping batch: could not create pending google_dmca_form")
                continue
            self.claim_google_form_queue()
            try:
                self.prepare_file()
            except BaseException as e:
                self.log("prepare_file/send_form exception: " + str(e))
                self.log_system_stats("after-exception")
            finally:
                self.log_system_stats("after-batch")
                # After a successful click, Google may already have the report.
                # Never restore the queue in that case — restoring caused duplicate
                # report 9-6226000040762 after 22020's click was accepted as 1-1175000041454.
                if self.submit_clicked:
                    if str(self.form_state) == "1":
                        self.form_state = "7"
                        if not self.error_msg:
                            self.error_msg = "Your removal notice may have been submitted"
                        if not self.warning_msg or self.warning_msg in ("Pending send", "Submitting to Google"):
                            self.warning_msg = "Send aborted after submit click; Google may already have the report"
                        self.saveToDb()
                        self.update()
                        self.delete_claimed_google_form_queue()
                    elif (self.warning_msg or "") in RETRYABLE_AFTER_CLICK_WARNINGS:
                        # Google explicitly refused this batch (quota / unable / too many links).
                        # Restore queue so it can be sent after quota resets / auto_send is turned back on.
                        self.restore_google_form_queue()
                    else:
                        self.delete_claimed_google_form_queue()
                elif str(self.form_state) == "1":
                    self.form_state = "-1"
                    if not self.error_msg:
                        self.error_msg = "Submission Failed"
                    if not self.warning_msg or self.warning_msg == "Pending send":
                        self.warning_msg = "Send aborted before completion"
                    self.saveToDb()
                    self.restore_google_form_queue()
                elif str(self.form_state) == "-1":
                    self.restore_google_form_queue()
                else:
                    self.delete_claimed_google_form_queue()
            
            # Ensure unique timestamp for next file
            time.sleep(0.1)
            
            self.link_ids = []
            self.all_dmcaDescription = []
            self.project_titles = []
            self.all_pirate_urls = []
            self.all_white_urls = []
            self.project_ids = []
            self.project_names = []
            self.file_name = ""
            self.the_file_name = ""
            self.video_name = ""
            self.copyright_holder = ""
            self.google_user = ""
            self.warning_msg = ""
            self.error_msg = ""
            self.content = ""
            self.result_form = ""
            self.is_unauthorized_livestream_related = False
            self.dmca_form_id = None
            self.submit_clicked = False
            self.claimed_form_ids = []
            self.form_state = "5"
            time.sleep(5)
        if quit_google:
            sys.exit()

    def prepare_file(self):
        # Add microseconds to ensure unique filename for each publisher
        timestamp = str(int(time.time() * 1000))  # milliseconds precision
        self.the_file_name = timestamp + '.txt'
        upload_dir = '/var/www/html'
        os.makedirs(upload_dir, exist_ok=True)
        self.file_name = os.path.join(upload_dir, self.the_file_name)
        # self.file_name = self.the_file_name
        with open(self.file_name, 'w', encoding="utf-8") as fh:
            for i in range(0, len(self.project_ids)):
                dmcaDescription = self.all_dmcaDescription[i].replace('è', '%C3%A8')
                dmcaDescription = dmcaDescription.replace('é', '%C3%A9')
                dmcaDescription = dmcaDescription.replace('ë', '%C3%AB')
                dmcaDescription = dmcaDescription.replace('ù', '%C3%B9');
                dmcaDescription = dmcaDescription.replace('ú', '%C3%BA')
                dmcaDescription = dmcaDescription.replace('à', '%C3%A0')
                dmcaDescription = dmcaDescription.replace('í', '%C3%AD')
                dmcaDescription = dmcaDescription.replace('ì', '%C3%AC')
                dmcaDescription = dmcaDescription.replace('ñ', '%C3%B1')
                dmcaDescription = dmcaDescription.replace('á', 'a')
                dmcaDescription = dmcaDescription.replace('Â', 'A')
                dmcaDescription = dmcaDescription.replace('ċ', 'c')
                dmcaDescription = dmcaDescription.replace('ò', 'o')
                dmcaDescription = dmcaDescription.replace('ó', 'o')
                dmcaDescription = dmcaDescription.replace('ö', 'o')
                dmcaDescription = dmcaDescription.replace(',', '')
                dmcaDescription = dmcaDescription.replace("'", '')

                # dmcaDescription = dmcaDescription.replace("<br />", '')
                dmcaDescription = dmcaDescription.replace(".", '')
                dmcaDescription = dmcaDescription.replace("-", ' ')

                dmcaDescription = dmcaDescription.replace('+', ' ')
                fh.write("#" + dmcaDescription + "\n")
                for white_url in self.all_white_urls[i]:
                    fh.write("#" + white_url + "\n")
                for pirate_url in self.all_pirate_urls[i]:
                    # URLs are already filtered by exclusion logic above - no need for additional checks
                    
                    # pirate_url = pirate_url.replace('\u202F', '')  # Remove narrow no-break space (U+202F)
                    pirate_url = pirate_url.replace('è', '%C3%A8')
                    pirate_url = pirate_url.replace('é', '%C3%A9')
                    pirate_url = pirate_url.replace('ù', '%C3%B9')
                    pirate_url = pirate_url.replace('ú', '%C3%BA')
                    pirate_url = pirate_url.replace('à', '%C3%A0')
                    pirate_url = pirate_url.replace('á', '%C3%A1')
                    pirate_url = pirate_url.replace('í', '%C3%AD')
                    pirate_url = pirate_url.replace('ò', '%C3%B2')
                    pirate_url = pirate_url.replace('ó', '%C3%B3')
                    pirate_url = pirate_url.replace('ö', '%C3%B6')
                    pirate_url = pirate_url.replace('й', '%D0%B9')
                    pirate_url = pirate_url.replace('ц', '%D1%86')

                    pirate_url = pirate_url.replace('я', '%D1%8F')
                    pirate_url = pirate_url.replace('и', '%D0%B8')
                    pirate_url = pirate_url.replace('р', '%D1%80')
                    pirate_url = pirate_url.replace('л', '%D0%BB')
                    pirate_url = pirate_url.replace('н', '%D0%BD')
                    pirate_url = pirate_url.replace('п', '%D0%BF')
                    pirate_url = pirate_url.replace('г', '%D0%B3')
                    pirate_url = pirate_url.replace('в', '%D0%B2')

                    pirate_url = pirate_url.replace('з', '%D0%B7')
                    pirate_url = pirate_url.replace('т', '%D1%82')
                    pirate_url = pirate_url.replace('а', '%D0%B0')
                    pirate_url = pirate_url.replace('о', '%D0%BE')

                    pirate_url = pirate_url.replace('е', '%D0%B5')
                    pirate_url = pirate_url.replace('ь', '%D1%8C')
                    pirate_url = pirate_url.replace('ñ', '%C3%B1')
                    pirate_url = pirate_url.replace('\u00A0', '%20')
                    pirate_url = pirate_url.replace(' ', '%20')
                    pirate_url = pirate_url.replace('ì', '%C3%AC')
                    pirate_url = pirate_url.replace('ë', '%C3%AB')
                    pirate_url = pirate_url.replace(' ', '+')
                    fh.write(pirate_url + "\n")
        
        self.send_form()

    def send_form(self):
        proxy = self.get_proxy()
        print(proxy)
        proxy_to_use = {
            'server': 'http://' + proxy,
        }
        command = "node /home/moses/scripts/user_agents.js"
        # command = "node user_agents.js"
        try:
            useragent = subprocess.check_output(command, shell=True)
        except BaseException as e:
            print(str(e))
            self.error_msg = "Submission Failed"
            self.form_state = "-1"
            self.warning_msg = "Failed to get user agent"
            self.saveToDb()
            return ''
        useragent = useragent.decode().rstrip("\n")
        self.log(f"Using user agent: {useragent[:120]}")

        with sync_playwright() as p:
            #browser = p.chromium.launch(headless=True, proxy=proxy_to_use)
            #print(useragent)
            browser = p.chromium.launch(
                headless=True,
                args=[
                    "--disable-dev-shm-usage",
                    "--disable-gpu",
                    "--no-sandbox",
                ],
            )
            context = browser.new_context(
                user_agent=useragent,
                storage_state="new_cookies.json",
                locale='en-US',
                timezone_id="Europe/Tallinn"
            )
            context.tracing.start(screenshots=True, snapshots=True, sources=True)
            page = context.new_page()
            page.set_default_timeout(120000)
            max_retries = 3  # Maximum number of retries
            attempt = 0  # Current attempt counter

            # self.video_name = os.path.basename(page.video.path())
            self.video_name = ""
            while attempt < max_retries:
                try:
                    self.log(f"Opening Google DMCA form, attempt {attempt + 1}/{max_retries}")
                    page.goto("https://reportcontent.google.com/forms/dmca_search?hl=en_US")
                    # time.sleep(7)
                    print("Browser Started")
                    first_name = "Oleksandr"
                    if self.current_google_user and self.current_google_user.get('first_name'):
                        first_name = self.current_google_user.get('first_name')
                    page.get_by_label("First name").fill(first_name, timeout=120000)
                    break  # If successful, exit the loop

                except BaseException as e:
                    attempt += 1  # Increment the attempt counter
                    self.log("Failed loading form page: " + str(e))

                    if attempt == max_retries:
                        # If the maximum number of retries is reached, handle the error
                        self.error_msg = "Submission Failed"
                        self.form_state = "-1"
                        self.warning_msg = "Failed to Load Google Form"
                        self.all_form_ids = []
                        self.delete_new_forms_created_on_error()
                        subject = "Cookies expired: google_form"
                        body = "Please update the cookies, google script stopped running."
                        self.send_mail_for_cookies(subject, body)
                        self.stop_google_form()
                        self.saveToDb()
                        self.save_trace(context, "failed_to_load_form")
                        try:
                            context.close()
                            browser.close()
                        except BaseException as e:
                            self.log("Browser cleanup after save failed: " + str(e))
                        return  # Exit the function after failure

            last_name = "Honcharenko"
            if self.current_google_user and self.current_google_user.get('last_name'):
                last_name = self.current_google_user.get('last_name')
            page.get_by_label("Last name").fill(last_name)

            company_name = "AXGHOUSE ANTIPIRACY OÜ"
            if self.current_google_user and self.current_google_user.get('company_name'):
                company_name = self.current_google_user.get('company_name')
            page.get_by_label("Company Name").fill(company_name)

            page.get_by_role("radio", name="Other").click()
            # page.get_by_label("Copyright holder you represent:").click()
            page.get_by_label("Add represented copyright holder").fill(self.copyright_holder)
            # page.keyboard.press("Enter")

            email = "removals@axghouse.com"
            if self.current_google_user and self.current_google_user.get('email'):
                email = self.current_google_user.get('email')
            page.get_by_label("Email address").fill(email)
            page.locator("//dropdown-button").click()
            country = "Estonia"
            if self.current_google_user and self.current_google_user.get('country'):
                country = self.current_google_user.get('country')
            try:
                page.get_by_role("option", name=country).click()
            except BaseException:
                page.get_by_role("option", name="Estonia").click()
            page.get_by_role("radio", name="Yes").nth(0).click()
            livestream_radio_group = page.locator('[data-test-id="form-container-component-unauthorized-livestream-radio"]')
            if self.is_unauthorized_livestream_related:
                livestream_radio_group.get_by_role("radio", name="Yes").click()
            else:
                livestream_radio_group.get_by_role("radio", name="No").click()
            page.get_by_role("radio", name="Upload file").click()

            with page.expect_file_chooser() as fc_info:
                page.locator('//div[text()="Browse"]').click()
                # page.get_by_role("button", name="Browse files to upload").click()
            file_chooser = fc_info.value
            file_chooser.set_files(self.file_name)

            page.wait_for_timeout(3000)
            self.mark_pending_form_submitting()
            self.submit_clicked = True
            try:
                # Wait for the submit XHR itself. Closing the browser while it is still
                # in-flight cancels it (net::ERR_FAILED / CORS) and Google never gets the form.
                with page.expect_response(
                    lambda response: "dmca_tcrp_search:submit" in (response.url or ""),
                    timeout=180000,
                ) as submit_response_info:
                    page.locator('[data-test-id="submit-button"]').click()
                    print("Form Submitted")
                    self.log("Submit clicked, waiting for Google submit API")
                submit_response = submit_response_info.value
                self.log(
                    f"Google submit API finished: status={submit_response.status} "
                    f"ok={submit_response.ok}"
                )
                self.log_system_stats("after-submit-api")
            except PlaywrightTimeoutError as e:
                self.log("Google submit API did not finish within 180s: " + str(e))
            except BaseException as e:
                self.log("Submit click raised: " + str(e))
                err = str(e).lower()
                maybe_sent = any(token in err for token in (
                    "timeout", "closed", "destroyed", "detached", "navigation",
                ))
                if maybe_sent:
                    self.error_msg = "Your removal notice may have been submitted"
                    self.form_state = "7"
                    self.warning_msg = "Submit click error; Google may already have the report"
                    try:
                        self.save_trace(context, "submit_click_maybe_sent")
                    except BaseException:
                        pass
                    try:
                        context.close()
                        browser.close()
                    except BaseException:
                        pass
                    self.saveToDb()
                    self.update()
                    return
                self.submit_clicked = False
                self.error_msg = "Submission Failed"
                self.form_state = "-1"
                self.warning_msg = "Google Form hung up. Possibly a cookies issue"
                self.all_form_ids = []
                self.save_trace(context, "submit_click_failed")
                self.delete_new_forms_created_on_error()
                context.close()
                browser.close()
                self.saveToDb()
                return

            try:
                post_submit_state = self.wait_for_post_submit_state(page, total_timeout_ms=120000, interval_ms=2000)
            except BaseException as e:
                self.log("wait_for_post_submit_state raised after click: " + str(e))
                post_submit_state = "timeout"
            self.log(f"Post-submit state detected: {post_submit_state}")

            if post_submit_state == "timeout":
                # No success/quota/unable page after 120s. These batches were not received
                # by Google in practice, so restore the queue (do not mark sent).
                self.error_msg = "Submission Failed"
                self.form_state = "-1"
                self.warning_msg = "Post-submit timeout; Google may already have the report"
                self.log("Post-submit timeout after click; restoring queue so the batch can be retried.")
                try:
                    self.save_trace(context, "post_submit_timeout_no_success_page")
                except BaseException as e:
                    self.log("Trace save after timeout failed: " + str(e))
                try:
                    context.close()
                    browser.close()
                except BaseException as e:
                    self.log("Browser cleanup after timeout failed: " + str(e))
                self.saveToDb()
                return

            if post_submit_state == "quota":
                self.error_msg = "Quota completed"
                self.form_state = "-1"
                self.warning_msg = "Quota exceeded"
                self.all_form_ids = []
                self.delete_new_forms_created_on_error()
                subject = "Google DMCA quota exceeded"
                body = "Your daily quota is not enough for this submission, please stop the form"
                self.send_mail_for_cookies(subject,body)
                self.stop_google_form()
                context.close()
                browser.close()
                self.saveToDb()
                return

            if post_submit_state == "unable":
                if "resubmitting" in page.locator('//*[text()="Unable to submit"]').all_inner_texts():
                    self.error_msg = "A system error occured"
                    self.form_state = "-1"
                    self.warning_msg = "Try resubmitting the form"
                    self.all_form_ids = []
                    self.delete_new_forms_created_on_error()
                    context.close()
                    browser.close()
                    self.saveToDb()
                    return
                self.error_msg = "Submission Failed"
                self.form_state = "-1"
                self.warning_msg = "Unable to submit form"
                self.all_form_ids = []
                self.delete_new_forms_created_on_error()
                context.close()
                browser.close()
                self.saveToDb()
                return

            if post_submit_state == "no_new_urls":
                self.error_msg = "Your removal notice was submitted successfully"
                self.form_state = "5"
                self.warning_msg = "No new URLs were found in your submission"
                self.saveToDb()
                self.update()
                try:
                    context.close()
                    browser.close()
                except BaseException as e:
                    self.log("Browser cleanup after save failed: " + str(e))
                return

            if post_submit_state == "max_links":
                self.error_msg = "Uploaded file have more than 1k links per group"
                self.form_state = "-1"
                self.warning_msg = "Uploaded file have more than 1k links per group"
                self.all_form_ids = []
                self.delete_new_forms_created_on_error()
                context.close()
                browser.close()
                self.saveToDb()
                return
            try:
                page.wait_for_selector('//success-page', timeout=30000)
                print("Success Page visible")
            except BaseException as e:
                self.log("Success page wait failed after post-submit checks: " + str(e))
                if page.locator(
                        '//*[contains(text(),"Your daily quota is not enough for this submission")]').is_visible():
                    self.error_msg = "Quota completed"
                    self.form_state = "-1"
                    self.warning_msg = "Quota exceeded"
                    self.all_form_ids = []
                    self.delete_new_forms_created_on_error()
                    subject = "Google DMCA quota exceeded"
                    body = "Your daily quota is not enough for this submission, please stop the form"
                    self.send_mail_for_cookies(subject, body)
                    context.close()
                    browser.close()
                    self.saveToDb()
                    return
                if page.locator(
                        '//*[contains(text(),"uploaded file only allows")]').is_visible():
                    self.error_msg = "Uploaded file have more than 1k links per group"
                    self.form_state = "-1"
                    self.warning_msg = "Uploaded file have more than 1k links per group"
                    self.all_form_ids = []
                    self.delete_new_forms_created_on_error()
                    # subject = "Google DMCA quota exceeded"
                    # body = "Your daily quota is not enough for this submission, please stop the form"
                    # self.send_mail_for_cookies(subject, body)
                    context.close()
                    browser.close()
                    self.saveToDb()
                    return
                if page.locator('//*[text()="Unable to submit"]').is_visible() and "No new URLs were found" in page.content() :
                    self.error_msg = "Your removal notice was submitted successfully"
                    self.form_state = "5"
                    self.warning_msg = "No new URLs were found in your submission"
                    # self.all_form_ids = []
                    self.saveToDb()
                    self.update()
                    try:
                        context.close()
                        browser.close()
                    except BaseException as e:
                        self.log("Browser cleanup after save failed: " + str(e))
                    return
                elif page.locator('//*[text()="Unable to submit"]').is_visible():
                    self.error_msg = "Submission Failed"
                    self.form_state = "-1"
                    self.warning_msg = "Unable to submit form"
                    self.all_form_ids = []
                    self.delete_new_forms_created_on_error()
                    subject = "Google Form : Unable to submit the form"
                    body = "Unable to submit the form , please check google forms"
                    self.send_mail_for_cookies(subject, body)
                    context.close()
                    browser.close()
                    self.saveToDb()
                    return

                else:
                    # Success page never appeared. Treat like quota: do not mark sent, restore queue.
                    self.error_msg = "Submission Failed"
                    self.form_state = "-1"
                    self.warning_msg = "Success page is not visible after submit; Google may already have the report"
                    self.log("Success page not visible after submit; restoring queue so the batch can be retried.")
                    try:
                        self.save_trace(context, "success_page_not_visible")
                    except BaseException as e:
                        self.log("Trace save after missing success page failed: " + str(e))
                    try:
                        context.close()
                        browser.close()
                    except BaseException as e:
                        self.log("Browser cleanup after missing success page failed: " + str(e))
                    self.saveToDb()
                    return

            try:
                content = page.content()
            except BaseException as e:
                self.log("Failed reading success page content after click: " + str(e))
                self.error_msg = "Your removal notice may have been submitted"
                self.form_state = "7"
                self.warning_msg = "Could not read Google response after submit; report may already exist"
                try:
                    context.close()
                    browser.close()
                except BaseException:
                    pass
                self.saveToDb()
                self.update()
                return
            self.result_form = content

            report_id = ""
            if 'Thank you for submitting your report' in content:
                try:
                    report_id_field = page.locator("p.gmTypographyHeadline5")
                    report_id_parts = report_id_field.inner_text().split(':')
                    report_id = report_id_parts[1].strip()
                except BaseException as e:
                    self.log("Failed extracting report ID after success page: " + str(e))
                    report_id = ""
                self.error_msg = 'Your removal notice was submitted successfully'
                self.form_state = "5"
                self.warning_msg = ""

                if len(report_id) < 3 or re.search(r'\b[0-9]{1,13}\b', report_id) is None:
                    if 'No new URLs were found in your submission' in content:
                        self.warning_msg = "No new URLs were found in your submission"
                        self.form_state = "6"
                        duplicate = True
                    elif 'uploaded file only allows' in content:
                        self.warning_msg = "Infringement group content exceeded"
                        self.form_state = "6"
                        duplicate = True

                print(report_id)
            self.google_user = report_id
            self.saveToDb()
            self.update()
            page.wait_for_timeout(2000)
            try:
                context.close()
                browser.close()
            except BaseException as e:
                self.log("Browser cleanup after save failed: " + str(e))
            return
        self.saveToDb()
        self.update()

    def send_mail_for_cookies(self, subject, body):
        # Send to inbox instead of email (mail code kept below, commented)
        send_to_inbox(subject=subject, message=body, notification_type="script")
        # --- Original mail code (commented) ---
        # message = f"""\
        # From: Axghouse Antipiracy <removals@axghouse.com>
        # MIME-Version: 1.0
        # Content-type: text/html
        # Subject: {subject}
        # {body}
        # """
        # username = 'AKIAYJJ2GCT4ZILVPU6C'
        # password = 'BK/DjNVeavqxMZSBMBB/TGVbpHjiVXF7QZfF9E+NhTSh'
        # sender = 'removals@axghouse.com'
        # receivers = ['support@axghouse.com', 'santhoshkasturi7@gmail.com']
        # try:
        #     server = smtplib.SMTP('email-smtp.eu-west-1.amazonaws.com', 587)
        #     server.starttls()
        #     server.login(username, password)
        #     server.sendmail(sender, receivers, message)
        #     server.quit()
        #     print("Email sent successfully!")
        # except smtplib.SMTPException as e:
        #     print("Error sending email:", e)

    def saveToDb(self):
        # Stale pending marker must not survive a successful send.
        if str(self.form_state) == "5" and self.warning_msg == "Pending send":
            self.warning_msg = ""

        data = self._dmca_form_payload()

        try:
            if self.form_state == "-1":
                total_error_forms = self.get_failed_dmca_submissions()
                if total_error_forms > 3:
                    self.stop_google_form()
                    subject = "Google DMCA form error"
                    body = "Google DMCA form has failed 5 times, please check the form"
                    self.send_mail_for_cookies(subject, body)
                    print("Stopping Google Form")

            print(Fore.YELLOW + "Saving to DB ")
            print(Style.RESET_ALL)

            if self.dmca_form_id:
                sql = """
                    UPDATE google_dmca_form
                    SET state = %s,
                        error_msg = %s,
                        warning_msg = %s,
                        google_user = %s,
                        google_user_email = %s,
                        copyright_holder = %s,
                        white_urls = %s,
                        pirate_urls = %s,
                        description = %s,
                        project_id = %s,
                        link_ids = %s,
                        project_name = %s,
                        result_form = %s,
                        load_form_error_count = %s,
                        text_file = %s,
                        video = %s
                    WHERE id = %s
                """
                self.cur.execute(sql, (
                    data['state'],
                    data['error_msg'],
                    data['warning_msg'],
                    data['google_user'],
                    data['google_user_email'],
                    data['copyright_holder'],
                    data['white_urls'],
                    data['pirate_urls'],
                    data['description'],
                    data['project_id'],
                    data['link_ids'],
                    data['project_name'],
                    data['result_form'],
                    data['load_form_error_count'],
                    data['text_file'],
                    data['video'],
                    self.dmca_form_id,
                ))
                self.con.commit()
                self.log(f"Updated google_dmca_form id={self.dmca_form_id} state={data['state']}")
            else:
                keys = ','.join(data.keys())
                values = ','.join(['%s'] * len(data))
                sql = 'INSERT INTO {thetable}({keys}) VALUES({values}) RETURNING id'.format(
                    thetable='google_dmca_form', keys=keys, values=values)
                self.cur.execute(sql, tuple(data.values()))
                row = self.cur.fetchone()
                if row:
                    self.dmca_form_id = row['id']
                self.con.commit()
                self.log(f"Inserted google_dmca_form id={self.dmca_form_id} state={data['state']}")

        except BaseException as e:
            print("Error: " + str(e))
            return

    def get_failed_dmca_submissions(self):
        # Ignore pending (state=1) mid-flight rows so they do not count as failures.
        self.cur.execute("""
            SELECT id, state
            FROM google_dmca_form
            WHERE state::text IS DISTINCT FROM '1'
            ORDER BY created DESC
            LIMIT 5
        """)
        rows = self.cur.fetchall()

        if not rows:
            print("No recent DMCA form records found.")
            return 0

        # Count how many have status = '-1'
        failed_count = sum(1 for row in rows if str(row['state']) == '-1')

        print(f"Out of last 5 submissions, {failed_count} failed.")
        return failed_count

    def update(self):
        print("STARTED UPDATING LINKS")
        chunk_size = 1000

        try:
            for i in range(0, len(self.link_ids), chunk_size):
                chunk = self.link_ids[i:i + chunk_size]

                # Start a transaction
                # self.cur.execute("START TRANSACTION")

                try:
                    placeholders = ",".join(["%s"] * len(chunk))
                    sql_update = f"UPDATE reference SET google_form_sent = '1', pirate_status = '4' WHERE id IN ({placeholders}) AND pirate_status != 1"
                    sql_backup_update = f"UPDATE reference_backup SET google_form_sent = '1', pirate_status = '4' WHERE id IN ({placeholders}) AND pirate_status != 1"

                    self.cur.execute(sql_update, tuple(chunk))
                    self.cur.execute(sql_backup_update, tuple(chunk))

                    self.con.commit()

                except Exception as e:
                    print(f"Error updating chunk {i//chunk_size + 1}: {e}")

        except Exception as e:
            print(f"Error dividing link_ids into chunks: {e}")

        return

    def regroup(self, link_data):
        project_id = link_data['project_id']

        sql = "SELECT * FROM google_form WHERE project_id = (%s) ORDER BY id DESC LIMIT 900"
        self.cur.execute(sql, [project_id])
        if not self.cur.rowcount:
            print("Missing project ")
            return
        new_links = ""
        project_data = self.cur.fetchall()
        print('The length is: ' + str(len(project_data)))
        if len(project_data) < 1:
            return

        print('Regrouping ... ')
        for project in project_data:
            log_id = project['id']
            link_id = project['link_ids']
            publisher = project['publisher']
            if len(link_id) < 5:  # missing link ids
                print("missing link ids")
                continue
            link_ids = link_id.split('*')
            print('Links length: ' + str(len(link_ids)))
            if len(link_ids) > 200:  # skip long link ids to avoid mysql insert error
                continue
            # we are  also adding duplicates here ..

            new_links += str(link_id) + '*'
            sql = "DELETE FROM google_form WHERE id = %s"
            self.cur.execute(sql, [log_id])
            self.con.commit()

            publisher = project['publisher']
            holder = project['project_official_rightholder_page']
            project_name = project['project_name']
            project_title = project['project_title']
            project_author = project['project_author']
        """
        file_name = '/home/moses/' + str(project_id) + '_' + str(int(time.time())) + '.log'
        with open(file_name,'w',encoding="utf-8") as fh:
            fh.write(new_links)
        """

        thesql = "INSERT INTO google_form (project_id,project_name,project_title,project_author,project_official_rightholder_page,publisher,link_ids) VALUES (%s,%s,%s,%s,%s,%s,%s)"
        try:
            self.cur.execute(thesql,
                             (project_id, project_name, project_title, project_author, holder, publisher, new_links))
            self.con.commit()
        except BaseException as e:
            print("Error: " + str(e))

    def delete_new_forms_created_on_error(self):
        for a_form_id in self.all_new_form_ids:
            sql = "DELETE FROM google_form WHERE id=%s"
            self.cur.execute(sql, [a_form_id])
        self.con.commit()
        time.sleep(5)

    def regroup_projects(self):
        # PostgreSQL doesn't need group_concat_max_len setting
        # self.cur.execute("set group_concat_max_len = 1045576")  # MySQL specific, not needed in PostgreSQL
        self.con.commit()
        print("Started grouping projects")
        self.cur.execute("""
            SELECT project_id, 
                   STRING_AGG(id::text, ',') AS id_list,
                   STRING_AGG(link_ids, '*') AS combined_links,
                   publisher, 
                   project_name, 
                   project_title, 
                   project_author, 
                   project_official_rightholder_page
            FROM google_form 
            GROUP BY project_id, publisher, project_name, project_title, project_author, project_official_rightholder_page;
        """)
        if self.cur.rowcount == 0:
            print("No entries")
            return
        projects = self.cur.fetchall()

        for project in projects:
            project_id = project['project_id']
            id_list = project['id_list']
            combined_links = project['combined_links']
            publisher = project['publisher']
            project_name = project['project_name']
            project_title = project['project_title']
            project_author = project['project_author']
            project_official_rightholder_page = project['project_official_rightholder_page']

            print(f'Project ID: {project_id}')

            unique_links = {
                link_id.strip()
                for link_id in combined_links.split('*')
                if link_id and link_id.strip().isdigit()
            }

            links = list(unique_links)
            batch_links = []
            for link in links:
                batch_links.append(link)
                if len(batch_links) == 1000:
                    # Insert the batch of links into the database
                    link_count = len(batch_links)
                    batch_links_str = '*'.join(batch_links)
                    self.cur.execute("""
                        INSERT INTO google_form (project_id, link_ids, link_count, publisher, project_name,
                        project_title, project_author, project_official_rightholder_page, grouped)
                        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
                    """, (
                    project_id, batch_links_str, link_count, publisher, project_name, project_title, project_author,
                    project_official_rightholder_page, 1))

                    batch_links = []

            if batch_links:
                filtered_count = [x for x in batch_links if x !='']
                link_count = len(filtered_count)

                batch_links_str = '*'.join(batch_links)
                self.cur.execute("""
                    INSERT INTO google_form (project_id, link_ids, link_count, publisher, project_name,
                    project_title, project_author, project_official_rightholder_page, grouped )
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
                """, (project_id, batch_links_str, link_count, publisher, project_name, project_title, project_author,
                      project_official_rightholder_page, 1))

            # print(f'Deleting IDs: {id_list}')
            self.cur.execute("DELETE FROM google_form WHERE id IN (" + id_list + ")")

        self.con.commit()


    def check_and_remove_duplicate_linkids(self):
        sql = "SELECT DISTINCT project_id FROM google_form"
        self.cur.execute(sql)
        if not self.cur.rowcount:
            print("Missing project ")
            return

        distinct_project_ids = self.cur.fetchall()

        # Process each distinct project ID
        for project_id_tuple in distinct_project_ids:
            project_id = project_id_tuple['project_id']

            select_rows_query = f"SELECT id, project_id, link_ids FROM google_form WHERE project_id = {project_id}"
            self.cur.execute(select_rows_query)

            # Fetch all rows for the current project ID
            all_rows = self.cur.fetchall()

            # Combine unique link IDs from all rows and join again
            unique_link_ids = set()
            for row in all_rows:
                # print(row['link_ids'])
                unique_link_ids.update(row['link_ids'].split('*'))

            combined_link_ids = '*'.join(sorted(unique_link_ids))
            # Update the first row with the combined and cleaned link_ids
            update_query = f"UPDATE google_form SET link_ids = '{combined_link_ids}' WHERE id = {all_rows[0]['id']}"
            self.cur.execute(update_query)

            # Delete other rows for the current project ID
            for row in all_rows[1:]:
                delete_query = f"DELETE FROM google_form WHERE id = {row['id']}"
                self.cur.execute(delete_query)

        self.con.commit()

        print("Data cleaned and updated in the database.")

    def stop_google_form(self):
        self.cur.execute("SELECT * FROM user_groups WHERE user_group_id = '7'")
        row = self.cur.fetchone()

        if not row:
            print("User group is empty")
            quit_google = True
            return
        settings = json.loads(row['settings'])

        # Update specific fields in the settings dictionary
        settings['auto_send_google_dmca_form'] = '0'

        # Convert the updated settings dictionary back to JSON
        updated_settings = json.dumps(settings)

        self.cur.execute("UPDATE user_groups SET settings = %s WHERE user_group_id = %s", (updated_settings, '7'))
        self.con.commit()

        user_email = self.current_google_user_email if self.current_google_user_email else ''
        if user_email:
            self.cur.execute("UPDATE google_user SET status = %s WHERE email = %s", (0, user_email))
        self.con.commit()

        print("Settings updated successfully.")
    

    def getContentType(self, project_id):
        sql = "SELECT * FROM project_content_types WHERE project_id = (%s)"
        self.cur.execute(sql, [project_id])
        if not self.cur.rowcount:
            return ""
        rows = self.cur.fetchall()
        content_type_id = rows[0]['content_type_id']

        sql = "SELECT * FROM content_types WHERE content_type_id = (%s)"
        self.cur.execute(sql, [str(content_type_id)])
        rowss = self.cur.fetchall()
        return rowss[0]


def main():
    #sys.settrace(print_executed_line)
    vm = psutil.virtual_memory()
    cpu = psutil.cpu_percent(interval=0.2)
    print(
        f"startup ram={vm.percent:.1f}% avail={vm.available / (1024 * 1024):.0f}MB cpu={cpu:.1f}%",
        flush=True,
    )
    if vm.percent >= 95 and vm.available < 200 * 1024 * 1024:
        print("RAM too low to start google_dmca_two.py. Exiting.", flush=True)
        sys.exit()
    global LOCK_FILE_HANDLE
    lock_path = "/tmp/google_dmca_two.lock"
    LOCK_FILE_HANDLE = open(lock_path, "w")
    try:
        fcntl.flock(LOCK_FILE_HANDLE, fcntl.LOCK_EX | fcntl.LOCK_NB)
        LOCK_FILE_HANDLE.write(str(os.getpid()))
        LOCK_FILE_HANDLE.flush()
    except BlockingIOError:
        print("Another google_dmca_two.py instance is already running. Exiting.")
        sys.exit()
    app = App()
    app.begin()


if __name__ == "__main__": main()

