---

import sqlite3

# 1. Подключение и создание таблиц
conn = sqlite3.connect("clinic.db")
cursor = conn.cursor()
cursor.execute("PRAGMA foreign_keys = ON;")

cursor.executescript("""
CREATE TABLE IF NOT EXISTS Service (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT,
    price REAL NOT NULL
);

CREATE TABLE IF NOT EXISTS Patient (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    birthday TEXT NOT NULL,
    phone TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS Doctor (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    specialization TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS Cabinet (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    room_number TEXT NOT NULL,
    floor INTEGER NOT NULL,
    room_type TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS Appointment (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    patient_id INTEGER NOT NULL,
    doctor_id INTEGER NOT NULL,
    service_id INTEGER NOT NULL,
    cabinet_id INTEGER NOT NULL,
    datetime TEXT NOT NULL,
    status TEXT NOT NULL,
    complaints TEXT,
    diagnosis TEXT,
    FOREIGN KEY (patient_id) REFERENCES Patient(id) ON DELETE CASCADE,
    FOREIGN KEY (doctor_id) REFERENCES Doctor(id) ON DELETE RESTRICT,
    FOREIGN KEY (service_id) REFERENCES Service(id) ON DELETE RESTRICT,
    FOREIGN KEY (cabinet_id) REFERENCES Cabinet(id) ON DELETE RESTRICT
);
""")
conn.commit()


# ==========================================
# C — CREATE (Вставка записей через INSERT)
# ==========================================

# Добавляем пациента
cursor.execute("""
    INSERT INTO Patient (first_name, last_name, birthday, phone)
    VALUES (?, ?, ?, ?)
""", ("Иван", "Петров", "1995-04-12", "+79991234567"))
patient_id = cursor.lastrowid

# Добавляем врача
cursor.execute("""
    INSERT INTO Doctor (first_name, last_name, specialization)
    VALUES (?, ?, ?)
""", ("Анна", "Смирнова", "Терапевт"))
doctor_id = cursor.lastrowid

# Добавляем услугу
cursor.execute("""
    INSERT INTO Service (name, description, price)
    VALUES (?, ?, ?)
""", ("Первичный прием", "Осмотр и консультация врача", 2500.0))
service_id = cursor.lastrowid

# Добавляем кабинет
cursor.execute("""
    INSERT INTO Cabinet (room_number, floor, room_type)
    VALUES (?, ?, ?)
""", ("204", 2, "Консультационный"))
cabinet_id = cursor.lastrowid

# Создаем запись на прием (связываем внешние ключи)
cursor.execute("""
    INSERT INTO Appointment (patient_id, doctor_id, service_id, cabinet_id, datetime, status, complaints, diagnosis)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?)
""", (patient_id, doctor_id, service_id, cabinet_id, "2026-09-15 14:00", "Запланирован", "Боль в горле", None))
appointment_id = cursor.lastrowid

conn.commit()
print(f"[CREATE] Данные добавлены! ID приема: {appointment_id}")


# ==========================================
# R — READ (Чтение данных через SELECT)
# ==========================================

cursor.execute("""
    SELECT 
        a.id, 
        p.first_name || ' ' || p.last_name AS patient_name,
        d.first_name || ' ' || d.last_name AS doctor_name,
        s.name AS service_name,
        c.room_number,
        a.status
    FROM Appointment a
    JOIN Patient p ON a.patient_id = p.id
    JOIN Doctor d ON a.doctor_id = d.id
    JOIN Service s ON a.service_id = s.id
    JOIN Cabinet c ON a.cabinet_id = c.id
    WHERE a.id = ?
""", (appointment_id,))

record = cursor.fetchone()
print(f"[READ] Информация о приеме: {record}")


# ==========================================
# U — UPDATE (Обновление через UPDATE)
# ==========================================

# Врач провел прием, ставим статус "Завершен" и записываем диагноз
cursor.execute("""
    UPDATE Appointment
    SET status = ?, diagnosis = ?
    WHERE id = ?
""", ("Завершен", "ОРВИ", appointment_id))

# Также обновим номер телефона у пациента
cursor.execute("""
    UPDATE Patient
    SET phone = ?
    WHERE id = ?
""", ("+79990001122", patient_id))

conn.commit()
print("[UPDATE] Статус приема и телефон пациента обновлены.")


# ==========================================
# D — DELETE (Удаление через DELETE)
# ==========================================

# Удаляем созданную запись на прием
cursor.execute("""
    DELETE FROM Appointment
    WHERE id = ?
""", (appointment_id,))

conn.commit()
print(f"[DELETE] Прием #{appointment_id} успешно удален из базы.")

# Проверяем, что запись действительно исчезла
cursor.execute("SELECT * FROM Appointment WHERE id = ?", (appointment_id,))
print(f"[CHECK] Поиск удаленной записи: {cursor.fetchone()}")

conn.close()