"""Service Scheduling Phase 2 (INDL-34) Reshapes the scheduling data layer: 1. ALTER services — add 6 new columns (decedent_name, family_contact_email, event_note, notify_family_confirmation, notify_family_reminder_24h, reminder_job_id); backfill status to the allowed set; add CHECK constraint; drop 5 obsolete columns. 2. DROP service_staff_assignments (replaced by service_crew_assignments). 3. CREATE service_crew_assignments — services -> crew_members (INDL-33). 4. CREATE scheduling_run_sheet_items — replaces run_sheet_milestones JSONB. 5. CREATE scheduling_documents — S3-backed docs, soft-delete. 6. CREATE scheduling_activity_log — append-only audit trail. Chains off 0060 (develop head after merging in the FAQ/CMS and who-we-serve-categories migrations 0057-0060). Originally written against 0056 (crew_members); renumbered when develop was merged into this branch. Idempotent (IF [NOT] EXISTS) so it is safe to re-run. Fully reversible via downgrade(). """ from alembic import op import sqlalchemy as sa revision = "0061" down_revision = "0060" branch_labels = None depends_on = None def upgrade() -> None: conn = op.get_bind() conn.execute(sa.text( """ ALTER TABLE services ADD COLUMN IF NOT EXISTS decedent_name VARCHAR(255) NULL, ADD COLUMN IF NOT EXISTS family_contact_email VARCHAR(255) NULL, ADD COLUMN IF NOT EXISTS event_note TEXT NULL, ADD COLUMN IF NOT EXISTS plot_location VARCHAR(255) NULL, ADD COLUMN IF NOT EXISTS section VARCHAR(255) NULL, ADD COLUMN IF NOT EXISTS notify_family_confirmation BOOLEAN NOT NULL DEFAULT FALSE, ADD COLUMN IF NOT EXISTS notify_family_reminder_24h BOOLEAN NOT NULL DEFAULT FALSE, ADD COLUMN IF NOT EXISTS reminder_job_id VARCHAR(255) NULL """ )) conn.execute(sa.text( """ UPDATE services SET status = 'awaiting_family_confirm' WHERE status NOT IN ('draft', 'awaiting_family_confirm', 'confirmed') """ )) conn.execute(sa.text( "ALTER TABLE services DROP CONSTRAINT IF EXISTS chk_services_status" )) conn.execute(sa.text( """ ALTER TABLE services ADD CONSTRAINT chk_services_status CHECK (status IN ('draft', 'awaiting_family_confirm', 'confirmed')) """ )) conn.execute(sa.text( """ ALTER TABLE services DROP COLUMN IF EXISTS email_family, DROP COLUMN IF EXISTS add_to_briefing, DROP COLUMN IF EXISTS reminder_24h, DROP COLUMN IF EXISTS reminder_sent, DROP COLUMN IF EXISTS run_sheet_milestones """ )) conn.execute(sa.text("DROP TABLE IF EXISTS service_staff_assignments")) conn.execute(sa.text( """ CREATE TABLE IF NOT EXISTS service_crew_assignments ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE, service_id UUID NOT NULL REFERENCES services(id) ON DELETE CASCADE, crew_member_id UUID NOT NULL REFERENCES crew_members(id) ON DELETE CASCADE, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now() ) """ )) conn.execute(sa.text( "CREATE UNIQUE INDEX IF NOT EXISTS uq_service_crew " "ON service_crew_assignments (service_id, crew_member_id)" )) conn.execute(sa.text( "CREATE INDEX IF NOT EXISTS idx_service_crew_tenant " "ON service_crew_assignments (tenant_id, service_id)" )) conn.execute(sa.text( """ CREATE TABLE IF NOT EXISTS scheduling_run_sheet_items ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE, service_id UUID NOT NULL REFERENCES services(id) ON DELETE CASCADE, sort_order INTEGER NOT NULL DEFAULT 0, time_label VARCHAR(20) NULL, details TEXT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now() ) """ )) conn.execute(sa.text( "CREATE INDEX IF NOT EXISTS idx_run_sheet_service " "ON scheduling_run_sheet_items (service_id, sort_order)" )) conn.execute(sa.text( """ CREATE TABLE IF NOT EXISTS scheduling_documents ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE, service_id UUID NOT NULL REFERENCES services(id) ON DELETE CASCADE, file_name VARCHAR(255) NOT NULL, s3_key VARCHAR(500) NOT NULL, file_size INTEGER NOT NULL, mime_type VARCHAR(100) NOT NULL, uploaded_by UUID NOT NULL REFERENCES users(id), created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), deleted_at TIMESTAMPTZ NULL ) """ )) conn.execute(sa.text( "CREATE INDEX IF NOT EXISTS idx_scheduling_docs_service " "ON scheduling_documents (service_id, deleted_at)" )) conn.execute(sa.text( """ CREATE TABLE IF NOT EXISTS scheduling_activity_log ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE, service_id UUID NOT NULL REFERENCES services(id) ON DELETE CASCADE, actor_id UUID NOT NULL REFERENCES users(id), event_type VARCHAR(50) NOT NULL, from_status VARCHAR(30) NULL, to_status VARCHAR(30) NULL, description TEXT NOT NULL, metadata JSONB NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now() ) """ )) conn.execute(sa.text( "CREATE INDEX IF NOT EXISTS idx_scheduling_activity_service " "ON scheduling_activity_log (service_id, created_at DESC)" )) def downgrade() -> None: conn = op.get_bind() conn.execute(sa.text("DROP TABLE IF EXISTS scheduling_activity_log")) conn.execute(sa.text("DROP TABLE IF EXISTS scheduling_documents")) conn.execute(sa.text("DROP TABLE IF EXISTS scheduling_run_sheet_items")) conn.execute(sa.text("DROP TABLE IF EXISTS service_crew_assignments")) conn.execute(sa.text( """ CREATE TABLE IF NOT EXISTS service_staff_assignments ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE, service_id UUID NOT NULL REFERENCES services(id) ON DELETE CASCADE, user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, role VARCHAR(50) NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now() ) """ )) conn.execute(sa.text( "CREATE INDEX IF NOT EXISTS ix_service_staff_service_id " "ON service_staff_assignments (service_id)" )) conn.execute(sa.text( "CREATE INDEX IF NOT EXISTS ix_service_staff_tenant_id " "ON service_staff_assignments (tenant_id)" )) conn.execute(sa.text( """ ALTER TABLE services ADD COLUMN IF NOT EXISTS email_family BOOLEAN NOT NULL DEFAULT FALSE, ADD COLUMN IF NOT EXISTS add_to_briefing BOOLEAN NOT NULL DEFAULT FALSE, ADD COLUMN IF NOT EXISTS reminder_24h BOOLEAN NOT NULL DEFAULT FALSE, ADD COLUMN IF NOT EXISTS reminder_sent BOOLEAN NOT NULL DEFAULT FALSE, ADD COLUMN IF NOT EXISTS run_sheet_milestones JSONB NULL """ )) conn.execute(sa.text( "ALTER TABLE services DROP CONSTRAINT IF EXISTS chk_services_status" )) conn.execute(sa.text( """ ALTER TABLE services DROP COLUMN IF EXISTS reminder_job_id, DROP COLUMN IF EXISTS notify_family_reminder_24h, DROP COLUMN IF EXISTS notify_family_confirmation, DROP COLUMN IF EXISTS section, DROP COLUMN IF EXISTS plot_location, DROP COLUMN IF EXISTS event_note, DROP COLUMN IF EXISTS family_contact_email, DROP COLUMN IF EXISTS decedent_name """ ))