-- Week Planner Database Migration Script
-- Run these SQL commands on your MySQL database to create the required tables

-- Create weekplan table
CREATE TABLE weekplan (
    id int AUTO_INCREMENT PRIMARY KEY,
    name varchar(255) NOT NULL,
    startdate date NOT NULL,
    createdat datetime NOT NULL
);

-- Create dayplan table
CREATE TABLE dayplan (
    id int AUTO_INCREMENT PRIMARY KEY,
    weekplanid int NOT NULL,
    dayofweek int NOT NULL,
    date date NOT NULL,
    recipeid int NULL,
    mainingredient varchar(100) NULL,
    bannedingredients varchar(500) NULL,
    requiredingredients varchar(500) NULL,
    FOREIGN KEY (weekplanid) REFERENCES weekplan(id) ON DELETE CASCADE,
    FOREIGN KEY (recipeid) REFERENCES recepie(id) ON DELETE SET NULL
);

-- Add indexes for better performance
CREATE INDEX idx_weekplan_startdate ON weekplan(startdate);
CREATE INDEX idx_dayplan_weekplanid ON dayplan(weekplanid);
CREATE INDEX idx_dayplan_date ON dayplan(date);
CREATE INDEX idx_dayplan_recipeid ON dayplan(recipeid);