---

import sqlite3
import json

conn = sqlite3.connect('education.db')
cursor = conn.cursor()

cursor.execute('''
CREATE TABLE IF NOT EXISTS students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    full_name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    birth_date TEXT,
    registration_date TEXT DEFAULT CURRENT_TIMESTAMP,
    is_active INTEGER DEFAULT 1
)
''')

cursor.execute('''
CREATE TABLE IF NOT EXISTS courses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    description TEXT,
    duration_hours INTEGER,
    price REAL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
)
''')

cursor.execute('''
CREATE TABLE IF NOT EXISTS tests (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    course_id INTEGER,
    title TEXT NOT NULL,
    max_score INTEGER NOT NULL,
    passing_score INTEGER NOT NULL,
    time_limit_minutes INTEGER,
    FOREIGN KEY (course_id) REFERENCES courses(id)
)
''')

cursor.execute('''
CREATE TABLE IF NOT EXISTS marks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id INTEGER,
    course_id INTEGER,
    test_id INTEGER,
    score INTEGER NOT NULL,
    finish_time TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id),
    FOREIGN KEY (test_id) REFERENCES tests(id)
)
''')

conn.commit()

def add_student(full_name, email, birth_date=None):
    cursor.execute('INSERT INTO students (full_name, email, birth_date) VALUES (?, ?, ?)', (full_name, email, birth_date))
    conn.commit()
    return cursor.lastrowid

def remove_student(student_id):
    cursor.execute('DELETE FROM students WHERE id = ?', (student_id,))
    conn.commit()
    return cursor.rowcount > 0

def update_student(student_id, **kwargs):
    allowed = ['full_name', 'email', 'birth_date', 'is_active']
    updates = [f"{k} = ?" for k in kwargs if k in allowed]
    values = [v for k, v in kwargs.items() if k in allowed]
    if updates:
        cursor.execute(f'UPDATE students SET {", ".join(updates)} WHERE id = ?', values + [student_id])
        conn.commit()
        return cursor.rowcount > 0
    return False

def add_course(title, description, duration_hours, price):
    cursor.execute('INSERT INTO courses (title, description, duration_hours, price) VALUES (?, ?, ?, ?)', (title, description, duration_hours, price))
    conn.commit()
    return cursor.lastrowid

def remove_course(course_id):
    cursor.execute('DELETE FROM courses WHERE id = ?', (course_id,))
    conn.commit()
    return cursor.rowcount > 0

def update_course(course_id, **kwargs):
    allowed = ['title', 'description', 'duration_hours', 'price']
    updates = [f"{k} = ?" for k in kwargs if k in allowed]
    values = [v for k, v in kwargs.items() if k in allowed]
    if updates:
        cursor.execute(f'UPDATE courses SET {", ".join(updates)} WHERE id = ?', values + [course_id])
        conn.commit()
        return cursor.rowcount > 0
    return False

def add_test(course_id, title, max_score, passing_score, time_limit_minutes):
    cursor.execute('INSERT INTO tests (course_id, title, max_score, passing_score, time_limit_minutes) VALUES (?, ?, ?, ?, ?)', (course_id, title, max_score, passing_score, time_limit_minutes))
    conn.commit()
    return cursor.lastrowid

def remove_test(test_id):
    cursor.execute('DELETE FROM tests WHERE id = ?', (test_id,))
    conn.commit()
    return cursor.rowcount > 0

def update_test(test_id, **kwargs):
    allowed = ['course_id', 'title', 'max_score', 'passing_score', 'time_limit_minutes']
    updates = [f"{k} = ?" for k in kwargs if k in allowed]
    values = [v for k, v in kwargs.items() if k in allowed]
    if updates:
        cursor.execute(f'UPDATE tests SET {", ".join(updates)} WHERE id = ?', values + [test_id])
        conn.commit()
        return cursor.rowcount > 0
    return False

def add_mark(student_id, course_id, test_id, score):
    cursor.execute('INSERT INTO marks (student_id, course_id, test_id, score) VALUES (?, ?, ?, ?)', (student_id, course_id, test_id, score))
    conn.commit()
    return cursor.lastrowid

cursor.execute('''
CREATE TABLE IF NOT EXISTS activity (
    id INTEGER PRIMARY KEY,
    student_id INTEGER,
    course_id INTEGER,
    test_id INTEGER,
    action TEXT,
    time TEXT,
    details TEXT
)
''')

conn.commit()

cursor.execute('INSERT INTO activity (id, student_id, course_id, test_id, action, time, details) VALUES (?, ?, ?, ?, ?, ?, ?)', (1, 1, 10, 5, 'start', '2024-01-15 13:00', json.dumps({"attempt_number": 5})))
conn.commit()