77 lines
2.8 KiB
SQL
77 lines
2.8 KiB
SQL
-- Migration 008: Create services and schedules tables
|
|
-- Schedule: defines when services occur at a location (exactly ONE active per location, non-overlapping dates)
|
|
-- Service: individual work occurrence generated from schedule
|
|
|
|
-- Schedule for recurring services at an account address
|
|
CREATE TABLE schedules (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
account_address_id UUID REFERENCES account_addresses(id) ON DELETE CASCADE,
|
|
|
|
name VARCHAR(255),
|
|
|
|
-- Day-of-week flags
|
|
monday_service BOOLEAN NOT NULL DEFAULT FALSE,
|
|
tuesday_service BOOLEAN NOT NULL DEFAULT FALSE,
|
|
wednesday_service BOOLEAN NOT NULL DEFAULT FALSE,
|
|
thursday_service BOOLEAN NOT NULL DEFAULT FALSE,
|
|
friday_service BOOLEAN NOT NULL DEFAULT FALSE,
|
|
saturday_service BOOLEAN NOT NULL DEFAULT FALSE,
|
|
sunday_service BOOLEAN NOT NULL DEFAULT FALSE,
|
|
weekend_service BOOLEAN NOT NULL DEFAULT FALSE,
|
|
|
|
-- Schedule exceptions (free text)
|
|
schedule_exception TEXT,
|
|
|
|
-- Validity period (non-overlapping per location)
|
|
start_date DATE NOT NULL,
|
|
end_date DATE
|
|
);
|
|
|
|
-- Constraint: non-overlapping schedules per account address
|
|
ALTER TABLE schedules ADD CONSTRAINT schedules_no_overlap
|
|
EXCLUDE USING gist (
|
|
account_address_id WITH =,
|
|
daterange(start_date, COALESCE(end_date, '9999-12-31'::DATE), '[]') WITH &&
|
|
);
|
|
|
|
-- Individual service occurrence
|
|
CREATE TABLE services (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
account_id UUID REFERENCES accounts(id) ON DELETE SET NULL,
|
|
account_address_id UUID REFERENCES account_addresses(id) ON DELETE SET NULL,
|
|
|
|
date DATE NOT NULL,
|
|
status work_status NOT NULL DEFAULT 'SCHEDULED',
|
|
|
|
notes TEXT,
|
|
|
|
-- Google Calendar integration
|
|
calendar_event_id VARCHAR(255)
|
|
);
|
|
|
|
-- M2M: Service to TeamProfile (assigned team members)
|
|
CREATE TABLE service_team_members (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
service_id UUID NOT NULL REFERENCES services(id) ON DELETE CASCADE,
|
|
team_profile_id UUID NOT NULL REFERENCES team_profiles(id) ON DELETE CASCADE,
|
|
|
|
UNIQUE (service_id, team_profile_id)
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX idx_schedules_address ON schedules(account_address_id);
|
|
CREATE INDEX idx_services_account ON services(account_id);
|
|
CREATE INDEX idx_services_address ON services(account_address_id);
|
|
CREATE INDEX idx_services_date ON services(date);
|
|
CREATE INDEX idx_services_status ON services(status);
|
|
CREATE INDEX idx_service_team_members_service ON service_team_members(service_id);
|
|
CREATE INDEX idx_service_team_members_team ON service_team_members(team_profile_id);
|