---

import sqlite3

# Подключение к БД (файл clinic.db создастся автоматически в текущей папке)
conn = sqlite3.connect('clinic.db')
cursor = conn.cursor()

# Включаем строгую проверку внешних ключей (по умолчанию в SQLite она выключена)
cursor.execute("PRAGMA foreign_keys = ON;")

# 1. Таблица услуг
cursor.execute("""
CREATE TABLE IF NOT EXISTS Service (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT,
    price REAL NOT NULL
);
""")

# 2. Таблица пациентов
cursor.execute("""
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
);
""")

# 3. Таблица врачей
cursor.execute("""
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
);
""")

# 4. Таблица кабинетов
cursor.execute("""
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
);
""")

# 5. Центральная таблица записей на прием
cursor.execute("""
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 RESTRICT,
    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
);
""")

# 6. Таблица назначений / рецептов
cursor.execute("""
CREATE TABLE IF NOT EXISTS Prescription (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    appointment_id INTEGER NOT NULL,
    medication_name TEXT NOT NULL,
    instructions TEXT,
    FOREIGN KEY (appointment_id) REFERENCES Appointment(id) ON DELETE CASCADE
);
""")

# 7. Таблица оплаты
cursor.execute("""
CREATE TABLE IF NOT EXISTS Payment (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    appointment_id INTEGER NOT NULL,
    sum REAL NOT NULL,
    payment_method TEXT NOT NULL,
    datetime TEXT NOT NULL,
    status TEXT NOT NULL,
    FOREIGN KEY (appointment_id) REFERENCES Appointment(id) ON DELETE CASCADE
);
""")

conn.commit()
print("База данных clinic.db и все таблицы успешно созданы!")