from datetime import date

import pytest
from sqlalchemy import create_engine
from sqlalchemy.orm import Session

from app.api.routes.admin_imports import COL_COURSE_NAME, COL_STAGE_NAME, build_import_template_workbook
from app.db.base import Base
from app.models import Certificate, Learner, ProjectCourse
from app.services.certificate_issuance import CertificateIssueData, DuplicateCertificate, issue_certificate
from app.services.learner_identity import LearnerIdentityConflict, resolve_learner


@pytest.fixture
def db() -> Session:
    engine = create_engine("sqlite+pysqlite:///:memory:")
    Base.metadata.create_all(engine)
    with Session(engine) as session:
        yield session


def add_project(db: Session) -> None:
    db.add(
        ProjectCourse(
            code="DBY",
            name="大本营",
            default_certificate_name="培训结业证书",
            default_course_name="智慧赋能疗愈师",
            default_stage_name="初级",
            default_template_code="classic",
        )
    )
    db.flush()


def test_practice_template_has_only_its_required_certificate_columns():
    sheet = build_import_template_workbook("practice-camp")["证书导入模板"]
    headers = [cell.value for cell in sheet[1]]

    assert headers == ["姓名", "手机号", "项目代码", "课程开始日期", "课程结束日期", "发证日期"]
    assert COL_COURSE_NAME not in headers
    assert COL_STAGE_NAME not in headers


def test_learner_identity_reuses_exact_name_and_phone_but_rejects_name_conflict(db: Session):
    learner, created = resolve_learner(db, "张三", "138-0000-0000", source="test")
    reused, reused_created = resolve_learner(db, "张三", "+86 13800000000", source="test")

    assert created is True
    assert reused_created is False
    assert reused.id == learner.id
    with pytest.raises(LearnerIdentityConflict, match="姓名.*不一致"):
        resolve_learner(db, "李四", "13800000000", source="test")


def test_one_learner_can_hold_multiple_template_certificates(db: Session):
    add_project(db)
    common = dict(
        learner_name="张三",
        learner_phone="13800000000",
        project_code="DBY",
        course_start_date=date(2026, 3, 1),
        course_end_date=date(2026, 8, 31),
    )
    classic, _, classic_created = issue_certificate(
        db,
        CertificateIssueData(
            **common,
            template_code="classic",
            issue_date=date(2026, 9, 5),
            course_name="智慧赋能疗愈师",
            stage_name="初级",
        ),
        source="test",
    )
    practice, _, practice_created = issue_certificate(
        db,
        CertificateIssueData(
            **common,
            template_code="practice-camp",
            issue_date=date(2026, 9, 6),
        ),
        source="test",
    )

    assert classic_created is True
    assert practice_created is False
    assert classic.learner_id == practice.learner_id
    assert db.query(Learner).count() == 1
    assert db.query(Certificate).count() == 2
    assert practice.course_name is None
    assert practice.stage_name is None


def test_duplicate_certificate_is_rejected(db: Session):
    add_project(db)
    data = CertificateIssueData(
        learner_name="张三",
        learner_phone="13800000000",
        project_code="DBY",
        template_code="practice-camp",
        course_start_date=date(2026, 3, 1),
        course_end_date=date(2026, 8, 31),
        issue_date=date(2026, 9, 5),
    )
    issue_certificate(db, data, source="test")

    with pytest.raises(DuplicateCertificate):
        issue_certificate(db, data, source="test")