"""Tests for the import-crawls subcommand."""

from __future__ import annotations

from pathlib import Path

import duckdb
import pytest

from suivi_seo.import_crawls import find_matches, parse_mapping, run


def _bootstrap_db(db_path: Path) -> None:
    """Create the schema needed by import-crawl.sql."""
    con = duckdb.connect(str(db_path))
    con.execute(
        """
        CREATE TABLE sites (slug VARCHAR PRIMARY KEY);
        INSERT INTO sites (slug) VALUES ('demo'), ('other');
        CREATE SEQUENCE seq_cp START 1;
        CREATE TABLE competitor_pages (
            id INTEGER PRIMARY KEY DEFAULT nextval('seq_cp'),
            site VARCHAR REFERENCES sites(slug),
            competitor_domain VARCHAR,
            url VARCHAR,
            status_code INTEGER,
            title VARCHAR,
            title_length INTEGER,
            meta_description VARCHAR,
            meta_description_length INTEGER,
            h1_first VARCHAR,
            h1_count INTEGER,
            h2_count INTEGER,
            h3_count INTEGER,
            word_count INTEGER,
            internal_links_count INTEGER,
            external_links_count INTEGER,
            image_count INTEGER,
            images_without_alt INTEGER,
            seo_score INTEGER,
            canonical VARCHAR,
            has_open_graph BOOLEAN,
            has_twitter_cards BOOLEAN,
            response_time_ms DECIMAL(10,2),
            page_size_bytes INTEGER,
            crawl_date DATE,
            source_file VARCHAR,
            UNIQUE(site, competitor_domain, url, crawl_date)
        );
        """
    )
    con.close()


def _csv_header() -> str:
    return (
        "url,status_code,title,title_length,meta_description,meta_description_length,"
        "h1_first,h1_count,h2_count,h3_count,word_count,internal_links_count,"
        "external_links_count,image_count,images_without_alt,seo_score,canonical,"
        "has_open_graph,has_twitter_cards,response_time_ms,page_size_bytes\n"
    )


def _csv_row(url: str) -> str:
    return f"{url},200,Title,5,Desc,4,H1,1,2,3,800,12,3,4,0,75,{url},true,false,123.45,50000\n"


def test_parse_mapping_valid() -> None:
    assert parse_mapping(["a:foo*.csv", "b:bar_*.csv"]) == [
        ("a", "foo*.csv"),
        ("b", "bar_*.csv"),
    ]


def test_parse_mapping_rejects_invalid() -> None:
    with pytest.raises(ValueError, match="invalid --mapping"):
        parse_mapping(["nope"])
    with pytest.raises(ValueError, match="invalid --mapping"):
        parse_mapping([":pattern"])
    with pytest.raises(ValueError, match="invalid --mapping"):
        parse_mapping(["site:"])


def test_find_matches_globs_multiple_files(tmp_path: Path) -> None:
    (tmp_path / "site_a_1.csv").write_text("x")
    (tmp_path / "site_a_2.csv").write_text("x")
    (tmp_path / "other.csv").write_text("x")
    matches = find_matches(tmp_path, "site_a_*.csv")
    names = [p.name for p in matches]
    assert names == ["site_a_1.csv", "site_a_2.csv"]


def test_run_imports_matching_csvs(tmp_path: Path) -> None:
    """Mapping should match multiple files and import each into competitor_pages."""
    crawl_dir = tmp_path / "crawls"
    crawl_dir.mkdir()
    (crawl_dir / "siren_com_20260315.csv").write_text(
        _csv_header() + _csv_row("https://siren.com/a") + _csv_row("https://siren.com/b"),
        encoding="utf-8",
    )
    (crawl_dir / "siren_com_20260316.csv").write_text(
        _csv_header() + _csv_row("https://siren.com/c"),
        encoding="utf-8",
    )

    db_path = tmp_path / "test.duckdb"
    _bootstrap_db(db_path)

    rc = run(
        crawl_dir=crawl_dir,
        db_path=db_path,
        mapping=["demo:siren_com_*.csv"],
    )
    assert rc == 0

    con = duckdb.connect(str(db_path))
    count = con.execute("SELECT COUNT(*) FROM competitor_pages WHERE site = 'demo'").fetchone()[0]
    con.close()
    # Note: import-crawl.sql uses UNIQUE(site, competitor_domain, url, crawl_date), so
    # two crawls on the same day for the same URLs dedupe. Three distinct URLs total.
    assert count == 3


def test_run_warns_on_no_match(tmp_path: Path, capsys: pytest.CaptureFixture) -> None:
    """No file matches → warning, exit 0, no DB row inserted."""
    crawl_dir = tmp_path / "crawls"
    crawl_dir.mkdir()
    (crawl_dir / "unrelated.csv").write_text(_csv_header(), encoding="utf-8")

    db_path = tmp_path / "test.duckdb"
    _bootstrap_db(db_path)

    rc = run(
        crawl_dir=crawl_dir,
        db_path=db_path,
        mapping=["demo:does_not_match_*.csv"],
    )
    assert rc == 0
    captured = capsys.readouterr()
    assert "no files matched" in captured.err.lower()

    con = duckdb.connect(str(db_path))
    count = con.execute("SELECT COUNT(*) FROM competitor_pages").fetchone()[0]
    con.close()
    assert count == 0


def test_run_fails_on_missing_crawl_dir(tmp_path: Path, capsys: pytest.CaptureFixture) -> None:
    db_path = tmp_path / "test.duckdb"
    _bootstrap_db(db_path)
    rc = run(
        crawl_dir=tmp_path / "nope",
        db_path=db_path,
        mapping=["demo:*.csv"],
    )
    assert rc == 1
    assert "does not exist" in capsys.readouterr().err