# database.py - نظام قاعدة البيانات البسيطة للملاحظات

import sqlite3
import json
import os
from datetime import datetime
from typing import List, Dict, Optional

class NotesDatabase:
    """قاعدة بيانات بسيطة لحفظ الملاحظات والملخصات"""
    
    def __init__(self, db_path: str = "lecture_notes.db"):
        self.db_path = db_path
        self.init_database()
    
    def init_database(self):
        """إنشاء قاعدة البيانات والجداول"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            # جدول الملاحظات الرئيسي
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS lecture_notes (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    title TEXT NOT NULL,
                    original_text TEXT NOT NULL,
                    translated_text TEXT,
                    summary TEXT,
                    key_points TEXT,
                    subject TEXT,
                    date_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    date_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    audio_file_path TEXT,
                    language_detected TEXT,
                    target_language TEXT,
                    markers TEXT
                )
            ''')
            
            # جدول الملخصات السريعة
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS quick_summaries (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    note_id INTEGER,
                    summary_type TEXT,
                    content TEXT,
                    date_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (note_id) REFERENCES lecture_notes (id)
                )
            ''')
            
            conn.commit()
    
    def save_lecture_note(self, data: Dict) -> int:
        """حفظ ملاحظة محاضرة جديدة"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            cursor.execute('''
                INSERT INTO lecture_notes 
                (title, original_text, translated_text, summary, key_points, 
                 subject, audio_file_path, language_detected, target_language, markers)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ''', (
                data.get('title', 'محاضرة جديدة'),
                data.get('original_text', ''),
                data.get('translated_text', ''),
                data.get('summary', ''),
                data.get('key_points', ''),
                data.get('subject', ''),
                data.get('audio_file_path', ''),
                data.get('language_detected', ''),
                data.get('target_language', ''),
                json.dumps(data.get('markers', []))
            ))
            
            note_id = cursor.lastrowid
            conn.commit()
            return note_id
    
    def get_all_notes(self, limit: int = 50) -> List[Dict]:
        """استرجاع جميع الملاحظات"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            cursor.execute('''
                SELECT * FROM lecture_notes 
                ORDER BY date_created DESC 
                LIMIT ?
            ''', (limit,))
            
            columns = [description[0] for description in cursor.description]
            notes = []
            
            for row in cursor.fetchall():
                note = dict(zip(columns, row))
                # تحويل markers من JSON string إلى list
                if note['markers']:
                    try:
                        note['markers'] = json.loads(note['markers'])
                    except:
                        note['markers'] = []
                notes.append(note)
            
            return notes
    
    def get_note_by_id(self, note_id: int) -> Optional[Dict]:
        """استرجاع ملاحظة محددة بالـ ID"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            cursor.execute('SELECT * FROM lecture_notes WHERE id = ?', (note_id,))
            row = cursor.fetchone()
            
            if row:
                columns = [description[0] for description in cursor.description]
                note = dict(zip(columns, row))
                if note['markers']:
                    try:
                        note['markers'] = json.loads(note['markers'])
                    except:
                        note['markers'] = []
                return note
            
            return None
    
    def update_note(self, note_id: int, data: Dict) -> bool:
        """تحديث ملاحظة موجودة"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            # بناء query التحديث بناءً على البيانات الموجودة
            update_fields = []
            values = []
            
            for field in ['title', 'summary', 'key_points', 'subject']:
                if field in data:
                    update_fields.append(f"{field} = ?")
                    values.append(data[field])
            
            if not update_fields:
                return False
            
            update_fields.append("date_modified = CURRENT_TIMESTAMP")
            values.append(note_id)
            
            query = f"UPDATE lecture_notes SET {', '.join(update_fields)} WHERE id = ?"
            
            cursor.execute(query, values)
            conn.commit()
            
            return cursor.rowcount > 0
    
    def delete_note(self, note_id: int) -> bool:
        """حذف ملاحظة"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            # حذف الملخصات المرتبطة أولاً
            cursor.execute('DELETE FROM quick_summaries WHERE note_id = ?', (note_id,))
            
            # ثم حذف الملاحظة
            cursor.execute('DELETE FROM lecture_notes WHERE id = ?', (note_id,))
            
            conn.commit()
            return cursor.rowcount > 0
    
    def search_notes(self, query: str) -> List[Dict]:
        """البحث في الملاحظات"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            search_query = f"%{query}%"
            cursor.execute('''
                SELECT * FROM lecture_notes 
                WHERE title LIKE ? OR original_text LIKE ? 
                OR translated_text LIKE ? OR summary LIKE ?
                ORDER BY date_created DESC
            ''', (search_query, search_query, search_query, search_query))
            
            columns = [description[0] for description in cursor.description]
            notes = []
            
            for row in cursor.fetchall():
                note = dict(zip(columns, row))
                if note['markers']:
                    try:
                        note['markers'] = json.loads(note['markers'])
                    except:
                        note['markers'] = []
                notes.append(note)
            
            return notes
    
    def get_notes_by_subject(self, subject: str) -> List[Dict]:
        """استرجاع الملاحظات حسب المادة"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            cursor.execute('''
                SELECT * FROM lecture_notes 
                WHERE subject = ?
                ORDER BY date_created DESC
            ''', (subject,))
            
            columns = [description[0] for description in cursor.description]
            notes = []
            
            for row in cursor.fetchall():
                note = dict(zip(columns, row))
                if note['markers']:
                    try:
                        note['markers'] = json.loads(note['markers'])
                    except:
                        note['markers'] = []
                notes.append(note)
            
            return notes
    
    def get_subjects(self) -> List[str]:
        """استرجاع قائمة المواد الدراسية"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            cursor.execute('''
                SELECT DISTINCT subject FROM lecture_notes 
                WHERE subject IS NOT NULL AND subject != ''
                ORDER BY subject
            ''')
            
            return [row[0] for row in cursor.fetchall()]