---

import sqlite3
import csv

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

cursor.execute('''
CREATE TABLE IF NOT EXISTS passazhir (
    id_passazhira INTEGER PRIMARY KEY AUTOINCREMENT,
    fio TEXT NOT NULL,
    pasport TEXT NOT NULL,
    data_rozhdeniya TEXT NOT NULL
)
''')

cursor.execute('''
CREATE TABLE IF NOT EXISTS samolet (
    id_samolyota INTEGER PRIMARY KEY AUTOINCREMENT,
    model TEXT NOT NULL,
    mest_vsego INTEGER NOT NULL
)
''')

cursor.execute('''
CREATE TABLE IF NOT EXISTS reys (
    id_reysa INTEGER PRIMARY KEY AUTOINCREMENT,
    nomer TEXT NOT NULL,
    naznachenie TEXT NOT NULL,
    data TEXT NOT NULL,
    aviakompaniya TEXT NOT NULL,
    id_samolyota INTEGER NOT NULL,
    FOREIGN KEY (id_samolyota) REFERENCES samolet (id_samolyota)
)
''')

cursor.execute('''
CREATE TABLE IF NOT EXISTS bilet (
    id_bileta INTEGER PRIMARY KEY AUTOINCREMENT,
    mesto TEXT NOT NULL,
    klass TEXT NOT NULL,
    tsena INTEGER NOT NULL,
    id_passazhira INTEGER NOT NULL,
    id_reysa INTEGER NOT NULL,
    FOREIGN KEY (id_passazhira) REFERENCES passazhir (id_passazhira),
    FOREIGN KEY (id_reysa) REFERENCES reys (id_reysa)
)
''')

cursor.execute('''
CREATE TABLE IF NOT EXISTS registratsiya (
    id_registratsii INTEGER PRIMARY KEY AUTOINCREMENT,
    fakt INTEGER NOT NULL,
    bagazh TEXT NOT NULL,
    id_bileta INTEGER NOT NULL UNIQUE,
    FOREIGN KEY (id_bileta) REFERENCES bilet (id_bileta)
)
''')

with open('registratsiya.csv', 'r', encoding='utf-8') as file:
    csv_reader = csv.DictReader(file)
    for row in csv_reader:
        cursor.execute('''INSERT INTO registratsiya (fakt, bagazh, id_bileta, id_registratsii) VALUES (?, ?, ?, ?)''',
                       (row['fakt'], row['bagazh'], row['id_bileta'], row['id_registratsii'])

print('Регистрация:')
for registratsiya in cursor.fetchall():
    print(registratsiya)



cursor.execute('DELETE FROM registratsiya')
cursor.execute('DELETE FROM bilet')
cursor.execute('DELETE FROM reys')
cursor.execute('DELETE FROM samolet')
cursor.execute('DELETE FROM passazhir')

cursor.execute('DELETE FROM sqlite_sequence')

cursor.execute('''
INSERT INTO passazhir (fio, pasport, data_rozhdeniya)
VALUES (?, ?, ?)
''', ('Иванов Иван Иванович', '4510 123456', '1990-03-15'))
id_passazhira = cursor.lastrowid

cursor.execute('''
INSERT INTO samolet (model, mest_vsego)
VALUES (?, ?)
''', ('Boeing 737-800', 180))
id_samolyota = cursor.lastrowid

cursor.execute('''
INSERT INTO reys (nomer, naznachenie, data, aviakompaniya, id_samolyota)
VALUES (?, ?, ?, ?, ?)
''', ('SU1234', 'Сочи', '2026-04-20', 'Аэрофлот', id_samolyota))
id_reysa = cursor.lastrowid

cursor.execute('''
INSERT INTO bilet (mesto, klass, tsena, id_passazhira, id_reysa)
VALUES (?, ?, ?, ?, ?)
''', ('12A', 'Эконом', 5500, id_passazhira, id_reysa))
id_bileta = cursor.lastrowid

cursor.execute('''
INSERT INTO registratsiya (fakt, bagazh, id_bileta)
VALUES (?, ?, ?)
''', (1, '1 место (23 кг)', id_bileta))

conn.commit()


print("\n=== ПАССАЖИР ===")
cursor.execute('SELECT * FROM passazhir')
for row in cursor.fetchall():
    print(row)

print("\n=== САМОЛЁТ ===")
cursor.execute('SELECT * FROM samolet')
for row in cursor.fetchall():
    print(row)

print("\n=== РЕЙС ===")
cursor.execute('SELECT * FROM reys')
for row in cursor.fetchall():
    print(row)

print("\n=== БИЛЕТ ===")
cursor.execute('SELECT * FROM bilet')
for row in cursor.fetchall():
    print(row)

print("\n=== РЕГИСТРАЦИЯ ===")
cursor.execute('SELECT * FROM registratsiya')
for row in cursor.fetchall():
    print(row)

conn.close()