import datetime
import io

import pytest

from backend.actions.ccpa_upload import (
    parse_and_validate_ccpa_csv,
    upload_ccpa_emails_from_csv,
)
from backend.dtos import CcpaUploadResult


def test_parse_valid_csv():
    csv_content = (
        b"email,requested_date\ntest@gmail.com,01/15/24\nuser@outlook.com,02/20/24"
    )
    rows, errors = parse_and_validate_ccpa_csv(io.BytesIO(csv_content))
    assert len(rows) == 2
    assert errors == []
    assert rows[0].email == "test@gmail.com"
    assert rows[0].requested_date == datetime.date(2024, 1, 15)
    assert rows[1].email == "user@outlook.com"
    assert rows[1].requested_date == datetime.date(2024, 2, 20)


def test_parse_csv_case_insensitive_headers():
    csv_content = b"EMAIL,REQUESTED_DATE\ntest@gmail.com,01/15/24"
    rows, errors = parse_and_validate_ccpa_csv(io.BytesIO(csv_content))
    assert len(rows) == 1
    assert errors == []


def test_parse_csv_mm_dd_yy_format():
    csv_content = b"email,requested_date\ntest@gmail.com,01/15/24"
    rows, errors = parse_and_validate_ccpa_csv(io.BytesIO(csv_content))
    assert len(rows) == 1
    assert errors == []
    assert rows[0].requested_date == datetime.date(2024, 1, 15)


def test_parse_csv_emails_lowercased():
    csv_content = b"email,requested_date\nTEST@GMAIL.COM,01/15/24"
    rows, errors = parse_and_validate_ccpa_csv(io.BytesIO(csv_content))
    assert len(rows) == 1
    assert rows[0].email == "test@gmail.com"


def test_parse_csv_strips_whitespace():
    csv_content = b"email,requested_date\n  test@gmail.com  ,  01/15/24  "
    rows, errors = parse_and_validate_ccpa_csv(io.BytesIO(csv_content))
    assert len(rows) == 1
    assert errors == []
    assert rows[0].email == "test@gmail.com"


def test_parse_csv_strips_bom():
    csv_content = b"\xef\xbb\xbfemail,requested_date\ntest@gmail.com,01/15/24"
    rows, errors = parse_and_validate_ccpa_csv(io.BytesIO(csv_content))
    assert len(rows) == 1
    assert errors == []


def test_parse_csv_empty_file():
    rows, errors = parse_and_validate_ccpa_csv(io.BytesIO(b""))
    assert rows == []
    assert len(errors) == 1
    assert "empty" in errors[0].lower()


def test_parse_csv_header_only():
    csv_content = b"email,requested_date"
    rows, errors = parse_and_validate_ccpa_csv(io.BytesIO(csv_content))
    assert rows == []
    assert len(errors) == 1
    assert "header row" in errors[0].lower()


def test_parse_csv_missing_email_column():
    csv_content = b"name,requested_date\nJohn,01/15/24"
    rows, errors = parse_and_validate_ccpa_csv(io.BytesIO(csv_content))
    assert rows == []
    assert len(errors) == 1
    assert "email" in errors[0].lower()


def test_parse_csv_missing_date_column():
    csv_content = b"email,name\ntest@gmail.com,John"
    rows, errors = parse_and_validate_ccpa_csv(io.BytesIO(csv_content))
    assert rows == []
    assert len(errors) == 1
    assert "requested_date" in errors[0].lower()


def test_parse_csv_invalid_email():
    csv_content = b"email,requested_date\nnot-an-email,01/15/24"
    rows, errors = parse_and_validate_ccpa_csv(io.BytesIO(csv_content))
    assert rows == []
    assert len(errors) == 1
    assert "invalid email" in errors[0].lower()


def test_parse_csv_empty_email():
    csv_content = b"email,requested_date\n,01/15/24"
    rows, errors = parse_and_validate_ccpa_csv(io.BytesIO(csv_content))
    assert rows == []
    assert len(errors) == 1
    assert "email is empty" in errors[0].lower()


def test_parse_csv_empty_date():
    csv_content = b"email,requested_date\ntest@gmail.com,"
    rows, errors = parse_and_validate_ccpa_csv(io.BytesIO(csv_content))
    assert rows == []
    assert len(errors) == 1
    assert "requested_date is empty" in errors[0].lower()


def test_parse_csv_invalid_date():
    csv_content = b"email,requested_date\ntest@gmail.com,not-a-date"
    rows, errors = parse_and_validate_ccpa_csv(io.BytesIO(csv_content))
    assert rows == []
    assert len(errors) == 1
    assert "invalid date" in errors[0].lower()


def test_parse_csv_multiple_errors():
    csv_content = (
        b"email,requested_date\n"
        b"bad-email,01/15/24\n"
        b"test@gmail.com,not-a-date\n"
        b",01/15/24"
    )
    rows, errors = parse_and_validate_ccpa_csv(io.BytesIO(csv_content))
    assert rows == []
    assert len(errors) == 3


def test_parse_csv_skips_blank_rows():
    csv_content = (
        b"email,requested_date\ntest@gmail.com,01/15/24\n\n\nuser@outlook.com,02/20/24"
    )
    rows, errors = parse_and_validate_ccpa_csv(io.BytesIO(csv_content))
    assert len(rows) == 2
    assert errors == []


def test_parse_csv_rejects_yyyy_mm_dd_format():
    csv_content = b"email,requested_date\ntest@gmail.com,2024-01-15"
    rows, errors = parse_and_validate_ccpa_csv(io.BytesIO(csv_content))
    assert rows == []
    assert len(errors) == 1
    assert "invalid date" in errors[0].lower()
    assert "MM/DD/YY" in errors[0]


def test_parse_csv_rejects_mm_dd_yyyy_format():
    csv_content = b"email,requested_date\ntest@gmail.com,01/15/2024"
    rows, errors = parse_and_validate_ccpa_csv(io.BytesIO(csv_content))
    assert rows == []
    assert len(errors) == 1
    assert "invalid date" in errors[0].lower()


def test_parse_csv_deduplicates_emails_keeps_oldest_date():
    csv_content = (
        b"email,requested_date\n"
        b"test@gmail.com,06/15/24\n"
        b"test@gmail.com,01/10/24\n"
        b"test@gmail.com,03/20/24"
    )
    rows, errors = parse_and_validate_ccpa_csv(io.BytesIO(csv_content))
    assert len(rows) == 1
    assert errors == []
    assert rows[0].email == "test@gmail.com"
    assert rows[0].requested_date == datetime.date(2024, 1, 10)


def test_parse_csv_deduplicates_multiple_emails():
    csv_content = (
        b"email,requested_date\n"
        b"alice@gmail.com,06/15/24\n"
        b"bob@gmail.com,03/01/24\n"
        b"alice@gmail.com,01/10/24\n"
        b"bob@gmail.com,07/20/24"
    )
    rows, errors = parse_and_validate_ccpa_csv(io.BytesIO(csv_content))
    assert len(rows) == 2
    assert errors == []
    rows_by_email = {r.email: r for r in rows}
    assert rows_by_email["alice@gmail.com"].requested_date == datetime.date(2024, 1, 10)
    assert rows_by_email["bob@gmail.com"].requested_date == datetime.date(2024, 3, 1)


def test_parse_csv_extra_columns_ignored():
    csv_content = b"email,requested_date,extra\ntest@gmail.com,01/15/24,foo"
    rows, errors = parse_and_validate_ccpa_csv(io.BytesIO(csv_content))
    assert len(rows) == 1
    assert errors == []


def test_upload_all_or_nothing_rejects_on_errors(session):
    csv_content = b"email,requested_date\nvalid@gmail.com,01/15/24\nbad-email,01/15/24"
    result = upload_ccpa_emails_from_csv(
        session, io.BytesIO(csv_content), "TEST_USER", "QA"
    )
    assert result.inserted_count == 0
    assert result.updated_count == 0
    assert result.skipped_count == 0
    assert len(result.errors) == 1


def test_upload_empty_file(session):
    result = upload_ccpa_emails_from_csv(session, io.BytesIO(b""), "TEST_USER", "QA")
    assert result.inserted_count == 0
    assert result.updated_count == 0
    assert result.skipped_count == 0
    assert len(result.errors) == 1


@pytest.mark.skip(reason="session.sql() not supported in local testing mode")
def test_upload_valid_csv_inserts(session):
    session.sql(
        """
        CREATE OR REPLACE TEMPORARY TABLE FANSIFTER_APP_REPORTING.QA.CCPA_FAN_LIST (
            ID NUMBER(38,0) NOT NULL AUTOINCREMENT,
            EMAIL VARCHAR(255) NOT NULL,
            REQUESTED_AT TIMESTAMP_NTZ(9),
            CREATED_AT TIMESTAMP_NTZ(9) DEFAULT CURRENT_TIMESTAMP(),
            UPDATED_AT TIMESTAMP_NTZ(9) DEFAULT CURRENT_TIMESTAMP(),
            CREATED_BY VARCHAR(255) NOT NULL,
            UPDATED_BY VARCHAR(255) NOT NULL,
            PRIMARY KEY (ID),
            UNIQUE (EMAIL)
        )
        """
    ).collect()

    csv_content = (
        b"email,requested_date\ntest1@gmail.com,01/15/24\ntest2@outlook.com,02/20/24"
    )
    result = upload_ccpa_emails_from_csv(
        session, io.BytesIO(csv_content), "TEST_USER", "QA"
    )

    assert isinstance(result, CcpaUploadResult)
    assert result.inserted_count == 2
    assert result.updated_count == 0
    assert result.skipped_count == 0
    assert result.errors == []


@pytest.mark.skip(reason="session.sql() not supported in local testing mode")
def test_upload_upsert_existing(session):
    session.sql(
        """
        CREATE OR REPLACE TEMPORARY TABLE FANSIFTER_APP_REPORTING.QA.CCPA_FAN_LIST (
            ID NUMBER(38,0) NOT NULL AUTOINCREMENT,
            EMAIL VARCHAR(255) NOT NULL,
            REQUESTED_AT TIMESTAMP_NTZ(9),
            CREATED_AT TIMESTAMP_NTZ(9) DEFAULT CURRENT_TIMESTAMP(),
            UPDATED_AT TIMESTAMP_NTZ(9) DEFAULT CURRENT_TIMESTAMP(),
            CREATED_BY VARCHAR(255) NOT NULL,
            UPDATED_BY VARCHAR(255) NOT NULL,
            PRIMARY KEY (ID),
            UNIQUE (EMAIL)
        )
        """
    ).collect()

    session.sql(
        """
        INSERT INTO FANSIFTER_APP_REPORTING.QA.CCPA_FAN_LIST (EMAIL, REQUESTED_AT, CREATED_BY, UPDATED_BY)
        VALUES ('existing@gmail.com', '2024-01-01', 'ORIGINAL_USER', 'ORIGINAL_USER')
        """
    ).collect()

    csv_content = (
        b"email,requested_date\nexisting@gmail.com,06/15/24\nnew@outlook.com,07/20/24"
    )
    result = upload_ccpa_emails_from_csv(
        session, io.BytesIO(csv_content), "NEW_USER", "QA"
    )

    assert result.inserted_count == 1
    assert result.updated_count == 1
    assert result.skipped_count == 0
    assert result.errors == []