CREATE DATABASE staff_food_management;
USE staff_food_management;

CREATE TABLE staff (
    id INT AUTO_INCREMENT PRIMARY KEY,
    staff_id VARCHAR(50),
    name VARCHAR(100),
    mobile VARCHAR(20),
    password VARCHAR(255),
    photo VARCHAR(255),
    department VARCHAR(100),
    role VARCHAR(100),
    joining_date DATE,
    status VARCHAR(20)
);

CREATE TABLE food_entries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    staff_id INT,
    entry_date DATE,

    breakfast TINYINT(1) DEFAULT 0,
    lunch TINYINT(1) DEFAULT 0,
    dinner TINYINT(1) DEFAULT 0,
    tea TINYINT(1) DEFAULT 0,

    breakfast_qty INT DEFAULT 0,
    lunch_qty INT DEFAULT 0,
    dinner_qty INT DEFAULT 0,
    tea_qty INT DEFAULT 0,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE meal_rates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    breakfast_rate DECIMAL(10,2),
    lunch_rate DECIMAL(10,2),
    dinner_rate DECIMAL(10,2),
    tea_rate DECIMAL(10,2)
);

INSERT INTO meal_rates (
    breakfast_rate,
    lunch_rate,
    dinner_rate,
    tea_rate
)
VALUES (
    20,
    50,
    60,
    10
);