backend_val.ts

/** @jsxImportSource npm:react */
import { sqlite } from "https://esm.town/v/std/sqlite";
import * as cheerio from "npm:cheerio";

// -----------------------------------------------------------------------------
// 1. TYPES & INTERFACES
// -----------------------------------------------------------------------------

type DataSource = "realtime" | "manual";

interface CityData {
    city_slug: string;
    city_name: string;
    percentage: number;
    current_volume?: number;
    total_capacity?: number;
    recorded_at?: string;
    // Data source info
    data_source: DataSource;
    data_date?: string; // The actual date the data represents (for manual entries)
}

interface ApiResponse {
    last_updated: string;
    data: CityData[];
    history?: any[]; // For now, we return latest. Can be expanded.
}

// -----------------------------------------------------------------------------
// 2. DATABASE SETUP
// -----------------------------------------------------------------------------

async function setupDatabase() {
    await sqlite.execute(`
    CREATE TABLE IF NOT EXISTS daily_records (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      city_slug TEXT NOT NULL,
      city_name TEXT NOT NULL,
      percentage REAL NOT NULL,
      current_volume REAL,
      recorded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      data_source TEXT DEFAULT 'realtime',
      data_date TEXT
    );
  `);
    
    // Add new columns if they don't exist (for existing databases)
    try {
        await sqlite.execute(`ALTER TABLE daily_records ADD COLUMN data_source TEXT DEFAULT 'realtime'`);
    } catch (e) {
        // Column already exists, ignore
    }
    try {
        await sqlite.execute(`ALTER TABLE daily_records ADD COLUMN data_date TEXT`);
    } catch (e) {
        // Column already exists, ignore
    }
}

async function insertRecord(data: CityData) {
    await sqlite.execute({
        sql: `
      INSERT INTO daily_records (city_slug, city_name, percentage, current_volume, data_source, data_date)
      VALUES (:city_slug, :city_name, :percentage, :current_volume, :data_source, :data_date)
    `,
        args: {
            city_slug: data.city_slug,
            city_name: data.city_name,
            percentage: data.percentage,
            current_volume: data.current_volume || null,
            data_source: data.data_source || "realtime",
            data_date: data.data_date || null,
        },
    });
}

async function getLatestRecords(): Promise<CityData[]> {
    // Get the most recent record for each city
    const result = await sqlite.execute(`
    SELECT city_slug, city_name, percentage, current_volume, recorded_at, data_source, data_date
    FROM daily_records 
    WHERE id IN (
        SELECT MAX(id) 
        FROM daily_records 
        GROUP BY city_slug
    )
  `);
    
    // Map array rows to objects
    return result.rows.map((row: any) => ({
        city_slug: row[0],
        city_name: row[1],
        percentage: row[2],
        current_volume: row[3],
        recorded_at: row[4],
        data_source: row[5] || "realtime",
        data_date: row[6] || null,
    }));
}

async function getHistoryForCity(citySlug: string, days: number = 30): Promise<CityData[]> {
    const result = await sqlite.execute({
        sql: `
        SELECT city_slug, city_name, percentage, current_volume, recorded_at, data_source, data_date
        FROM daily_records 
        WHERE city_slug = :city_slug
        ORDER BY recorded_at DESC
        LIMIT :limit
        `,
        args: { city_slug: citySlug, limit: days }
    });
    
    return result.rows.map((row: any) => ({
        city_slug: row[0],
        city_name: row[1],
        percentage: row[2],
        current_volume: row[3],
        recorded_at: row[4],
        data_source: row[5] || "realtime",
        data_date: row[6] || null,
    }));
}

async function getPreviousDayRecords(): Promise<Map<string, number>> {
    // Get yesterday's record for each city (for trend comparison)
    const result = await sqlite.execute(`
    SELECT city_slug, percentage
    FROM daily_records 
    WHERE DATE(recorded_at) = DATE('now', '-1 day')
    GROUP BY city_slug
  `);
    
    const map = new Map<string, number>();
    for (const row of result.rows as any[]) {
        map.set(row[0], row[1]);
    }
    return map;
}

// -----------------------------------------------------------------------------
// 3. SCRAPERS
// -----------------------------------------------------------------------------

// Proxy services for SSL bypass (try multiple)
const PROXY_SERVICES = [
    (url: string) => `https://corsproxy.io/?${encodeURIComponent(url)}`,
    (url: string) => `https://api.allorigins.win/raw?url=${encodeURIComponent(url)}`,
    (url: string) => `https://proxy.cors.sh/${url}`,
];

// Helper to fetch with headers (mimic browser)
async function fetchHtml(url: string, useProxy = false) {
    const headers = {
        "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36",
        "Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8",
    };
    
    if (!useProxy) {
        const res = await fetch(url, { headers });
        if (!res.ok) throw new Error(`HTTP ${res.status}`);
        return res.text();
    }
    
    // Try each proxy service until one works
    for (const proxyFn of PROXY_SERVICES) {
        try {
            const proxyUrl = proxyFn(url);
            const res = await fetch(proxyUrl, { headers });
            if (res.ok) {
                return res.text();
            }
        } catch (e) {
            // Try next proxy
            continue;
        }
    }
    
    throw new Error(`All proxies failed for ${url}`);
}

// Helper to fetch JSON with proxy support
async function fetchJson(url: string, useProxy = false) {
    const headers = {
        "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36",
        "Accept": "application/json",
    };
    
    if (!useProxy) {
        const res = await fetch(url, { headers });
        if (!res.ok) throw new Error(`HTTP ${res.status}`);
        return res.json();
    }
    
    // Try each proxy service until one works
    for (const proxyFn of PROXY_SERVICES) {
        try {
            const proxyUrl = proxyFn(url);
            const res = await fetch(proxyUrl, { headers });
            if (res.ok) {
                const text = await res.text();
                return JSON.parse(text);
            }
        } catch (e) {
            // Try next proxy
            continue;
        }
    }
    
    throw new Error(`All proxies failed for ${url}`);
}

async function scrapeIstanbul(): Promise<CityData | null> {
    // Istanbul (ISKI) - Baraj Doluluk Oranları
    // The site uses JavaScript to render content, so we need special approaches
    
    // Approach 1: Try Jina AI reader which renders JavaScript
    try {
        const jinaUrl = "https://r.jina.ai/https://iski.istanbul/baraj-doluluk/";
        const jinaResponse = await fetch(jinaUrl, {
            headers: {
                "Accept": "text/plain",
                "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36",
            }
        });
        
        if (jinaResponse.ok) {
            const text = await jinaResponse.text();
            
            // Look for the percentage in the rendered content
            // Pattern: "23.93%" or similar
            const percentMatch = text.match(/(\d{1,2}[.,]\d{2})\s*%/);
            if (percentMatch) {
                const percentage = parseFloat(percentMatch[1].replace(",", "."));
                if (percentage >= 5 && percentage <= 100) {
                    console.log(`Istanbul: Found percentage ${percentage}% via Jina`);
                    return {
                        city_slug: "istanbul",
                        city_name: "Istanbul",
                        percentage: percentage,
                        data_source: "realtime" as DataSource,
                    };
                }
            }
            
            // Also try looking for "doluluk" near a number
            const dolulukMatch = text.match(/[Dd]oluluk[^0-9]*?(\d{1,2}[.,]\d{1,2})/i);
            if (dolulukMatch) {
                const percentage = parseFloat(dolulukMatch[1].replace(",", "."));
                if (percentage > 0 && percentage <= 100) {
                    console.log(`Istanbul: Found percentage ${percentage}% via Jina (doluluk pattern)`);
                    return {
                        city_slug: "istanbul",
                        city_name: "Istanbul",
                        percentage: percentage,
                        data_source: "realtime" as DataSource,
                    };
                }
            }
        }
    } catch (error) {
        console.error("Istanbul Jina scraper error:", error);
    }
    
    // Approach 2: Try WebShare proxy that renders JS
    try {
        const webShareUrl = `https://api.webshare.io/v2/proxy/render?url=${encodeURIComponent("https://iski.istanbul/baraj-doluluk/")}`;
        // This is a placeholder - WebShare requires API key
    } catch (error) {
        // Skip
    }
    
    // Approach 3: Try scraping with different proxy services
    const PROXY_URLS = [
        `https://api.allorigins.win/raw?url=${encodeURIComponent("https://iski.istanbul/baraj-doluluk/")}`,
        `https://corsproxy.io/?${encodeURIComponent("https://iski.istanbul/baraj-doluluk/")}`,
    ];
    
    for (const proxyUrl of PROXY_URLS) {
        try {
            const response = await fetch(proxyUrl, {
                headers: {
                    "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36",
                }
            });
            
            if (response.ok) {
                const html = await response.text();
                const $ = cheerio.load(html);
                
                // Look in script tags for embedded data
                const scripts = $('script').toArray();
                for (const script of scripts) {
                    const content = $(script).html() || '';
                    
                    // Look for JSON data with doluluk/percentage
                    // Many sites embed initial state as JSON
                    const jsonMatches = content.matchAll(/\{[^}]*"?(?:doluluk|percentage|oran)"?\s*:\s*"?(\d{1,2}[.,]\d{1,2})"?[^}]*\}/gi);
                    for (const match of jsonMatches) {
                        const percentage = parseFloat(match[1].replace(",", "."));
                        if (percentage >= 5 && percentage <= 100) {
                            console.log(`Istanbul: Found percentage ${percentage}% in script JSON`);
                            return {
                                city_slug: "istanbul",
                                city_name: "Istanbul",
                                percentage: percentage,
                                data_source: "realtime" as DataSource,
                            };
                        }
                    }
                    
                    // Look for array data like [23.93] or value: 23.93
                    const valueMatch = content.match(/(?:value|data|doluluk|oran)['":\s]*(\d{1,2}\.\d{2})/i);
                    if (valueMatch) {
                        const percentage = parseFloat(valueMatch[1]);
                        if (percentage >= 5 && percentage <= 100) {
                            console.log(`Istanbul: Found percentage ${percentage}% in script value`);
                            return {
                                city_slug: "istanbul",
                                city_name: "Istanbul",
                                percentage: percentage,
                                data_source: "realtime" as DataSource,
                            };
                        }
                    }
                }
                
                // Check body text
                const bodyText = $('body').text();
                const percentMatch = bodyText.match(/(\d{1,2}[.,]\d{2})\s*%/);
                if (percentMatch) {
                    const percentage = parseFloat(percentMatch[1].replace(",", "."));
                    if (percentage >= 5 && percentage <= 100) {
                        console.log(`Istanbul: Found percentage ${percentage}% in body text`);
                        return {
                            city_slug: "istanbul",
                            city_name: "Istanbul",
                            percentage: percentage,
                            data_source: "realtime" as DataSource,
                        };
                    }
                }
            }
        } catch (error) {
            console.error(`Istanbul proxy scraper error for ${proxyUrl}:`, error);
        }
    }
    
    // Approach 4: Try direct fetch with various headers
    const directUrls = [
        "https://iski.istanbul/baraj-doluluk/",
        "https://www.iski.istanbul/baraj-doluluk/",
    ];
    
    for (const url of directUrls) {
        try {
            const response = await fetch(url, {
                headers: {
                    "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36",
                    "Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8",
                    "Accept-Language": "tr-TR,tr;q=0.9,en;q=0.8",
                    "Cache-Control": "no-cache",
                }
            });
            
            if (response.ok) {
                const html = await response.text();
                const $ = cheerio.load(html);
                
                // Look for inline data or API URLs in the page
                const allScripts = $('script').toArray();
                for (const script of allScripts) {
                    const src = $(script).attr('src');
                    const content = $(script).html() || '';
                    
                    // Log script sources for debugging
                    if (src) {
                        console.log(`Istanbul: Found script src: ${src}`);
                    }
                    
                    // Look for API endpoint URLs
                    const apiMatch = content.match(/["'](\/api\/[^"']+|https?:\/\/[^"']*api[^"']*baraj[^"']*)["']/i);
                    if (apiMatch) {
                        console.log(`Istanbul: Found potential API: ${apiMatch[1]}`);
                        // Try to fetch from this API
                        try {
                            const apiUrl = apiMatch[1].startsWith('http') ? apiMatch[1] : `https://iski.istanbul${apiMatch[1]}`;
                            const apiResponse = await fetch(apiUrl);
                            if (apiResponse.ok) {
                                const apiData = await apiResponse.json();
                                console.log(`Istanbul: API response:`, JSON.stringify(apiData).substring(0, 200));
                                // Try to extract percentage from API response
                                const apiText = JSON.stringify(apiData);
                                const apiPercentMatch = apiText.match(/(\d{1,2}\.\d{2})/);
                                if (apiPercentMatch) {
                                    const percentage = parseFloat(apiPercentMatch[1]);
                                    if (percentage >= 5 && percentage <= 100) {
                                        return {
                                            city_slug: "istanbul",
                                            city_name: "Istanbul",
                                            percentage: percentage,
                                            data_source: "realtime" as DataSource,
                                        };
                                    }
                                }
                            }
                        } catch (apiError) {
                            console.error(`Istanbul API fetch error:`, apiError);
                        }
                    }
                }
            }
        } catch (error) {
            console.error(`Istanbul direct fetch error for ${url}:`, error);
        }
    }

    console.error("Istanbul scraper failed: Could not parse percentage from any source.");
    return null;
}

async function scrapeAnkara(): Promise<CityData | null> {
    // Ankara (ASKI) uses an ASP.NET AJAX endpoint for date updates.
    // URL: https://www.aski.gov.tr/TR/Default.aspx/Counter

    const API_URL = "https://www.aski.gov.tr/TR/Default.aspx/Counter";

    try {
        const response = await fetch(API_URL, {
            method: "POST",
            headers: {
                "Content-Type": "application/json; charset=utf-8",
                "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36",
            },
            body: JSON.stringify({}) // Empty body for this specific endpoint
        });

        if (!response.ok) throw new Error(`HTTP helper error ${response.status}`);

        const json = await response.json();
        // The API returns { d: "{\"success\":true,\"BarajDegeri\":\"13.95\",\"SonucSu\":\"29,26\",\"sonuc\":\"...\"}" }
        // We need to parse the inner JSON string in 'd'.

        if (json.d) {
            const innerData = JSON.parse(json.d);
            // SonucSu contains the actual dam fill percentage, not BarajDegeri
            if (innerData.SonucSu) {
                return {
                    city_slug: "ankara",
                    city_name: "Ankara",
                    percentage: parseFloat(innerData.SonucSu.replace(",", ".")),
                    data_source: "realtime" as DataSource,
                };
            }
        }

        console.error("Ankara scraper failed: API response structure changed.", json);
        return null;

    } catch (error) {
        console.error("Ankara scraper error:", error);
        return null;
    }
}

async function scrapeIzmir(): Promise<CityData | null> {
    // Izmir (IZSU) - Multiple URL attempts
    // Note: Main IZSU site requires e-Devlet auth, trying alternative approaches
    
    const URLS = [
        "https://www.izsu.gov.tr/",
        "https://www.izsu.gov.tr/tr/Kurumsal/1",
    ];
    
    for (const url of URLS) {
        try {
            const headers = {
                "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36",
                "Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8",
                "Accept-Language": "tr-TR,tr;q=0.9,en;q=0.8",
                "Cookie": "AspxAutoDetectCookieSupport=1",
            };
            
            const res = await fetch(url, { 
                headers,
                redirect: "manual" // Don't follow redirects automatically
            });
            
            // Handle redirect manually if needed
            if (res.status >= 300 && res.status < 400) {
                const location = res.headers.get("Location");
                if (location) {
                    const redirectRes = await fetch(location.startsWith("http") ? location : `https://www.izsu.gov.tr${location}`, { headers });
                    if (!redirectRes.ok) continue;
                    const html = await redirectRes.text();
                    const $ = cheerio.load(html);
                    const bodyText = $('body').text();
                    
                    // Look for percentage patterns
                    const match = bodyText.match(/(?:Doluluk|Baraj)[^0-9]*?(\d{1,2}[.,]\d{1,2})\s*%/i);
                    if (match) {
                        const percentage = parseFloat(match[1].replace(",", "."));
                        if (percentage > 0 && percentage <= 100) {
                            return { city_slug: "izmir", city_name: "Izmir", percentage, data_source: "realtime" as DataSource };
                        }
                    }
                }
                continue;
            }
            
            if (!res.ok) continue;
            
            const html = await res.text();
            const $ = cheerio.load(html);
            const bodyText = $('body').text();
            
            // Strategy 1: Look for doluluk/baraj percentage patterns
            const patterns = [
                /(?:Doluluk|Baraj)[^0-9]*?(\d{1,2}[.,]\d{1,2})\s*%/i,
                /(?:Toplam|Genel)[^0-9]*?%?\s*(\d{1,2}[.,]\d{1,2})\s*%?/i,
                /%\s*(\d{1,2}[.,]\d{1,2})/,
            ];
            
            for (const pattern of patterns) {
                const match = bodyText.match(pattern);
                if (match) {
                    const percentage = parseFloat(match[1].replace(",", "."));
                    if (percentage > 0 && percentage <= 100) {
                        return { city_slug: "izmir", city_name: "Izmir", percentage, data_source: "realtime" as DataSource };
                    }
                }
            }
            
        } catch (error) {
            console.error(`Izmir scraper error for ${url}:`, error);
            // Continue to next URL
        }
    }
    
    console.error("Izmir scraper failed: Could not access data (may require e-Devlet auth).");
    return null;
}

async function scrapeAdana(): Promise<CityData | null> {
    // Adana (ASKİ) - Dam fill rate page
    // URL: https://www.adana-aski.gov.tr/web/barajdoluluk.aspx
    // The percentage is shown in the header: "Barajlarımızdaki Doluluk Oranı: XX,XX"
    // NOTE: Uses proxy due to SSL certificate issues
    
    const MAIN_URL = "https://www.adana-aski.gov.tr/web/barajdoluluk.aspx";
    
    try {
        // Use proxy to bypass SSL issues
        const html = await fetchHtml(MAIN_URL, true);
        const $ = cheerio.load(html);
        const bodyText = $('body').text();
        
        // Strategy 1: Look for the header text "Doluluk Oranı:XX,XX"
        // Pattern from site: "Barajlarımızdaki Doluluk Oranı:65,26"
        const headerMatch = bodyText.match(/Doluluk\s*Oran[ıi]\s*:?\s*(\d{1,2}[.,]\d{1,2})/i);
        if (headerMatch) {
            const percentage = parseFloat(headerMatch[1].replace(",", "."));
            if (percentage > 0 && percentage <= 100) {
                return {
                    city_slug: "adana",
                    city_name: "Adana",
                    percentage: percentage,
                    data_source: "realtime" as DataSource,
                };
            }
        }
        
        // Strategy 2: Look for percentage in any format
        const percentMatch = bodyText.match(/%\s*(\d{1,2}[.,]\d{1,2})|(\d{1,2}[.,]\d{1,2})\s*%/);
        if (percentMatch) {
            const value = percentMatch[1] || percentMatch[2];
            const percentage = parseFloat(value.replace(",", "."));
            if (percentage > 0 && percentage <= 100) {
                return {
                    city_slug: "adana",
                    city_name: "Adana",
                    percentage: percentage,
                    data_source: "realtime" as DataSource,
                };
            }
        }
        
        console.error("Adana scraper failed: Could not find percentage data.");
        return null;
        
    } catch (error) {
        console.error("Adana scraper error:", error);
        return null;
    }
}

async function scrapeMugla(): Promise<CityData | null> {
    // Muğla (MUSKİ) - Dam fill rate page
    // URL: https://www.muski.gov.tr/baraj-doluluk-orani
    // Shows individual dams with their percentages
    
    const MAIN_URL = "https://www.muski.gov.tr/baraj-doluluk-orani";
    
    try {
        const html = await fetchHtml(MAIN_URL);
        const $ = cheerio.load(html);
        const bodyText = $('body').text();
        
        // Strategy 1: Look for "Doluluk Oranı: %XX" pattern
        const dolulukMatch = bodyText.match(/Doluluk\s*Oran[ıi]\s*:?\s*%?\s*(\d{1,2}(?:[.,]\d{1,2})?)/i);
        if (dolulukMatch) {
            const percentage = parseFloat(dolulukMatch[1].replace(",", "."));
            if (percentage > 0 && percentage <= 100) {
                return {
                    city_slug: "mugla",
                    city_name: "Mugla",
                    percentage: percentage,
                    data_source: "realtime" as DataSource,
                };
            }
        }
        
        // Strategy 2: Look for percentage patterns with %
        const percentMatches = bodyText.matchAll(/%\s*(\d{1,2}(?:[.,]\d{1,2})?)|(\d{1,2}(?:[.,]\d{1,2})?)\s*%/g);
        const percentages = (Array.from(percentMatches) as RegExpMatchArray[])
            .map(m => parseFloat((m[1] || m[2]).replace(",", ".")))
            .filter(p => p > 0 && p <= 100);
        
        if (percentages.length > 0) {
            // Return the first (most prominent) percentage
            return {
                city_slug: "mugla",
                city_name: "Mugla",
                percentage: percentages[0],
                data_source: "realtime" as DataSource,
            };
        }
        
        console.error("Mugla scraper failed: Could not find percentage data.");
        return null;
        
    } catch (error) {
        console.error("Mugla scraper error:", error);
        return null;
    }
}

async function scrapeSakarya(): Promise<CityData | null> {
    // Sakarya (SASKİ) - Uses JSON API endpoints
    // Main dam: Çakmak Barajı
    // API returns: [{"Doluluk":XX.XX,"Meter":XXX.XX,"HacimHM3":XX.XX}]
    // NOTE: Uses proxy due to SSL certificate issues
    
    const MAIN_API = "https://proxy.saski.gov.tr/cakmak-baraji.php";
    
    try {
        // Use proxy to bypass SSL issues
        const data = await fetchJson(MAIN_API, true);
        
        if (Array.isArray(data) && data[0] && typeof data[0].Doluluk === 'number') {
            const percentage = data[0].Doluluk;
            if (percentage > 0 && percentage <= 100) {
                return {
                    city_slug: "sakarya",
                    city_name: "Sakarya",
                    percentage: Math.round(percentage * 100) / 100,
                    data_source: "realtime" as DataSource,
                };
            }
        }
        
        console.error("Sakarya scraper failed: Invalid data format.", data);
        return null;
        
    } catch (error) {
        console.error("Sakarya scraper error:", error);
        
        // Fallback: Try scraping the HTML page with proxy
        try {
            const html = await fetchHtml("https://www.saski.gov.tr/barajlar/", true);
            const $ = cheerio.load(html);
            
            // Look for percentage in the page
            const bodyText = $('body').text();
            const match = bodyText.match(/Doluluk[^0-9]*(\d{1,2}[.,]\d{1,2})/i);
            if (match) {
                const percentage = parseFloat(match[1].replace(",", "."));
                if (percentage > 0 && percentage <= 100) {
                    return {
                        city_slug: "sakarya",
                        city_name: "Sakarya",
                        percentage: percentage,
                        data_source: "realtime" as DataSource,
                    };
                }
            }
        } catch (fallbackError) {
            console.error("Sakarya fallback scraper error:", fallbackError);
        }
        
        return null;
    }
}

async function scrapeGaziantep(): Promise<CityData | null> {
    // Gaziantep (GASKİ) - Homepage has percentage data
    // URL: https://www.gaski.gov.tr/
    
    try {
        const html = await fetchHtml("https://www.gaski.gov.tr/");
        const $ = cheerio.load(html);
        const bodyText = $('body').text();
        
        // Look for percentage patterns
        const percentMatches = bodyText.matchAll(/(\d{1,2}[.,]\d{1,3})%/g);
        const percentages = (Array.from(percentMatches) as RegExpMatchArray[])
            .map(m => parseFloat(m[1].replace(",", ".")))
            .filter(p => p > 10 && p <= 100);
        
        if (percentages.length > 0) {
            // First percentage is usually the main dam
            return {
                city_slug: "gaziantep",
                city_name: "Gaziantep",
                percentage: percentages[0],
                data_source: "realtime" as DataSource,
            };
        }
        
        console.error("Gaziantep scraper failed: Could not find percentage data.");
        return null;
        
    } catch (error) {
        console.error("Gaziantep scraper error:", error);
        return null;
    }
}

async function scrapeDenizli(): Promise<CityData | null> {
    // Denizli (DESKİ) - Homepage has doluluk data
    // URL: https://www.deski.gov.tr/
    
    try {
        const html = await fetchHtml("https://www.deski.gov.tr/");
        const $ = cheerio.load(html);
        const bodyText = $('body').text();
        
        // Look for doluluk percentage
        const dolulukMatch = bodyText.match(/[Dd]oluluk[^0-9]*(\d{1,2}[.,]\d+)%?/);
        if (dolulukMatch) {
            const percentage = parseFloat(dolulukMatch[1].replace(",", "."));
            if (percentage > 0 && percentage <= 100) {
                return {
                    city_slug: "denizli",
                    city_name: "Denizli",
                    percentage: percentage,
                    data_source: "realtime" as DataSource,
                };
            }
        }
        
        // Fallback: look for percentage patterns
        const percentMatches = bodyText.matchAll(/(\d{1,2}[.,]\d+)%/g);
        const percentages = (Array.from(percentMatches) as RegExpMatchArray[])
            .map(m => parseFloat(m[1].replace(",", ".")))
            .filter(p => p > 5 && p <= 100);
        
        if (percentages.length > 0) {
            return {
                city_slug: "denizli",
                city_name: "Denizli",
                percentage: percentages[0],
                data_source: "realtime" as DataSource,
            };
        }
        
        console.error("Denizli scraper failed: Could not find percentage data.");
        return null;
        
    } catch (error) {
        console.error("Denizli scraper error:", error);
        return null;
    }
}

async function scrapeEskisehir(): Promise<CityData | null> {
    // Eskişehir (ESKİ) - Has baraj doluluk page
    // URL: https://www.eski.gov.tr/
    
    const URLS = [
        "https://www.eski.gov.tr/",
        "https://www.eski.gov.tr/baraj-doluluk-oranlari",
    ];
    
    for (const url of URLS) {
        try {
            const html = await fetchHtml(url);
            const $ = cheerio.load(html);
            const bodyText = $('body').text();
            
            // Look for doluluk percentage
            const dolulukMatch = bodyText.match(/[Dd]oluluk[^0-9]*(\d{1,2}[.,]\d+)%?/);
            if (dolulukMatch) {
                const percentage = parseFloat(dolulukMatch[1].replace(",", "."));
                if (percentage > 0 && percentage <= 100) {
                    return {
                        city_slug: "eskisehir",
                        city_name: "Eskisehir",
                        percentage: percentage,
                        data_source: "realtime" as DataSource,
                    };
                }
            }
            
            // Look for baraj percentage
            const barajMatch = bodyText.match(/[Bb]araj[^0-9]*(\d{1,2}[.,]\d+)%?/);
            if (barajMatch) {
                const percentage = parseFloat(barajMatch[1].replace(",", "."));
                if (percentage > 0 && percentage <= 100) {
                    return {
                        city_slug: "eskisehir",
                        city_name: "Eskisehir",
                        percentage: percentage,
                        data_source: "realtime" as DataSource,
                    };
                }
            }
            
        } catch (error) {
            console.error(`Eskisehir scraper error for ${url}:`, error);
        }
    }
    
    console.error("Eskisehir scraper failed: Could not find percentage data.");
    return null;
}

async function scrapeTrabzon(): Promise<CityData | null> {
    // Trabzon (TİSKİ) - Has baraj doluluk data
    // URL: https://www.tiski.gov.tr/
    
    try {
        const html = await fetchHtml("https://www.tiski.gov.tr/");
        const $ = cheerio.load(html);
        const bodyText = $('body').text();
        
        // Look for doluluk percentage
        const dolulukMatch = bodyText.match(/[Dd]oluluk[^0-9]*(\d{1,2}[.,]\d+)%?/);
        if (dolulukMatch) {
            const percentage = parseFloat(dolulukMatch[1].replace(",", "."));
            if (percentage > 0 && percentage <= 100) {
                return {
                    city_slug: "trabzon",
                    city_name: "Trabzon",
                    percentage: percentage,
                    data_source: "realtime" as DataSource,
                };
            }
        }
        
        // Fallback: look for percentage patterns
        const percentMatches = bodyText.matchAll(/(\d{1,2}[.,]\d+)%/g);
        const percentages = (Array.from(percentMatches) as RegExpMatchArray[])
            .map(m => parseFloat(m[1].replace(",", ".")))
            .filter(p => p > 10 && p <= 100);
        
        if (percentages.length > 0) {
            return {
                city_slug: "trabzon",
                city_name: "Trabzon",
                percentage: percentages[0],
                data_source: "realtime" as DataSource,
            };
        }
        
        console.error("Trabzon scraper failed: Could not find percentage data.");
        return null;
        
    } catch (error) {
        console.error("Trabzon scraper error:", error);
        return null;
    }
}

async function scrapeKocaeli(): Promise<CityData | null> {
    // Kocaeli (İSU) - Baraj doluluk page
    // URL: https://www.isu.gov.tr/
    
    const URLS = [
        "https://www.isu.gov.tr/",
        "https://www.isu.gov.tr/baraj-doluluk-oranlari",
    ];
    
    for (const url of URLS) {
        try {
            const html = await fetchHtml(url);
            const $ = cheerio.load(html);
            const bodyText = $('body').text();
            
            // Look for doluluk percentage
            const dolulukMatch = bodyText.match(/[Dd]oluluk[^0-9]*(\d{1,2}[.,]\d+)%?/);
            if (dolulukMatch) {
                const percentage = parseFloat(dolulukMatch[1].replace(",", "."));
                if (percentage > 0 && percentage <= 100) {
                    return {
                        city_slug: "kocaeli",
                        city_name: "Kocaeli",
                        percentage: percentage,
                        data_source: "realtime" as DataSource,
                    };
                }
            }
            
            // Look for baraj percentage
            const barajMatch = bodyText.match(/[Bb]araj[^0-9]*(\d{1,2}[.,]\d+)%?/);
            if (barajMatch) {
                const percentage = parseFloat(barajMatch[1].replace(",", "."));
                if (percentage > 0 && percentage <= 100) {
                    return {
                        city_slug: "kocaeli",
                        city_name: "Kocaeli",
                        percentage: percentage,
                        data_source: "realtime" as DataSource,
                    };
                }
            }
            
        } catch (error) {
            console.error(`Kocaeli scraper error for ${url}:`, error);
        }
    }
    
    console.error("Kocaeli scraper failed: Could not find percentage data.");
    return null;
}

async function scrapeAntalya(): Promise<CityData | null> {
    // Antalya (ASAT) - Water utility
    // URL: https://www.asat.gov.tr/
    
    try {
        const html = await fetchHtml("https://www.asat.gov.tr/");
        const $ = cheerio.load(html);
        const bodyText = $('body').text();
        
        // Look for doluluk/baraj percentage
        const dolulukMatch = bodyText.match(/[Dd]oluluk[^0-9]*(\d{1,2}[.,]\d+)%?/);
        if (dolulukMatch) {
            const percentage = parseFloat(dolulukMatch[1].replace(",", "."));
            if (percentage > 0 && percentage <= 100) {
                return {
                    city_slug: "antalya",
                    city_name: "Antalya",
                    percentage: percentage,
                    data_source: "realtime" as DataSource,
                };
            }
        }
        
        // Fallback: percentage patterns
        const percentMatches = bodyText.matchAll(/(\d{1,2}[.,]\d+)%/g);
        const percentages = (Array.from(percentMatches) as RegExpMatchArray[])
            .map(m => parseFloat(m[1].replace(",", ".")))
            .filter(p => p > 10 && p <= 100);
        
        if (percentages.length > 0) {
            return {
                city_slug: "antalya",
                city_name: "Antalya",
                percentage: percentages[0],
                data_source: "realtime" as DataSource,
            };
        }
        
        console.error("Antalya scraper failed: Could not find percentage data.");
        return null;
        
    } catch (error) {
        console.error("Antalya scraper error:", error);
        return null;
    }
}


// -----------------------------------------------------------------------------
// 4. MANUAL DATA ENTRIES (e-Devlet)
// -----------------------------------------------------------------------------
// Bu veriler e-Devlet üzerinden manuel olarak eklenmektedir.
// İnternet üzerinde anlık veri kaynağı bulunmayan şehirler için kullanılır.
// Yaklaşık 1 hafta gecikme olabilir.
//
// Format:
// {
//   city_slug: "sehir_slug",
//   city_name: "Şehir Adı",
//   percentage: 45.5,
//   data_source: "manual",
//   data_date: "2026-01-10", // Verinin ait olduğu tarih
// }

interface ManualDataEntry {
    city_slug: string;
    city_name: string;
    percentage: number;
    data_date: string; // YYYY-MM-DD format
}

// Manuel veri girişleri - Bu listeyi güncelleyerek yeni veri ekleyebilirsiniz
// e-Devlet'te gördüğünüz tarihi data_date olarak girin
const MANUAL_DATA_ENTRIES: ManualDataEntry[] = [
    // Erzurum - e-Devlet'ten alınan veri
    {
        city_slug: "erzurum",
        city_name: "Erzurum",
        percentage: 35.29,
        data_date: "2026-01-05", // e-Devlet'te gösterilen tarih
    },
    // Yeni şehir eklemek için bu formatı kullanın:
    // {
    //     city_slug: "sehir_adi",  // küçük harf, türkçe karakter yok
    //     city_name: "Şehir Adı",
    //     percentage: 23.00,
    //     data_date: "2026-01-17", // e-Devlet'te gösterilen tarih (YYYY-MM-DD)
    // },
];

// Manuel verileri veritabanına ekle (sadece daha yeni veri varsa)
async function insertManualDataIfNewer() {
    for (const entry of MANUAL_DATA_ENTRIES) {
        // Mevcut en son kaydı kontrol et
        const existingResult = await sqlite.execute({
            sql: `
                SELECT data_date FROM daily_records 
                WHERE city_slug = :city_slug 
                ORDER BY id DESC LIMIT 1
            `,
            args: { city_slug: entry.city_slug }
        });
        
        const existingDate = existingResult.rows[0]?.[0] as string | null;
        
        // Eğer mevcut veri yoksa veya yeni veri daha güncel ise ekle
        if (!existingDate || entry.data_date > existingDate) {
            await insertRecord({
                city_slug: entry.city_slug,
                city_name: entry.city_name,
                percentage: entry.percentage,
                data_source: "manual",
                data_date: entry.data_date,
            });
            console.log(`Manuel veri eklendi: ${entry.city_name} - ${entry.percentage}% (${entry.data_date})`);
        }
    }
}

// -----------------------------------------------------------------------------
// 5. MAIN HANDLER
// -----------------------------------------------------------------------------

export default async function (req: Request): Promise<Response> {
    // Handle CORS preflight
    if (req.method === "OPTIONS") {
        return new Response(null, {
            headers: {
                "Access-Control-Allow-Origin": "*",
                "Access-Control-Allow-Methods": "GET, POST, OPTIONS",
                "Access-Control-Allow-Headers": "Content-Type",
            }
        });
    }
    
    // Ensure DB exists on every request (cheap in SQLite)
    await setupDatabase();

    const url = new URL(req.url);

    // POST: Multiple actions based on path or body
    if (req.method === "POST") {
        const path = url.pathname;
        
        // POST /add - Manuel veri ekleme endpoint'i
        if (path === "/add") {
            try {
                const body = await req.json();
                
                // Validate required fields
                if (!body.city_slug || !body.city_name || body.percentage === undefined || !body.data_date) {
                    return Response.json({ 
                        error: "Eksik alan. Gerekli: city_slug, city_name, percentage, data_date" 
                    }, { status: 400, headers: { "Access-Control-Allow-Origin": "*" } });
                }
                
                // Validate percentage
                const percentage = parseFloat(body.percentage);
                if (isNaN(percentage) || percentage < 0 || percentage > 100) {
                    return Response.json({ 
                        error: "Geçersiz percentage değeri (0-100 arası olmalı)" 
                    }, { status: 400, headers: { "Access-Control-Allow-Origin": "*" } });
                }
                
                // Validate date format (YYYY-MM-DD)
                const dateRegex = /^\d{4}-\d{2}-\d{2}$/;
                if (!dateRegex.test(body.data_date)) {
                    return Response.json({ 
                        error: "Geçersiz tarih formatı (YYYY-MM-DD olmalı, örn: 2026-01-17)" 
                    }, { status: 400, headers: { "Access-Control-Allow-Origin": "*" } });
                }
                
                // Insert the manual record
                await insertRecord({
                    city_slug: body.city_slug.toLowerCase(),
                    city_name: body.city_name,
                    percentage: percentage,
                    data_source: "manual",
                    data_date: body.data_date,
                });
                
                return Response.json({ 
                    status: "ok", 
                    message: `${body.city_name} için veri eklendi`,
                    data: {
                        city_slug: body.city_slug.toLowerCase(),
                        city_name: body.city_name,
                        percentage: percentage,
                        data_date: body.data_date,
                        data_source: "manual"
                    }
                }, { headers: { "Access-Control-Allow-Origin": "*" } });
                
            } catch (error) {
                return Response.json({ 
                    error: "JSON parse hatası" 
                }, { status: 400, headers: { "Access-Control-Allow-Origin": "*" } });
            }
        }
        
        // POST / - Trigger Scrapers (Scheduled Task)
        console.log("Starting Scheduled Scrape...");

        // 4 reliable cities only (realtime scrapers)
        const results = await Promise.all([
            scrapeIstanbul(),
            scrapeAnkara(),
            scrapeIzmir(),
            scrapeMugla(),
        ]);

        let successCount = 0;
        for (const data of results) {
            if (data) {
                // Validate
                if (data.percentage >= 0 && data.percentage <= 100) {
                    await insertRecord(data);
                    successCount++;
                }
            }
        }
        
        // Manuel veri girişlerini de ekle (e-Devlet verileri)
        await insertManualDataIfNewer();

        return Response.json({ 
            status: "ok", 
            scraped: successCount, 
            total_attempted: results.length,
            manual_entries_checked: MANUAL_DATA_ENTRIES.length
        }, { headers: { "Access-Control-Allow-Origin": "*" } });
    }

    // GET: Serve Data
    if (req.method === "GET") {
        const citySlug = url.searchParams.get("city");
        const historyDays = parseInt(url.searchParams.get("history") || "0");
        
        // If requesting specific city history
        if (citySlug && historyDays > 0) {
            const history = await getHistoryForCity(citySlug, historyDays);
            return Response.json({
                last_updated: new Date().toISOString(),
                city: citySlug,
                history: history,
            }, {
                headers: { "Access-Control-Allow-Origin": "*" }
            });
        }
        
        // Default: return latest records with trend data
        const records = await getLatestRecords();
        const previousDay = await getPreviousDayRecords();
        
        // Filter out excluded cities (e.g., Konya)
        const excludedCities = ["konya"];
        const filteredRecords = records.filter(r => !excludedCities.includes(r.city_slug));
        
        // Add trend to each record
        const dataWithTrend = filteredRecords.map(record => ({
            ...record,
            previous_percentage: previousDay.get(record.city_slug) || null,
            trend: previousDay.has(record.city_slug) 
                ? (record.percentage > previousDay.get(record.city_slug)! ? "up" : 
                   record.percentage < previousDay.get(record.city_slug)! ? "down" : "stable")
                : null,
            data_source: record.data_source || "realtime",
            data_date: record.data_date || null,
        }));
        
        return Response.json({
            last_updated: new Date().toISOString(),
            data: dataWithTrend,
        }, {
            headers: { "Access-Control-Allow-Origin": "*" }
        });
    }

    return new Response("Method Not Allowed", { status: 405 });
}