"""
Excel Link & Image Monitor
==========================
Excel dosyasındaki linkleri izler ve VDS'ye gönderir.

Özellikler:
- Direct Download Link ve MediaFire linklerini VDS'ye gönderir
- imageFirst sütunundaki resimleri indirir
- Başarısız linkleri takip eder ve tekrar dener
- GÜVENLİ EXCEL: Dosya kilidi + Bozuk dosya kurtarma

Kullanım:
    python excel_link_monitor.py
"""

import time
import requests
from datetime import datetime
import os
import logging
from logging.handlers import RotatingFileHandler

# Güvenli Excel modülünü import et
from safe_excel_ops import (
    safe_read_only_excel,
    check_excel_health,
    restore_from_backup,
    create_backup
)

# ═══════════════════════════════════════════════════════════════════
# AYARLAR
# ═══════════════════════════════════════════════════════════════════
EXCEL_FILE = "/home/projects/public_html/scraper_project/tcz-sync-03aug.xlsx"
FLASK_API_URL = "http://38.102.124.163:5000/add-download"
IMAGE_API_URL = "http://38.102.124.163:5000/download-image"
CHECK_INTERVAL = 15 * 60  # 15 dakika
START_ROW = 1

# Dosya yolları
BASE_DIR = "/home/projects/public_html/scraper_project"
LOG_FOLDER = os.path.join(BASE_DIR, "logs")
LOG_FILE = os.path.join(LOG_FOLDER, "monitor.log")
ERROR_LOG_FILE = os.path.join(LOG_FOLDER, "errors.log")
FAILED_LINKS_FILE = os.path.join(BASE_DIR, "failed_links.txt")
FAILED_IMAGES_FILE = os.path.join(BASE_DIR, "failed_images.txt")
PROCESSED_FILE = os.path.join(BASE_DIR, "processed_rows.txt")
PROCESSED_IMAGES_FILE_PATH = os.path.join(BASE_DIR, "processed_images.txt")

# Resim indirme ayarları (VDS'de)
IMAGE_FOLDER = r"C:\Downloads\Images"
IMAGE_PREFIX = "xyt"

# ═══════════════════════════════════════════════════════════════════
# LOGLAMA AYARLARI
# ═══════════════════════════════════════════════════════════════════
os.makedirs(LOG_FOLDER, exist_ok=True)

logger = logging.getLogger("ExcelMonitor")
logger.setLevel(logging.DEBUG)

# Mevcut handler'ları temizle
logger.handlers.clear()

# Konsol handler
console_handler = logging.StreamHandler()
console_handler.setLevel(logging.INFO)
console_format = logging.Formatter('%(message)s')
console_handler.setFormatter(console_format)

# Dosya handler (rotating)
file_handler = RotatingFileHandler(
    LOG_FILE, maxBytes=5*1024*1024, backupCount=5, encoding='utf-8'
)
file_handler.setLevel(logging.DEBUG)
file_format = logging.Formatter('%(asctime)s | %(levelname)-8s | %(message)s', datefmt='%Y-%m-%d %H:%M:%S')
file_handler.setFormatter(file_format)

# Hata handler
error_handler = RotatingFileHandler(
    ERROR_LOG_FILE, maxBytes=2*1024*1024, backupCount=3, encoding='utf-8'
)
error_handler.setLevel(logging.ERROR)
error_handler.setFormatter(file_format)

logger.addHandler(console_handler)
logger.addHandler(file_handler)
logger.addHandler(error_handler)


# ═══════════════════════════════════════════════════════════════════
# BAŞARISIZ LİNK TAKİBİ
# ═══════════════════════════════════════════════════════════════════

def save_failed_link(url, row_num, error_msg, link_type="download"):
    """Başarısız linki kaydet"""
    filename = FAILED_IMAGES_FILE if link_type == "image" else FAILED_LINKS_FILE
    try:
        with open(filename, 'a', encoding='utf-8') as f:
            f.write(f"{datetime.now().isoformat()}|{row_num}|{error_msg}|{url}\n")
    except Exception as e:
        logger.error(f"Failed link kaydedilemedi: {e}")


def load_failed_links(link_type="download"):
    """Başarısız linkleri yükle"""
    filename = FAILED_IMAGES_FILE if link_type == "image" else FAILED_LINKS_FILE
    failed = []
    if os.path.exists(filename):
        try:
            with open(filename, 'r', encoding='utf-8') as f:
                for line in f:
                    parts = line.strip().split('|')
                    if len(parts) >= 4:
                        failed.append({
                            'timestamp': parts[0],
                            'row': int(parts[1]),
                            'error': parts[2],
                            'url': parts[3]
                        })
        except Exception as e:
            logger.error(f"Failed links yüklenemedi: {e}")
    return failed


def clear_failed_link(url, link_type="download"):
    """Başarılı linki listeden çıkar"""
    filename = FAILED_IMAGES_FILE if link_type == "image" else FAILED_LINKS_FILE
    if not os.path.exists(filename):
        return
    
    try:
        with open(filename, 'r', encoding='utf-8') as f:
            lines = f.readlines()
        
        with open(filename, 'w', encoding='utf-8') as f:
            for line in lines:
                if url not in line:
                    f.write(line)
    except Exception as e:
        logger.error(f"Failed link silinemedi: {e}")


# ═══════════════════════════════════════════════════════════════════
# İŞLENEN SATIRLAR
# ═══════════════════════════════════════════════════════════════════

def load_processed_rows(filename):
    """İşlenen satırları yükle"""
    if os.path.exists(filename):
        try:
            with open(filename, 'r') as f:
                return set(int(line.strip()) for line in f if line.strip())
        except:
            pass
    return set()


def save_processed_row(filename, row_num):
    """İşlenen satırı kaydet"""
    with open(filename, 'a') as f:
        f.write(f"{row_num}\n")


# ═══════════════════════════════════════════════════════════════════
# API FONKSİYONLARI
# ═══════════════════════════════════════════════════════════════════

def send_to_api(url, row_num):
    """Link'i Flask API'ye gönder"""
    try:
        response = requests.post(
            FLASK_API_URL,
            json={"download_url": url},
            timeout=60
        )
        if response.status_code == 200:
            logger.info(f"  ✓ Satır {row_num}: API'ye gönderildi - {url[:60]}...")
            clear_failed_link(url, "download")
            return True
        else:
            error_msg = f"API hatası - HTTP {response.status_code}"
            logger.warning(f"  ✗ Satır {row_num}: {error_msg}")
            save_failed_link(url, row_num, error_msg, "download")
            return False
    except requests.exceptions.Timeout:
        error_msg = "Timeout (60s)"
        logger.error(f"  ✗ Satır {row_num}: {error_msg}")
        save_failed_link(url, row_num, error_msg, "download")
        return False
    except requests.exceptions.ConnectionError as e:
        error_msg = f"Bağlantı hatası: {str(e)[:50]}"
        logger.error(f"  ✗ Satır {row_num}: {error_msg}")
        save_failed_link(url, row_num, error_msg, "download")
        return False
    except Exception as e:
        error_msg = f"Beklenmeyen hata: {str(e)[:50]}"
        logger.error(f"  ✗ Satır {row_num}: {error_msg}")
        save_failed_link(url, row_num, error_msg, "download")
        return False


def send_image_to_api(url, row_num):
    """Resim linkini Image API'ye gönder"""
    try:
        response = requests.post(
            IMAGE_API_URL,
            json={
                "url": url,
                "folder": IMAGE_FOLDER,
                "prefix": IMAGE_PREFIX
            },
            timeout=120
        )
        if response.status_code == 200:
            data = response.json()
            logger.info(f"  ✓ Satır {row_num}: Resim indirildi - {data.get('filename', 'unknown')}")
            clear_failed_link(url, "image")
            return True
        else:
            error_msg = response.json().get('message', f'HTTP {response.status_code}')
            logger.warning(f"  ✗ Satır {row_num}: Resim hatası - {error_msg}")
            save_failed_link(url, row_num, error_msg, "image")
            return False
    except requests.exceptions.Timeout:
        error_msg = "Timeout (120s)"
        logger.error(f"  ✗ Satır {row_num}: Resim {error_msg}")
        save_failed_link(url, row_num, error_msg, "image")
        return False
    except requests.exceptions.ConnectionError as e:
        error_msg = f"Bağlantı hatası: {str(e)[:50]}"
        logger.error(f"  ✗ Satır {row_num}: Resim {error_msg}")
        save_failed_link(url, row_num, error_msg, "image")
        return False
    except Exception as e:
        error_msg = f"Beklenmeyen hata: {str(e)[:50]}"
        logger.error(f"  ✗ Satır {row_num}: Resim {error_msg}")
        save_failed_link(url, row_num, error_msg, "image")
        return False


# ═══════════════════════════════════════════════════════════════════
# YARDIMCI FONKSİYONLAR
# ═══════════════════════════════════════════════════════════════════

def find_column_index(ws, column_name):
    """Sütun adına göre index bul"""
    for col in range(1, ws.max_column + 1):
        cell_value = ws.cell(row=1, column=col).value
        if cell_value and column_name.lower() in str(cell_value).lower():
            return col
    return None


# ═══════════════════════════════════════════════════════════════════
# ANA KONTROL FONKSİYONU
# ═══════════════════════════════════════════════════════════════════

def check_excel():
    """Excel dosyasını kontrol et ve linkleri API'ye gönder"""
    logger.info(f"\n{'='*60}")
    logger.info(f"Excel kontrol ediliyor...")
    logger.info(f"{'='*60}")

    if not os.path.exists(EXCEL_FILE):
        logger.error(f"Excel dosyası bulunamadı: {EXCEL_FILE}")
        return

    # Excel sağlık kontrolü
    healthy, msg = check_excel_health(EXCEL_FILE)
    if not healthy:
        logger.error(f"Excel dosyası bozuk: {msg}")
        logger.info("Yedekten geri yükleme deneniyor...")
        
        if restore_from_backup(EXCEL_FILE):
            logger.info("✓ Yedekten geri yüklendi!")
        else:
            logger.error("Kurtarma başarısız! Scraper'ın dosyayı yeniden oluşturmasını bekleyin.")
            return

    wb = None
    release_func = None
    
    try:
        # Excel'i dosya kilidi ile aç (read-only)
        logger.info("Excel dosyası açılıyor (kilit ile)...")
        wb, release_func = safe_read_only_excel(EXCEL_FILE)
        ws = wb.active

        # Sütun indexlerini bul
        download_link1_col = find_column_index(ws, "downloadLink1")
        direct_download_col = find_column_index(ws, "Direct Download Link")
        image_first_col = find_column_index(ws, "imageFirst")

        logger.info(f"Sütun indexleri:")
        if download_link1_col:
            logger.info(f"  downloadLink1: Sütun {download_link1_col}")
        if direct_download_col:
            logger.info(f"  Direct Download Link: Sütun {direct_download_col}")
        if image_first_col:
            logger.info(f"  imageFirst: Sütun {image_first_col}")
        
        if not download_link1_col and not direct_download_col and not image_first_col:
            logger.error("Hiçbir geçerli sütun bulunamadı!")
            return

        # İşlenen satırları yükle
        processed_rows = load_processed_rows(PROCESSED_FILE)
        processed_images = load_processed_rows(PROCESSED_IMAGES_FILE_PATH)
        logger.info(f"Daha önce işlenen dosya satırları: {len(processed_rows)}")
        logger.info(f"Daha önce işlenen resim satırları: {len(processed_images)}")

        # Sayaçlar
        new_links_found = 0
        sent_count = 0
        new_images_found = 0
        images_sent = 0
        failed_count = 0
        failed_images_count = 0

        max_row = ws.max_row
        logger.info(f"Toplam satır: {max_row}, Kontrol başlangıç: {START_ROW}")

        for row_num in range(START_ROW, max_row + 1):
            urls_to_send = []
            
            # ═══════════════════════════════════════════════════════════
            # DOSYA İNDİRME
            # ═══════════════════════════════════════════════════════════
            if row_num not in processed_rows:
                # Direct Download Link sütunu
                if direct_download_col:
                    direct_link = ws.cell(row=row_num, column=direct_download_col).value
                    if direct_link:
                        direct_link_str = str(direct_link).strip()
                        
                        # Virgülle ayrılmış linkler
                        if ',' in direct_link_str:
                            links = [link.strip() for link in direct_link_str.split(',')]
                            for link in links:
                                if link.startswith("http"):
                                    urls_to_send.append(link)
                        elif direct_link_str.startswith("http"):
                            urls_to_send.append(direct_link_str)

                # MediaFire linki
                if not urls_to_send and download_link1_col:
                    download_link1 = ws.cell(row=row_num, column=download_link1_col).value
                    if download_link1 and "mediafire.com" in str(download_link1).lower():
                        urls_to_send.append(str(download_link1).strip())

                # Linkleri gönder
                if urls_to_send:
                    link_count = len(urls_to_send)
                    new_links_found += link_count
                    
                    logger.info(f"\n[Satır {row_num}] {link_count} dosya linki bulundu:")
                    
                    all_sent = True
                    for i, url in enumerate(urls_to_send, 1):
                        logger.info(f"  [{i}/{link_count}] URL: {url[:70]}...")
                        
                        if send_to_api(url, row_num):
                            sent_count += 1
                            time.sleep(2)
                        else:
                            all_sent = False
                            failed_count += 1
                    
                    if all_sent:
                        save_processed_row(PROCESSED_FILE, row_num)

            # ═══════════════════════════════════════════════════════════
            # RESİM İNDİRME (video/mediafire linki varsa)
            # ═══════════════════════════════════════════════════════════
            if image_first_col and row_num not in processed_images and urls_to_send:
                image_url = ws.cell(row=row_num, column=image_first_col).value
                
                if image_url and str(image_url).strip().startswith("http"):
                    image_url = str(image_url).strip()
                    
                    new_images_found += 1
                    logger.info(f"  [Resim] URL: {image_url[:70]}...")

                    if send_image_to_api(image_url, row_num):
                        save_processed_row(PROCESSED_IMAGES_FILE_PATH, row_num)
                        images_sent += 1
                        time.sleep(1)
                    else:
                        failed_images_count += 1

        # Özet
        logger.info(f"\n{'='*60}")
        logger.info(f"Kontrol tamamlandı!")
        logger.info(f"  Dosyalar:")
        logger.info(f"    Yeni link: {new_links_found}")
        logger.info(f"    Gönderilen: {sent_count}")
        if failed_count > 0:
            logger.warning(f"    Başarısız: {failed_count}")
        logger.info(f"  Resimler:")
        logger.info(f"    Yeni resim: {new_images_found}")
        logger.info(f"    İndirilen: {images_sent}")
        if failed_images_count > 0:
            logger.warning(f"    Başarısız: {failed_images_count}")
        logger.info(f"{'='*60}")

    except Exception as e:
        logger.error(f"Excel işleme hatası: {e}", exc_info=True)
    
    finally:
        # Kilidi bırak
        if release_func:
            release_func()
            logger.debug("Excel kilidi bırakıldı")


# ═══════════════════════════════════════════════════════════════════
# RETRY VE YARDIMCI FONKSİYONLAR
# ═══════════════════════════════════════════════════════════════════

def retry_failed_links():
    """Başarısız linkleri tekrar dene"""
    logger.info("\n" + "="*60)
    logger.info("Başarısız linkler tekrar deneniyor...")
    logger.info("="*60)
    
    # İndirme linkleri
    failed_downloads = load_failed_links("download")
    if failed_downloads:
        logger.info(f"Bekleyen indirme: {len(failed_downloads)}")
        success = 0
        for item in failed_downloads:
            if send_to_api(item['url'], item['row']):
                success += 1
                time.sleep(2)
        logger.info(f"  Başarılı: {success}/{len(failed_downloads)}")
    
    # Resimler
    failed_images = load_failed_links("image")
    if failed_images:
        logger.info(f"Bekleyen resim: {len(failed_images)}")
        success = 0
        for item in failed_images:
            if send_image_to_api(item['url'], item['row']):
                success += 1
                time.sleep(1)
        logger.info(f"  Başarılı: {success}/{len(failed_images)}")
    
    if not failed_downloads and not failed_images:
        logger.info("Bekleyen başarısız link yok!")


def show_failed_summary():
    """Başarısız link özeti"""
    logger.info("\n" + "="*60)
    logger.info("Başarısız Link Özeti")
    logger.info("="*60)
    
    failed_downloads = load_failed_links("download")
    failed_images = load_failed_links("image")
    
    if failed_downloads:
        logger.info(f"\nİndirmeler ({len(failed_downloads)}):")
        for item in failed_downloads[-10:]:
            logger.info(f"  Satır {item['row']}: {item['error']}")
    
    if failed_images:
        logger.info(f"\nResimler ({len(failed_images)}):")
        for item in failed_images[-10:]:
            logger.info(f"  Satır {item['row']}: {item['error']}")
    
    if not failed_downloads and not failed_images:
        logger.info("Başarısız link yok! 🎉")


def show_image_config():
    """VDS resim ayarlarını göster"""
    try:
        response = requests.get(
            "http://38.102.124.163:5000/get-image-config",
            timeout=30
        )
        if response.status_code == 200:
            data = response.json()
            logger.info("\n" + "="*60)
            logger.info("Resim Ayarları (VDS)")
            logger.info("="*60)
            logger.info(f"  Klasör: {data['folder']}")
            logger.info(f"  Ön ek: {data['prefix']}")
            logger.info(f"  Sayaç: {data['current_counter']}")
            logger.info(f"  Sonraki: {data['next_filename']}")
        else:
            logger.error(f"Ayarlar alınamadı: {response.status_code}")
    except Exception as e:
        logger.error(f"Bağlantı hatası: {e}")


def configure_image_settings():
    """Resim ayarlarını yapılandır"""
    global IMAGE_FOLDER, IMAGE_PREFIX
    
    logger.info("\n" + "="*60)
    logger.info("Resim Ayarları")
    logger.info("="*60)
    
    folder = input(f"Klasör [{IMAGE_FOLDER}]: ").strip()
    if folder:
        IMAGE_FOLDER = folder
    
    prefix = input(f"Ön ek [{IMAGE_PREFIX}]: ").strip()
    if prefix:
        IMAGE_PREFIX = prefix
    
    reset = input("Sayacı sıfırla? (e/h) [h]: ").strip().lower()
    
    try:
        response = requests.post(
            "http://38.102.124.163:5000/set-image-config",
            json={
                "folder": IMAGE_FOLDER,
                "prefix": IMAGE_PREFIX,
                "reset_counter": reset == 'e'
            },
            timeout=30
        )
        if response.status_code == 200:
            logger.info("✓ Ayarlar güncellendi!")
        else:
            logger.error(f"Güncelleme hatası: {response.status_code}")
    except Exception as e:
        logger.error(f"Bağlantı hatası: {e}")


# ═══════════════════════════════════════════════════════════════════
# ANA FONKSİYON
# ═══════════════════════════════════════════════════════════════════

def main():
    """Ana döngü"""
    logger.info("="*60)
    logger.info("Excel Link & Image Monitor v2.0")
    logger.info("="*60)
    logger.info(f"Excel: {EXCEL_FILE}")
    logger.info(f"API: {FLASK_API_URL}")
    logger.info(f"Image API: {IMAGE_API_URL}")
    logger.info(f"Interval: {CHECK_INTERVAL // 60} dakika")
    logger.info(f"Başlangıç: Satır {START_ROW}")
    logger.info("="*60)
    logger.info("\nKomutlar: c=ayarla, s=göster, r=retry, f=failed, q=çık")
    logger.info("="*60)
    
    show_image_config()
    show_failed_summary()

    while True:
        try:
            check_excel()
        except KeyboardInterrupt:
            logger.info("\n\nDurduruldu.")
            break
        except Exception as e:
            logger.error(f"Hata: {e}", exc_info=True)

        logger.info(f"\nSonraki kontrol: {CHECK_INTERVAL // 60} dakika sonra...")
        
        try:
            for _ in range(CHECK_INTERVAL):
                time.sleep(1)
        except KeyboardInterrupt:
            cmd = input("\nKomut (c/s/r/f/q/Enter): ").strip().lower()
            if cmd == 'c':
                configure_image_settings()
            elif cmd == 's':
                show_image_config()
            elif cmd == 'r':
                retry_failed_links()
            elif cmd == 'f':
                show_failed_summary()
            elif cmd == 'q':
                logger.info("Çıkılıyor...")
                break


if __name__ == "__main__":
    main()