-- Add migration script here
CREATE TABLE date (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    subtitle TEXT NOT NULL,
    start_time DATETIME NOT NULL,
    end_time DATETIME NOT NULL
);
INSERT INTO date (id, name, subtitle, start_time, end_time)
VALUES (
        1,
        'OS 2023',
        '2023',
        '2023-02-28 12:00:00',
        '2023-02-28 18:00:00'
    );
-- Create a temporary table
CREATE TEMPORARY TABLE temp_appointment_slots AS
SELECT *
FROM appointment_slots;
DROP TABLE appointment_slots;
-- Recreate the appointments table with the new column and foreign key constraint
CREATE TABLE appointment_slots (
    id INTEGER PRIMARY KEY,
    start_time DATETIME NOT NULL,
    end_time DATETIME NOT NULL,
    date_id INTEGER DEFAULT 1 Not NULL,
    -- Add the new column
    FOREIGN KEY (date_id) REFERENCES date(id) -- Add the foreign key constraint
);
-- Insert data back into the new appointments table from the temporary table
INSERT INTO appointment_slots
SELECT *,
    1
FROM temp_appointment_slots;
-- Drop the temporary table
DROP TABLE temp_appointment_slots;
CREATE TABLE teacher_dates (
    teacher_id INTEGER NOT NULL,
    date_id INTEGER NOT NULL,
    PRIMARY KEY (teacher_id, date_id),
    FOREIGN KEY (teacher_id) REFERENCES teachers(id),
    FOREIGN KEY (date_id) REFERENCES date(id)
);
INSERT INTO teacher_dates (teacher_id, date_id)
SELECT teachers.id AS teacher_id,
    date.id AS date_id
FROM teachers,
    date;