"""
Ultra Description DB — Save/load ultra descriptions to/from PostgreSQL.
Schema: dashboard_canifa.ultra_descriptions
Pattern: Same as sql_agent/persistence.py (psycopg sync + pool).
"""

import json
import logging
from typing import Any

from common.pool_wrapper import get_pooled_connection_compat

logger = logging.getLogger(__name__)

SCHEMA = "dashboard_canifa"
TABLE = f"{SCHEMA}.ultra_descriptions"

__all__ = ["UltraDescriptionDB"]


class UltraDescriptionDB:
    """Manages ultra_descriptions table in dashboard_canifa schema."""

    _initialized = False

    @classmethod
    def ensure_table(cls) -> None:
        """Create ultra_descriptions table if it doesn't exist."""
        if cls._initialized:
            return

        from config import USE_LOCAL_SQLITE
        conn = None
        try:
            conn = get_pooled_connection_compat()
            cur = conn.cursor()

            if USE_LOCAL_SQLITE:
                # SQLite: execute each statement separately (SQLite only allows one at a time)
                cur.execute(f"""
                    CREATE TABLE IF NOT EXISTS {TABLE} (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        internal_ref_code TEXT NOT NULL,
                        magento_ref_code TEXT,
                        base_ref_code TEXT,
                        product_name TEXT,
                        product_image_url TEXT,
                        product_line TEXT,
                        description_data TEXT NOT NULL,
                        phase TEXT DEFAULT 'enriched',
                        status INTEGER DEFAULT 0,
                        clean_description TEXT DEFAULT '',
                        tags TEXT DEFAULT '[]',
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                    )
                """)
                cur.execute(f"CREATE INDEX IF NOT EXISTS idx_ud_internal ON {TABLE} (internal_ref_code)")
                cur.execute(f"CREATE INDEX IF NOT EXISTS idx_ud_magento ON {TABLE} (magento_ref_code)")
                cur.execute(f"CREATE INDEX IF NOT EXISTS idx_ud_base ON {TABLE} (base_ref_code)")
            else:
                # PostgreSQL: Original logic
                cur.execute(f"""
                    CREATE TABLE IF NOT EXISTS {TABLE} (
                        id SERIAL PRIMARY KEY,
                        internal_ref_code VARCHAR(50) NOT NULL,
                        product_name VARCHAR(500),
                        product_image_url TEXT,
                        product_line VARCHAR(200),
                        description_data JSONB NOT NULL,
                        phase VARCHAR(20) DEFAULT 'enriched',
                        status SMALLINT DEFAULT 0,
                        created_at TIMESTAMPTZ DEFAULT NOW(),
                        updated_at TIMESTAMPTZ DEFAULT NOW()
                    );
                    ALTER TABLE {TABLE} ADD COLUMN IF NOT EXISTS status SMALLINT DEFAULT 0;
                    ALTER TABLE {TABLE} ADD COLUMN IF NOT EXISTS clean_description TEXT DEFAULT '';
                    ALTER TABLE {TABLE} ADD COLUMN IF NOT EXISTS tags JSONB DEFAULT '[]'::jsonb;
                    ALTER TABLE {TABLE} ADD COLUMN IF NOT EXISTS magento_ref_code VARCHAR(100);
                    ALTER TABLE {TABLE} ADD COLUMN IF NOT EXISTS base_ref_code VARCHAR(50);
                """)

            cur.close()
            conn.commit()
            cls._initialized = True
            logger.info("✅ Table %s ready (Mock: %s)", TABLE, USE_LOCAL_SQLITE)
        except Exception as e:
            logger.error("Error creating ultra_descriptions table: %s", e)
        finally:
            if conn:
                conn.close()

    @classmethod
    def save(
        cls,
        internal_ref_code: str,
        product_name: str | None,
        product_image_url: str | None,
        product_line: str | None,
        description_data: dict,
        phase: str = "enriched",
        clean_description: str = "",
        magento_ref_code: str | None = None,
        base_ref_code: str | None = None,
    ) -> int | None:
        """Save or update an ultra description. Returns row id.
        If magento_ref_code is given, uses it as UNIQUE key (new v2 flow).
        Falls back to internal_ref_code for legacy records.
        """
        cls.ensure_table()
        conn = None
        # Derive defaults
        if not magento_ref_code:
            magento_ref_code = internal_ref_code  # loai 1: same code
        if not base_ref_code:
            base_ref_code = internal_ref_code
        try:
            conn = get_pooled_connection_compat()
            cur = conn.cursor()
            # Manual upsert: check if magento_ref_code exists first
            cur.execute(f"SELECT id FROM {TABLE} WHERE magento_ref_code = %s", (magento_ref_code,))
            existing = cur.fetchone()
            if existing:
                cur.execute(
                    f"""UPDATE {TABLE} SET
                        product_name = %s, product_image_url = %s, product_line = %s,
                        description_data = %s::jsonb, phase = %s, status = 0,
                        clean_description = %s, base_ref_code = %s, updated_at = NOW()
                    WHERE magento_ref_code = %s RETURNING id""",
                    (product_name, product_image_url, product_line,
                     json.dumps(description_data, ensure_ascii=False), phase,
                     clean_description, base_ref_code, magento_ref_code),
                )
            else:
                cur.execute(
                    f"""INSERT INTO {TABLE}
                        (internal_ref_code, magento_ref_code, base_ref_code,
                         product_name, product_image_url, product_line,
                         description_data, phase, status, clean_description, updated_at)
                    VALUES (%s, %s, %s, %s, %s, %s, %s::jsonb, %s, 0, %s, NOW())
                    RETURNING id""",
                    (internal_ref_code, magento_ref_code, base_ref_code,
                     product_name, product_image_url, product_line,
                     json.dumps(description_data, ensure_ascii=False), phase, clean_description),
                )
            row = cur.fetchone()
            row_id = row[0] if row else None
            cur.close() # Close cursor FIRST
            conn.commit()
            logger.info("💾 Saved ultra desc: %s / magento=%s (id=%s)", internal_ref_code, magento_ref_code, row_id)
            return row_id
        except Exception as e:
            if conn:
                conn.rollback()
            logger.error("Error saving ultra desc %s: %s", magento_ref_code, e)
            return None
        finally:
            if conn:
                conn.close()


    @classmethod
    def get_by_code(cls, internal_ref_code: str) -> dict | None:
        """Get a single ultra description by code (magento_ref_code first, fallback to internal_ref_code)."""
        cls.ensure_table()
        conn = None
        try:
            conn = get_pooled_connection_compat()
            cur = conn.cursor()
            # Try magento_ref_code first
            cur.execute(
                f"SELECT * FROM {TABLE} WHERE magento_ref_code = %s",
                (internal_ref_code,),
            )
            row = cur.fetchone()
            if not row:
                # Fallback to internal_ref_code (legacy)
                cur.execute(
                    f"SELECT * FROM {TABLE} WHERE internal_ref_code = %s LIMIT 1",
                    (internal_ref_code,),
                )
                row = cur.fetchone()
            if not row:
                cur.close()
                return None
            cols = [desc[0] for desc in cur.description]
            cur.close()
            return dict(zip(cols, row))
        except Exception as e:
            logger.error("Error getting ultra desc %s: %s", internal_ref_code, e)
            return None
        finally:
            if conn:
                conn.close()

    @classmethod
    def get_by_magento_code(cls, magento_ref_code: str) -> dict | None:
        """Get a single ultra description by magento_ref_code."""
        return cls.get_by_code(magento_ref_code)

    @classmethod
    def get_all_by_base_code(cls, base_ref_code: str) -> list[dict]:
        """Get all color-variants for a base product (internal_ref_code)."""
        cls.ensure_table()
        conn = None
        try:
            conn = get_pooled_connection_compat()
            cur = conn.cursor()
            cur.execute(
                f"SELECT * FROM {TABLE} WHERE base_ref_code = %s OR internal_ref_code = %s ORDER BY magento_ref_code",
                (base_ref_code, base_ref_code),
            )
            rows = cur.fetchall()
            cols = [desc[0] for desc in cur.description]
            cur.close()
            return [dict(zip(cols, r)) for r in rows]
        except Exception as e:
            logger.error("Error getting variants for %s: %s", base_ref_code, e)
            return []
        finally:
            if conn:
                conn.close()

    @classmethod
    def update_data(cls, code: str, description_data: dict, clean_description: str) -> bool:
        """Update description_data and clean_description from manual edits.
        Tries magento_ref_code first, falls back to internal_ref_code."""
        cls.ensure_table()
        conn = None
        try:
            conn = get_pooled_connection_compat()
            cur = conn.cursor()
            data_json = json.dumps(description_data, ensure_ascii=False)
            # Try magento_ref_code first
            cur.execute(
                f"UPDATE {TABLE} SET description_data = %s::jsonb, clean_description = %s, updated_at = NOW() WHERE magento_ref_code = %s",
                (data_json, clean_description, code),
            )
            if cur.rowcount == 0:
                # Fallback to internal_ref_code
                cur.execute(
                    f"UPDATE {TABLE} SET description_data = %s::jsonb, clean_description = %s, updated_at = NOW() WHERE internal_ref_code = %s",
                    (data_json, clean_description, code),
                )
            updated = cur.rowcount > 0
            conn.commit()           # ← was missing! without this UPDATE gets rolled back
            cur.close()
            if updated:
                logger.info("✅ Manually updated description for: %s", code)
            return updated
        except Exception as e:
            if conn:
                conn.rollback()
            logger.error("Error updating desc data for %s: %s", code, e)
            return False
        finally:
            if conn:
                conn.close()

    @classmethod
    def update_clean_text(cls, code: str, clean_description: str) -> bool:
        """Update ONLY the clean_description (manual text/HTML edits).
        Tries magento_ref_code first, falls back to internal_ref_code."""
        cls.ensure_table()
        conn = None
        try:
            conn = get_pooled_connection_compat()
            cur = conn.cursor()
            # Try magento_ref_code first
            cur.execute(
                f"UPDATE {TABLE} SET clean_description = %s, updated_at = NOW() WHERE magento_ref_code = %s",
                (clean_description, code),
            )
            if cur.rowcount == 0:
                # Fallback to internal_ref_code
                cur.execute(
                    f"UPDATE {TABLE} SET clean_description = %s, updated_at = NOW() WHERE internal_ref_code = %s",
                    (clean_description, code),
                )
            updated = cur.rowcount > 0
            conn.commit()           # ← was missing!
            cur.close()
            if updated:
                logger.info("✅ Manually updated clean_description text for: %s", code)
            return updated
        except Exception as e:
            if conn:
                conn.rollback()
            logger.error("Error updating clean text for %s: %s", code, e)
            return False
        finally:
            if conn:
                conn.close()

    @classmethod
    def update_tags(cls, internal_ref_code: str, tags: list[str]) -> bool:
        """Update the tags array for a specific product."""
        cls.ensure_table()
        conn = None
        try:
            conn = get_pooled_connection_compat()
            cur = conn.cursor()
            # Try magento_ref_code first (color variant), fallback to internal_ref_code
            cur.execute(
                f"UPDATE {TABLE} SET tags = %s::jsonb, updated_at = NOW() WHERE magento_ref_code = %s",
                (json.dumps(tags, ensure_ascii=False), internal_ref_code),
            )
            if cur.rowcount == 0:
                cur.execute(
                    f"UPDATE {TABLE} SET tags = %s::jsonb, updated_at = NOW() WHERE internal_ref_code = %s",
                    (json.dumps(tags, ensure_ascii=False), internal_ref_code),
                )
            updated = cur.rowcount > 0
            conn.commit()           # ← was missing! tags were silently discarded
            cur.close()
            if updated:
                logger.info("✅ Tags updated for: %s (%d tags)", internal_ref_code, len(tags))
            return updated
        except Exception as e:
            if conn:
                conn.rollback()
            logger.error("Error updating tags for %s: %s", internal_ref_code, e)
            return False
        finally:
            if conn:
                conn.close()

    @staticmethod
    def is_clean_description_ready(clean_description: Any) -> bool:
        """Check whether clean_description looks complete enough for downstream use."""
        if not isinstance(clean_description, str):
            return False
        text = clean_description.strip()
        if not text:
            return False
        if len(text) < 120:
            return False
        required_markers = [
            "==================================================",
            "── 📦 THÔNG TIN CƠ BẢN ──",
            "── 🧵 CHẤT LIỆU & BẢO QUẢN ──",
            "── 🎯 ĐỐI TƯỢNG ──",
            "── 📅 DỊP MẶC & STYLING ──",
            "── ❓ FAQ ──",
            "── 🛒 HỖ TRỢ BÁN HÀNG ──",
        ]
        return all(marker in text for marker in required_markers)

    @classmethod
    def get_all_codes_with_status(cls) -> dict[str, int]:
        """Get all codes with their status: {code: status}."""
        cls.ensure_table()
        conn = None
        try:
            conn = get_pooled_connection_compat()
            cur = conn.cursor()
            cur.execute(f"SELECT COALESCE(magento_ref_code, internal_ref_code), COALESCE(status, 0) FROM {TABLE}")
            result = {r[0]: r[1] for r in cur.fetchall()}
            cur.close()
            return result
        except Exception as e:
            logger.error("Error getting all ultra desc codes: %s", e)
            return {}
        finally:
            if conn:
                conn.close()

    @classmethod
    def get_codes_by_tag(cls, tag: str) -> set[str]:
        """Get set of codes that have a specific tag."""
        cls.ensure_table()
        conn = None
        try:
            conn = get_pooled_connection_compat()
            cur = conn.cursor()
            # Match strictly inside the JSON array
            cur.execute(
                f"SELECT COALESCE(magento_ref_code, internal_ref_code) FROM {TABLE} WHERE tags::jsonb @> %s::jsonb",
                (json.dumps([tag]),)
            )
            codes = {r[0] for r in cur.fetchall()}
            cur.close()
            return codes
        except Exception as e:
            logger.error("Error getting codes by tag %s: %s", tag, e)
            return set()
        finally:
            if conn:
                conn.close()

    @classmethod
    def get_stats(cls) -> dict:
        """Get count of stored descriptions."""
        cls.ensure_table()
        conn = None
        try:
            conn = get_pooled_connection_compat()
            cur = conn.cursor()
            cur.execute(f"""
                SELECT
                    COUNT(*) AS total,
                    COUNT(CASE WHEN status = 1 THEN 1 END) AS approved,
                    COUNT(CASE WHEN status = 0 THEN 1 END) AS pending,
                    COUNT(CASE WHEN clean_description IS NOT NULL AND TRIM(clean_description) != '' THEN 1 END) AS has_clean_desc,
                    COUNT(CASE WHEN phase = 'enriched' THEN 1 END) AS enriched,
                    COUNT(CASE WHEN phase = 'raw' THEN 1 END) AS raw_only,
                    MIN(created_at) AS first_created,
                    MAX(updated_at) AS last_updated
                FROM {TABLE}
            """)
            row = cur.fetchone()
            cur.close()
            if not row:
                return {}
            return {
                "total": row[0],
                "approved": row[1],
                "pending": row[2],
                "has_clean_desc": row[3],
                "enriched": row[4],
                "raw_only": row[5],
                "first_created": row[6],
                "last_updated": row[7],
            }
        except Exception as e:
            logger.error("Error getting ultra desc stats: %s", e)
            return {}
        finally:
            if conn:
                conn.close()

    @classmethod
    def delete_by_code(cls, internal_ref_code: str) -> bool:
        """Delete a description by code."""
        cls.ensure_table()
        conn = None
        try:
            conn = get_pooled_connection_compat()
            cur = conn.cursor()
            cur.execute(
                f"DELETE FROM {TABLE} WHERE internal_ref_code = %s",
                (internal_ref_code,),
            )
            deleted = cur.rowcount > 0
            cur.close()
            return deleted
        except Exception as e:
            logger.error("Error deleting ultra desc %s: %s", internal_ref_code, e)
            return False
        finally:
            if conn:
                conn.close()


    @classmethod
    def set_status(cls, internal_ref_code: str, status: int, clean_description: str = "") -> bool:
        """Set status: 0 = pending, 1 = approved. Optionally save clean_description."""
        cls.ensure_table()
        conn = None
        try:
            conn = get_pooled_connection_compat()
            cur = conn.cursor()
            if clean_description:
                cur.execute(
                    f"UPDATE {TABLE} SET status = %s, clean_description = %s, updated_at = NOW() WHERE internal_ref_code = %s",
                    (status, clean_description, internal_ref_code),
                )
            else:
                cur.execute(
                    f"UPDATE {TABLE} SET status = %s, updated_at = NOW() WHERE internal_ref_code = %s",
                    (status, internal_ref_code),
                )
            updated = cur.rowcount > 0
            cur.close()
            if updated:
                logger.info("✅ Status updated: %s → %s", internal_ref_code, status)
            return updated
        except Exception as e:
            logger.error("Error updating status %s: %s", internal_ref_code, e)
            return False
        finally:
            if conn:
                conn.close()

    @classmethod
    def get_approved_codes(cls) -> set[str]:
        """Get codes with status = 1 (approved)."""
        cls.ensure_table()
        conn = None
        try:
            conn = get_pooled_connection_compat()
            cur = conn.cursor()
            cur.execute(f"SELECT internal_ref_code FROM {TABLE} WHERE status = 1")
            codes = {r[0] for r in cur.fetchall()}
            cur.close()
            return codes
        except Exception as e:
            logger.error("Error getting approved codes: %s", e)
            return set()
        finally:
            if conn:
                conn.close()

    @classmethod
    def approve_all_pending(cls, render_fn=None) -> int:
        """Bulk approve all pending (status=0) → approved (status=1).
        If render_fn provided, also generates clean_description for each row."""
        cls.ensure_table()
        conn = None
        try:
            conn = get_pooled_connection_compat()
            cur = conn.cursor()

            if render_fn:
                # Fetch pending rows, preserve any existing manual clean_description,
                # otherwise render it from description_data.
                cur.execute(
                    f"SELECT internal_ref_code, product_name, description_data, clean_description FROM {TABLE} WHERE status = 0"
                )
                rows = cur.fetchall()
                count = 0
                for code, name, desc_data, clean_description in rows:
                    clean = (clean_description or "").strip()
                    if not clean:
                        if isinstance(desc_data, str):
                            import json as _json
                            try:
                                desc_data = _json.loads(desc_data)
                            except Exception:
                                desc_data = {}
                        clean = render_fn(desc_data, name or "")
                    cur.execute(
                        f"UPDATE {TABLE} SET status = 1, clean_description = %s, updated_at = NOW() WHERE internal_ref_code = %s",
                        (clean, code),
                    )
                    count += 1
            else:
                cur.execute(f"UPDATE {TABLE} SET status = 1, updated_at = NOW() WHERE status = 0")
                count = cur.rowcount

            cur.close()
            logger.info("✅ Bulk approved %d descriptions", count)
            return count
        except Exception as e:
            logger.error("Error bulk approving: %s", e)
            return 0
        finally:
            if conn:
                conn.close()

    @classmethod
    def count_missing_clean_descriptions(cls) -> int:
        """Count rows where clean_description is empty or not formatted as expected."""
        cls.ensure_table()
        conn = None
        try:
            conn = get_pooled_connection_compat()
            cur = conn.cursor()
            cur.execute(f"SELECT clean_description FROM {TABLE}")
            rows = cur.fetchall()
            cur.close()
            return sum(0 if cls.is_clean_description_ready(row[0]) else 1 for row in rows)
        except Exception as e:
            logger.error(f"Error counting missing clean descriptions: {e}")
            return 0
        finally:
            if conn:
                conn.close()

    @classmethod
    def count_missing_tags(cls) -> int:
        """Count rows where tags is NULL or empty array."""
        cls.ensure_table()
        conn = None
        try:
            conn = get_pooled_connection_compat()
            cur = conn.cursor()
            cur.execute(f"SELECT COUNT(*) FROM {TABLE} WHERE tags IS NULL")
            count = cur.fetchone()[0]
            cur.close()
            return count
        except Exception as e:
            logger.error(f"Error counting missing tags: {e}")
            return 0
        finally:
            if conn:
                conn.close()

    @classmethod
    def backfill_missing_clean_descriptions(cls, render_fn) -> int:
        """Render and save clean_description for rows still missing/invalid."""
        cls.ensure_table()
        conn = None
        try:
            conn = get_pooled_connection_compat()
            cur = conn.cursor()
            cur.execute(
                f"SELECT internal_ref_code, product_name, description_data, clean_description FROM {TABLE}"
            )
            rows = cur.fetchall()
            updated_count = 0
            for code, name, desc_data, clean_description in rows:
                if cls.is_clean_description_ready(clean_description):
                    continue
                if isinstance(desc_data, str):
                    try:
                        desc_data = json.loads(desc_data)
                    except Exception:
                        desc_data = {}
                if not isinstance(desc_data, dict) or not desc_data:
                    continue
                rendered = render_fn(desc_data, name or "")
                if not cls.is_clean_description_ready(rendered):
                    continue
                cur.execute(
                    f"UPDATE {TABLE} SET clean_description = %s, updated_at = NOW() WHERE internal_ref_code = %s",
                    (rendered, code),
                )
                updated_count += 1
            cur.close()
            logger.info("✅ Backfilled clean_description for %d rows", updated_count)
            return updated_count
        except Exception as e:
            logger.error("Error backfilling clean descriptions: %s", e)
            return 0
        finally:
            if conn:
                conn.close()


# Removed auto-init on import to prevent blocking thread when DB is slow
# try:
#     UltraDescriptionDB.ensure_table()
# except Exception:
#     pass


# ═══════════════════════════════════════════════════════════════
# DescFieldConfig — Dynamic field management for AI descriptions
# ═══════════════════════════════════════════════════════════════
FIELD_TABLE = f"{SCHEMA}.desc_field_config"

# Default 28 fields (seeded on first run)
_DEFAULT_FIELDS = [
    ("ten_san_pham", "Tên sản phẩm", "Dùng tên thật từ database", 1),
    ("mo_ta_chinh", "Mô tả chính", "3 câu: form dáng + thiết kế + tác dụng lên cơ thể", 2),
    ("tagline", "Tagline", "1 câu slogan ngắn, gợi cảm xúc", 3),
    ("hook_quang_cao", "Hook quảng cáo", "1 câu marketing hook thu hút click", 4),
    ("chat_lieu", "Chất liệu", "NẾU có mô tả gốc Magento → trích xuất. Nếu không → ghi Không xác định", 5),
    ("tinh_nang_vai", "Tính năng vải", "Co giãn, kháng khuẩn, thấm hút... NẾU có Magento → trích xuất", 6),
    ("huong_dan_bao_quan", "Hướng dẫn bảo quản", "NẾU có mô tả gốc → trích xuất. Nếu không → chuẩn theo loại vải", 7),
    ("phong_cach", "Phong cách", "Casual, minimalist, streetwear...", 8),
    ("gioi_tinh", "Giới tính", "Nam / Nữ / Unisex", 9),
    ("do_tuoi", "Độ tuổi", "Phạm vi tuổi phù hợp", 10),
    ("mua", "Mùa", "Mùa phù hợp để mặc", 11),
    ("dip_mac", "Dịp mặc", "4+ dịp cụ thể, ngăn dấu ·", 12),
    ("phoi_do", "Phối đồ", "3 combo cụ thể với format: item1 + item2 → mô tả", 13),
    ("nguyen_tac_phoi_do", "Nguyên tắc phối đồ", "Giải thích RÕ lý do phối đồ, tỷ lệ cơ thể, sự phù hợp", 14),
    ("tranh_phoi_cung", "Tránh phối cùng", "Negative constraints cảnh báo chatbot", 15),
    ("layer", "Layer", "Cách layer khi thời tiết thay đổi", 16),
    ("cross_sell", "Cross-sell", "2-3 dòng SP Canifa khác nên mua kèm", 17),
    ("loi_song", "Lối sống", "Mô tả lifestyle khách hàng mục tiêu", 18),
    ("tinh_cach", "Tính cách", "Tính cách phù hợp với SP", 19),
    ("ly_do_mua", "Lý do mua", "Lý do thuyết phục khách mua", 20),
    ("luu_y_size", "Lưu ý size", "Hướng dẫn chọn size phù hợp", 21),
    ("tags", "Tags", "Từ khóa SEO ngắn, phân tách bằng dấu phẩy", 22),
    ("faq_1_q", "FAQ 1 - Câu hỏi", "Câu hỏi về form dáng/fit", 23),
    ("faq_1_a", "FAQ 1 - Trả lời", "Trả lời chi tiết", 24),
    ("faq_2_q", "FAQ 2 - Câu hỏi", "Câu hỏi về size", 25),
    ("faq_2_a", "FAQ 2 - Trả lời", "Trả lời chi tiết", 26),
    ("faq_3_q", "FAQ 3 - Câu hỏi", "Câu hỏi về mix match", 27),
    ("faq_3_a", "FAQ 3 - Trả lời", "Trả lời chi tiết", 28),
]


class DescFieldConfig:
    """Manages desc_field_config table — dynamic field schema for AI descriptions."""

    _initialized = False

    @classmethod
    def ensure_table(cls) -> None:
        if cls._initialized:
            return
        conn = None
        try:
            conn = get_pooled_connection_compat()
            cur = conn.cursor()
            cur.execute(f"""
                CREATE TABLE IF NOT EXISTS {FIELD_TABLE} (
                    field_key VARCHAR(100) PRIMARY KEY,
                    field_label VARCHAR(200) NOT NULL,
                    field_instruction TEXT NOT NULL DEFAULT '',
                    is_active BOOLEAN NOT NULL DEFAULT TRUE,
                    sort_order INT NOT NULL DEFAULT 0,
                    created_at TIMESTAMPTZ DEFAULT NOW(),
                    updated_at TIMESTAMPTZ DEFAULT NOW()
                );
            """)
            # Seed defaults if table is empty
            cur.execute(f"SELECT COUNT(*) FROM {FIELD_TABLE}")
            count = cur.fetchone()[0]
            if count == 0:
                for key, label, instruction, sort in _DEFAULT_FIELDS:
                    cur.execute(
                        f"""INSERT INTO {FIELD_TABLE} (field_key, field_label, field_instruction, is_active, sort_order)
                            VALUES (%s, %s, %s, TRUE, %s) ON CONFLICT DO NOTHING""",
                        (key, label, instruction, sort),
                    )
                logger.info("🌱 Seeded %d default fields into %s", len(_DEFAULT_FIELDS), FIELD_TABLE)
            cur.close()
            cls._initialized = True
            logger.info("✅ Table %s ready", FIELD_TABLE)
        except Exception as e:
            logger.error("Error creating desc_field_config table: %s", e)
        finally:
            if conn:
                conn.close()

    @classmethod
    def get_active_fields(cls) -> list[dict]:
        """Get active fields ordered by sort_order (used by prompt builder)."""
        cls.ensure_table()
        conn = None
        try:
            conn = get_pooled_connection_compat()
            cur = conn.cursor()
            cur.execute(f"""
                SELECT field_key, field_label, field_instruction, sort_order
                FROM {FIELD_TABLE}
                WHERE is_active = TRUE
                ORDER BY sort_order
            """)
            rows = cur.fetchall()
            cur.close()
            return [
                {"field_key": r[0], "field_label": r[1], "field_instruction": r[2], "sort_order": r[3]}
                for r in rows
            ]
        except Exception as e:
            logger.error("Error getting active fields: %s", e)
            return []
        finally:
            if conn:
                conn.close()

    @classmethod
    def get_all_fields(cls) -> list[dict]:
        """Get ALL fields (active + inactive) for admin UI."""
        cls.ensure_table()
        conn = None
        try:
            conn = get_pooled_connection_compat()
            cur = conn.cursor()
            cur.execute(f"""
                SELECT field_key, field_label, field_instruction, is_active, sort_order
                FROM {FIELD_TABLE}
                ORDER BY sort_order
            """)
            rows = cur.fetchall()
            cur.close()
            return [
                {"field_key": r[0], "field_label": r[1], "field_instruction": r[2],
                 "is_active": r[3], "sort_order": r[4]}
                for r in rows
            ]
        except Exception as e:
            logger.error("Error getting all fields: %s", e)
            return []
        finally:
            if conn:
                conn.close()

    @classmethod
    def add_field(cls, field_key: str, field_label: str, field_instruction: str = "", sort_order: int = 99) -> bool:
        cls.ensure_table()
        conn = None
        try:
            conn = get_pooled_connection_compat()
            cur = conn.cursor()
            cur.execute(
                f"""INSERT INTO {FIELD_TABLE} (field_key, field_label, field_instruction, is_active, sort_order)
                    VALUES (%s, %s, %s, TRUE, %s)""",
                (field_key, field_label, field_instruction, sort_order),
            )
            cur.close()
            logger.info("➕ Added field: %s", field_key)
            return True
        except Exception as e:
            logger.error("Error adding field %s: %s", field_key, e)
            return False
        finally:
            if conn:
                conn.close()

    @classmethod
    def update_field(cls, field_key: str, field_label: str | None = None,
                     field_instruction: str | None = None, is_active: bool | None = None,
                     sort_order: int | None = None) -> bool:
        cls.ensure_table()
        sets = []
        params = []
        if field_label is not None:
            sets.append("field_label = %s")
            params.append(field_label)
        if field_instruction is not None:
            sets.append("field_instruction = %s")
            params.append(field_instruction)
        if is_active is not None:
            sets.append("is_active = %s")
            params.append(is_active)
        if sort_order is not None:
            sets.append("sort_order = %s")
            params.append(sort_order)
        if not sets:
            return False
        sets.append("updated_at = NOW()")
        params.append(field_key)

        conn = None
        try:
            conn = get_pooled_connection_compat()
            cur = conn.cursor()
            cur.execute(
                f"UPDATE {FIELD_TABLE} SET {', '.join(sets)} WHERE field_key = %s",
                params,
            )
            updated = cur.rowcount > 0
            cur.close()
            if updated:
                logger.info("✏️ Updated field: %s", field_key)
            return updated
        except Exception as e:
            logger.error("Error updating field %s: %s", field_key, e)
            return False
        finally:
            if conn:
                conn.close()

    @classmethod
    def delete_field(cls, field_key: str) -> bool:
        cls.ensure_table()
        conn = None
        try:
            conn = get_pooled_connection_compat()
            cur = conn.cursor()
            cur.execute(f"DELETE FROM {FIELD_TABLE} WHERE field_key = %s", (field_key,))
            deleted = cur.rowcount > 0
            cur.close()
            if deleted:
                logger.info("🗑️ Deleted field: %s", field_key)
            return deleted
        except Exception as e:
            logger.error("Error deleting field %s: %s", field_key, e)
            return False
        finally:
            if conn:
                conn.close()


# Removed auto-init on import to prevent blocking thread when DB is slow
# try:
#     DescFieldConfig.ensure_table()
# except Exception:
#     pass
