-- eskuel:system=sqlite
-- eskuel:systemMinVersion=3.37.0

-- Create the "driving_instructor" table
CREATE TABLE IF NOT EXISTS driving_instructor (
    code VARCHAR(10) PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    phone_number VARCHAR(15)
);

-- Create the "driving_student" table
CREATE TABLE IF NOT EXISTS driving_student (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    date_of_birth DATE,
    theory_test_passed BOOLEAN,
    number_of_driving_lessons INT,
    instructor_code VARCHAR(10),
    FOREIGN KEY (instructor_code) REFERENCES driving_instructor(code)
);

-- Insert driving instructors
INSERT INTO driving_instructor (code, first_name, last_name, phone_number)
VALUES
    ('Dem', 'Aylin', 'Demir', '0176-48392715'),
    ('Had', 'Sammy', 'Hadamard', '0157-20968431'),
    ('Pet', 'Peter', 'Petersen', '0172-61590384'),
    ('Schm', 'Elaine', 'Schmidt', '0160-74825196');

INSERT INTO driving_student (first_name, last_name, date_of_birth, theory_test_passed, number_of_driving_lessons, instructor_code)
VALUES
    ('Hans', 'Schmidt', '2000-01-01', FALSE, 10, 'Dem'),
    ('Lisa', 'Terhoog', '2002-02-02', TRUE, 15, 'Had'),
    ('Karl', 'Scholz', '2004-03-03', FALSE, 12, 'Pet'),
    ('Marie', 'Korting', '2006-04-04', TRUE, 20, 'Schm'),
    ('Fritz', 'Feidel', '2008-05-05', FALSE, 8, 'Dem'),
    ('Marie', 'Hermes', '2001-12-02', TRUE, 17, 'Dem'),
    ('Jana', 'Bisslich', '2010-06-06', TRUE, 18, 'Dem');