import csv
import os
import sys
import tempfile
import pytest
import openpyxl
from unittest.mock import MagicMock

sys.path.insert(0, os.path.join(os.path.dirname(__file__), '..'))
from update_labor_hours import read_spreadsheet, backup_labor_hours, apply_updates, SCRIPT_DIR


def make_test_xlsx(rows):
    """Helper: write a minimal xlsx with header + given rows, return path."""
    wb = openpyxl.Workbook()
    ws = wb.active
    # Match real spreadsheet column layout: B=LocationID (col 2), I=Amount (col 9)
    ws.append(['ID', 'LocationID', 'LocationName', 'LogDate', 'ItemID',
               'Name', 'Rpt_Cat', 'Qty', 'Amount', 'Weekly LaborHours'])
    for row in rows:
        ws.append(row)
    tmp = tempfile.NamedTemporaryFile(suffix='.xlsx', delete=False)
    tmp.close()
    wb.save(tmp.name)
    return tmp.name


def test_read_spreadsheet_normal():
    path = make_test_xlsx([
        [None, 101, '101 - Tomball', None, None, None, None, None, 45.079, None],
        [None, 102, '102 - Fairfield', None, None, None, None, None, 48.045, None],
    ])
    result = read_spreadsheet(path)
    os.unlink(path)
    assert result == [(101, 316), (102, 336)]


def test_read_spreadsheet_zero_hours():
    path = make_test_xlsx([
        [None, 304, '304 - Ardmore', None, None, None, None, None, 0, None],
    ])
    result = read_spreadsheet(path)
    os.unlink(path)
    assert result == [(304, 0)]


def test_read_spreadsheet_rounding():
    # 10.1 * 7 = 70.7 → rounds to 71
    path = make_test_xlsx([
        [None, 200, '200 - Test', None, None, None, None, None, 10.1, None],
    ])
    result = read_spreadsheet(path)
    os.unlink(path)
    assert result == [(200, 71)]


def test_backup_creates_csv():
    mock_cursor = MagicMock()
    mock_cursor.fetchall.return_value = [
        (101, 'Tomball', 291),
        (102, 'Fairfield', 318),
    ]
    mock_conn = MagicMock()
    mock_conn.cursor.return_value = mock_cursor

    backup_path = backup_labor_hours(mock_conn)

    assert backup_path.exists()
    with open(backup_path, newline='') as f:
        reader = csv.reader(f)
        rows = list(reader)
    assert rows[0] == ['locationID', 'locationName', 'laborHours']
    assert rows[1] == ['101', 'Tomball', '291']
    assert rows[2] == ['102', 'Fairfield', '318']
    backup_path.unlink()  # clean up


def test_backup_raises_on_db_error():
    mock_conn = MagicMock()
    mock_conn.cursor.side_effect = Exception("DB connection lost")
    with pytest.raises(Exception, match="DB connection lost"):
        backup_labor_hours(mock_conn)


def _make_mock_conn(db_rows):
    """db_rows: list of (locationID, locationName, laborHours)"""
    mock_cursor = MagicMock()
    mock_cursor.fetchall.return_value = db_rows
    mock_conn = MagicMock()
    mock_conn.cursor.return_value = mock_cursor
    return mock_conn, mock_cursor


def test_apply_updates_dry_run_no_execute():
    conn, cursor = _make_mock_conn([
        (101, 'Tomball', 291),
        (102, 'Fairfield', 318),
    ])
    spreadsheet_data = [(101, 316), (102, 336)]
    summary = apply_updates(conn, spreadsheet_data, dry_run=True)
    cursor.execute.assert_called_once()  # only the SELECT, no UPDATEs
    assert summary == {'updated': 2, 'set_to_zero': 0, 'not_found': 0}


def test_apply_updates_live_executes_updates():
    conn, cursor = _make_mock_conn([
        (101, 'Tomball', 291),
    ])
    spreadsheet_data = [(101, 316)]
    summary = apply_updates(conn, spreadsheet_data, dry_run=False)
    # Should call SELECT once, then UPDATE once
    assert cursor.execute.call_count == 2
    update_call = cursor.execute.call_args_list[1]
    assert update_call.args[0] == "UPDATE locationInfo SET laborHours = %s WHERE locationID = %s"
    assert update_call.args[1] == (316, 101)
    conn.commit.assert_called_once()
    assert summary == {'updated': 1, 'set_to_zero': 0, 'not_found': 0}


def test_apply_updates_zero_hours():
    conn, cursor = _make_mock_conn([
        (304, 'Ardmore', None),
    ])
    spreadsheet_data = [(304, 0)]
    summary = apply_updates(conn, spreadsheet_data, dry_run=False)
    assert summary == {'updated': 0, 'set_to_zero': 1, 'not_found': 0}


def test_apply_updates_not_found():
    conn, cursor = _make_mock_conn([
        (101, 'Tomball', 291),
    ])
    spreadsheet_data = [(999, 316)]  # locationID 999 not in DB
    summary = apply_updates(conn, spreadsheet_data, dry_run=False)
    assert summary == {'updated': 0, 'set_to_zero': 0, 'not_found': 1}