import sqlite3
import logging
from datetime import datetime
from pathlib import Path
from bot.config import DB_PATH

logger = logging.getLogger('db')


def get_connection():
    """Получение соединения с БД"""
    # Создаём директорию если не существует
    DB_PATH.parent.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn


def init_db():
    """Инициализация базы данных"""
    try:
        conn = get_connection()
        cursor = conn.cursor()

        # Таблица целевых групп
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS target_groups (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                group_id INTEGER UNIQUE NOT NULL,
                group_name TEXT,
                group_username TEXT,
                group_type TEXT DEFAULT 'channel',
                comments_group_id INTEGER,
                is_active INTEGER DEFAULT 1,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')

        # Таблица комментариев
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS comments (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                message_id INTEGER NOT NULL,
                chat_id INTEGER NOT NULL,
                channel_id INTEGER,
                post_text TEXT,
                comment_text TEXT NOT NULL,
                status TEXT NOT NULL DEFAULT 'pending',
                regenerations INTEGER DEFAULT 0,
                session_file TEXT,
                sent_message_id INTEGER,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(message_id, chat_id, session_file)
            )
        ''')

        # Таблица сессий
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS sessions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                session_file TEXT UNIQUE NOT NULL,
                user_id INTEGER,
                username TEXT,
                first_name TEXT,
                last_name TEXT,
                phone TEXT,
                is_active INTEGER DEFAULT 1,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')

        # Таблица статистики
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS stats (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                date DATE NOT NULL,
                session_file TEXT NOT NULL,
                generated INTEGER DEFAULT 0,
                approved INTEGER DEFAULT 0,
                rejected INTEGER DEFAULT 0,
                sent INTEGER DEFAULT 0,
                UNIQUE(date, session_file)
            )
        ''')

        # Индексы
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_comments_status ON comments(status)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_comments_message ON comments(message_id, chat_id)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_sessions_active ON sessions(is_active)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_stats_date ON stats(date)')

        conn.commit()
        logger.info("База данных успешно инициализирована")
        return conn
    except Exception as e:
        logger.error(f"Ошибка при инициализации базы данных: {e}")
        raise
    finally:
        if 'conn' in locals():
            conn.close()


# === Комментарии ===

def save_comment(message_id: int, chat_id: int, comment_text: str, session_file: str = None, post_text: str = None, channel_id: int = None) -> bool:
    """Сохранение комментария в базу данных"""
    try:
        conn = get_connection()
        cursor = conn.cursor()

        cursor.execute('''
            INSERT INTO comments (message_id, chat_id, comment_text, session_file, post_text, channel_id)
            VALUES (?, ?, ?, ?, ?, ?)
            ON CONFLICT(message_id, chat_id, session_file) 
            DO UPDATE SET comment_text = ?, updated_at = CURRENT_TIMESTAMP
        ''', (message_id, chat_id, comment_text, session_file, post_text, channel_id, comment_text))

        conn.commit()
        logger.info(f"Комментарий сохранён: message_id={message_id}, session={session_file}")
        return True
    except Exception as e:
        logger.error(f"Ошибка при сохранении комментария: {e}")
        return False
    finally:
        if 'conn' in locals():
            conn.close()


def get_comment(message_id: int, chat_id: int, session_file: str = None) -> dict | None:
    """Получение комментария из базы данных"""
    try:
        conn = get_connection()
        cursor = conn.cursor()

        if session_file:
            cursor.execute('''
                SELECT * FROM comments 
                WHERE message_id = ? AND chat_id = ? AND session_file = ?
            ''', (message_id, chat_id, session_file))
        else:
            cursor.execute('''
                SELECT * FROM comments 
                WHERE message_id = ? AND chat_id = ?
            ''', (message_id, chat_id))

        row = cursor.fetchone()
        return dict(row) if row else None
    except Exception as e:
        logger.error(f"Ошибка при получении комментария: {e}")
        return None
    finally:
        if 'conn' in locals():
            conn.close()


def get_comments_for_post(message_id: int, chat_id: int) -> list:
    """Получение всех комментариев для поста"""
    try:
        conn = get_connection()
        cursor = conn.cursor()

        cursor.execute('''
            SELECT * FROM comments 
            WHERE message_id = ? AND chat_id = ?
            ORDER BY session_file
        ''', (message_id, chat_id))

        return [dict(row) for row in cursor.fetchall()]
    except Exception as e:
        logger.error(f"Ошибка при получении комментариев: {e}")
        return []
    finally:
        if 'conn' in locals():
            conn.close()


def update_comment_status(comment_id: int, status: str) -> bool:
    """Обновление статуса комментария"""
    try:
        conn = get_connection()
        cursor = conn.cursor()

        cursor.execute('''
            UPDATE comments 
            SET status = ?, updated_at = CURRENT_TIMESTAMP 
            WHERE id = ?
        ''', (status, comment_id))

        conn.commit()
        logger.info(f"Статус комментария {comment_id} обновлён на {status}")
        return True
    except Exception as e:
        logger.error(f"Ошибка при обновлении статуса: {e}")
        return False
    finally:
        if 'conn' in locals():
            conn.close()


def update_comment_sent(comment_id: int, sent_message_id: int) -> bool:
    """Обновление ID отправленного комментария"""
    try:
        conn = get_connection()
        cursor = conn.cursor()

        cursor.execute('''
            UPDATE comments 
            SET sent_message_id = ?, status = 'sent', updated_at = CURRENT_TIMESTAMP 
            WHERE id = ?
        ''', (sent_message_id, comment_id))

        conn.commit()
        return True
    except Exception as e:
        logger.error(f"Ошибка при обновлении отправленного комментария: {e}")
        return False
    finally:
        if 'conn' in locals():
            conn.close()


def increment_regeneration(comment_id: int) -> bool:
    """Увеличение счётчика регенераций"""
    try:
        conn = get_connection()
        cursor = conn.cursor()

        cursor.execute('''
            UPDATE comments 
            SET regenerations = regenerations + 1, updated_at = CURRENT_TIMESTAMP 
            WHERE id = ?
        ''', (comment_id,))

        conn.commit()
        return True
    except Exception as e:
        logger.error(f"Ошибка при увеличении счётчика регенераций: {e}")
        return False
    finally:
        if 'conn' in locals():
            conn.close()


def get_pending_comments() -> list:
    """Получение всех ожидающих комментариев"""
    try:
        conn = get_connection()
        cursor = conn.cursor()

        cursor.execute('''
            SELECT * FROM comments WHERE status = 'pending'
            ORDER BY created_at DESC
        ''')

        return [dict(row) for row in cursor.fetchall()]
    except Exception as e:
        logger.error(f"Ошибка при получении ожидающих комментариев: {e}")
        return []
    finally:
        if 'conn' in locals():
            conn.close()


def get_pending_comments_by_session(session_file: str) -> list:
    """Получение ожидающих комментариев для конкретной сессии"""
    try:
        conn = get_connection()
        cursor = conn.cursor()

        cursor.execute('''
            SELECT * FROM comments 
            WHERE status = 'pending' AND session_file = ?
            ORDER BY created_at DESC
        ''', (session_file,))

        return [dict(row) for row in cursor.fetchall()]
    except Exception as e:
        logger.error(f"Ошибка при получении ожидающих комментариев для сессии: {e}")
        return []
    finally:
        if 'conn' in locals():
            conn.close()


def get_pending_comments_by_group(group_id: str) -> list:
    """Получение ожидающих комментариев для конкретной группы"""
    try:
        conn = get_connection()
        cursor = conn.cursor()

        cursor.execute('''
            SELECT * FROM comments 
            WHERE status = 'pending' AND chat_id = ?
            ORDER BY created_at DESC
        ''', (group_id,))

        return [dict(row) for row in cursor.fetchall()]
    except Exception as e:
        logger.error(f"Ошибка при получении ожидающих комментариев для группы: {e}")
        return []
    finally:
        if 'conn' in locals():
            conn.close()


# === Сессии ===

def save_session(session_file: str, user_info: dict) -> bool:
    """Сохранение информации о сессии"""
    try:
        conn = get_connection()
        cursor = conn.cursor()

        cursor.execute('''
            INSERT INTO sessions (session_file, user_id, username, first_name, last_name, phone)
            VALUES (?, ?, ?, ?, ?, ?)
            ON CONFLICT(session_file) 
            DO UPDATE SET 
                user_id = ?, username = ?, first_name = ?, last_name = ?, phone = ?,
                updated_at = CURRENT_TIMESTAMP
        ''', (
            session_file, user_info.get('user_id'), user_info.get('username'),
            user_info.get('first_name'), user_info.get('last_name'), user_info.get('phone'),
            user_info.get('user_id'), user_info.get('username'),
            user_info.get('first_name'), user_info.get('last_name'), user_info.get('phone')
        ))

        conn.commit()
        logger.info(f"Сессия сохранена: {session_file}")
        return True
    except Exception as e:
        logger.error(f"Ошибка при сохранении сессии: {e}")
        return False
    finally:
        if 'conn' in locals():
            conn.close()


def get_all_sessions() -> list:
    """Получение всех сессий"""
    try:
        conn = get_connection()
        cursor = conn.cursor()

        cursor.execute('SELECT * FROM sessions ORDER BY created_at')
        return [dict(row) for row in cursor.fetchall()]
    except Exception as e:
        logger.error(f"Ошибка при получении сессий: {e}")
        return []
    finally:
        if 'conn' in locals():
            conn.close()


def get_active_sessions() -> list:
    """Получение активных сессий"""
    try:
        conn = get_connection()
        cursor = conn.cursor()

        cursor.execute('SELECT * FROM sessions WHERE is_active = 1 ORDER BY created_at')
        return [dict(row) for row in cursor.fetchall()]
    except Exception as e:
        logger.error(f"Ошибка при получении активных сессий: {e}")
        return []
    finally:
        if 'conn' in locals():
            conn.close()


def toggle_session(session_file: str, is_active: bool) -> bool:
    """Активация/деактивация сессии"""
    try:
        conn = get_connection()
        cursor = conn.cursor()

        cursor.execute('''
            UPDATE sessions 
            SET is_active = ?, updated_at = CURRENT_TIMESTAMP 
            WHERE session_file = ?
        ''', (1 if is_active else 0, session_file))

        conn.commit()
        return True
    except Exception as e:
        logger.error(f"Ошибка при переключении сессии: {e}")
        return False
    finally:
        if 'conn' in locals():
            conn.close()


def delete_session(session_file: str) -> bool:
    """Удаление сессии"""
    try:
        conn = get_connection()
        cursor = conn.cursor()

        # Удаляем комментарии этой сессии
        cursor.execute('DELETE FROM comments WHERE session_file = ?', (session_file,))
        # Удаляем сессию
        cursor.execute('DELETE FROM sessions WHERE session_file = ?', (session_file,))

        conn.commit()
        return True
    except Exception as e:
        logger.error(f"Ошибка при удалении сессии: {e}")
        return False
    finally:
        if 'conn' in locals():
            conn.close()


# === Статистика ===

def update_stats(date: str, session_file: str, field: str, value: int = 1) -> bool:
    """Обновление статистики"""
    try:
        conn = get_connection()
        cursor = conn.cursor()

        # Проверяем существование записи
        cursor.execute('''
            SELECT id FROM stats WHERE date = ? AND session_file = ?
        ''', (date, session_file))

        if cursor.fetchone():
            cursor.execute(f'''
                UPDATE stats SET {field} = {field} + ? 
                WHERE date = ? AND session_file = ?
            ''', (value, date, session_file))
        else:
            cursor.execute('''
                INSERT INTO stats (date, session_file, {field})
                VALUES (?, ?, ?)
            '''.format(field=field), (date, session_file, value))

        conn.commit()
        return True
    except Exception as e:
        logger.error(f"Ошибка при обновлении статистики: {e}")
        return False
    finally:
        if 'conn' in locals():
            conn.close()


def get_stats(date_from: str = None, date_to: str = None) -> list:
    """Получение статистики"""
    try:
        conn = get_connection()
        cursor = conn.cursor()

        if date_from and date_to:
            cursor.execute('''
                SELECT * FROM stats 
                WHERE date BETWEEN ? AND ?
                ORDER BY date DESC
            ''', (date_from, date_to))
        elif date_from:
            cursor.execute('''
                SELECT * FROM stats 
                WHERE date >= ?
                ORDER BY date DESC
            ''', (date_from,))
        else:
            cursor.execute('SELECT * FROM stats ORDER BY date DESC LIMIT 30')

        return [dict(row) for row in cursor.fetchall()]
    except Exception as e:
        logger.error(f"Ошибка при получении статистики: {e}")
        return []
    finally:
        if 'conn' in locals():
            conn.close()


def get_summary_stats() -> dict:
    """Получение сводной статистики"""
    try:
        conn = get_connection()
        cursor = conn.cursor()

        cursor.execute('''
            SELECT 
                COUNT(*) as total_comments,
                SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending,
                SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) as approved,
                SUM(CASE WHEN status = 'rejected' THEN 1 ELSE 0 END) as rejected,
                SUM(CASE WHEN status = 'sent' THEN 1 ELSE 0 END) as sent
            FROM comments
        ''')

        row = cursor.fetchone()
        return dict(row) if row else {}
    except Exception as e:
        logger.error(f"Ошибка при получении сводной статистики: {e}")
        return {}
    finally:
        if 'conn' in locals():
            conn.close()


# === Целевые группы ===

def add_target_group(group_id: int, group_name: str = None, group_username: str = None, group_type: str = 'channel', comments_group_id: int = None) -> bool:
    """Добавление целевой группы"""
    try:
        conn = get_connection()
        cursor = conn.cursor()

        cursor.execute('''
            INSERT INTO target_groups (group_id, group_name, group_username, group_type, comments_group_id)
            VALUES (?, ?, ?, ?, ?)
            ON CONFLICT(group_id) 
            DO UPDATE SET 
                group_name = COALESCE(?, group_name),
                group_username = COALESCE(?, group_username),
                group_type = COALESCE(?, group_type),
                comments_group_id = COALESCE(?, comments_group_id),
                is_active = 1,
                updated_at = CURRENT_TIMESTAMP
        ''', (group_id, group_name, group_username, group_type, comments_group_id, group_name, group_username, group_type, comments_group_id))

        conn.commit()
        logger.info(f"Группа {group_id} добавлена")
        return True
    except Exception as e:
        logger.error(f"Ошибка при добавлении группы: {e}")
        return False
    finally:
        if 'conn' in locals():
            conn.close()


def get_target_groups() -> list:
    """Получение всех активных целевых групп"""
    try:
        conn = get_connection()
        cursor = conn.cursor()

        cursor.execute('''
            SELECT * FROM target_groups WHERE is_active = 1
            ORDER BY created_at
        ''')

        return [dict(row) for row in cursor.fetchall()]
    except Exception as e:
        logger.error(f"Ошибка при получении групп: {e}")
        return []
    finally:
        if 'conn' in locals():
            conn.close()


def get_all_target_groups() -> list:
    """Получение всех целевых групп"""
    try:
        conn = get_connection()
        cursor = conn.cursor()

        cursor.execute('SELECT * FROM target_groups ORDER BY created_at')
        return [dict(row) for row in cursor.fetchall()]
    except Exception as e:
        logger.error(f"Ошибка при получении групп: {e}")
        return []
    finally:
        if 'conn' in locals():
            conn.close()


def remove_target_group(group_id: int) -> bool:
    """Удаление целевой группы"""
    try:
        conn = get_connection()
        cursor = conn.cursor()

        cursor.execute('DELETE FROM target_groups WHERE group_id = ?', (group_id,))

        conn.commit()
        logger.info(f"Группа {group_id} удалена")
        return True
    except Exception as e:
        logger.error(f"Ошибка при удалении группы: {e}")
        return False
    finally:
        if 'conn' in locals():
            conn.close()


def toggle_target_group(group_id: int, is_active: bool) -> bool:
    """Активация/деактивация группы"""
    try:
        conn = get_connection()
        cursor = conn.cursor()

        cursor.execute('''
            UPDATE target_groups 
            SET is_active = ?, updated_at = CURRENT_TIMESTAMP 
            WHERE group_id = ?
        ''', (1 if is_active else 0, group_id))

        conn.commit()
        return True
    except Exception as e:
        logger.error(f"Ошибка при переключении группы: {e}")
        return False
    finally:
        if 'conn' in locals():
            conn.close()


def is_target_group(group_id: int) -> bool:
    """Проверка, является ли группа целевой"""
    try:
        conn = get_connection()
        cursor = conn.cursor()

        cursor.execute('''
            SELECT 1 FROM target_groups 
            WHERE group_id = ? AND is_active = 1
        ''', (group_id,))

        return cursor.fetchone() is not None
    except Exception as e:
        logger.error(f"Ошибка при проверке группы: {e}")
        return False
    finally:
        if 'conn' in locals():
            conn.close()