CREATE TABLE wo_schedule_events (
 id bigserial PRIMARY KEY, tenant_id bigint NOT NULL, company_id bigint NOT NULL, unit_id bigint NOT NULL,
 booking_key uuid NOT NULL, version integer NOT NULL CHECK(version>0), previous_id bigint UNIQUE REFERENCES wo_schedule_events(id),
 order_id bigint NOT NULL REFERENCES wo_orders(id), equipment_id bigint NOT NULL REFERENCES wo_equipment(id), technician_id bigint NOT NULL REFERENCES users(id),
 starts_at timestamptz NOT NULL, ends_at timestamptz NOT NULL, state text NOT NULL CHECK(state IN('scheduled','cancelled')),
 reason text NOT NULL CHECK(length(reason)>=10), actor_id bigint NOT NULL REFERENCES users(id), command_key uuid NOT NULL,
 request_hash char(64) NOT NULL, source_revision char(64) NOT NULL, snapshot jsonb NOT NULL, created_at timestamptz NOT NULL DEFAULT now(),
 CHECK(ends_at>starts_at AND ends_at<=starts_at+interval '7 days'),
 UNIQUE(tenant_id,company_id,unit_id,booking_key,version), UNIQUE(tenant_id,company_id,unit_id,actor_id,command_key),
 FOREIGN KEY(tenant_id,company_id,unit_id) REFERENCES units(tenant_id,company_id,id)
);
CREATE FUNCTION sgf_schedule_guard() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE o wo_orders; p wo_schedule_events;
BEGIN
 PERFORM sgf_wo_environment();
 IF TG_OP<>'INSERT' THEN RAISE EXCEPTION 'SGF_SCHEDULE_APPEND_ONLY'; END IF;
 PERFORM 1 FROM companies WHERE id=NEW.company_id FOR UPDATE;
 SELECT * INTO STRICT o FROM wo_orders WHERE id=NEW.order_id;
 IF ROW(o.tenant_id,o.company_id,o.unit_id,o.equipment_id) IS DISTINCT FROM ROW(NEW.tenant_id,NEW.company_id,NEW.unit_id,NEW.equipment_id)
 OR NOT EXISTS(SELECT 1 FROM permission_grants WHERE user_id=NEW.actor_id AND tenant_id=NEW.tenant_id AND company_id=NEW.company_id AND unit_id=NEW.unit_id AND permission='workshop.assign')
 OR (NEW.state='scheduled' AND (NOT EXISTS(SELECT 1 FROM permission_grants WHERE user_id=NEW.technician_id AND tenant_id=NEW.tenant_id AND company_id=NEW.company_id AND unit_id=NEW.unit_id AND permission='workshop.record') OR NEW.technician_id IS DISTINCT FROM (SELECT technician_id FROM wo_entries WHERE order_id=NEW.order_id AND kind='assignment' ORDER BY id DESC LIMIT 1)))
 THEN RAISE EXCEPTION 'SGF_SCHEDULE_SCOPE_OR_AUTHORITY'; END IF;
 SELECT * INTO p FROM wo_schedule_events WHERE (tenant_id,company_id,unit_id,booking_key)=(NEW.tenant_id,NEW.company_id,NEW.unit_id,NEW.booking_key) ORDER BY version DESC LIMIT 1;
 IF (p.id IS NULL AND (NEW.version<>1 OR NEW.previous_id IS NOT NULL OR NEW.state<>'scheduled')) OR
 (p.id IS NOT NULL AND (NEW.previous_id IS DISTINCT FROM p.id OR NEW.version<>p.version+1 OR NEW.order_id<>p.order_id OR p.state='cancelled')) THEN RAISE EXCEPTION 'SGF_SCHEDULE_STALE_VERSION'; END IF;
 IF NEW.state='scheduled' AND EXISTS(SELECT 1 FROM wo_schedule_events e WHERE e.tenant_id=NEW.tenant_id AND e.company_id=NEW.company_id AND e.unit_id=NEW.unit_id AND e.booking_key<>NEW.booking_key AND e.state='scheduled'
 AND NOT EXISTS(SELECT 1 FROM wo_schedule_events n WHERE n.previous_id=e.id)
 AND (e.technician_id=NEW.technician_id OR e.equipment_id=NEW.equipment_id)
 AND e.starts_at<NEW.ends_at AND e.ends_at>NEW.starts_at) THEN RAISE EXCEPTION 'SGF_SCHEDULE_RESOURCE_CONFLICT' USING ERRCODE='23514'; END IF;
 RETURN NEW;
END; $$;
CREATE TRIGGER sgf_schedule_guard BEFORE INSERT OR UPDATE OR DELETE ON wo_schedule_events FOR EACH ROW EXECUTE FUNCTION sgf_schedule_guard();