131 lines
5.6 KiB
SQL
131 lines
5.6 KiB
SQL
-- ==============================================================
|
||
-- SQLite schema for the scheduling system
|
||
-- * round‑robin queue is driven by Members.LastAcceptedAt
|
||
-- * one‑day cool‑off is driven by Members.LastDeclinedAt (DATE)
|
||
-- * reservations are stored in Schedules (Status = 'pending' | 'accepted' | 'declined')
|
||
-- * audit tables: AcceptedLog, DeclineLog, ScheduledLog
|
||
-- ==============================================================
|
||
|
||
PRAGMA foreign_keys = ON;
|
||
|
||
-- -----------------------------------------------------------------
|
||
-- 1. Core lookup tables
|
||
-- -----------------------------------------------------------------
|
||
CREATE TABLE Classifications (
|
||
ClassificationId INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
ClassificationName TEXT NOT NULL UNIQUE
|
||
);
|
||
|
||
CREATE TABLE ServiceTypes (
|
||
ServiceTypeId INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
TypeName TEXT NOT NULL UNIQUE
|
||
);
|
||
|
||
-- -----------------------------------------------------------------
|
||
-- 2. Members – stores round‑robin timestamps and cool‑off date
|
||
-- -----------------------------------------------------------------
|
||
CREATE TABLE Members (
|
||
MemberId INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
FirstName TEXT NOT NULL,
|
||
LastName TEXT NOT NULL,
|
||
Email TEXT UNIQUE,
|
||
PhoneNumber TEXT,
|
||
ClassificationId INTEGER,
|
||
Notes TEXT,
|
||
IsActive INTEGER DEFAULT 1 CHECK (IsActive IN (0,1)),
|
||
|
||
-- Queue‑related columns -------------------------------------------------
|
||
LastScheduledAt DATETIME, -- set when a position is SCHEDULED
|
||
LastAcceptedAt DATETIME, -- set when a position is ACCEPTED
|
||
LastDeclinedAt DATETIME, -- set the time a member last DECLINED
|
||
DeclineStreak INTEGER DEFAULT 0,
|
||
-------------------------------------------------------------------------
|
||
|
||
FOREIGN KEY (ClassificationId) REFERENCES Classifications(ClassificationId)
|
||
);
|
||
|
||
CREATE INDEX idx_members_queue
|
||
ON Members (ClassificationId, IsActive,
|
||
LastDeclinedAt, LastAcceptedAt, LastScheduledAt);
|
||
|
||
|
||
-- -----------------------------------------------------------------
|
||
-- 3. Services – a concrete service on a given date
|
||
-- -----------------------------------------------------------------
|
||
CREATE TABLE Services (
|
||
ServiceId INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
ServiceTypeId INTEGER NOT NULL,
|
||
ServiceDate DATE NOT NULL,
|
||
FOREIGN KEY (ServiceTypeId) REFERENCES ServiceTypes(ServiceTypeId)
|
||
);
|
||
|
||
-- -----------------------------------------------------------------
|
||
-- 4. ServiceAvailability – which members are eligible for which service types
|
||
-- -----------------------------------------------------------------
|
||
CREATE TABLE ServiceAvailability (
|
||
ServiceAvailabilityId INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
MemberId INTEGER NOT NULL,
|
||
ServiceTypeId INTEGER NOT NULL,
|
||
UNIQUE (MemberId, ServiceTypeId),
|
||
FOREIGN KEY (MemberId) REFERENCES Members(MemberId),
|
||
FOREIGN KEY (ServiceTypeId) REFERENCES ServiceTypes(ServiceTypeId)
|
||
);
|
||
|
||
-- -----------------------------------------------------------------
|
||
-- 5. Schedules – also acts as the reservation/lock table
|
||
-- -----------------------------------------------------------------
|
||
CREATE TABLE Schedules (
|
||
ScheduleId INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
ServiceId INTEGER NOT NULL,
|
||
MemberId INTEGER NOT NULL,
|
||
|
||
-- Reservation / status columns -----------------------------------------
|
||
Status TEXT NOT NULL CHECK (Status IN ('pending','accepted','declined')),
|
||
ScheduledAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, -- set when status -> 'pending'
|
||
AcceptedAt DATETIME, -- set when status -> 'accepted'
|
||
DeclinedAt DATETIME, -- set when status -> 'declined'
|
||
ExpiresAt DATETIME, -- pending rows expire after X minutes
|
||
DeclineReason TEXT,
|
||
-------------------------------------------------------------------------
|
||
|
||
UNIQUE (ServiceId, MemberId), -- one row per member per service
|
||
FOREIGN KEY (MemberId) REFERENCES Members(MemberId),
|
||
FOREIGN KEY (ServiceId) REFERENCES Services(ServiceId)
|
||
);
|
||
|
||
CREATE INDEX idx_sched_member_service_status
|
||
ON Schedules(MemberId, ServiceId, Status);
|
||
|
||
-- -----------------------------------------------------------------
|
||
-- 6. Audit tables
|
||
-- -----------------------------------------------------------------
|
||
CREATE TABLE AcceptedLog (
|
||
LogId INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
MemberId INTEGER NOT NULL,
|
||
ServiceId INTEGER NOT NULL,
|
||
AcceptedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (MemberId) REFERENCES Members(MemberId),
|
||
FOREIGN KEY (ServiceId) REFERENCES Services(ServiceId)
|
||
);
|
||
|
||
CREATE TABLE DeclineLog (
|
||
DeclineId INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
MemberId INTEGER NOT NULL,
|
||
ServiceId INTEGER NOT NULL,
|
||
DeclinedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
DeclineDate DATE NOT NULL, -- the service day that was declined
|
||
Reason TEXT,
|
||
FOREIGN KEY (MemberId) REFERENCES Members(MemberId),
|
||
FOREIGN KEY (ServiceId) REFERENCES Services(ServiceId)
|
||
);
|
||
|
||
CREATE TABLE ScheduledLog (
|
||
LogId INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
MemberId INTEGER NOT NULL,
|
||
ServiceId INTEGER NOT NULL,
|
||
ScheduledAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
ExpiresAt DATETIME,
|
||
FOREIGN KEY (MemberId) REFERENCES Members(MemberId),
|
||
FOREIGN KEY (ServiceId) REFERENCES Services(ServiceId)
|
||
);
|