import importlib.util
import json
import pathlib
import sqlite3
import tempfile
import unittest

MODULE = pathlib.Path(__file__).resolve().parents[3] / 'scripts/propertylab/prepare_research_import.py'
spec = importlib.util.spec_from_file_location('prepare_research_import', MODULE)
module = importlib.util.module_from_spec(spec)
spec.loader.exec_module(module)


class ResearchImportTest(unittest.TestCase):
    def fixture(self, path):
        conn = sqlite3.connect(path)
        conn.execute('CREATE TABLE records(dataset TEXT,record_id TEXT,payload TEXT,PRIMARY KEY(dataset,record_id))')
        def put(dataset, record_id, value):
            conn.execute('INSERT INTO records VALUES(?,?,?)', (dataset, record_id, json.dumps(value)))
        for name in ['ALPHA', 'BETA']:
            put('property_map', name, {'scheme': name, 'cls': 'Landed', 'lat': 3.1, 'lon': 101.6, 'median_rm': 100000, 'psf': 100, 'n': 2})
            put('history_name_checks', name, {'requested_scheme': name, 'returned_scheme': name if name == 'ALPHA' else 'WRONG', 'result': 'exact_name_match'})
            put('monthly_property_summaries', name + ':2026-01', {'scheme': name, 'month': '2026-01', 'n': 2, 'median_rm': 100000, 'psf': 0})
            for row in [1, 2]:
                put('recent_sales_distinct_pages', name + ':page:' + str(row), {'scheme': name, 'month': '2026-01', 'area_sqm': 92.90304, 'price_rm': 100000, 'psf_rm': 100, 'row_position': row, 'page_content_id': name + ':page', 'source_url': 'https://source.example/' + name})
        put('amenities', 'source-amenity-id', {'name': 'Park', 'kind': 'park', 'lat': 3.1, 'lon': 101.6})
        put('rail_stations', 'planned-station', {'name': 'Future rail', 'kind': 'LRT', 'status': 'planned', 'lat': 3.1, 'lon': 101.6})
        conn.commit()
        conn.close()

    def test_provenance_and_duplicate_looking_rows_survive(self):
        with tempfile.TemporaryDirectory() as temporary:
            root = pathlib.Path(temporary); source = root / 'archive.sqlite'; self.fixture(source)
            manifest = module.convert(source, root / 'out', '2026-09-07')
            rows = [json.loads(line) for line in (root / 'out/sale_observations.ndjson').read_text().splitlines()]
            self.assertEqual(4, len(rows))
            self.assertEqual(4, len({row['sale_row_id'] for row in rows}))
            self.assertEqual('ALPHA:page:1', rows[0]['source_ref']['sourceRecordId'])
            self.assertEqual('ALPHA:page', rows[0]['source_ref']['pageContentId'])
            self.assertEqual(1, rows[0]['source_ref']['rowPosition'])
            self.assertEqual('unknown', rows[0]['area_basis'])
            self.assertEqual('unverified_scheme_link', rows[2]['link_status'])
            self.assertEqual(1, manifest['historyLinksWithheld'])
            monthly = [json.loads(line) for line in (root / 'out/monthly_observations.ndjson').read_text().splitlines()]
            self.assertIsNone(monthly[0]['reported_psf'])
            self.assertEqual(100000, monthly[0]['price_rm'])

    def test_same_archive_and_date_produce_identical_immutable_snapshot(self):
        with tempfile.TemporaryDirectory() as temporary:
            root = pathlib.Path(temporary); source = root / 'archive.sqlite'; self.fixture(source)
            first = module.convert(source, root / 'one', '2026-09-07')
            second = module.convert(source, root / 'two', '2026-09-07')
            self.assertEqual(first, second)
            with self.assertRaises(ValueError): module.convert(source, root / 'one', '2026-09-07')

    def test_invalid_coordinate_fails_before_manifest_is_published(self):
        with tempfile.TemporaryDirectory() as temporary:
            root = pathlib.Path(temporary); source = root / 'archive.sqlite'; self.fixture(source)
            conn = sqlite3.connect(source)
            conn.execute("UPDATE records SET payload=? WHERE dataset='property_map' AND record_id='ALPHA'", (json.dumps({'scheme': 'ALPHA', 'cls': 'Landed', 'lat': 300, 'lon': 101}),))
            conn.commit(); conn.close()
            with self.assertRaises(ValueError): module.convert(source, root / 'out', '2026-09-07')
            self.assertFalse((root / 'out/manifest.json').exists())


if __name__ == '__main__':
    unittest.main()