from __future__ import annotations

import sqlite3
from datetime import datetime, timezone

import pytest

from jarvis_finance.storage.migrations import apply_migrations, get_schema_version
from jarvis_finance.services.grocery_optimizer import add_demo_migros_receipt, run_grocery_optimization
from jarvis_finance.services.grocery_price_providers import (
    AldiSuisseProvider,
    CoopProvider,
    DennerProvider,
    LidlSchweizProvider,
    MigrosProvider,
    OttosProvider,
    GroceryProviderRateLimitError,
    compare_product_prices,
    search_and_store_product_matches,
)


def db() -> sqlite3.Connection:
    conn = sqlite3.connect(':memory:')
    conn.row_factory = sqlite3.Row
    apply_migrations(conn)
    return conn


def test_schema_version_33_provider_cache_columns_exist() -> None:
    conn = db()
    assert get_schema_version(conn) == 43
    cols = {row['name'] for row in conn.execute('PRAGMA table_info(grocery_product_details_cache)').fetchall()}
    assert {'brand', 'image_url', 'price_decimal_text', 'currency', 'unit', 'unit_price_decimal_text', 'availability_status', 'promotion_text', 'source', 'confidence', 'quality_flags_json', 'raw_result_json'}.issubset(cols)


def test_migros_and_coop_provider_search_parse_mock_html_and_cache() -> None:
    conn = db()
    migros = MigrosProvider(fetcher=lambda url: '<article data-product><a href="/milch">Migros Milch 1L</a><span class="price">CHF 1.60</span><span class="unit-price">1.60/l</span><span class="brand">Migros</span></article>')
    coop = CoopProvider(fetcher=lambda url: '<article data-product><a href="/milch">Prix Garantie Milch 1L</a><span class="price">CHF 1.20</span><span class="unit-price">1.20/l</span><span class="brand">Prix Garantie</span></article>')

    first = migros.search_products(conn, 'milch', use_cache=False, max_results=3)
    assert first[0]['retailer'] == 'Migros'
    assert first[0]['price_decimal_text'] == '1.60'
    assert first[0]['currency'] == 'CHF'
    assert first[0]['source'] == 'web_fetch'
    cached = migros.search_products(conn, 'milch', use_cache=True, max_results=3)
    assert cached[0]['source'] == 'cache'
    assert cached[0]['product_url'].endswith('/milch')
    coop_result = coop.search_products(conn, 'milch', use_cache=False, max_results=3)[0]
    assert coop_result['retailer'] == 'Coop'
    assert coop_result['unit_price_decimal_text'] == '1.20'


def test_provider_rate_limit_and_skeleton_future_status() -> None:
    conn = db()
    def rate_limited(_url: str) -> str:
        raise GroceryProviderRateLimitError('rate_limited')
    provider = MigrosProvider(fetcher=rate_limited)
    result = provider.search_products(conn, 'milch', use_cache=False)
    assert result[0]['status'] == 'needs_review'
    assert 'rate_limited' in result[0]['quality_flags']
    for skeleton in [AldiSuisseProvider(), LidlSchweizProvider(), DennerProvider(), OttosProvider()]:
        rows = skeleton.search_products(conn, 'milch')
        assert rows[0]['future_status'] == 'skeleton_provider_not_live'
        assert rows[0]['status'] == 'needs_review'


def test_search_and_store_product_matches_uses_sources_and_no_provider_call_on_render() -> None:
    conn = db()
    receipt = add_demo_migros_receipt(conn, purchase_date='2026-05-19', store_name='Migros Test', items=[{'raw_product_name': 'Milch 1L', 'total_price_text': '1.50'}])
    calls = []
    provider = CoopProvider(fetcher=lambda url: calls.append(url) or '<article data-product><a href="/milch">Prix Garantie Milch 1L</a><span class="price">CHF 1.20</span><span class="unit-price">1.20/l</span></article>')
    matches = search_and_store_product_matches(conn, receipt['receipt_id'], providers=[provider], included_product_item_ids=[receipt['items'][0]['product_item_id']], max_products=1, use_cache=False)
    assert calls
    assert matches['provider_calls'] == 1
    stored = conn.execute('SELECT * FROM grocery_product_matches').fetchone()
    assert stored['source'] == 'web_fetch'
    assert stored['candidate_url'].startswith('https://')
    assert stored['fetched_at']


def test_price_comparison_prefers_unit_price_and_excludes_uncertain_matches() -> None:
    safe = compare_product_prices(original_price_text='1.50', candidate_price_text='1.20', original_unit_price_text='1.50/l', candidate_unit_price_text='1.20/l', currency='CHF', quality_flags=['close_match'])
    assert safe['can_calculate_savings'] is True
    assert safe['savings_text'] == '0.30'
    assert safe['basis'] == 'unit_price'
    package = compare_product_prices(original_price_text='3.00', candidate_price_text='2.50', original_unit_price_text=None, candidate_unit_price_text=None, currency='CHF', quality_flags=['exact_match'])
    assert package['can_calculate_savings'] is True
    assert 'package_price_fallback' in package['quality_flags']
    unsafe = compare_product_prices(original_price_text='3.00', candidate_price_text='2.50', currency='CHF', quality_flags=['needs_review'])
    assert unsafe['can_calculate_savings'] is False
    assert unsafe['savings_text'] == '0.00'
    expensive = compare_product_prices(original_price_text='1.50', candidate_price_text='2.00', original_unit_price_text='1.50/l', candidate_unit_price_text='2.00/l', currency='CHF', quality_flags=['exact_match'])
    assert expensive['can_calculate_savings'] is False
    assert 'not_cheaper' in expensive['quality_flags']


def test_higher_priced_provider_result_is_not_suggested() -> None:
    conn = db()
    receipt = add_demo_migros_receipt(conn, purchase_date='2026-05-19', store_name='Migros Test', items=[{'raw_product_name': 'Milch 1L', 'unit_price_text': '1.50/l', 'total_price_text': '1.50'}])
    provider = CoopProvider(fetcher=lambda _url: '<article data-product><a href="/milch">Milch 1L</a><span class="price">CHF 2.00</span><span class="unit-price">2.00/l</span></article>')
    result = search_and_store_product_matches(conn, receipt['receipt_id'], providers=[provider], included_product_item_ids=[receipt['items'][0]['product_item_id']], use_cache=False)
    assert conn.execute('SELECT COUNT(*) AS c FROM grocery_product_matches').fetchone()['c'] == 1
    stored = conn.execute('SELECT status,candidate_price_text,quality_flags_json FROM grocery_product_matches').fetchone()
    assert stored['status'] == 'needs_review'
    assert stored['candidate_price_text'] == '2.00'


def test_search_and_store_preserves_provider_cache_and_source_timestamp() -> None:
    conn = db()
    receipt = add_demo_migros_receipt(conn, purchase_date='2026-05-19', store_name='Migros Test', items=[{'raw_product_name': 'Milch 1L', 'total_price_text': '1.50'}])
    calls = []
    provider = CoopProvider(fetcher=lambda url: calls.append(url) or '<article data-product><a href="/milch">Prix Garantie Milch 1L</a><span class="price">CHF 1.20</span><span class="unit-price">1.20/l</span></article>')
    first = search_and_store_product_matches(conn, receipt['receipt_id'], providers=[provider], included_product_item_ids=[receipt['items'][0]['product_item_id']], use_cache=False)
    first_timestamp = first['matches'][0]['source_fetched_at']
    second = search_and_store_product_matches(conn, receipt['receipt_id'], providers=[provider], included_product_item_ids=[receipt['items'][0]['product_item_id']], use_cache=True)
    assert len(calls) == 1
    assert second['cache_hits'] >= 1
    assert second['matches'][0]['source'] == 'cache'
    assert second['matches'][0]['source_fetched_at'] == first_timestamp


def test_missing_provider_price_is_not_stored_as_zero_match() -> None:
    conn = db()
    receipt = add_demo_migros_receipt(conn, purchase_date='2026-05-19', store_name='Migros Test', items=[{'raw_product_name': 'Milch 1L', 'total_price_text': '1.50'}])
    provider = CoopProvider(fetcher=lambda _url: '<article data-product><a href="/milch">Coop Milch ohne Preis</a></article>')
    result = search_and_store_product_matches(conn, receipt['receipt_id'], providers=[provider], included_product_item_ids=[receipt['items'][0]['product_item_id']], use_cache=False)
    assert result['matches'][0]['status'] == 'needs_review'
    assert result['matches'][0]['price_text'] is None
    assert result['matches'][0]['match_id']
    stored = conn.execute('SELECT status,candidate_price_text,quality_flags_json FROM grocery_product_matches').fetchone()
    assert stored['status'] == 'needs_review'
    assert stored['candidate_price_text'] is None


def test_stale_cache_is_not_used_without_refresh() -> None:
    conn = db()
    provider = CoopProvider(fetcher=lambda _url: '<article data-product><a href="/milch">Coop Milch</a><span class="price">CHF 1.20</span></article>')
    rows = provider.search_products(conn, 'milch', use_cache=False)
    conn.execute("UPDATE grocery_product_details_cache SET fetched_at='2000-01-01T00:00:00+00:00'")
    conn.commit()
    calls_before = provider.request_count
    rows = provider.search_products(conn, 'milch', use_cache=True, max_age_seconds=60)
    assert provider.request_count == calls_before + 1
    assert rows[0]['source'] == 'web_fetch'


def test_provider_matches_feed_optimizer_only_when_safe() -> None:
    conn = db()
    receipt = add_demo_migros_receipt(conn, purchase_date='2026-05-19', store_name='Migros Test', items=[{'raw_product_name': 'Milch 1L', 'unit_price_text': '1.50/l', 'total_price_text': '1.50'}])
    provider = CoopProvider(fetcher=lambda url: '<article data-product><a href="/milch">Prix Garantie Milch 1L</a><span class="price">CHF 1.20</span><span class="unit-price">1.20/l</span></article>')
    search_and_store_product_matches(conn, receipt['receipt_id'], providers=[provider], included_product_item_ids=[receipt['items'][0]['product_item_id']], use_cache=False)
    run = run_grocery_optimization(conn, receipt['receipt_id'], selected_retailers=['Coop'], max_store_count=1, included_product_item_ids=[receipt['items'][0]['product_item_id']])
    assert run['summary']['estimated_savings_text'] == '0.30'
    assert run['product_comparisons'][0]['source_url'].startswith('https://')