Help simplifying office hours db structure

To simplify your office hours database structure while ensuring it meets your requirements, you can streamline the tables and relationships. Here's a proposed structure and approach:

Proposed Database Structure

1. office_hours: This table will store the default office hours for each day of the week.
2. modified_hours: This table will store any modifications to the default office hours for specific dates.
3. sessions: This table will store the session details for each date.
4. days: This table will store the dates.

Table Definitions

office_hours

CREATE TABLE office_hours (
    id INT AUTO_INCREMENT PRIMARY KEY,
    day_of_week TINYINT NOT NULL, -- 0 = Sunday, 1 = Monday, ..., 6 = Saturday
    open TIME NOT NULL,
    close TIME NOT NULL,
    is_closed BOOLEAN DEFAULT FALSE
);

modified_hours

CREATE TABLE modified_hours (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date DATE NOT NULL,
    open TIME,
    close TIME,
    is_closed BOOLEAN DEFAULT FALSE,
    nps INT,
    reason VARCHAR(255)
);

sessions

CREATE TABLE sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date DATE NOT NULL,
    open TIME NOT NULL,
    close TIME NOT NULL,
    is_closed BOOLEAN DEFAULT FALSE,
    reason VARCHAR(255),
    nps INT,
    header VARCHAR(255)
);

days

CREATE TABLE days (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date DATE NOT NULL UNIQUE
);

Logic for Handling Office Hours

1. Default Office Hours: Use the office_hours table to get the default hours for each day of the week.
2. Modified Hours: Check the modified_hours table for any modifications on a specific date. If a record exists, it overrides the default office hours.
3. Sessions: Use the sessions table to store session details. If there are no sessions for a date and no modified hours, fall back to the default office hours.

Example Query to Get Office Hours for a Specific Date

SELECT 
    COALESCE(mh.open, oh.open) AS open,
    COALESCE(mh.close, oh.close) AS close,
    COALESCE(mh.is_closed, oh.is_closed) AS is_closed
FROM 
    days d
LEFT JOIN 
    modified_hours mh ON d.date = mh.date
LEFT JOIN 
    office_hours oh ON DAYOFWEEK(d.date) - 1 = oh.day_of_week
WHERE 
    d.date = '2023-10-10';

Example Query to Get Sessions for a Specific Date

SELECT 
    s.open,
    s.close,
    s.is_closed,
    s.reason,
    s.nps,
    s.header
FROM 
    sessions s
WHERE 
    s.date = '2023-10-10';

Using Cron and Model Events

1. Cron Job: Set up a cron job to run daily and check for any modifications or sessions for the upcoming days. This job can update the days table with the relevant office hours or session details.
2. Model Events: Use model events (e.g., created, updated, deleted) to handle changes in the modified_hours and sessions tables. When a record is added, updated, or deleted, update the corresponding entry in the days table.

Example Laravel Model Event

class ModifiedHour extends Model
{
    protected static function booted()
    {
        static::saved(function ($modifiedHour) {
            // Update the corresponding entry in the days table
            Day::updateOrCreate(
                ['date' => $modifiedHour->date],
                [
                    'open' => $modifiedHour->open,
                    'close' => $modifiedHour->close,
                    'is_closed' => $modifiedHour->is_closed
                ]
            );
        });

        static::deleted(function ($modifiedHour) {
            // Handle deletion logic
            $day = Day::where('date', $modifiedHour->date)->first();
            if ($day) {
                $day->delete();
            }
        });
    }
}

This structure and approach should simplify your database schema and make it easier to manage office hours, modified hours, and sessions.