---

import sqlite3

# Подключение к БД и активация внешних ключей
conn = sqlite3.connect("clinic.db")
cursor = conn.cursor()
cursor.execute("PRAGMA foreign_keys = ON;")

# 1. Создание структуры таблиц
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()


# ========================================================
# 1. CREATE — создаем справочники и 2 записи на прием
# ========================================================

# Пациенты
cursor.execute("INSERT INTO Patient (first_name, last_name, birthday, phone) VALUES (?, ?, ?, ?)",
               ("Иван", "Петров", "1995-04-12", "+79991112233"))
patient_1 = cursor.lastrowid

cursor.execute("INSERT INTO Patient (first_name, last_name, birthday, phone) VALUES (?, ?, ?, ?)",
               ("Елена", "Сидорова", "2001-08-25", "+79994445566"))
patient_2 = cursor.lastrowid

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

cursor.execute("INSERT INTO Doctor (first_name, last_name, specialization) VALUES (?, ?, ?)",
               ("Олег", "Кузнецов", "Офтальмолог"))
doctor_2 = cursor.lastrowid

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

cursor.execute("INSERT INTO Service (name, description, price) VALUES (?, ?, ?)",
               ("Проверка зрения", "Диагностика на аппарате", 1800.0))
service_2 = cursor.lastrowid

# Кабинеты
cursor.execute("INSERT INTO Cabinet (room_number, floor, room_type) VALUES (?, ?, ?)",
               ("101", 1, "Терапия"))
cabinet_1 = cursor.lastrowid

cursor.execute("INSERT INTO Cabinet (room_number, floor, room_type) VALUES (?, ?, ?)",
               ("205", 2, "Офтальмология"))
cabinet_2 = cursor.lastrowid

# Создаем прием #1 (его мы позже удалим)
cursor.execute("""
    INSERT INTO Appointment (patient_id, doctor_id, service_id, cabinet_id, datetime, status, complaints, diagnosis)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?)
""", (patient_1, doctor_1, service_1, cabinet_1, "2026-09-15 10:00", "Запланирован", "Температура 38", None))
appointment_1 = cursor.lastrowid

# Создаем прием #2 (он останется в базе)
cursor.execute("""
    INSERT INTO Appointment (patient_id, doctor_id, service_id, cabinet_id, datetime, status, complaints, diagnosis)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?)
""", (patient_2, doctor_2, service_2, cabinet_2, "2026-09-16 12:30", "Запланирован", "Ухудшилось зрение вдаль", None))
appointment_2 = cursor.lastrowid

conn.commit()
print(f"[CREATE] Создано 2 приема: ID {appointment_1} и ID {appointment_2}")


# ========================================================
# 2. UPDATE — обновляем данные только для приема #2
# ========================================================

cursor.execute("""
    UPDATE Appointment
    SET status = ?, diagnosis = ?
    WHERE id = ?
""", ("Завершен", "Миопия слабой степени", appointment_2))

conn.commit()
print(f"[UPDATE] Прием #{appointment_2} получил статус 'Завершен' и диагноз.")


# ========================================================
# 3. DELETE — удаляем только прием #1
# ========================================================

cursor.execute("DELETE FROM Appointment WHERE id = ?", (appointment_1,))
conn.commit()
print(f"[DELETE] Прием #{appointment_1} удален из базы (отменен).")


# ========================================================
# 4. READ — читаем базу и выводим остаток
# ========================================================

cursor.execute("""
    SELECT 
        a.id, 
        p.first_name || ' ' || p.last_name AS patient,
        d.first_name || ' ' || d.last_name AS doctor,
        s.name AS service,
        c.room_number,
        a.datetime,
        a.status,
        a.diagnosis
    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
""")

print("\n--- ТЕКУЩИЕ ЗАПИСИ В БАЗЕ (Остался только прием #2) ---")
rows = cursor.fetchall()
for row in rows:
    print(f"ID приема: {row[0]}")
    print(f"Пациент: {row[1]}")
    print(f"Врач: {row[2]}")
    print(f"Услуга: {row[3]}")
    print(f"Кабинет: {row[4]}")
    print(f"Время: {row[5]}")
    print(f"Статус: {row[6]}")
    print(f"Диагноз: {row[7]}")

conn.close()