from __future__ import annotations

import sqlite3
from pathlib import Path

import pytest

from jarvis_finance.storage.migrations import apply_migrations, get_schema_version
from jarvis_finance.services.grocery_optimizer import (
    add_demo_migros_receipt,
    accept_grocery_product_match,
    reject_grocery_product_match,
    create_manual_grocery_mapping,
    refresh_grocery_mapping_price,
    get_grocery_optimizer_dashboard,
    run_grocery_optimization,
    generate_grocery_optimization_report,
    get_grocery_provider_strategy,
    build_rappn_link_out,
    preview_manual_grocery_mapping,
    confirm_manual_grocery_mapping,
)
from jarvis_finance.services.grocery_price_providers import search_and_store_product_matches, search_open_prices_spike, CoopProvider, GroceryProductProvider, GroceryProviderResult


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


def seed_receipt(conn: sqlite3.Connection, raw='Milch 1L', receipt_id='r1') -> dict:
    return add_demo_migros_receipt(conn, purchase_date='2026-05-19', store_name='Migros', receipt_id=receipt_id, items=[{'raw_product_name': raw, 'quantity_text': '1L', 'unit': 'l', 'unit_price_text': '1.50/l', 'total_price_text': '1.50'}])


class StaticProvider(GroceryProductProvider):
    def __init__(self, retailer: str, results: list[GroceryProviderResult]) -> None:
        super().__init__(fetcher=lambda _url: "")
        self.retailer = retailer
        self.base_url = f'https://{retailer.lower().replace(" ", "-")}.example/'
        self._results = results

    def search_products(self, conn: sqlite3.Connection, query: str, retailer: str | None = None, locale: str = 'de-CH', use_cache: bool = True, max_results: int = 5, max_age_seconds: int = 86400) -> list[dict]:
        self.request_count += 1
        return [r.as_dict() for r in self._results[:max_results]]


def test_accept_match_creates_preferred_mapping_and_next_receipt_reuses_without_provider_call() -> None:
    conn = db()
    first = seed_receipt(conn, receipt_id='r1')
    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>')
    found = search_and_store_product_matches(conn, 'r1', providers=[provider], included_product_item_ids=[first['items'][0]['product_item_id']], use_cache=False)
    accepted = accept_grocery_product_match(conn, found['matches'][0]['match_id'], user_note='passt')
    assert accepted['status'] == 'accepted'
    assert accepted['target_retailer'] == 'Coop'

    second = seed_receipt(conn, receipt_id='r2')
    fail_provider = CoopProvider(fetcher=lambda _url: (_ for _ in ()).throw(AssertionError('provider must not be called')))
    reused = search_and_store_product_matches(conn, 'r2', providers=[fail_provider], included_product_item_ids=[second['items'][0]['product_item_id']], prefer_known_mappings=True, use_cache=True)
    assert reused['provider_calls'] == 0
    assert reused['known_mapping_hits'] == 1
    assert reused['matches'][0]['source'] == 'known_mapping'
    run = run_grocery_optimization(conn, 'r2', selected_retailers=['Coop'], included_product_item_ids=[second['items'][0]['product_item_id']])
    assert run['summary']['replaceable_product_count'] == 1


def test_rejected_match_is_persisted_and_not_recommended_again() -> None:
    conn = db()
    receipt = seed_receipt(conn)
    provider = CoopProvider(fetcher=lambda _url: '<article data-product><a href="/milch">Coop Milch 1L</a><span class="price">CHF 1.20</span><span class="unit-price">1.20/l</span></article>')
    found = search_and_store_product_matches(conn, 'r1', providers=[provider], included_product_item_ids=[receipt['items'][0]['product_item_id']], use_cache=False)
    rejected = reject_grocery_product_match(conn, found['matches'][0]['match_id'], user_note='falsch')
    assert rejected['status'] == 'rejected'

    second = seed_receipt(conn, receipt_id='r2')
    reused = search_and_store_product_matches(conn, 'r2', providers=[provider], included_product_item_ids=[second['items'][0]['product_item_id']], prefer_known_mappings=True, use_cache=True)
    assert all(m.get('status') != 'suggested' for m in reused['matches'])
    run = run_grocery_optimization(conn, 'r2', selected_retailers=['Coop'], included_product_item_ids=[second['items'][0]['product_item_id']])
    assert run['summary']['replaceable_product_count'] == 0
    assert 'Milch 1L' in run['optimized_lists'][0]['rejected_products']


def test_manual_mapping_can_be_saved_and_used_after_sourced_cache_refresh() -> None:
    conn = db()
    receipt = seed_receipt(conn)
    mapping = create_manual_grocery_mapping(conn, product_item_id=receipt['items'][0]['product_item_id'], target_retailer='Denner', target_product_name='Denner Milch 1L', target_product_url='https://www.denner.ch/milch', target_brand='Denner', target_package_size='1L', target_unit='l', match_type='manual_match', candidate_price_text='1.10', candidate_unit_price_text='1.10/l', user_note='manuell notiert')
    assert mapping['status'] == 'accepted'
    assert mapping['match_type'] == 'manual_match'
    assert mapping['last_price_checked_at'] is None
    run_before = run_grocery_optimization(conn, 'r1', selected_retailers=['Denner'], included_product_item_ids=[receipt['items'][0]['product_item_id']])
    assert run_before['summary']['replaceable_product_count'] == 0
    conn.execute("INSERT INTO grocery_product_details_cache(detail_id,retailer,product_url,product_name,price_text,unit_price_text,package_size,ingredients_text,nutrition_json,fetched_at,cache_status,source_hash) VALUES ('d1','Denner','https://www.denner.ch/milch','Denner Milch 1L','1.10','1.10/l','1L',NULL,'{}','2026-05-19T10:00:00+00:00','cached','hash')")
    refreshed = refresh_grocery_mapping_price(conn, mapping['mapping_id'])
    assert refreshed['last_price_checked_at'] == '2026-05-19T10:00:00+00:00'
    run_after = run_grocery_optimization(conn, 'r1', selected_retailers=['Denner'], included_product_item_ids=[receipt['items'][0]['product_item_id']])
    assert run_after['optimized_total_text'] == '1.10'


def test_manual_mapping_rejects_unsafe_url_and_match_type() -> None:
    conn = db()
    receipt = seed_receipt(conn)
    for unsafe_url in ['javascript:alert(1)', 'http://127.1/x', 'http://[::1]/x', 'http://169.254.169.254/latest', 'http://172.31.0.1/x', 'http://localhost/x']:
        with pytest.raises(ValueError):
            create_manual_grocery_mapping(conn, product_item_id=receipt['items'][0]['product_item_id'], target_retailer='Coop', target_product_name='Bad', target_product_url=unsafe_url)
    with pytest.raises(ValueError):
        create_manual_grocery_mapping(conn, product_item_id=receipt['items'][0]['product_item_id'], target_retailer='Coop', target_product_name='Bad', target_product_url='https://coop.example/bad', match_type='evil_match')


def test_dashboard_surfaces_known_mappings_and_cache_status() -> None:
    conn = db()
    receipt = seed_receipt(conn)
    mapping = create_manual_grocery_mapping(conn, product_item_id=receipt['items'][0]['product_item_id'], target_retailer='Coop', target_product_name='Coop Milch 1L', target_product_url='https://coop.example/milch', match_type='manual_match', candidate_price_text='1.20')
    dash = get_grocery_optimizer_dashboard(conn)
    item = dash['receipts'][0]['items'][0]
    assert item['known_mappings'][0]['mapping_id'] == mapping['mapping_id']
    assert item['known_mappings'][0]['cache_status'] in {'frisch', 'veraltet', 'fehlt', 'fehler'}


def test_report_contains_known_mappings_sources_and_disclaimer_runtime_only(tmp_path: Path) -> None:
    conn = db()
    receipt = seed_receipt(conn)
    create_manual_grocery_mapping(conn, product_item_id=receipt['items'][0]['product_item_id'], target_retailer='Coop', target_product_name='Coop Milch 1L', target_product_url='https://coop.example/milch', match_type='manual_match', candidate_price_text='1.20')
    run = run_grocery_optimization(conn, 'r1', selected_retailers=['Coop'], included_product_item_ids=[receipt['items'][0]['product_item_id']])
    report = generate_grocery_optimization_report(conn, run['run_id'], reports_dir=tmp_path / 'runtime-reports', repo_root=tmp_path / 'repo')
    text = Path(report['report_path']).read_text()
    assert report['runtime_only'] is True
    assert 'Bon-Auswahl' in text
    assert 'Analysierte Produkte' in text
    assert 'Akzeptierte Alternativen' in text
    assert 'Abgelehnte Alternativen' in text
    assert 'Unsichere Produkte' in text
    assert 'Nicht gefundene Produkte' in text
    assert 'Geschätzte Ersparnis nur aus sicheren Matches' in text
    assert 'Bekannte Mappings' in text
    assert 'https://coop.example/milch' in text
    assert 'keine medizinische Empfehlung' in text


def test_no_price_provider_candidate_is_persisted_and_rejectable() -> None:
    conn = db()
    receipt = seed_receipt(conn)
    provider = StaticProvider('Coop', [GroceryProviderResult(retailer='Coop', product_name='Coop Milch ohne Preis', product_url='https://coop.example/no-price', price_text=None, unit_price_text=None, confidence='0.50')])
    found = search_and_store_product_matches(conn, 'r1', providers=[provider], included_product_item_ids=[receipt['items'][0]['product_item_id']], use_cache=False)
    assert found['matches'][0]['status'] == 'needs_review'
    assert found['matches'][0]['match_id']
    accepted = accept_grocery_product_match(conn, found['matches'][0]['match_id'], save_as_preferred=False)
    assert accepted['status'] == 'needs_review'
    assert accepted['match_type'] == 'manual_match'
    rejected = reject_grocery_product_match(conn, found['matches'][0]['match_id'])
    assert rejected['status'] == 'rejected'


def test_rejected_exact_match_does_not_suppress_other_retailers() -> None:
    conn = db()
    receipt = seed_receipt(conn)
    coop = StaticProvider('Coop', [GroceryProviderResult(retailer='Coop', product_name='Coop Milch 1L', product_url='https://coop.example/milch', price_text='1.20', unit_price_text='1.20/l', quality_flags=['close_match'], confidence='0.80')])
    found = search_and_store_product_matches(conn, 'r1', providers=[coop], included_product_item_ids=[receipt['items'][0]['product_item_id']], use_cache=False)
    reject_grocery_product_match(conn, found['matches'][0]['match_id'])
    denner = StaticProvider('Denner', [GroceryProviderResult(retailer='Denner', product_name='Denner Milch 1L', product_url='https://denner.example/milch', price_text='1.10', unit_price_text='1.10/l', quality_flags=['close_match'], confidence='0.80')])
    second = seed_receipt(conn, receipt_id='r2')
    found_again = search_and_store_product_matches(conn, 'r2', providers=[denner], included_product_item_ids=[second['items'][0]['product_item_id']], prefer_known_mappings=True, use_cache=False)
    assert any(m['retailer'] == 'Denner' and m['status'] == 'suggested' for m in found_again['matches'])


def test_provider_strategy_documents_rappn_linkout_and_llm_limits() -> None:
    strategy = get_grocery_provider_strategy()
    rappn = next(row for row in strategy['provider_matrix'] if row['provider'] == 'Rappn.ch')
    assert rappn['official_api'] is False
    assert 'Link-Out' in rappn['recommendation'] or 'Link-Out' in strategy['rappn']['mode']
    assert strategy['rappn']['mode'] == 'link_out_only'
    assert any('Preise erfinden' in item for item in strategy['llm_policy']['forbidden'])
    assert any('Produktnamen normalisieren' in item for item in strategy['llm_policy']['allowed'])
    link = build_rappn_link_out('Milch 1L')
    assert link['mode'] == 'link_out_only'
    assert link['manual_source'] == 'manual/rappn_user_checked'
    assert 'api.rappn' not in link['url']


def test_open_prices_without_price_is_never_secure() -> None:
    payload = {'items': [{'id': 7, 'product': {'code': '123', 'product_name': 'Milch', 'product_quantity': '1'}, 'location': {'osm_name': 'Test Shop'}, 'proof': {'date': '2026-05-19', 'currency': 'CHF'}}]}
    result = search_open_prices_spike(query='Milch', fetcher=lambda _url: __import__('json').dumps(payload))
    candidate = result['candidates'][0]
    assert candidate['source_url'].endswith('/7')
    assert candidate['price_text'] is None
    assert candidate['can_count_as_secure_saving'] is False
    assert 'no_price' in candidate['quality_flags']


def test_open_prices_with_price_still_candidate_with_source_and_date() -> None:
    payload = {'items': [{'id': 8, 'price': '1.25', 'currency': 'CHF', 'date': '2026-05-19', 'product': {'code': '123', 'product_name': 'Milch', 'product_quantity': '1L'}, 'location': {'osm_name': 'Open Shop'}, 'proof': {'date': '2026-05-18'}}]}
    result = search_open_prices_spike(barcode='123', fetcher=lambda _url: __import__('json').dumps(payload))
    candidate = result['candidates'][0]
    assert candidate['price_text'] == '1.25'
    assert candidate['price_date'] == '2026-05-19'
    assert candidate['source_url'].endswith('/8')
    assert candidate['can_count_as_secure_saving'] is False
    assert candidate['attribution']


def test_manual_price_preview_confirm_audit_and_secure_saving_when_complete() -> None:
    conn = db()
    receipt = seed_receipt(conn)
    item_id = receipt['items'][0]['product_item_id']
    preview = preview_manual_grocery_mapping(conn, product_item_id=item_id, target_retailer='Denner', target_product_name='Denner Milch 1L', target_product_url='https://www.denner.ch/milch', candidate_price_text='1.10', candidate_unit_price_text='1.10/l', target_package_size='1L', target_unit='l', target_price_date='2026-05-19', target_price_source='manual/rappn_user_checked', user_note='manuell geprüft')
    assert preview['requires_confirm'] is True
    assert preview['can_count_as_secure_saving'] is True
    mapping = confirm_manual_grocery_mapping(conn, preview)
    assert mapping['target_price_date'] == '2026-05-19'
    assert mapping['target_price_source'] == 'manual/rappn_user_checked'
    audits = conn.execute("SELECT action FROM grocery_mapping_audit_events WHERE product_item_id=? ORDER BY created_at", (item_id,)).fetchall()
    assert [row['action'] for row in audits] == ['manual_mapping_preview', 'mapping_inserted']
    run = run_grocery_optimization(conn, 'r1', selected_retailers=['Denner'], included_product_item_ids=[item_id])
    assert run['summary']['replaceable_product_count'] == 1
    assert run['summary']['estimated_savings_text'] == '0.40'


def test_manual_price_without_date_source_is_review_only_no_secure_saving() -> None:
    conn = db()
    receipt = seed_receipt(conn)
    item_id = receipt['items'][0]['product_item_id']
    preview = preview_manual_grocery_mapping(conn, product_item_id=item_id, target_retailer='Denner', target_product_name='Denner Milch 1L', target_product_url='https://www.denner.ch/milch', candidate_price_text='1.10', candidate_unit_price_text='1.10/l')
    assert preview['can_count_as_secure_saving'] is False
    mapping = confirm_manual_grocery_mapping(conn, preview)
    assert 'needs_review' in mapping['quality_flags_json']
    run = run_grocery_optimization(conn, 'r1', selected_retailers=['Denner'], included_product_item_ids=[item_id])
    assert run['summary']['replaceable_product_count'] == 0
    assert run['summary']['estimated_savings_text'] == '0.00'