-- PostgreSQL Schema for SEO Web Crawler Enterprise

-- 1. Sites Table (Domain bilgileri & E-posta Bildirim Adresi)
CREATE TABLE IF NOT EXISTS sites (
    id SERIAL PRIMARY KEY,
    domain VARCHAR(255) UNIQUE NOT NULL,
    notification_email VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 2. Scans Table (Tarama oturumları, SEO Sağlık Skoru, Sitemap & Robots.txt)
CREATE TABLE IF NOT EXISTS scans (
    id SERIAL PRIMARY KEY,
    site_id INT NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
    status VARCHAR(50) DEFAULT 'running',
    total_pages INT DEFAULT 0,
    seo_score INT DEFAULT 100, -- 0 ile 100 arasında genel SEO Sağlık Skoru
    sitemap_urls_count INT DEFAULT 0,
    sitemap_missing_in_crawl INT DEFAULT 0,
    robots_txt_found BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP WITH TIME ZONE
);

-- 3. Pages Table (SEO metrikleri, PageSpeed, Canonical, OG & Outbound Links)
CREATE TABLE IF NOT EXISTS pages (
    id SERIAL PRIMARY KEY,
    scan_id INT NOT NULL REFERENCES scans(id) ON DELETE CASCADE,
    url TEXT NOT NULL,
    status_code INT NOT NULL,
    title TEXT,
    title_length INT DEFAULT 0,
    meta_description TEXT,
    meta_description_length INT DEFAULT 0,
    h1_count INT DEFAULT 0,
    h1s JSONB DEFAULT '[]'::jsonb,
    has_multiple_h1 BOOLEAN DEFAULT FALSE,
    images_total INT DEFAULT 0,
    images_with_alt INT DEFAULT 0,
    images_missing_alt INT DEFAULT 0,
    images_details JSONB DEFAULT '[]'::jsonb,
    internal_links_count INT DEFAULT 0,
    canonical_url TEXT DEFAULT NULL,
    is_canonical_match BOOLEAN DEFAULT TRUE,
    og_title TEXT DEFAULT NULL,
    og_image TEXT DEFAULT NULL,
    outbound_links_total INT DEFAULT 0,
    outbound_links_broken INT DEFAULT 0,
    outbound_links_details JSONB DEFAULT '[]'::jsonb,
    pagespeed_mobile INT DEFAULT NULL,   -- Google PageSpeed Mobil Skoru (0-100)
    pagespeed_desktop INT DEFAULT NULL,  -- Google PageSpeed Masaüstü Skoru (0-100)
    page_seo_score INT DEFAULT 100,     -- Sayfa Bazlı SEO Skoru (0-100)
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Hızlı sorgular için indeksler
CREATE INDEX IF NOT EXISTS idx_pages_scan_id ON pages(scan_id);
CREATE INDEX IF NOT EXISTS idx_pages_url ON pages(url);
CREATE INDEX IF NOT EXISTS idx_scans_site_id ON scans(site_id);