import pyodbc


class SQLClient:
    def __init__(self):
        self.conn   = None
        self.cursor = None

    def connect(self, server: str, database: str,
                username: str, password: str) -> bool:
        try:
            conn_str = (
                f"DRIVER={{ODBC Driver 18 for SQL Server}};"
                f"SERVER={server};"
                f"DATABASE={database};"
                f"UID={username};"
                f"PWD={password};"
                f"TrustServerCertificate=yes;"
                f"Encrypt=optional;"
            )
            self.conn   = pyodbc.connect(conn_str, timeout=10)
            self.cursor = self.conn.cursor()
            print(f"[DB] 연결 성공: {server}/{database}")
            return True
        except Exception as e:
            print(f"[DB] 연결 실패: {e}")
            self.conn = None
            return False

    def disconnect(self):
        if self.cursor:
            self.cursor.close()
        if self.conn:
            self.conn.close()
        self.conn   = None
        self.cursor = None
        print("[DB] 연결 해제")

    def is_connected(self) -> bool:
        return self.conn is not None

    def get_reflector_list(self) -> list:
        """
        vi_AI_mt_Article 뷰에서 리플렉터 제품 목록 조회.
        반환: [{"article_id": ..., "article": ..., "buyer_article_no": ...}, ...]
        """
        if not self.is_connected():
            return []
        try:
            self.cursor.execute("""
                SELECT ArticleID, Article, BuyerArticleNo
                FROM vi_AI_mt_Article
                WHERE Article LIKE '%REF%'
                ORDER BY ArticleID
            """)
            rows = self.cursor.fetchall()
            return [
                {
                    "article_id":      row[0],
                    "article":         row[1],
                    "buyer_article_no": row[2],
                }
                for row in rows
            ]
        except Exception as e:
            print(f"[DB] 조회 실패: {e}")
            return []

    def save_inspection_result(self, article_id: str,
                               result: str, score: float) -> bool:
        """검사 결과 저장 — 테이블 확정 후 구현."""
        # TODO: 결과 저장 테이블 확정 후 쿼리 구현
        print(f"[DB] 검사 결과 저장: {article_id} {result} {score}")
        return True